Well made a new interesting discovery today. Combining TOP with ORDER BY can severely slow down a query.
I was looking into a slow query which was taking around 10 seconds to return 69 results. It was getting the "TOP 100" from a specific date range and then ordering by ID descending. It was fascinating to me to discover that removing the TOP 100 returned the results in 0.005 seconds. Removing the ORDER BY instead of the TOP 100 also resulted in again producing the results in 0.005 seconds.
So the moral of the story is don't use TOP and ORDER BY if you want your results back quickly.
I have no idea why this kills performance right now, but it is perhaps even more interesting that even though the query TOP 100 ORDER BY ID ASC results in the same set of results as TOP 100 without an ORDER BY command, it takes 8.8 seconds rather than 0.005 seconds. There are definitely some huge optimisations available in respect to these queries.
Well after reporting this to Intersystems I was directed to %NOTOPOPT query optimisation method. It states that without this TOP is optimised for first row retrieval rather than the full result set. This implies to me that a TOP 1 should be faster without %NOTOPOPT and that returning more than 1 has the chance to be faster with it. However, when I was testing across various queries whether it was TOP 1 or TOP 100 with an ORDER BY I always retrieved the results much faster with %NOTOPOPT. If you have a TOP query running slowly I recommend testing with this optimisation option, as you may see much better performance as I did.