June 15, 2015 at 11:50 pm
I have a WHERE clause in a DELETE statement and the x.[Timestamp] field is the PK:
DELETE x
FROM TableX x
JOIN TableY y ON (x.Id = y.Id)
WHERE x.[Timestamp] <= CASE t.z
WHEN 'yy' THEN DATEADD(yy, -1, GETDATE())
WHEN 'qq' THEN DATEADD(qq, -1, GETDATE())
WHEN 'mm' THEN DATEADD(mm, -1, GETDATE())
WHEN 'wk' THEN DATEADD(wk, -1, GETDATE())
WHEN 'dd' THEN DATEADD(dd, -1, GETDATE())
WHEN 'hh' THEN DATEADD(hh, -1, GETDATE())
WHEN 'mi' THEN DATEADD(mi, -1, GETDATE())
END
I know that if I were to use a Function on the LHS of the WHERE clause the index would be ignored.
However, in the above scenario would this be an efficient way to perform the DELETE given the RHS includes calculations?
June 16, 2015 at 3:00 am
shindle 17293 (6/15/2015)
I have a WHERE clause in a DELETE statement and the x.[Timestamp] field is the PK:DELETE x
FROM TableX x
JOIN TableY y ON (x.Id = y.Id)
WHERE x.[Timestamp] <= CASE t.z
WHEN 'yy' THEN DATEADD(yy, -1, GETDATE())
WHEN 'qq' THEN DATEADD(qq, -1, GETDATE())
WHEN 'mm' THEN DATEADD(mm, -1, GETDATE())
WHEN 'wk' THEN DATEADD(wk, -1, GETDATE())
WHEN 'dd' THEN DATEADD(dd, -1, GETDATE())
WHEN 'hh' THEN DATEADD(hh, -1, GETDATE())
WHEN 'mi' THEN DATEADD(mi, -1, GETDATE())
END
I know that if I were to use a Function on the LHS of the WHERE clause the index would be ignored.
However, in the above scenario would this be an efficient way to perform the DELETE given the RHS includes calculations?
Where are you getting the "t.z" value for use in the case to calculate the datepart to evaluate against?
June 16, 2015 at 6:39 pm
Apologies for the typo - it should be y.z
June 17, 2015 at 11:57 am
I'd put it all in the JOIN clause, since that's what those conditions effectively are:
DELETE x
FROM TableX x
INNER JOIN TableY y ON (x.Id = y.Id) AND
(x.[Timestamp] <= CASE y.z
WHEN 'yy' THEN DATEADD(YEAR, -1, GETDATE())
WHEN 'qq' THEN DATEADD(QUARTER, -1, GETDATE())
WHEN 'mm' THEN DATEADD(MONTH, -1, GETDATE())
WHEN 'wk' THEN DATEADD(WEEK, -1, GETDATE())
WHEN 'dd' THEN DATEADD(DAY, -1, GETDATE())
WHEN 'hh' THEN DATEADD(HOUR, -1, GETDATE())
WHEN 'mi' THEN DATEADD(MINUTE, -1, GETDATE())
END)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 17, 2015 at 1:27 pm
shindle 17293 (6/15/2015)
I have a WHERE clause in a DELETE statement and the x.[Timestamp] field is the PK:DELETE x
FROM TableX x
JOIN TableY y ON (x.Id = y.Id)
WHERE x.[Timestamp] <= CASE t.z
WHEN 'yy' THEN DATEADD(yy, -1, GETDATE())
WHEN 'qq' THEN DATEADD(qq, -1, GETDATE())
WHEN 'mm' THEN DATEADD(mm, -1, GETDATE())
WHEN 'wk' THEN DATEADD(wk, -1, GETDATE())
WHEN 'dd' THEN DATEADD(dd, -1, GETDATE())
WHEN 'hh' THEN DATEADD(hh, -1, GETDATE())
WHEN 'mi' THEN DATEADD(mi, -1, GETDATE())
END
I know that if I were to use a Function on the LHS of the WHERE clause the index would be ignored.
However, in the above scenario would this be an efficient way to perform the DELETE given the RHS includes calculations?
The optimizer doesn't actually care which "side" of a condition a function is on. That is a myth about indexes being ignored. It has to do with if a column is wrapped in a function and has nothing to do with which side of the condition the function is on.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply