Início / dba / Perguntas / 258897
Accepted
Pedro Ludovico Bozzini
Asked: 2020-02-05 11:17:29 +0800 CST

SQL Server - Selecione o registro mais recente de cada grupo quando o desempenho for crítico

  • 772

Eu executo um banco de dados SQL Server 2016 onde tenho a seguinte tabela com mais de 100 milhões de linhas:

StationId | ParameterId |       DateTime       | Value
    1     |      2      | 2020-02-04 15:00:000 |  5.20
    1     |      2      | 2020-02-04 14:00:000 |  5.20
    1     |      2      | 2020-02-04 13:00:000 |  5.20
    1     |      3      | 2020-02-04 15:00:000 |  2.81
    1     |      3      | 2020-02-04 14:00:000 |  2.81
    1     |      4      | 2020-02-04 15:00:000 |  5.23
    2     |      2      | 2020-02-04 15:00:000 |  3.70
    2     |      4      | 2020-02-04 15:00:000 |  12.20
    3     |      2      | 2020-02-04 15:00:000 |  1.10

Esta tabela possui um índice clusterizado para StationId, ParameterId e DateTime, nesta ordem, todos crescentes.

O que eu preciso é, para cada par exclusivo StationId - ParameterId, retornar o valor mais recente da coluna DateTime:

StationId | ParameterId |       LastDate       
    1     |      2      | 2020-02-04 15:00:000 
    1     |      3      | 2020-02-04 15:00:000 
    1     |      4      | 2020-02-04 15:00:000 
    2     |      2      | 2020-02-04 15:00:000 
    2     |      4      | 2020-02-04 15:00:000 
    3     |      2      | 2020-02-04 15:00:000

O que estou fazendo agora é a seguinte consulta, que leva cerca de 90 a 120 segundos para ser executada:

    SELECT StationId, ParameterId, MAX(DateTime) AS LastDate
    FROM  MyTable WITH (NOLOCK)
    GROUP BY StationId, ParameterId

Também vi muitos posts sugerindo o seguinte, que leva mais de 10 minutos para ser executado:

    SELECT StationId, ParameterId, DateTime AS LastDate
    FROM
    (
       SELECT StationId, ParameterId, DateTime
       ,ROW_NUMBER() OVER (PARTITION BY StationId,ParameterIdORDER BY DateTime DESC) as row_num
       FROM  MyTable WITH (NOLOCK)
    )
    WHERE row_num = 1

Mesmo no melhor caso (usando a cláusula GROUP BY e a função agregada MAX), o plano de execução não indica uma busca de índice:

Plano de execução da consulta

Detalhe da varredura de índice

Gostaria de saber se existe uma maneira melhor de realizar essa consulta (ou construir o índice) para obter um melhor tempo de execução.

sql-server index

3 respostas

  1. Best Answer

    Se você tiver um número pequeno o suficiente de pares (StationID, ParameterID), tente uma consulta como esta:

    select StationID, ParameterID, m.DateTime LastDate 
from StationParameter sp
cross apply 
  (
     select top 1 DateTime 
     from MyTable 
     where StationID = sp.StationID
      and ParameterID = sp.ParameterID
     order by DateTime desc
  ) m

    Para habilitar o SQL Server para realizar uma pesquisa, buscando o mais recente DateTimepara cada par (StationID,ParameterID).

    Com apenas um índice clusterizado em (StationID, ParameterID, DateTime), não há como o SQL Server descobrir os pares distintos (StationID, ParameterID) sem verificar o nível folha do índice e pode encontrar o maior DateTime durante a verificação.

    Também com mais de 100 milhões de linhas, esta tabela pode ser melhor como um Clustered Columnstore em vez de um BTree Clustered Index.

    • 27

  2. Se o desempenho é realmente crítico e você costuma perguntar à sua tabela a data mais recente... Por que não criar uma tabela de pesquisa de estação e parâmetro como chave com o carimbo de hora mais recente. Você precisa atualizar esta tabela toda vez que modificar a grande, mas desta forma você terá seus resultados quando precisar em milissegundos.

    • 0

  3. Com a abordagem CTE row_number, tente criar um índice não clusterizado em StationId, ParameterId, DateTime desc. Descobri que ter um índice com a ordem de classificação adequada que minha partição por cláusula de ordem usa melhorou o desempenho para mim.

    • 0

relate perguntas