Join tables in two databases make query slow? is better to partition db?

Question

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)

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.