Query Performance

Question

Query Performance

manickam

Valued Member

Points: 59
More actions
September 23, 2007 at 10:48 pm

#177935

Is there any difference in performance while joining tables between same database and tables from different database?
Say we have sql server SERV1.
And two databases DB1 and DB2.
DB1 has two tables tbl1 and tbl2.
DB2 has tow tables tbl3 and tbl3.
Query1: join tables tbl1 and tbl2
Query2: join tables tbl1 and tbl3
how do query performance differs..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

It is my understanding that other than the additional security verifications (which aren't too onerous) there would be no difference in the query performance. The optimizer will still be able to access all index/statistical information to develope the optimal query. Now, if the databases were on another SERVER it would be another matter entirely!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

I'll second that... due to a 3rd party bit of software :alien:, we have hundreds of "usage" databases on one server... they all talk fine with each other with no additional lag (although I've not tested for this specific metric).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

If you have cross-database ownership chaining on, you add some security overhead. If you are like most and have simply given access to all of the objects in both databases, you will see no performance difference.

I would suggest that if you are doing this a lot, you should look at the Synonyms in SQL 2005. In addition to eliminating 3 part name calls, it gives you a level of abstraction that allows your T-SQL to not care where the second database is. If you decide to rename it, move it to another server, etc. you will be able to manage it pretty easily.