SQL Server 2008 R2 – Optimize WHERE Clause Using Dates

optimizationsql-server-2008-r2t-sql

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses:

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

The CalcDate udf based on the type field value makes some comparisons and returns a date. Then adds an amount of days to that date and compares to current date. In order to be able to use an existing index on due_days I want to transform the operation to apply all the transformations to GETDATE(), let's say I want to make it sargable, if possible. Also, if there is some recommendation on what can be done to improve the use of the udf even better.

Best Answer

It's not so hard to do this transformation. Step by step:

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

means:

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

then we have to break the ISNULL() into 2 cases:

    [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()

which can be written as:

    (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)

so we can use 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()
              )

and finally:

    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

Corrected, taking care of the time parts:

    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

You could simplify it a bit, with the use of 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
          )
Related Question