December 16, 2008 at 4:27 am
I have a query which creates two temp tables and selects data from them.
This takes 7 seconds when run as a query with hardcoded parameters.
Now, when i convert it into a stored proc and same parameters are passed to it it takes MORE THAN 5 mins.
I just dont understand why this is happening..
Stored procedures are supposed to give better performance than queries.
My requirement is to display the data on a report through the stored proc.
Can any one help here?
December 16, 2008 at 4:40 am
are your parameters dates?
If so sounds like parameter sniffing
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 16, 2008 at 4:41 am
can u post the query?
December 16, 2008 at 4:42 am
takes 4 parameters-- one of them is a date..
what is parameter sniffing?
and how to resolve it?
December 16, 2008 at 4:45 am
its too big to post here..
December 16, 2008 at 4:47 am
one way around it is to declare a local variable and assign the value of the parameter to that variable and use that in your proc rather than the actual input parameter
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 16, 2008 at 4:49 am
Also read this thread
http://www.sqlservercentral.com/Forums/Topic548393-8-1.aspx?Highlight=sniffing
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 16, 2008 at 4:59 am
Christopher Stobbs (12/16/2008)
are your parameters dates?If so sounds like parameter sniffing
Hi Christopher
Why do you think that it is because of date parameter? As far as I know parameter sniffing is done to all parameters regardless of there type. Can you pleas explain it?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 16, 2008 at 5:02 am
i have tried this...but to no effect...
i have read some articles on parameters sniffing and they dont really explain how to resolve this. 🙁
December 16, 2008 at 5:27 am
Christopher Stobbs (12/16/2008)
one way around it is to declare a local variable and assign the value of the parameter to that variable and use that in your proc rather than the actual input parameter
this worked...
thanks a lot..
December 17, 2008 at 2:28 am
Hi Sorry about the confusion.
In my experience I tend to notice that people who have parameter sniffing problems, tend to have it with dates. Having said this you are correct that it could be any field and what I mentioned above about dates is by no means a fact or hard and fast rule, just something from experience...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 18, 2008 at 9:48 pm
Try using SET NOCUNT ON in the procedure. You will see the difference.
December 18, 2008 at 9:49 pm
Sorry, its
SET NOCOUNT ON
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply