I have a view,
complicated_view -- there are a few joins and where clauses. Now,
select * from complicated_view (9000 records)
is faster, much faster, than
select top 500 * from complicated_view
We're talking 19 seconds vs. 5+ minutes.
The first query returns all 9000 records. How is only picking up the top 500 ridiculously longer?
Obviously, I'm going to look at the execution plans here ---- but once I figure out why SQL Server is running the "top 500" in suboptimal fashion, how do I actually tell it to run the plan the quick way, like taking the full table?
Of course, I may have to rewrite the view entirely --- but quite odd.
Basically, I'm connecting this data table to a 3rd party software that pre-checks tables with a default
select top 500 * query that cannot be modified. So other than dumping this view into an actual table (quite sloppy) - I can't get around their "top 500" addendum either.
This is SQL Server 2012.
EDIT: Disagree on the duplicate flag. The other question, the top was FASTER than all. This would be the EXPECTED behavior, returning less rows. My case is the opposite. Also, my understanding is that Top 100 is a different algorithm than Top 100+. I don't even think the duplicate question has the correct answer. Which is, the TOP X query will SORT potentially massive tables very early on, not AFTER they are aggregated/ filtered/ etc. The why is a mystery, but the how is plainly there.