June 2, 2008 at 12:24 pm
A sproc that I created takes about 1.5 hrs to finish. But when I run the same sproc as a TSQL script, It takes less than 5 seconds.
SP_Recompile doesn't help.
What am I missing here?
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 2, 2008 at 12:45 pm
Check the execution plan of both executions and see what is the difference...
It might be parameter sniffing issue...if it is then use RECOMPILE Query Hint to Solve Parameter Sniffing Problems...
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
MohammedU
Microsoft SQL Server MVP
June 2, 2008 at 3:14 pm
Thanks for the response Mohammed!!
I tried the WITH RECOMPILE option, with no effect.
Also, SP_Recompile will recompile the sproc once, in effect doing what With Recompile option is doing but only once.
What I have is 6 sprocs being called from a main sproc, with 13 parameters each and no defaults at all.
on SQL 2005 std sp1 and win2003
I dont want to have to create 26 parameters and reassign to one another, as Ken Henderson suggested in the blog(btw, both are very informative articles)
At this point I probably have to completely read the MS white paper and see if there is any thing I can try before resorting to creating 26 input parameters.
Any word of the wise is greatly appreciated.
Thanks
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 3, 2008 at 7:12 am
Hi,
First execute everything from query analyser or development studio that rules out any effects of the ado or ado.net layer you that's what you are using.
Second if you are using temporary tables be aware of the recompile event (it wil lonly take 5 insertions for a recompile - see differences between table vars and tempo tables in that case), use the profiler to check how often that recompile event is taking place.
best regard,
June 3, 2008 at 9:43 am
First execute everything from query analyser or development studio that rules out any effects of the ado or ado.net layer you that's what you are using.
I tried this, it takes less than 5 seconds to finish. So, as a script, it doesnt have any proplems.
Second if you are using temporary tables be aware of the recompile event (it wil lonly take 5 insertions for a recompile - see differences between table vars and tempo tables in that case), use the profiler to check how often that recompile event is taking place.
I do have atleast 2 temp tables in all of the 7 sprocs. I wont be able to use the temp variable, as the dataset being accumelated in these temp tables are almost a million rows.
I Tried to set it to Recompile. but doesnt seem to work.
My question is, is there any way I can make it work as it did when it ran as a script.
Thanks for the response.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 4, 2008 at 1:15 am
Hi,
Do not know if it is possible to run as a script, depends on the stored proc. you've developed.
One tip I've still got for you, when running into #tables I switched to permanent tables. In some siutations this solves the problem. If you are working with several concurrent calls to that procedure you could introduce a 'for this procedure indicator column'.
good luck
June 4, 2008 at 7:48 am
Thanks Paul!!
I will test it using the permanent tables.
At this point I think it has to do with the Parameter Sniffing. For this reason (i think), the SQL server is trying to follow a different plan from the one that it'd normally take; if ran as a script.
I will further test it and will update the post with the results.
Thanks
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 24, 2008 at 1:44 pm
This sproc was pushed to the back burner until now. Now that it is resolved on my end, I just wanted to update the post with the solution I used.
Turns out the delay is caused by “parameter sniffing”. (Thanks to Mohammed for pointing me in the right direction)
/** Original Code **/
CREATE PROCEDURE usptest(
DataYear int = 2008,
DataMonth int = 1
) as
BEGIN
SELECT col1,
col2
FROM UserTable (nolock)
WHERE DataYear = @DataYear
AND DataMonth = @DataMonth
END
This is the original I had created; it was taking more that 1.5hrs to finish. After reading Ken's blog and the white paper about this subject I changed the code to below.
Basically, tricking the SS engine into thinking that its just a parameter assignment. SQL Server engine doesn't care (in execution plan caching stage) about what you do with the parameters after the first assignment. The code is pretty self explanatory.
/** Code changed to account for Parameter Sniffing **/
CREATE PROCEDURE usptest(
TmpDataYear int = 2008,
TmpDataMonth int = 1
) as
BEGIN
DECLARE @DataYear INT,
@DataMonth INT
SELECT @DataYear = @TmpDataYear,
@DataMonth = @TmpDataMonth
SELECT col1,
col2
FROM UserTable (nolock)
WHERE DataYear = @DataYear
AND DataMonth = @DataMonth
END
This code runs in under 5secs, which is normal.
Note: I have simplified the code above to demonstrate what I essentially did. Actual code is much more complicated than that.
And Thanks to all of you for responding to this question!!!
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply