help store procedure timeout

hi
I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
takes 7 minutes to run but if I get the query and tput it to the
queryanalizer it takes 16 seconds!!! I dont understand why, two days ago
that SP was working right but sudenly got slow!!! i dont know what to do ,
please helpppppppppp
Antother tip, my asp.net application reports the timeout problem but If I
run the same app with a copy of that DataBase on my nootebook (xp) using
the debuger all works fine!!! I am really confused
THANKS for your help
SergioHi Sergio
How did you run the query in Query Analyser? If you ran the query with hard
coded parameter values it is not the same as running the stored procedure.
Have you updated statistics and defragemented your indexes? You should have
a
regular maintenance plan to do this and also do this if you make significant
changes to the data (such as a batch update!). If the indexes and statistics
are up-to-date you could have a poor query plan, try executing the procedure
with the with RECOMPILE option or use sp_recompile to force a new query plan
.
If your code does not branch in the stored procedure you may be better
splitting it into multiple sub-procedures.
John
"SergioT" wrote:

> hi
> I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
> takes 7 minutes to run but if I get the query and tput it to the
> queryanalizer it takes 16 seconds!!! I don′t understand why, two days ago
> that SP was working right but sudenly got slow!!! i don′t know what to do
,
> please helpppppppppp
> Antother tip, my asp.net application reports the timeout problem but If
I
> run the same app with a copy of that DataBase on my nootebook (xp) using
> the debuger all works fine!!! I am really confused
>
> THANKS for your help
> Sergio
>
>|||Hi Sergio
How did you run the query in Query Analyser? If you ran the query with hard
coded parameter values it is not the same as running the stored procedure.
Have you updated statistics and defragemented your indexes? You should have
a
regular maintenance plan to do this and also do this if you make significant
changes to the data (such as a batch update!). If the indexes and statistics
are up-to-date you could have a poor query plan, try executing the procedure
with the with RECOMPILE option or use sp_recompile to force a new query plan
.
If your code does not branch in the stored procedure you may be better
splitting it into multiple sub-procedures.
John
"SergioT" wrote:

> hi
> I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
> takes 7 minutes to run but if I get the query and tput it to the
> queryanalizer it takes 16 seconds!!! I don′t understand why, two days ago
> that SP was working right but sudenly got slow!!! i don′t know what to do
,
> please helpppppppppp
> Antother tip, my asp.net application reports the timeout problem but If
I
> run the same app with a copy of that DataBase on my nootebook (xp) using
> the debuger all works fine!!! I am really confused
>
> THANKS for your help
> Sergio
>
>