Por que "select *" é mais rápido que "select top 500 *" no SQL Server? [duplicado]

Adicionar uma TOPcláusula a uma consulta introduz um objetivo de linha à consulta. O otimizador de consulta tentará usar o fato de não precisar retornar todas as linhas para criar um plano de consulta mais eficiente. A meta de linha pode fazer com que o custo de alguns operadores seja reduzido. A otimização de meta de linha pode funcionar contra o sintonizador de consulta devido a limitações de modelo ou informações incompletas em objetos de estatísticas. Abaixo, tenho uma demonstração em relação a uma visualização simples para a qual a adição TOP 500degrada o desempenho.

Primeiro insira apenas números inteiros ímpares em uma tabela. Observe que estou reunindo estatísticas completas no final.

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;

Em seguida, insira apenas números inteiros pares em uma tabela diferente. Estou fazendo algumas coisas com valores repetidos e tamanho de linha para fazer a demonstração funcionar. Eu ainda atualizo as estatísticas na íntegra no final.

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;

Aqui está a definição de visualização:

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
);

Considere a seguinte consulta:

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

Veja como é o plano de consulta:

Uma limitação de custo faz com que a varredura completa da EVENtabela no lado interno da junção de loop aninhado tenha um custo relativo baixo. Com base em como construí os dados, sabemos que o otimizador precisará verificar 500 * 100.000 = 50 milhões de linhas da EVENtabela para retornar as primeiras 500 linhas ao cliente. Isso é realmente o que acontece, e a consulta leva cerca de 16 segundos para ser executada na minha máquina:

A remoção da TOPcláusula da consulta fornece um plano diferente e mais eficiente :

Esta consulta é executada em menos de meio segundo na minha máquina. Apenas 100.000 linhas são lidas da EVENtabela.

Para o SQL Server 2016 e versões posteriores, você pode contornar esse problema sem alterar a definição da exibição adicionando OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))à consulta. Essa dica desativa a otimização da meta de linha no nível da consulta. Para o SQL Server 2012, você pode usar o sinalizador de rastreamento 4138 no nível de consulta via OPTION (QUERYTRACEON 4138), mas isso requer SA.

Não posso dizer nada sobre sua consulta em particular sem ver os planos de consulta, mas espero que este exemplo ilustre o ponto geral.