Query speeds much different, don't seem right

  • 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.

  • have a look at the actual execution plans of the different queries, this should show what is causing the difference.

  • @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