Início / dba / Perguntas / 30210
Accepted
usr
Asked: 2012-12-12 11:39:24 +0800 CST

Por que existem diferenças no plano de execução entre OFFSET ... FETCH e o antigo esquema ROW_NUMBER?

  • 772

O novo OFFSET ... FETCHmodelo introduzido com o SQL Server 2012 oferece paginação simples e mais rápida. Por que existem diferenças considerando que as duas formas são semanticamente idênticas e muito comuns?

Alguém poderia supor que o otimizador reconhece ambos e os otimiza (trivialmente) ao máximo.

Aqui está um caso muito simples onde OFFSET ... FETCHé ~2x mais rápido de acordo com a estimativa de custo.

SELECT * INTO #objects FROM sys.objects

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #objects
) x
WHERE r >= 30 AND r < (30 + 10)
    ORDER BY object_id

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

offset-fetch.png

Pode-se variar este caso de teste criando um IC object_idou adicionando filtros, mas é impossível remover todas as diferenças de plano. OFFSET ... FETCHé sempre mais rápido porque faz menos trabalho em tempo de execução.

sql-server sql-server-2012

3 respostas

  1. Best Answer

    Os exemplos na questão não produzem exatamente os mesmos resultados (o OFFSETexemplo tem um erro de diferença). Os formulários atualizados abaixo corrigem esse problema, removem a classificação extra para o ROW_NUMBERcaso e usam variáveis ​​para tornar a solução mais geral:

    DECLARE 
    @PageSize bigint = 10,
    @PageNumber integer = 3;

WITH Numbered AS
(
    SELECT TOP ((@PageNumber + 1) * @PageSize) 
        o.*,
        rn = ROW_NUMBER() OVER (
            ORDER BY o.[object_id])
    FROM #objects AS o
    ORDER BY 
        o.[object_id]
)
SELECT
    x.name,
    x.[object_id],
    x.principal_id,
    x.[schema_id],
    x.parent_object_id,
    x.[type],
    x.type_desc,
    x.create_date,
    x.modify_date,
    x.is_ms_shipped,
    x.is_published,
    x.is_schema_published
FROM Numbered AS x
WHERE
    x.rn >= @PageNumber * @PageSize
    AND x.rn < ((@PageNumber + 1) * @PageSize)
ORDER BY
    x.[object_id];

SELECT
    o.name,
    o.[object_id],
    o.principal_id,
    o.[schema_id],
    o.parent_object_id,
    o.[type],
    o.type_desc,
    o.create_date,
    o.modify_date,
    o.is_ms_shipped,
    o.is_published,
    o.is_schema_published
FROM #objects AS o
ORDER BY 
    o.[object_id]
    OFFSET @PageNumber * @PageSize - 1 ROWS 
    FETCH NEXT @PageSize ROWS ONLY;

    O ROW_NUMBERplano tem um custo estimado de 0,0197935 :

    Plano de número de linha

    O OFFSETplano tem um custo estimado de 0,0196955 :

    Plano de Compensação

    Isso é uma economia de 0,000098 unidades de custo estimado (embora o OFFSETplano exija operadores extras se você quiser retornar um número de linha para cada linha). O OFFSETplano ainda será um pouco mais barato, em geral, mas lembre-se de que os custos estimados são exatamente isso - testes reais ainda são necessários. A maior parte do custo em ambos os planos é o custo da classificação completa do conjunto de entrada, portanto, índices úteis beneficiariam ambas as soluções.

    Onde valores literais constantes são usados ​​(por exemplo OFFSET 30, no exemplo original), o otimizador pode usar uma classificação TopN em vez de uma classificação completa seguida por um Top. Quando as linhas necessárias da Classificação TopN são literais constantes e <= 100 (a soma de OFFSETe FETCH), o mecanismo de execução pode usar um algoritmo de classificação diferente que pode ser executado mais rapidamente do que a classificação TopN generalizada. Todos os três casos têm diferentes características de desempenho em geral.

    Existem vários motivos para o motivo pelo qual o otimizador não transforma automaticamente o ROW_NUMBERpadrão de sintaxe a ser usado OFFSET:

    1. É quase impossível escrever uma transformação que corresponda a todos os usos existentes
    2. Ter algumas consultas de paginação transformadas automaticamente e outras não pode ser confuso
    3. O OFFSETplano não é garantido para ser melhor em todos os casos

    Um exemplo para o terceiro ponto acima ocorre quando o conjunto de paginação é bastante amplo. Pode ser muito mais eficiente buscar as chaves necessárias usando um índice não clusterizado e pesquisar manualmente no índice clusterizado em comparação com a varredura do índice com OFFSETou ROW_NUMBER. Há questões adicionais a serem consideradas se o aplicativo de paginação precisar saber quantas linhas ou páginas existem no total. Há outra boa discussão sobre os méritos relativos dos métodos 'key seek' e 'offset' aqui .

    No geral, provavelmente é melhor que as pessoas tomem uma decisão informada de alterar suas consultas de paginação para usar OFFSET, se apropriado, após testes completos.

    • 14

  2. Com uma ligeira alteração em sua consulta, obtenho uma estimativa de custo igual (50/50) e estatísticas de IO iguais:

    ; WITH cte AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #objects
)
SELECT *
FROM cte
WHERE r >= 30 AND r < 40
ORDER BY r

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

    Isso evita a classificação adicional que aparece em sua versão, classificando em rvez de object_id.

    • 5

  3. Eles modificaram o otimizador de consulta para adicionar esse recurso. Isso significa que implementaram mecanismos especificamente para oferecer suporte ao comando offset ... fetch. Em outras palavras, para a consulta principal, o SQL Server precisa fazer muito mais trabalho. Daí a diferença nos planos de consulta.

    • -3

relate perguntas