November 28, 2007 at 2:51 pm
Hi guys,
I have a proc that runs very fast in the Query Analyzer (actually I’m using the Management Studio), but it takes forever when the webpage calls it.
This proc is composed by several queries, in the following structure:
-- First query
SELECT…..
FROM ….
WHERE …
-- second query
SELECT…..
FROM ….
WHERE …
-- third query
SELECT…..
FROM ….
WHERE …
…
All those queries (in the same proc) run really fast, but the second one takes almost a whole minute to run when the proc is called by the webpage. If I execute this same proc from Query Analyzer, the query runs very fast.
From the tests that I did so far, my conclusion is that SQL is choosing a bad execution plan when the proc is called by the webpage, but it is choosing a good execution plan when the proc is called from Query Analyzer.
The solution I found here was to use HINT, forcing SQL to follow my execution plan. But I have had this same problem in other procs, and I don’t want to use HINT everywhere.
Can anybody help me with this?
Thanks a lot,
Luiz.
November 28, 2007 at 7:17 pm
Are you running them all in the same batch in both situations?
You could try a stored proc instead and use WITH RECOMPILE.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 28, 2007 at 7:38 pm
I thought they were already in a stored proc?
Maybe try updating your statistics and/or clearing the procedure cache.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
November 29, 2007 at 8:12 am
Guys,
Thank you for your answers.
Clarifying: all those queries are in a stored proc.
How do I clear the procedure cache?
November 29, 2007 at 8:32 am
Another option besides clearing cache is updating the table and index statistics.
I have had similar problems. Runs fine in SSMS and not from a web request. Ran some trace logs and could see the different query execution plans.
daryl
November 29, 2007 at 8:34 am
Hi,
Check Thoses links
http://www.databasejournal.com/features/mssql/article.php/2203601
http://sqlserver-qa.net/blogs/perftune/archive/2007/10/25/2549.aspx
You can spend time using the profiler to be sure if it's really the proc is recompiled , each time it's called.
Regards,
Ahmed
November 29, 2007 at 12:53 pm
Luiz (11/29/2007)
Guys,Thank you for your answers.
Clarifying: all those queries are in a stored proc.
How do I clear the procedure cache?
DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE. See BOL for details on what exactly these do. There will be a performance hit after these commands are run as things are recompiled.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply