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?
MauryDo you use sp_executesql somewhere? if yes, then you've run into the same
problem I posted a few minutes ago...
"Maury Markowitz" wrote:
> 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|||"Jochen Wezel" wrote:
> 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|||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|||Okay, then it might be another problem. Sorry.
"Maury Markowitz" wrote:
> "Jochen Wezel" wrote:
> > 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