May 23, 2007 at 12:13 am
I have come a cross with this type of problem 1st time
I execute Stored Procedure from QA it takes around 30 secs
If i execute content of SP seperate in QA by setting value to its parameter in QA it takes 3 secs
It should be reverese way
Logic
We are executing Dynamic Query using views. Records are in millions
Can anybody help its too urgent we got only 2 hours permission to execute queries. We want the logic behind SP is taking time & Directly run in TSQL in QA takes less time.
Stored Procedure name is sa_sp_name
Waiting !!!
May 23, 2007 at 3:28 am
This sounds like parameter sniffing. The first time the stored procedure is compiled it uses the parameters which were passed to it to generate the most efficient query plan for those parameters. This may not be the most efficient query plan for other sets of parameters.
The quickest way to sort this out is to create the stored procedure WITH RECOMPILE. This recompiles the SP every time it is run.
Ideally you should take time and work out what combination of parameters are most efficient with a different query plan and then call different SPs based on the values of the parameters. This will avoid the overhead of recompiling all the time.
You should also ensure that the database statistics are up to date as the optimizer uses these when generating the query plan.
May 23, 2007 at 3:33 am
just to add ...
if you're using parameters in your (dynamic) queries, make sure they match the datatype and length of the corresponding columns in the tables !
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
May 23, 2007 at 3:37 am
Is it your case?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=367848
_____________
Code for TallyGenerator
May 24, 2007 at 7:35 am
Ken is probably correct. This happens a lot. Try just adding a recompile to the stored procedure and see if it "solves" the problem. If it does, take the time to do something like adding query hints to get a balanced execution plan.
May 25, 2007 at 1:16 am
We suddenly got caught out by this last year. Having had a Stored Procedure in production for best part of a year, usually running in a few seconds, it started to take several minutes until it timed out. We wouldn't have let it go live with such performance.
It almost seemed to be after some patching took place, but investigation with our DBAs and Server people got us nowhere.
I also found the parameter sniffing issue and the recompile didn't seem to help. What I did instead was add a set of variables in the stored procedure, set them equal to the parameters and then run the query with those instead. eg:
CREATE PROCEDURE Do_Something
(
@param1 int
)
Declare @var1 int
set @var1 = param1
Select * from Table
where field = @var1
Performance was then immediately back to what it was when the application went live.
Graham Cottle
May 25, 2007 at 4:17 am
Whenever you create a dynamic query in SP , it will be recompiled everytime adding to cost and time to execute it. Try writing Static SQL in SP and you will see improvement.
IN case of dynamic query in QA, its execution plan is stored and hence takes less time. If you try same query with new connections, it should take more time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply