Início / dba / Perguntas / 333571
Accepted
utterly confuzzled
Asked: 2023-11-29 23:48:12 +0800 CST

Quais índices devo colocar nesta tabela, dada esta ilustração de seu conteúdo e a consulta que executarei nela?

  • 772

Estou trabalhando com uma tabela (que eu controlo) que está sendo preenchida por outro aplicativo (que não controlo), cada vez que um registro de um tipo específico em um sistema externo é criado ou atualizado. Abaixo está uma ilustração bastante simplificada desta tabela, que chamo de "AllRecordVersions".

Registro de data e hora ID do registro Estado de registro Campo de Dados1 Campo de Dados2 Campo de Dados3
29/11/2023 09:08:00 A0000001 Processado ABC123 Verdadeiro 102.11
29/11/2023 09:13:00 A0000002 Processado DEF789 Falso 96,48
29/11/2023 09:20:00 A0000001 Não processado ABC123 Falso 105,59
29/11/2023 09:22:00 A0000001 Não processado ABC124 Falso 106.02
29/11/2023 09:37:00 A0000002 Não processado DEF789 Falso 99,73

O valor de "RecordState" refere-se a se meu próprio aplicativo já obteve ou não o registro. Consigo atualizar esta coluna para rastrear a atividade do meu aplicativo.

Estou usando uma consulta no formato abaixo (que simplifiquei/anonimizei) para escolher a versão mais recente de cada registro da tabela, de forma que eu possa comparar os valores de cada um de seus campos de dados com os valores que eles estavam na versão anterior do mesmo registro que processei.

SELECT

    -- Here I can compare the latest version of each record
    -- with the last version that I had processed, e.g. with
    -- "CASE WHEN [LatestUnprocessedRecordVersion].[DataField1] <> [LatestProcessedRecordVersion].[DataField1] ...",
    -- so that I can check if and how certain fields have been changed,
    -- and use that to decide what updates to push into some other external system

FROM (
    [AllRecordVersions] AS [LatestUnprocessedRecordVersion]
    JOIN (
        SELECT
          MAX([RecordTimestamp]) AS [RecordTimestamp_Max],
          [RecordID]
        FROM [AllRecordVersions]
        WHERE [RecordState] = 'Unprocessed'
        GROUP BY [RecordID]
    ) AS [LatestUnprocessedRecordVersion_Timestamp]
          ON [LatestUnprocessedRecordVersion].[RecordTimestamp] = [LatestUnprocessedRecordVersion_Timestamp].[RecordTimestamp_Max]
         AND [LatestUnprocessedRecordVersion].[RecordID] = [LatestUnprocessedRecordVersion_Timestamp].[RecordID]
)
JOIN (
    [AllRecordVersions] AS [LatestProcessedRecordVersion]
    JOIN (
        SELECT
          MAX([RecordTimestamp]) AS [RecordTimestamp_Max],
          [RecordID]
        FROM [AllRecordVersions]
        WHERE [RecordState] = 'Processed'
        GROUP BY [RecordID]
    ) AS [LatestProcessedRecordVersion_Timestamp]
          ON [LatestProcessedRecordVersion].[RecordTimestamp] = [LatestProcessedRecordVersion_Timestamp].[RecordTimestamp_Max]
         AND [LatestProcessedRecordVersion].[RecordID] = [LatestProcessedRecordVersion_Timestamp].[RecordID]
)
    ON [LatestUnprocessedRecordVersion].[RecordID] = [LatestProcessedRecordVersion].[RecordID]

Minha dúvida é: Quais índices devo colocar na tabela "AllRecordVersions" para que a consulta acima seja executada com eficiência? O banco de dados é o Microsoft SQL Server.

Para ajudar também na eficiência, a tabela "AllRecordVersions" será periodicamente removida removendo:

  1. Todas, exceto a última versão processada de cada registro da tabela (uma vez que nunca serão necessárias para comparação)
  2. Todas, exceto a última versão não processada de cada registro da tabela (uma vez que estes nunca serão processados)
sql-server

1 respostas

  1. Best Answer

    Não estou convencido de que essa consulta seja a que você deveria executar em primeiro lugar.

    Obviamente, é gerado por alguma ferramenta como o Access e pode ter uma boa dose de funções de janela.

    SELECT

    -- Here I can compare the latest version of each record
    -- with the last version that I had processed, e.g. with
    -- "CASE WHEN LatestUnprocessedRecordVersion.DataField1 <> LatestProcessedRecordVersion.DataField1 ...",
    -- so that I can check if and how certain fields have been changed,
    -- and use that to decide what updates to push into some other external system

FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY arv.RecordID ORDER BY arv.RecordTimestamp DESC) AS rn
    FROM AllRecordVersions AS arv
    WHERE arv.RecordState = 'Unprocessed'
) LatestUnprocessedRecordVersion
JOIN (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY arv.RecordID ORDER BY arv.RecordTimestamp DESC) AS rn
    FROM AllRecordVersions AS arv
    WHERE arv.RecordState = 'Processed'
) LatestProcessedRecordVersion
    ON LatestUnprocessedRecordVersion.RecordID = LatestProcessedRecordVersion.RecordID
WHERE LatestUnprocessedRecordVersion.rn = 1
AND LatestProcessedRecordVersion.rn = 1;

    Observe que a semântica é um pouco diferente, pois não incluirá vínculos. Use DENSE_RANKse quiser gravatas.

    Para que esta consulta seja executada com eficiência, você deseja o seguinte índice:

    AllRecordVersions (RecordState, RecordID, RecordTimestamp DESC) INCLUDE (DataField1, DataField2, DataField3)

    banco de dados<> violino

    • 0

relate perguntas