SQL Server Performance Forum – Threads Archive

Aren’t stored procedures meant to be faster?

I am connecting to MSDE from ASP and executing the following stored procedure in a loop: Alter Procedure includes_specific_drug_confirmorder_1 @doseid INT =-1, @supplierid INT =-1, @parentid INT =-1, @pipcode NVARCHAR(50) =" ", @orderid INT=-1, @price FLOAT =-1 WITH RECOMPILE As
SET DATEFORMAT DMY;
UPDATE order_contents
SET [email protected], [email protected], [email protected], [email protected]
WHERE [email protected] AND [email protected] return For a quite a lot of rows this takes about 78 seconds to run (slow test machine). If however I just replace this with a direct SQL call "UPDATE order_contents, SET…." then it only takes 17 seconds!!!! I was under the impression that using stored procedures was the way to go as they run significantly faster than add-hoc queries. But my example above seems to suggest that this isn’t in fact the case. Am I doing something wrong or is MSDE just significantly different from the real SQL Server (and could I thus expect the real SQL server to behave differently)?
Mads

Well why are you using "WITH RECOMPILE" in the stored procedure? This means a cached query plan can not be reused. When you are issuing just a direct UPDATE… this will get cached the first time and can be reused the second time it is run. Assuming you are using the same values in your loop. Also in your SP try adding SET NOCOUNT ON in the beginning. And remove the SET DATEFORMAT DMY if you are not using it in the update statment And to answer your question about performance, stored procedure are not that much faster than dynamic sql in most cases. SQL 2000 (and SQL 7 too) are quite good at caching dynamic queries as well. But SPs has other benefits like code encapsulation and easier security management. /Argyle

Thank you for your answer. I’ve tried running it again in various ways. Here is a breakdown of time taken: Add-hoc query: 13 secs SQL SP using WITH RECOMPILE and SET DATEFORMAT DMY: 65 secs SQL SP using WITH RECOMPILE: 76 secs (!) SQL SP using SET NOCOUNT ON: 12 secs (hooray!) SQL SP no switches: 18 secs I simply misunderstood the advice in the SP-optimization article on this site about the WHERE sentence being dynamic or not! Thanks again – my faith in SPs is restored. Now I’m off to add SET NOCOUNT ON to all my other SPs – hopefully I’ll get a 33% speed increase on all of them [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Mads

Why do u need to set the dateformat. Using SET statements in stored procedure may also lead to recompilation which is a performance overhead. Gaurav

Also note that ad-hoc queries dont necessarily have to use the same values in order for the execution plan to be cached. Ad-Hoc query plans will often be autoparameterised by sql2000

I don’t think you will see a 33% increase from adding SET NOCOUNT ON on all of your stored procedures. The biggest problems with your procedure are the WITH RECOMPILE and SET DATEFORMAT DMY. If you use WITH RECOMPILE, as Argyle mentioned, the execution plan will never be cached, and the sp itself must be compiled every time it is run. Removing this operator in itself will improve performance by a huge amount in this case. The SET DATEFORMAT DMY is just plain unnecessary in this case, and is sapping performance for no purpose whatsoever. Just remove those 2 operators, then try your your stored proc with and without SET NOCOUNT ON to see how performance varies. It will likely be a very minor difference. The real value of SET NOCOUNT ON is reducing network traffic by passing less data, which over a lot of SP calls adds up to a lot of saved network traffic.

]]>