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)