|
|
Fast SQL, slow storedproc? |
|
Author |
Message |
willpops
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
|
|
|
|
|
|