SQL Server Performance Forum – Threads Archive

Joining tables in different databases performance

Hi,
we are designing a local document management for our company which would have multiple Repositories (each of them would be a separate mdf file). There would be a mdf file for settings,users,permissions and other general topics(call it General). We predict that in a 3 to 5 year range the records in the Users table would reach to 2,000 and the Permission table could reach to 2,000,000 records. For permission checking there would be many joining queries between Permissions and Users in General database (mdf) to Files table in Repositories in a separate database (mdf). I want to know how is the performance of joining tables in different databases at this size. If there is a performance issue isn’t it better to handle all of them in a single database (mdf) and handle repositories with multiple tables creation in the same database? Thanks for your patience. Saman Zakerzadeh

As long as it is on the same server, I don’t think there will be any performance issue for cross db joins…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for reply Another question in this case.
Is there any improvement in cross db join performance (or total sql server performance) if I put General mdf file in one hard disk and Repositories mdf(s) in another hard disk? Is this reduces access time and so boosting performance? Thanks,
Saman Zakerzadeh

Why don’t you take help of indexed views in this case using correct edition of SQL Server, if you perform regular joins between two or more tables in your queries, performance will be optimized if each of the joined columns have their own indexes. Based on disk configuration & your database query optimization it will have a benefit. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks for your reply As I mentioned one of those tables is Permission which has many columns that are all important and managing indexes on them are messy. but any way I would like to know the
direct answer to my question about the effect of putting mdfs in deferent hard disk on
cross db join performance . Thanks

Theoritically it will have an advantage on having data files on different disks provided they are RAID and properly configured the READ/WRITE with MEMORY configuration on SQL Server. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Check the following…
http://www.databasejournal.com/features/mssql/article.php/3114651
http://www.sql-server-performance.com/jc_system_storage_configuration.asp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>