Slow execution of stored procedure

[wawalter]

Hello,

I have an sp that runs in less than a second if run from query analyzer, but if the same sp on the same db server with the same parameters is run from a web site or windows app it takes about 5 minutes to run. Other sp's run at the same speed whether called from the web site or windows app or query analyzer.

Does anyone have any ideas on why the same code runs at different speeds depending upon where it is called from?

Thanks,
Bill

 

[SQLDenis]

Parameter sniffing, google for it

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

here is a good explanation for you

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[wawalter]

I tried using different names for the parameters and the variables actually used in the sp, which was one way to deal with that issue if I read it correctly, and it didn't make any difference in the execution speed.

Thanks for the suggestion.

 

[wawalter]

A co-worker seems to have found a fix for this problem.

SET ARITHABORT ON

We aren't sure why it fixed it, but it did.

Thanks.