0

I have a stored procedure that I need to make more efficient. It currently looks like

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < DATEADD(DAY, -1 * @MaxAgeInDays, GETDATE())

    IF @@ROWCOUNT > 0
        GOTO delete_more

The first step was to extract some variables

DECLARE @Now DATETIME = GETDATE()
DECLARE @EarliestDate DATETIME = DATEADD(DAY, -1 * @MaxAgeInDays, @Now)

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < @EarliestDate

    IF @@ROWCOUNT > 0
        GOTO delete_more

But then I thought that perhaps it might be faster if I change @EarliestDate to

DECLARE @EarliestDate DATETIME = @Now - @MaxAgeInDays

Which one would be faster or more clear to both the database, and a dba?

1
  • By the way, your statements are missing statement terminators ; – ypercubeᵀᴹ Aug 11 '16 at 22:12
3

The difference, if any, in performance should be so small that it wouldn't be measurable. The calculation is done either way just once. Deleting thousands of rows will surely need far more time to execute than a single calculation.

Having said that, I'd choose the version with the DATEADD() as adding a date with an integer doesn't look nice or elegant. OK, it may look a bit like a cool "hack" but it's not best practice and is relying on the internal representation of date/datetime types.

See Aaron Bertrand's blog: Bad Habits to Kick : Using shorthand with date/time operations, where he explains how this kind of code works ok with DATE or DATETIME but will break your queries when someone changes the type of the column/variable to DATETIME2.

1
  • Another difference will be in how the parameter is sniffed. The local variable will result in a non-histogram estimate (2nd version). – Erik Darling Aug 12 '16 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy