March 29, 2012 at 11:04 am
I have a query which takes a parameter. If a parameter is passed. the query takes 11 minutes to complete whereas if you give the values directly it takes 7 seconds to execute, Is there any solution to it. I checked the execution plan of both queries and they both are different.
The below query takes 11 minutes to execute
Declare @Date as int
Set @Date = 3
Select * from TempTable where correct_date between (GETDATE()-@day) and GETDATE()
whereas this one takes 7 seconds to complete.
Select * from TempTable where correct_date between (GETDATE()-3) and GETDATE()
March 29, 2012 at 11:13 am
did you compare both sqlplans ?
how old is the cached plan for the parameterized query ?
( you can add the .sqlplan as attachment with your reply )
have you checked with statistics IO for physical IO ( so both queries actually run in the same conditions )
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
March 29, 2012 at 12:42 pm
Sanz (3/29/2012)
I have a query which takes a parameter. If a parameter is passed. the query takes 11 minutes to complete whereas if you give the values directly it takes 7 seconds to execute, Is there any solution to it. I checked the execution plan of both queries and they both are different.The below query takes 11 minutes to execute
Declare @Date as intSet @Date = 3
Select * from TempTable where correct_date between (GETDATE()-@day) and GETDATE()
whereas this one takes 7 seconds to complete.
Select * from TempTable where correct_date between (GETDATE()-3) and GETDATE()
That first one won't even syntax check. Is @Day declared somewhere else not shown and maybe doesn't have what you think is in it?
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@day".
March 29, 2012 at 8:21 pm
You may want to try to see if this runs any faster:
Declare @Date as int
Set @Date = 3
Select * from TempTable where correct_date between DATEADD(day,-@date,GETDATE()) and GETDATE()
--
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 10:11 pm
Try adjusting for parameter sniffing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply