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.
Best Answer
The most obvious cause would be that
(select max(ID) from tblR where cID = c.cID and ISNULL(aField,'') <> '')
sub-query in theWHERE
clause in combination with theTOP 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
c
s should bex
s or vice-versa, otherwise the query would not run as the aliasc
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.