A coworker asked me to look at indexing on some tables because his query was running very long. Over an hour.
select count(1)
from databaseA.dbo.table1
inner join databaseA.dbo.table2 on (table1.key = table2.key)
inner join databaseB.dbo.table3 on (table1.key = table3.key)
Note the different databases. This was being run from DatabaseB
Tables 1 and 2 were over 2 million records long. Table3 had a dozen records or so.
I looked at the query plan and the optimizer decided to do nested-loop index seeks into tables 1 and 2 with Table3 as the driving table!
My first assumption was that statistics were seriously messed up on Tables1 & 2 but before updating statistics I tried adding a join hint thusly:
select count(1)
from databaseA.dbo.table1
inner HASH join databaseA.dbo.table2 on (table1.key = table2.key)
inner join databaseB.dbo.table3 on (table1.key = table3.key)
Results returned in 15 seconds.
Since I was short on time, I passed the results back to him but I'm worried that this might result in problems down the road.
Should I revisit the statistics issue and resolve the problem that way? Could the bad query plan have resulted from the join being from a separate databases?
Can anyone offer me some ideas based on your experience?
I would suspect the statistics first.
As you are no doubt aware, Join hints should be avoided in 99% of cases and used only when you have proof that they are absolutely required.