Início / dba / Perguntas / 64209
Accepted
Yaroslav
Asked: 2014-05-01 02:27:42 +0800 CST

Otimize a cláusula where usando datas

  • 772

Estou tentando otimizar várias consultas que usam um padrão semelhante em uma das WHEREcláusulas:

AND (DATEADD(DAY
            , ISNULL(a.[due_days], 30) + 30
            , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL))
            ) < GETDATE()

O CalcDateudf baseado no typevalor do campo faz algumas comparações e retorna uma data. Em seguida, adiciona uma quantidade de dias a essa data e compara com a data atual. Para poder usar um índice existente em due_daysQuero transformar a operação para aplicar todas as transformações a GETDATE(), digamos que quero fazer isso sargable, se possível. Além disso, se houver alguma recomendação sobre o que pode ser feito para melhorar ainda mais o uso do udf.

t-sql sql-server-2008-r2

1 respostas

  1. Best Answer

    Não é tão difícil fazer essa transformação. Passo a passo:

    DATEADD(DAY
       , ISNULL(a.[due_days], 30) + 30
       , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
       ) < GETDATE()

    significa:

    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
  + (ISNULL(a.[due_days], 30) + 30) DAYS
< GETDATE()

    então temos que dividir o ISNULL()em 2 casos:

        [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (a.[due_days] + 30) DAYS
    < GETDATE()
OR
    a.[due_days] IS NULL
  AND 
    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (30 + 30) DAYS
    < GETDATE()

    que pode ser escrita como:

        (a.[due_days] + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
OR
    a.[due_days] IS NULL
  AND 
    (30 + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)

    então podemos usar DATEDIFF():

        (a.[due_days] + 30) 
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )
OR
    a.[due_days] IS NULL
  AND 
    (30 + 30) 
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )

    e finalmente:

        a.[due_days]  
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
OR
    a.[due_days] IS NULL
  AND 
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30

    Corrigido, cuidando das partes do tempo:

        a.[due_days]  
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END
OR
    a.[due_days] IS NULL
  AND 
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END

    Você poderia simplificar um pouco, com o uso de CROSS APPLY:

    CROSS APPLY
    ( SELECT gdt = GETDATE(),
             calc = [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
    ) AS c
CROSS APPLY
    ( SELECT diff = x.diff - CASE WHEN DATEADD( day, x.diff, c.calc ) > c.gdt
                                 THEN 1 ELSE 0 
                             END
      FROM
          ( SELECT diff = DATEDIFF( day, c.calc, c.gdt) - 30
          ) AS x
    ) AS y    
----
    WHERE (  a.[due_days] < y.diff
         OR  a.[due_days] IS NULL  AND  30 < y.diff
          )
    • 11

relate perguntas