I asked this question before but never got a fully satisfactory answer.
Take a moderately complicated view — subqueries, joins, etc. A select * from view takes a minute or two …. a select top 500 * from view takes …. 30+ minutes.
There is absolutely no logical reason why this is needed. Really, the query optimizer, in this case, should be doing the “select all” function, then stopping after 500 results. Period. It should be shorter than select (all) * query … period. The optimizer is failing.
The reason why the top 500 * is taking longer is because extra work or functions are being done in places where they shouldn’t be occurring. I can’t say exactly what, but obviously, SOMETHING is, to account for the extra time.
My feeling is than an implicit “sort” or “order by” function is occurring in the process, and not at the end level (a sort would be trivial) but during full table scans early in the process. After all, a top N * …. order by … DOES sort the table as you are taking the top 10 largest/ smallest values by X.
I have a feeling a top N without an order by still implicitly sorts by some parameter … say, order the rows were inserted in the table. At the base tables.
I guess my question is —- is there a way to force a Top 500 * query to simply stop overthinking and sorting, and just return “Top all” and stop after 500 rows are returned?
