6 added [query-performance] to 2412 questions - Shog9 (Id=1924)
| link
5 duplicates list edited from 'SELECT TOP' performance question to How (and why) does TOP impact an execution plan?, 'SELECT TOP' performance question
| link
4 added 494 characters in body
source | link

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.

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.

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.

    Post Closed as "Duplicate of…" by Erik Darling, Tom V - try topanswers.xyz, mustaccio, LowlyDBA, McNets of
3 added version from comment
source | link
2 formatting, title
source | link
1
source | link