Quick query slow stored procedure...

[GShort]

Hello all,

We currently have a stored procedure that is giving us fits. We use this stored procedure on a web site to give our sale staff there stats.

This stored procedure seems to run fine at first but slows done after time. We found that if we run the stored procedures code (we physicaly change the website to run from the SQL code on the site instead of the stored procedure) the stored procedure then runs fast again after changing it back.

Any Suggestions would be great.

 

[ptheriault]

What is the time frame over the slow down. Is it over the course of a single day or many days?
How often do you update your statistics?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[GShort]

It is usally about a day and the data is is off of live data. There are two variables of start date and end date.

 

[ptheriault]

A few things you can look at.

1. If you are experiencing the slow down by the end of the day. Run update statistics on the tables involved in the sp.

2. Check for parameter sniffing.

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[GShort]

Well I found this and it great that I told the web programmer and he took credit for it too .

http://www.sql-server-performance.com/stored_procedures.asp

Anyway here is the link that I gave them and specifically the WITH RECOMPILE command in the stored procedures.

 

[unclejimbob]

You can get back that credit by mentioning that WITH RECOMPILE is only one option, another would be to use something like...

OPTION (HASH JOIN)

...to force a particular join approach within the SQL statement(s) - if that is where the original problem lies