adding an order by to this query returns faster than without, why?
The most obvious cause would be that (select max(ID) from tblR where cID = c.cID and ISNULL(aField,'') &lt;&gt; '') 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.