Slow stored procedures

[iker3000]

Hi!

My problem is the following. If I run a piece of SQL code as part of a stored procedure, it takes 50 seconds to complete. But when running directly from the Query Analyzer, it's only 13 seconds. What's more, if it is embedded in a stored procedure within an EXEC '...' statement, it's only 14 seconds!!!

Does anyone have any idea on what's going on?

Thanks, Iker

 

[tlbroadbent]

Perhaps, we can help if you post the code. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[iker3000]

It's very long and complicated (125k). Actually it does complicated financial computations according to the Hungarian accounting laws. Do you really want to see it ?

1)
create procedure sp_accounting
begin
blahblah
end

2)
create procedure sp_accounting
begin
EXEC('blahblah')
end

The question is that why is 1) extremely (5 times!) slower than 2). (It would be OK if 2) was slower.)

 

[fluteplr]

This is a total stab in the dark, but try adding the
'with recompile' option to the stored proc and see if it makes any difference.

 

[tlbroadbent]

Fluteplr made an excellent suggestion. The ad hoc query in Query Analyzer and the Exec(<sql statement>) will both be compiled at execution time. The stored procedure may have poor query plan that recompiling will replace.

You could use Query Analyzer to analyze the query plan for the SP as well as the ad hoc query. Select Query | Display Estimated Query Plan from the QA menu. Look for the high cost query activities in the plan. For instance, check for table or index scans where index seeks would be expected. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[iker3000]

Thank you for your reply. I restarted the server and it solved the problem: now both versions complete in 14 seconds.