suryam (1/11/2014)
Hi folks,The script is fine but as per performance concern we should not use any function in where condition. My script is
Convert(varchar(10),id_updt,101) >=Convert(varchar(10),getdate()-3,101)
data retrieving from trigger table for last 3 days to get the last updated/inserted/deleted from our production table but script is running very slow as well i am getting wrong data also as my date format is in datetimestamp format in trigger table
can any body please guide me how can i overcome this issue.
Yes and agreed. Wrapping columnns in a function usually results in a SCAN instead of a SEEK/Range scan and that can really hurt performance.
To convert your code to be able to use indexes on the id_updt (Seriously??? They abbreviated that??? :blink:) you need to absolutely avoid any calculations on table/view columns. The following is an exact but SARGable replacement for the code you've given.
WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-3,0) --Midnight 3 days ago not including today
"SARGable" effective means "can do an index seek" and the "0" in the code above is the date-serial number (shortcut) for '1900-01-01'. The code calculates the number of whole days since the "0" date and then converts that number of whole days back to a date effectively stripping the time off the date making it a WHOLE date.
The problem with your code and the replacement code above is that it's highly dependent on when you run it because there is no end date to it. What you might want to do is covert it to return the last 3 WHOLE days and that doesn't include today because today isn't ever done yet. I don't know what other people call it but I call it "boxing the dates".
The following code will return the rows for the last 3 whole days and will produce the same results no matter what time of day you run it (provided that id_updt rows are static).
WHERE id_updt >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-4,0) --Midnight 4 days ago not including today (4 necessary because TODAY is not included)
AND id_updt < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) --Midnight at the start of today
Let us know if you have any addition questions on this.
--Jeff Moden
Change is inevitable... Change for the better is not.