February 9, 2009 at 9:18 am
I have a table which has 1 crore + records. I need to select the records which has the createtime between the specified time. My issue is, if I give the date directly in the query, it is pretty fast. But if I set the date range to variables, then the query goes on and on and on. I don't understand what is happening.
select id, order_num
from order_table
where create_date between getdate() - 1 and getdate()
This is pretty fast, gets executed within a minute. But, if I give something like:
declare @begin datetime, @end datetime;
set @begin = getdate() - 1 -- dateadd(hh,-1,getdate())
set @end = getdate()
select id, order_num
from order_table
where create_date between @begin and @end
This is terribly slow, it is taking more than 20 minutes.
I have tried out if the issue is with other joins or where conditions etc. But, everything seems to work fine if I give the date range directly. The problem seems to be only if I give the date range in variables. But, I cannot hardcode the dates like this. It is supposed to be dynamic.
Where am I going wrong?
I dont really know how to read the query plan. So, pls tell me what is the issue with my query and what I could do to make it faster.
February 9, 2009 at 9:23 am
It would help if you upload the actual execution plan.
February 9, 2009 at 9:48 am
Thanks Lynn. I won't be able to post the execution plan, I am not allowed to post official work in public forums. Hope u understand.
Could u hint something for my problem.
February 9, 2009 at 10:00 am
Parameter sniffing, or more accurately the lack of parameter sniffing. SQL doesn't know the values of the variables at compile time and hence guesses the rows affected. If it guesses wrong, performance suffers.
See - http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2009 at 10:21 am
Did you try :
declare @begin datetime, @end datetime;
set @begin = getdate() - 1 -- dateadd(hh,-1,getdate())
set @end = getdate()
select id, order_num
from order_table
where create_date >=@begin
and create_date <=@end
I know it should do the same as between, but I've read somewhere datetimes and between did cause some issues.
(Although I haven't got an url available)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2009 at 10:31 am
GilaMonster (2/9/2009)
Parameter sniffing, or more accurately the lack of parameter sniffing. SQL doesn't know the values of the variables at compile time and hence guesses the rows affected. If it guesses wrong, performance suffers.See - http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Thanks Gail. I went through the link as well as the link which it pointed to.
Link_Part1: http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Link_Part2: http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
I am a little confused. The first part says to use local variables, the second part says using parameters is better than local variables. Which is better?
As the articles suggests, for my problem I could try out creating a stored proc only for that query and call it. But that would be my last option.
Would creating a non-clustered index on the (createtime, id) help?
The first link says to use OPTIMISE FOR query hint. How would this help? Should I specify this in the query?
It also says "A second option is to mark the proc or the query for recompilation each time it runs. Essentially telling SQL that there’s no single optimal plan for the proc/query, so please compile a new one at each execution." How to do this?
ALZDBA (2/9/2009)
Did you try :
select ...
where create_date >=@begin
and create_date <=@end
I know it should do the same as between, but I've read somewhere datetimes and between did cause some issues.
I haven't tried this method. I will try this and see. Thanks.
February 9, 2009 at 10:32 am
ALZDBA (2/9/2009)
I know it should do the same as between, but I've read somewhere datetimes and between did cause some issues.(Although I haven't got an url available)
It will run the same as a between. In fact, a between is converted (by the parser I believe) into exactly that statement before optimisation begins.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2009 at 10:39 am
gyessql (2/9/2009)
I am a little confused. The first part says to use local variables, the second part says using parameters is better than local variables. Which is better?
I don't believe I said anywhere that parameters are better than variables. The first and second articles in that miniseries (there are 3) are describing the opposite sides of the problem. The first shows a case where there is parameter sniffing and as a result the estimation is poor. The second shows a case where there isn't parameter sniffing (because of the use of variables) and as a result the estimation is poor.
If you have a parameter sniffing problem (often a result of data skew) then using variables may solve that. If you're using variables and getting much poorer performance than with parameters, then use parameters
I specifically pointed you at part 2, as that's the post applicable to this situation. Parts 1 and 3 are dealing with different aspects of parameter sniffing that's not directly applicable here.
As the articles suggests, for my problem I could try out creating a stored proc only for that query and call it. But that would be my last option.
Why is a stored proc (which is the best way to fix this) your last option?
Would creating a non-clustered index on the (createtime, id) help?
It's not going to eliminate the root problem, which is poor row estimations because of a lack of parameter sniffing.
The first link says to use OPTIMISE FOR query hint. How would this help? Should I specify this in the query?
That's for a parameter sniffing problem, which is the opposite to what you have.
It also says "A second option is to mark the proc or the query for recompilation each time it runs. Essentially telling SQL that there’s no single optimal plan for the proc/query, so please compile a new one at each execution." How to do this?
That's for a parameter sniffing problem, which is the opposite to what you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2009 at 2:13 pm
IF you are running this from stored procedure you can use "WITH RECOMPILE"
* Noel
February 10, 2009 at 8:21 am
noeld (2/9/2009)
IF you are running this from stored procedure you can use "WITH RECOMPILE"
Recompile won't help where there are variables. Recompile is for when the plan is not optimal because the sniffed parameters have resulted in a plan that has inaccurate estimates and hence non-optimal operators.
When the plan is non-optimal because of variable usage, the estimates are not based of of parameters or anything else that a recompile will change. The recompiled plan will look exactly like the old plan.
I believe that, on SQL 2008 only, OPTION RECOMPILE on the query itself will allow the optimiser to see the value of the variables and come up with a better plan. WITH RECOMPILE on the proc will not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2009 at 10:15 am
Gail, Noel - Thanks for all the suggestions.
Today morning I dropped the procedure with this query and recreated it without making any change. It ran very fast. I ran it a couple of times and it wasn't slow at all.
I figured probably since I dropped and recreated the procedure the plan might have been changed. Am I correct?
However, I created another proc for this particular query and am calling it in my old stored proc. Now, since my old proc itself was running fast today I dunno how to compare the performance difference by creating a proc for this query. How can I find that out?
February 10, 2009 at 1:06 pm
[font="Verdana"]Invent a time machine. Sell me one. :D[/font]
February 12, 2009 at 10:44 pm
gyessql (2/10/2009)
I figured probably since I dropped and recreated the procedure the plan might have been changed. Am I correct?
However, I created another proc for this particular query and am calling it in my old stored proc. Now, since my old proc itself was running fast today I dunno how to compare the performance difference by creating a proc for this query. How can I find that out?
How can I compare the performance of the query here?
February 12, 2009 at 11:59 pm
IMO it has to do with the original date range you specified.
SQLServer must have decided that set of data might be better served doing it in anoher way than generated with your current date ranges plan.
Once the sproc has a plan, it will reuse the plan in the same (SET) conditions and variable definitions.
If this sproc often generates the wrong execution plan, you may be forced to use some hints.
Check out "hint" and "Specifying Query Plans with Plan Forcing " in books online.
[b]Keep in mind, that you will have to check its behaviour with every fix/sp/release you perform ![/b]
So ..... avoid hints as much as possible !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply