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.

  • 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 Jan 15 '11 at 5:39
  • I was in similar situation but I figured that adding ORDER BY clause increased the plan cost and it then triggered(after reaching the parallelism threshold cost) the SQL Server to use parallelism hence faster. Aug 5 '15 at 19:17

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.

  • 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. 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. Jan 15 '11 at 13:32
  • Yah, I got that. Thanks for clarifying though. I can't look at it until Monday. 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. 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. Jan 17 '11 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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