Fast SQL, slow storedproc?  
Author Message
willpops





PostPosted: Mon Aug 27 10:24:02 CDT 2007 Top

SQL Server >> Fast SQL, slow storedproc?

I have a storedproc that takes "forever" to run. However, the SQL inside is
fairly simple, consisting of a series of SELECT...INTOs that build up a table
only 2500 rows long. There's only one time consuming query, and when I run
that by hand it only takes eight seconds, the other seven inside finish in
less than a second. That jives with what I think should happen, it should
take maybe 15 seconds to run, but instead times out after 2 minutes.

Can anyone offer some suggestions here?

Maury

SQL Server280  
 
 
JochenWezel





PostPosted: Mon Aug 27 10:24:02 CDT 2007 Top

SQL Server >> Fast SQL, slow storedproc? Do you use sp_executesql somewhere? if yes, then you've run into the same
problem I posted a few minutes ago...



> I have a storedproc that takes "forever" to run. However, the SQL inside is
> fairly simple, consisting of a series of SELECT...INTOs that build up a table
> only 2500 rows long. There's only one time consuming query, and when I run
> that by hand it only takes eight seconds, the other seven inside finish in
> less than a second. That jives with what I think should happen, it should
> take maybe 15 seconds to run, but instead times out after 2 minutes.
>
> Can anyone offer some suggestions here?
>
> Maury
 
 
MauryMarkowitz





PostPosted: Mon Aug 27 10:52:08 CDT 2007 Top

SQL Server >> Fast SQL, slow storedproc?

> Do you use sp_executesql somewhere? if yes, then you've run into the same
> problem I posted a few minutes ago...

I'm not sure what that is, but the SP has nothing but selects in it (with
two parameters) and I call it thus:

exec pGenerateHPLPriceList '8/24/07', 13

Maury
 
 
MauryMarkowitz





PostPosted: Mon Aug 27 10:56:01 CDT 2007 Top

SQL Server >> Fast SQL, slow storedproc? Ahh...

Taking a hint from your other thread, I googled up "recompile". Try this...

exec sp_recompile yourprocnamehere

Mine went from 3:09 to 0.06. Might want to try it :-)

Maury
 
 
JochenWezel





PostPosted: Mon Aug 27 10:56:03 CDT 2007 Top

SQL Server >> Fast SQL, slow storedproc? Okay, then it might be another problem. Sorry.




>
> > Do you use sp_executesql somewhere? if yes, then you've run into the same
> > problem I posted a few minutes ago...
>
> I'm not sure what that is, but the SP has nothing but selects in it (with
> two parameters) and I call it thus:
>
> exec pGenerateHPLPriceList '8/24/07', 13
>
> Maury