Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen?

SELECT TOP (500) r.cID,r.aField,a.Description 
       FROM dbo.tblR r  
            inner join dbo.tblA a on r.aID = a.ID  
            left join dbo.tblX x on x.cID = r.cID 
       WHERE (ISNULL(x.anotherField,'') <> r.anotherField or x.field3 is null) 
            and (r.ID=(select max(ID) from tblR where cID = r.cID and
            ISNULL(aField,'') <> '')) 
            and r.cID in (select ID from tblC)
       ORDER BY r.cID ASC -- when I comment this line out it runs much slower

The execution plans are not helping out much.

share|improve this question
2  
What indexes and the like do you have on the tables? –  jcolebrand Jan 14 '11 at 22:55
1  
Where is the alias c defined? –  bernd_k Jan 14 '11 at 23:43
    
Sorry guys, I was changing my table names to something generic and accidently switched from r to c half way through. I've edited it so that it should be correct now. What was c really should have been r –  Beth - loud ninja - Whitezel Jan 15 '11 at 5:39

1 Answer 1

up vote 3 down vote accepted

The most obvious cause would be that (select max(ID) from tblR where cID = c.cID and ISNULL(aField,'') <> '') sub-query in the WHERE clause in combination with the TOP 500 is making the order make a difference.

In either case it will probably be running that sub-query individually for every row it might otherwise return until it has found 500 that match the whole WHERE (actually, it will be bright enough to check the other parts of the WHERE first, as they are much less expensive, but still it will be running the sub-query for a portion of the rows it meets). At a guess, more rows near the top when sorted by c.cID match currently so it has to run the sub-query less often before finding 500 matches than it does when running down the results in the order the query planner chooses by default.

(I'm assuming that the cs should be xs or vice-versa, otherwise the query would not run as the alias c is not defined anywhere)

It is worth running queries like that in the Management Studio with the "actual query plan" view turned on - then you might see from the diagram which parts are causing the differeing performance.

share|improve this answer
    
Thanks, the max does make sense. I did look at the actual query plan but it didn't help me figure that out on my own. I'll play around with it tomorrow and see if that was it. –  Beth - loud ninja - Whitezel Jan 15 '11 at 5:43
    
Sorry, I think I was a bit unclear. I was meaning to make sure you used the "show actual query plan" option while running the queries, not just the "display estimated execution plan" command. –  David Spillett Jan 15 '11 at 13:32
    
Yah, I got that. Thanks for clarifying though. I can't look at it until Monday. –  Beth - loud ninja - Whitezel Jan 16 '11 at 22:09
    
After looking at the plan again today it looks like the addition of the order by allows for additional "parallelism" nodes. It looks like you are right that adding the order by produces a better order for finding the 500. –  Beth - loud ninja - Whitezel Jan 17 '11 at 22:11
    
One additional interesting note on this is that the larger the top X number is the longer the return takes. Also, if there aren't enough rows that match the criteria it takes a very long time. This all supports your statement that finding the 500 that match faster is what is speeding it up. –  Beth - loud ninja - Whitezel Jan 17 '11 at 22:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.