July 14, 2009 at 2:03 pm
I am absolutely stumped by this and I'm hoping someone can help me out. A user gave me a query that was taking a while to run if he used date functions in the where clause. And my long I mean it took 20 v. 1 second when he hard coded a date. My question is there any difference from an optimization standpoint in these two statements.
DATEADD(d,-1,DATEADD(m,DATEDIFF(m, 0, getdate()), 0))
cast(cast(month(getdate()) as varchar(2)) + '/' + '01' + '/' + cast(year(getdate()) as varchar(4)) as datetime)-1
They both return 6/30/09, but the last run runs in 1 second while the first one takes 20 seconds. The execution plan is different in both I just don't know why that would happen.
Thoughts?
July 14, 2009 at 2:11 pm
Have you considered doing the date math in a statement before the query? That usually helps that kind of thing tremendously. (Assign the value to a variable and use the variable in the Where clause.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2009 at 2:20 pm
And to simplify the date math even more
SELECTDATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)
N 56°04'39.16"
E 12°55'05.25"
July 14, 2009 at 2:27 pm
Tried the code below
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)
Took 19 seconds to run. Almost seems like it doesn't like DATEADD.
July 14, 2009 at 2:29 pm
GSquared (7/14/2009)
Have you considered doing the date math in a statement before the query? That usually helps that kind of thing tremendously. (Assign the value to a variable and use the variable in the Where clause.)
The developer tried this and it worked. As a dba and architect it's just bugging the hell out of me as to why the execution plans (and thus time to return results) are different.
July 14, 2009 at 2:31 pm
J.D. Gonzalez (7/14/2009)
GSquared (7/14/2009)
Have you considered doing the date math in a statement before the query? That usually helps that kind of thing tremendously. (Assign the value to a variable and use the variable in the Where clause.)The developer tried this and it worked. As a dba and architect it's just bugging the hell out of me as to why the execution plans (and thus time to return results) are different.
It has to guess what the result of the function will be. Different functions, different implementations.
That's one reason it's best to evaluate functions outside the Where clause. The other is it makes for more efficient index use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2009 at 2:39 pm
I thought I'd include the statistics IO for both runs. Any assistance in interpreting this would be greatly appreciated.
Fast execution
(9028 row(s) affected)
Table 'CODE_DETAIL'. Scan count 4, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SUPER_TABLE'. Scan count 1, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GROUP_HEADER'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MEMBERS'. Scan count 82, logical reads 49185, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PROVIDERS'. Scan count 9, logical reads 1406, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Much slower execution
(9028 row(s) affected)
Table 'CODE_DETAIL'. Scan count 35894, logical reads 80816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SUPER_TABLE'. Scan count 9028, logical reads 469456, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PROVIDERS'. Scan count 9028, logical reads 29048, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GROUP_HEADER'. Scan count 0, logical reads 18056, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MEMBERS'. Scan count 1, logical reads 152915, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
July 14, 2009 at 2:42 pm
J.D. Gonzalez (7/14/2009)
I thought I'd include the statistics IO for both runs. Any assistance in interpreting this would be greatly appreciated.Fast execution
(9028 row(s) affected)
Table 'CODE_DETAIL'. Scan count 4, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SUPER_TABLE'. Scan count 1, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GROUP_HEADER'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MEMBERS'. Scan count 82, logical reads 49185, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PROVIDERS'. Scan count 9, logical reads 1406, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Much slower execution
(9028 row(s) affected)
Table 'CODE_DETAIL'. Scan count 35894, logical reads 80816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SUPER_TABLE'. Scan count 9028, logical reads 469456, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PROVIDERS'. Scan count 9028, logical reads 29048, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GROUP_HEADER'. Scan count 0, logical reads 18056, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MEMBERS'. Scan count 1, logical reads 152915, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
It's going to be difficult to determine the differences between these two when we have not seen the queries involved in either one. I can tell from the above that the second one took longer - but, other than that - I really cannot say more.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2009 at 2:58 pm
Does anyone know what a 'Worktable' is? This appears in the first io block I posted earlier. The second one (the longer running one) does not have any 'worktables'
July 14, 2009 at 3:16 pm
A worktable is an internal table created by SQL Server as a helper table to solve certain things.
These things can be aggregations, joins and so on.
N 56°04'39.16"
E 12°55'05.25"
July 14, 2009 at 4:55 pm
Would it be possible for you to post the queries and the execution plans?
July 14, 2009 at 7:32 pm
David Betteridge (7/14/2009)
Would it be possible for you to post the queries and the execution plans?
I would have to agree with this. It is hard to diagnosis problems without seeing what you are doing.
July 14, 2009 at 7:39 pm
I'll get it posted tomorrow. Can't seem to VPN in to get now.
Sorry for not posting it earlier as I didn't think the query mattered as the only thing I changed was how the date was produced.
July 15, 2009 at 6:59 am
There must be a function or something that's radically changing the execution plan. You'll need to post both execution plans and please make them actual execution plans, not estimated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 15, 2009 at 7:29 am
I have a very dumb question, but how do you post an execution plan?
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply