January 31, 2011 at 2:31 pm
I am trying to get some opinions on using getdate() in sql code. I have various stored procedures created by developers that are using getdate() a multitude of times in SQL statements.. in where clauses, on joins.
For instance, one fairly complex query has getdate() subconditions on several different joins.
The same code has getdate() as a componenent of a dateadd function in the where clause.
I would think this has the potential to cause a few issues. For one, the multiple getdate() calls will have different values with each call, even if a very small amount (some timie, however small will pass, correct?).
The expected result of the developer was that he would receive the same value in all calls, not necessarily true I would think.
Also, the getdate() and dateadd functions in the where clause could cause performance issues.. I would think.
Would it not be better for performance and data quality perspective to just grab the date, perform whatever manipulation you would like, and then load it into a variable at the front end of the code and just reuse it everywhere instead of the getdate() and other date manipulation functions?
Thanks for any insight,
Jason
January 31, 2011 at 2:38 pm
In general... yes. Unless you're doing different forms of mathmatics to the date.
The DATEADD you're seeing, is it wrapping a DATEDIFF? If so, they're stripping away pieces of the date for things like midnight/first of month/3 months ago.
I'm not saying you can't replace the GETDATE() with a datevariable you declare and set at the beginning, but the call to GETDATE() is miniscule. The mathmatics might take a few hairs longer, depending on what you're doing with them.
As to SHOULD you replace these calls with a single date variable that does everything up front? No idea, not unless we saw the code and understood the expected business logic.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2011 at 2:48 pm
Here is a code snippet. This is just a small bit of code. Some have many more joins with many of these function calls in the joins and in the where clause.
FROM
AZVIEWS.DBO.VW_SNF601 HIS WITH (NOLOCK) --18877
LEFT OUTER JOIN
azVIEWS.DBO.VW_SNF617 A WITH (NOLOCK) --18552
ON HIS.EID = A.EID
AND A.ORDERSTARTDATE >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND A.ORDERSTARTDATE <= GETDATE()
LEFT OUTER JOIN
AZSITEHL7.DBO.DG1 B WITH (NOLOCK) --18552
ON HIS.EID = B.EID
AND B.DIAGNOSISDATETIME >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND B.DIAGNOSISDATETIME <= GETDATE()
LEFT OUTER JOIN
January 31, 2011 at 2:50 pm
Here is a code snippet. This is just a small bit of code. Some have many more joins with many of these function calls in the joins and in the where clause.
FROM
AZVIEWS.DBO.VW_SNF601 HIS WITH (NOLOCK) --18877
LEFT OUTER JOIN
azVIEWS.DBO.VW_SNF617 A WITH (NOLOCK) --18552
ON HIS.EID = A.EID
AND A.ORDERSTARTDATE >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND A.ORDERSTARTDATE <= GETDATE()
LEFT OUTER JOIN
AZSITEHL7.DBO.DG1 B WITH (NOLOCK) --18552
ON HIS.EID = B.EID
AND B.DIAGNOSISDATETIME >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND B.DIAGNOSISDATETIME <= GETDATE()
LEFT OUTER JOIN
January 31, 2011 at 3:41 pm
*facepalm* Picard's gonna make a comeback.
FROM
AZVIEWS.DBO.VW_SNF601 HIS WITH (NOLOCK) --18877
LEFT OUTER JOIN
azVIEWS.DBO.VW_SNF617 A WITH (NOLOCK) --18552
ON HIS.EID = A.EID
AND A.ORDERSTARTDATE >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND A.ORDERSTARTDATE <= GETDATE()
LEFT OUTER JOIN
AZSITEHL7.DBO.DG1 B WITH (NOLOCK) --18552
ON HIS.EID = B.EID
AND B.DIAGNOSISDATETIME >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND B.DIAGNOSISDATETIME <= GETDATE()
LEFT OUTER JOIN
Gaaaaah...
Alright:
CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME
This hurts my brain in many ways, but it's a time stripper. DATE only takes the date portion of a datetime mix. It's a better way than the DATEADD(DATEDIFF()) from 1/1/1900 we've used for a while, or at least easier to read. It's then converted back to datetime for the adding of 8 hours... so this is every day at 8 AM.
A.ORDERSTARTDATE >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND A.ORDERSTARTDATE <= GETDATE()
This is looking for everything between 8 am this morning, and NOW.
This is touchy, especially if you've got multiple selects in the same proc. NOW changes as the proc moves, but if you static the date at the beginning, not so much. If the first call in the query takes 2 seconds, anything between the first and second in those two seconds would be lost by a permanent parameter.
... and don't get me started on the NOLOCKS. If your data is transitory enough to require getdate() here instead of being able to use a specific parameter, that's going to cause all sorts of data consistency issues.
You're right, your code needs to be reviewed, re-analyzed, and discussed with business as to the requirements and see what you can do to clean this up. This would be a long haul task though, it's nothing you can quick-fix.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 9:20 am
would not the fact that a function is being used for the join mean this is not SARGable and therefore this would always table scan?
---------------------------------------------------------------------
February 3, 2011 at 12:56 pm
george sibbald (2/3/2011)
would not the fact that a function is being used for the join mean this is not SARGable and therefore this would always table scan?
It's SARGable, the functions are all being performed on the non-column side, so the index itself is usable because the functions compute before you go to locate the rows.
However, it's a left join, so you may end up with a scan anyway unless your indexes are perfect.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 3:22 pm
aaah, of course, I see. thanks.
---------------------------------------------------------------------
February 4, 2011 at 9:02 am
The time change is the exact reason I perform GETDATE() at the beginning of any proc it's needed in. That and IMO it's best practice to increase performance any where possible no matter how small a hit, so long as it is a good trade off in code complexity. So:
DECLARE @getdate-2 datetime = GETDATE();
For 8 am
DECLARE @8AM datetime = DATEADD(hh,8,CONVERT(datetime,CONVERT(date,@GETDATE)));
So instead of this (Bleck!):
AND B.DIAGNOSISDATETIME >=DATEADD(HOUR,8,CAST(CONVERT(DATE,GETDATE()-1)AS DATETIME)) AND B.DIAGNOSISDATETIME <= GETDATE()
It can look like this:
AND B.DIAGNOSISDATETIME BETWEEN @8AM AND @getdate-2
-------------------------------------------------------------------------------------------------
My SQL Server Blog
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply