Why is "select *" faster than "select top 500 *" in SQL Server? [duplicate]

Question

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.

Answer 1

Adding a TOP clause to a query introduces a row goal to the query. The query optimizer will attempt to use the fact that it doesn't need to return all rows to create a more efficient query plan. The row goal can cause the cost of some operators to be scaled down. The row goal optimization can work against the favor of the query tuner due to model limitations or incomplete information in statistics objects. Below I have a demo against a simple view for which adding TOP 500 degrades performance.

First insert only odd integers into a table. Note that I'm gathering statistics in full at the end.

DROP TABLE IF EXISTS dbo.ODD;

CREATE TABLE dbo.ODD (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(10)
);

INSERT INTO dbo.ODD WITH (TABLOCK)
SELECT TOP (100000)
  -1 + 2 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('FLUFF', 2)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE STATISTICS S ON dbo.ODD (ID) WITH FULLSCAN;

Then insert only even integers into a different table. I'm doing some things with repeated values and row size to make the demo work. I still update statistics in full at the end.

DROP TABLE IF EXISTS dbo.EVEN;

CREATE TABLE dbo.EVEN (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(3500)
);

INSERT INTO dbo.EVEN WITH (TABLOCK)
SELECT TOP (100000) 
  1000 * FLOOR ( ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 500)
, REPLICATE('FLUFF', 700)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

CREATE STATISTICS S ON dbo.EVEN (ID) WITH FULLSCAN;

Here's the view definition:

CREATE OR ALTER VIEW dbo.TRICKY_VIEW AS
SELECT o.ID
FROM dbo.ODD o
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.EVEN e WHERE o.ID = e.ID
);

Consider the following query:

SELECT TOP 500 *
FROM dbo.TRICKY_VIEW
OPTION (MAXDOP 1);

Here's what the query plan looks like:

A costing limitation causes the full scan of the EVEN table on the inner side of the nested loop join to have a low relative cost. Based on how I constructed the data we know that the optimizer will need to scan 500 * 100000 = 50 million rows from the EVEN table in order to return the first 500 rows to the client. This is indeed what happens, and the query takes about 16 seconds to execute on my machine:

Removing the TOP clause from the query gives a different and more efficient plan:

This query executes in less than half a second on my machine. Only 100000 rows are read from the EVEN table.

For SQL Server 2016 and later versions you can work around this issue without changing the view's definition by adding OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL')) to the query. That hint disables the row goal optimization at the query level. For SQL Server 2012 you can use trace flag 4138 at the query level via OPTION (QUERYTRACEON 4138), but that requires SA.

I can't say anything about your query in particular without seeing the query plans, but hopefully this example illustrates the general point.