Início / dba / Perguntas / 180409
Accepted
Chris Halcrow
Asked: 2017-07-21 21:40:19 +0800 CST

Consulta do SQL Server com intervalo de datas em WHERE - o plano de execução usa instância PK clusterizada e não índice de coluna de data

  • 772

Tenho a seguinte consulta no SQL server:

declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT  
    my_date = a.itemTimestamp
FROM
    my_table a with(nolock)
WHERE 
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)

É extremamente lento (90s para < 2000 registros), porque o plano de execução usa o índice clusterizado da coluna PK para 'my_table':

insira a descrição da imagem aqui

Por que isso está acontecendo e como posso otimizar o desempenho da consulta?

sql-server execution-plan

3 respostas

  1. Best Answer

    Qual é o tipo de dados de itemTimestamp? Essa coluna é indexada? A coluna é anulável?

    Supondo que as respostas sejam datetime, Sim e Não, respectivamente, você pode considerar

    WHERE  a.itemTimestamp >= isnull(@timestamp_start, '17530101')
       AND a.itemTimestamp < isnull(@timestamp_end, '99991231')

    Se você alterar datetime2e realmente armazenar datas anteriores a 1753, precisará alterar a condição de limite inferior para 00010101. Isso também não trará de volta as datas correspondentes ao limite superior de 9999-12-31- mas no mundo real é improvável que isso cause um problema.

    • 5

  2. É my_table.itemTimestampum DATEtipo de dados real ou DATETIME? Parece um nome de coluna estranho para um tipo de dados que não possui componente de tempo ;-). Se for um DATETIME, a etapa 1 é usar o tipo de dados apropriado para a variável. Mas, se for realmente uma DATEcoluna, tente a seguinte consulta. Ele divide as 4 permutações para que o SQL Server possa ter consultas simples e concisas em vez de uma consulta do tipo tudo com um monte de ORs que muitas vezes complicam os planos de consulta.

    DECLARE @timestamp_start DATE = GETDATE() - 1;
DECLARE @timestamp_end DATE = GETDATE();

IF (@timestamp_start IS NULL AND @timestamp_end IS NULL)
BEGIN
  SELECT   a.itemTimestamp AS [my_date]
  FROM     my_table a WITH (NOLOCK);
END;
ELSE
BEGIN
  IF (@timestamp_start IS NOT NULL AND @timestamp_end IS NOT NULL)
  BEGIN
    SELECT   a.itemTimestamp AS [my_date]
    FROM     my_table a WITH (NOLOCK)
    WHERE    a.itemTimestamp >= @timestamp_start
    AND      a.itemTimestamp < @timestamp_end;
  END;
  ELSE
  BEGIN
    IF (@timestamp_start IS NULL)
    BEGIN
      SELECT   a.itemTimestamp AS [my_date]
      FROM     my_table a WITH (NOLOCK)
      WHERE    a.itemTimestamp < @timestamp_end;
    END;
    ELSE
    BEGIN
      SELECT   a.itemTimestamp AS [my_date]
      FROM     my_table a WITH (NOLOCK)
      WHERE    a.itemTimestamp >= @timestamp_start;
    END;
  END;
END;
    • 1

  3. Apenas para bancar o advogado do diabo (em alguns círculos isso é desaprovado), se você tem CERTEZA de que deseja usar esse índice e melhorar as coisas, adicione uma dica de índice ... índices ou consultas no futuro. Adicionar: COM (ÍNDICE (###SEU NOME DO ÍNDICE AQUI##)) 

    declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT  
    my_date = a.itemTimestamp
FROM
    my_table a WITH (INDEX (###YOUR INDEX NAME HERE##))
WHERE 
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)
    • 0

relate perguntas