May 12, 2010 at 9:10 am
I have a question for those who might know the answer.
Here's the query in question - server.mydb.dbo.shipments is a linked SQL 2005 server.
select part,date,count(*) as qty
from
(
select distinct part,date
from server.mydb.dbo.shipments
) mytest
group by part,date
having count(part) <> 1
order by part
Running alone
select distinct part,date
from server.mydb.dbo.shipments
takes 21 seconds to run. However, running the entire query comes back instantly. If we change the second to last line to "having count(part) = 1" it returns the right information but in about 16 secs.
As if that isn't strange enough, I then used SSMS to connect directly to the linked server. I ran the inner query and it took 18 secs. I ran the outer query and it took 19 secs.
Does Query Analyzer keep the results cached somewhere? Or is it the linked server between 2000 and 2005 that is caching something? Maybe this is just some kind of bug, but it seems that if the inner query takes 18 secs, the outer should take at least that long.
Thanks in advance for any insight into this issue. My colleague and I are both stumped on this one.
May 12, 2010 at 10:42 am
have a look at the actual execution plans of the different queries, this should show what is causing the difference.
May 12, 2010 at 11:17 am
@steveb-2 - Yeah, that was the first thing I thought of. In SSMS they plans looks very similar, and the 99% cost is in Table Scan in both cases.
My question may have been lost in the details (or the number of questions that I asked ) - Why does Query Analyzer in SQL 2000 come back instantly while SSMS in SQL 2005 take the expected amount of time?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply