7

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.

..Update some years later... 24-09-18

be sure that the fields that you are joining are of same type, size and collation.

Example: some property is varchar(255) and another varchar(20) ... that can be a problem because the engine have to cast one type to another (an implicit conversion occurs) and while sometimes it runs faster... if a reindex or some change in the db occurs, you can see that in some moment the query start to took a lot more time to complete....

If you cannot change the field type to match in one of the db/tables, try doing an explicit cast to see if that improves the query speed. use cast(fieldname as type(size)) = fieldName2)

  • Do you have indexes on the JOIN fields? – Mihai Aug 30 '14 at 15:34
  • 5
    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 '14 at 15:36
  • 1
    Can you explain why you need 2 databases and not have all the data in one? – ypercubeᵀᴹ Aug 30 '14 at 16:09
  • 1
    can you post your actual execution plan? – Lmu92 Aug 30 '14 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 '14 at 12:04
21

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.

  • 10
    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 '14 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 '14 at 13:15
  • @FabianSilva Yes, I talk about exactly that scenario in the post I linked. – Aaron Bertrand Sep 1 '14 at 13:59
  • the 2nd answer was what I need to know, not sure why on THAT case was slow to join those dbs... but at least I can know that I was not doing something wrong joining tables on different db of same server. – FabianSilva May 26 '15 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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