homepage Welcome to WebmasterWorld Guest from 50.16.112.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
performance issue for JOIN between different
databases in MSSQL?
LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3921113 posted 7:02 pm on May 27, 2009 (gmt 0)

In MS SQL, we have 2 databases on the same server (same instance of SQL Server): DB1 (with table TableA), DB2 (with table TableB).

We currently do a JOIN across the databases, e.g.:
SELECT a.Field1, b.Field2
FROM TableA a INNER JOIN DB2.[dbo].TableB b ON a.ID=b.ID

Would there be any significant performance gain by moving TableB to DB1? And then we would just do:
SELECT a.Field1, b.Field2
FROM TableA a INNER JOIN TableB b ON a.ID=b.ID

 

da_pinky

5+ Year Member



 
Msg#: 3921113 posted 10:59 pm on May 27, 2009 (gmt 0)

i havent used ms sql since 2005, but it shouldnt make a big difference. depends on a lot of other things as well, like how many records do you have in each table, are they in cache etc.
i guess a.ID and b.ID already has an index on it?

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3921113 posted 11:23 pm on May 27, 2009 (gmt 0)

Yup- indexed. I finally copied the tables (the above example was simplified somewhat) in question over to the other database and ran some comparison tests.

No difference that I could see. Which is nice- now I don't have to go back and do a lot of recoding!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved