Stored Procedure Performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Stored Procedure Performance

<b><font color="blue"><font size="2"><font face="Verdana">HI There!,<br /><br />I’ve made quite a complex query, that takes some time to run. I added indexes to speed up this query. It worked. The Query that run in 16 secs., now does it in 2 secs.<br />So I decided I needed a Stored Procedure to run this Query. The exact same query. When I call the Stored Procedure, it takes about 3mins 26secs to run !!! It is the exat same code that I used for my query using the query analizer.<br /><br />Any ideas of what is happening?? Is this usual or is it a rare case??<br /><br />Thanks in advance!… [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />JuanP</font id="Verdana"></font id="size2"></font id="blue"></b><br /><br />JP
Have you tried to recomplie the stored procedure after you created?
Check the execution plan for SP too. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com


Yes, did that already.
I have to check carefully for both execution plans to check if there are any differences.
JP
Check this linkhttp://www.sql-server-performance.com/stored_procedures.asp as a starter. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

is there a hash join or hash match involed in the query plan?
are more than 10k rows involved in the hash?
if so, does the sp only run slow from a client app, and not from Query Analyzer?
if so, on the client side, don’t use CommandType.StoredProcedure with parameters, use CommandType Text, and explicitly spell out the sp and parameter values
]]>