Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2.

If I make a select query joining both tables, it's really slow to get the results.

A simple query like

SELECT * 
FROM db1.dbo.table1 t1 
LEFT JOIN db2.dbo.table2 t2 ON t1.k1 = t2.k2

is REALLY slow sometimes.

I not sure if this is common to SQL Server and "have to be like a rule" to "not join two tables that are from different databases".

In that case... I add to this question that I have one of those db binary data stored on a field and I like to separate from main db to not increase main table size... it is better to partition a db for this?

I tested with two simple tables and still joining those two tables is slow.

Thanks in advance for any help.

share|improve this question
    
Do you have indexes on the JOIN fields? –  Mihai Aug 30 at 15:34
4  
If the two tables are on different databases but on the same instance it shouldn't affect performance significantly. There's just an overhead to ckeck permissions across databases. The main reasons for performance issues usually are missing indexes and/or locking/blocking. –  Lmu92 Aug 30 at 15:36
1  
Can you explain why you need 2 databases and not have all the data in one? –  ypercube Aug 30 at 16:09
1  
can you post your actual execution plan? –  Lmu92 Aug 30 at 17:04
1  
@FabianSilva: That's no explanation at all. Please provide a better description of he business requirements that necessitate separate databases. –  Pieter Geerkens Aug 31 at 12:04

1 Answer 1

You've got a bunch of different questions in here, so let's break 'em out individually.

Q: If I join two tables in the same database with the above query, why is it slow?

A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a WHERE clause to just get the data you need.

Once you've done that, note that you're using a LEFT OUTER JOIN. This tells SQL Server, "Not all of the table1 records will have matching records in table2." That's totally fine if it's true - but if you know all t1 records will have at least one t2 record, use an INNER JOIN instead.

Next, indexing starts to come into play - depending on the width of the tables and the numbers of fields, you may want to add indexes on the fields you're using for the join. To get good advice on that, it's best to post the actual execution plan you're working with.

Q: If I the tables are in different databases on the same SQL Server, does that change anything?

A: No. There's some interesting gotchas around things like default isolation levels in different databases, but for the most part, your queries should produce the same execution plans and speeds.

Q: Should I use table partitioning to make this go faster?

A: You mentioned database partitioning, but there's no such thing in SQL Server - I'm guessing you meant table partitioning. Generally speaking, no, I wouldn't jump to database design changes in order to make a join go faster. Start with the basics - understanding SQL Server execution plans - and only make database design changes to solve problems that you can't fix with things like indexes.

share|improve this answer
9  
Also, especially if you don't need all of the columns (and at least the join column is redundant), stop using SELECT *. –  Aaron Bertrand Aug 30 at 17:32
    
thanks @AaronBertrand I previously have a "Select * " on a view and when altered one of the tables that use the view the "select *" has retrieved erroneous table columnns and have to alter table and save it again to make it to work. in general select * is really a monster that not have to exist I only put "select * " as a generic example of join two tables on two dbs.. not more than that. –  FabianSilva Sep 1 at 13:15
    
@FabianSilva Yes, I talk about exactly that scenario in the post I linked. –  Aaron Bertrand Sep 1 at 13:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.