Recently I came across a situation where a view took an excessive amount of time to return results when “SELECT TOP …” was applied to it. Normally the view took less than a second to run but when TOP was used, it took over an hour!
After consulting Google, I found the following really useful blog:
http://geekswithblogs.net/Martinez/archive/2013/01/30/why-sql-top-may-slow-down-your-query-and-how.aspx
As explained in the blog, the original SELECT used Hash Matches, whereas SELECT TOP used Nested Loops which are much less efficient with large sets. When I dug into the execution plans for my queries, I could see the exact same thing was happening with my view. The solution was to identify the joins in the query that were being changed to Nested Loops when SELECT TOP was used & force them to use Hash Matches, i.e.:
SELECT ...
FROM table1
INNER HASH JOIN table2
ON ...And that solved it. My view now takes less than a second to return results regardless of whether I return the whole set or use TOP to limit it.
Many thanks to Marcin Kasprzykowski from Geeks With Blogs 🙂
Be First to Comment