July 6, 2006 at 8:57 am
I'm doing a simple query that joins across two databases, and I'm getting sporadic, slow performance. The columns being joined on are all indexed.
Is there some inherent penalty for doing queries across multiple databases?
select a.blah, b.moreblah, c.evenmoreblah
from db1.dbo.atable a
left join db2.dbo.btable b on b.api = a.api
left join db2.dbo.ctable c on c.id_parent = b.id
where a.id = 12345
If I do a query with just tables b and c, the result is instant. But when I do just the a and b part, that's slow.
Thoughts?
July 6, 2006 at 10:36 am
Is it slow the first time you run it, then OK after that? Or is it completely sporadic?
I was thinking that maybe you have the 'Auto Close' database option switched on, in which case the db has to be 'woken up' when it retrieves a new request & has been idle for a while.
If you've not got this turned on, it's something else altogether! I do cross-db (& cross SQL Server-db!) queries all the time though, haven't noticed a noticeable performance hit
July 6, 2006 at 1:37 pm
I don't notice issues and we do this all day, everyday.
Is the count(a.api)>1 and/or count(b.api)>1? If there are 'duplicates' you are having to generate more rows than are in either source table.
Michelle
July 7, 2006 at 3:35 pm
I think that's it. I did go back and realize that one of the tables has multiple rows (in some cases) for each API, and I wondered if that might be the cause.
Thanks for confirming that, and suggesting it even (in the event that I'd not realized)!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply