Início / dba / Perguntas / 317999
Accepted
Geezer
Asked: 2022-10-10 05:53:11 +0800 CST

Por que estou recebendo uma classificação quando tenho um índice?

  • 772

Banco de Dados SQL do Azure.

Eu tenho uma tabela da qual preciso obter as primeiras e mais recentes linhas para Col1e Col2com base em CreateDate.

CREATE TABLE dbo.table1 (
    Id            INT    IDENTITY(1,1) PRIMARY KEY ,
    Col1        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    Col2        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    CreateDate    DATETIME NOT NULL
) ;

Eu tenho um índice assim:

CREATE INDEX IX__table1_ASC
ON dbo.table1 (Col1, Col2, CreateDate );

Minha consulta para obter a primeira linha é (planeje aqui ):

--Get the first row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate );

A verificação de índice está usando o índice ( IX__table1_ASC) que criei, mas por que estou recebendo uma classificação?

Insira a descrição da imagem aqui

Minha consulta para obter a linha mais recente (planeje aqui ):

--get latest row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate DESC); --desc here

Novamente, a varredura de índice está usando o índice ( IX__table1_ASC), mas desta vez estou obtendo duas classificações. O primeiro logo após a varredura do índice. O otimizador não é inteligente o suficiente para ler o índice na ordem inversa? Novamente, para que serve o segundo tipo?

A tabela real é bastante grande, então você pode imaginar que os tipos são caros. Como posso otimizar melhor aqui?

Insira a descrição da imagem aqui

sql-server query-performance

1 respostas

  1. Best Answer

    A varredura de índice está usando o índice (IX__table1_ASC) que criei, mas por que estou recebendo uma classificação?

    Porque você está usando uma maneira ineficiente de selecionar a linha superior por grupo.

    Apenas use

    WITH T
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY Col1, Col2
                    ORDER BY CreateDate ) AS RN
         FROM   table1)
SELECT *
FROM   T
WHERE  RN = 1

    insira a descrição da imagem aqui

    O TOP (1) WITH TIESaqui é apenas uma maneira mais ofuscada e menos eficiente de selecionar todas as linhas em que o número da linha é igual a 1. Infelizmente, alguns respondentes no StackOverflow usam esse método sem uma boa razão que eu possa discernir, exceto gostar da novidade.

    Em seu primeiro plano de execução, a classificação não existe para calcular o número da linha, mas para ordenar as linhas pelo resultado dessa numeração de linha depois que ela já foi calculada sem uma classificação.

    Em relação à sua segunda consulta, esta é uma limitação do otimizador de longa data - você pode obter a varredura de índice ordenada para trás e nenhuma classificação com o abaixo.

    WITH T
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY Col1, Col2
                    ORDER BY CreateDate DESC ) AS RN
         FROM   table1
         ORDER BY Col1 DESC, Col2 DESC, CreateDate DESC
         OFFSET 0 ROWS
         )
SELECT *
FROM   T
WHERE  RN = 1

    O OFFSET 0 ROWSé um hack para permitir ORDER BYem uma tabela derivada, o que normalmente não é permitido no SQL Server. O importante é dar ao otimizador uma razão separada para considerar a ordenação ideal.

    Um nível de apresentação ORDER BYserviria ao mesmo propósito, mas prefiro colocar o hack mais perto do que o requer. Essa abordagem também permite especificar uma ordem de apresentação diferente. Tenha em mente OFFSET 0que pode ser otimizado algum dia, como TOP (100) PERCENTé.

    No SQL Server 2000, algumas pessoas costumavam obter uma "exibição ordenada" adicionando um arquivo TOP 100 PERCENT ... ORDER BY. O efeito disso, pelo menos na maioria das vezes, era que apenas fazer um plain SELECTda view sem nenhum ORDER BYna consulta externa retornava as linhas na ordem desejada. Isso nunca foi garantido e no SQL Server 2005 a lógica foi adicionada ao otimizador que acabou de ser otimizado TOP 100 PERCENTneste tipo de caso como logicamente redundante. Potencialmente, o mesmo pode acontecer no futuro OFFSET 0 ROWS, pois é igualmente redundante.

    Pessoalmente, eu espero que qualquer esforço extra de engenharia vá para melhorar a otimização, então esse tipo de hack não é necessário primeiro!

    • 16

relate perguntas