| Author |
SP runs slow but code doesn't
|
|
|
| I have an SP that I can run in query analyzer and it takes about 45secs. The
code copied from the SP runs in about 5 seconds in query analyzer. There are
a couple datetime parameters passed to the SP. I have these as variables in
the code that is copied. I will note that the stats/IO of the SP are much
greater than the code ran by itself. I think that's the issue. The query
plans for each are about 150 lines and from what I can tell are identical.
But one causes a great deal more reads (for the SP) than the other (for the
code). Can someone tell me why this is? I rebuild indexes on weekends. How
can I fix it so that the SP runs as fast as its code copied to another query
analyzer window? I traced for recompiles as well...didn't see any.
Thanks,
Van
| |
| Bud Y. Zer 2005-11-29, 8:23 pm |
| Did you try it *with* recompile?
You are probably not recompiling and therefore using a bad cached plan.
"Van" <Van@discussions.microsoft.com> wrote in message
news:0B3AD70A-7428-44A4-BF9C- 2FECD4AB599A@microso
ft.com...
>I have an SP that I can run in query analyzer and it takes about 45secs.
>The
> code copied from the SP runs in about 5 seconds in query analyzer. There
> are
> a couple datetime parameters passed to the SP. I have these as variables
> in
> the code that is copied. I will note that the stats/IO of the SP are much
> greater than the code ran by itself. I think that's the issue. The query
> plans for each are about 150 lines and from what I can tell are identical.
> But one causes a great deal more reads (for the SP) than the other (for
> the
> code). Can someone tell me why this is? I rebuild indexes on weekends.
> How
> can I fix it so that the SP runs as fast as its code copied to another
> query
> analyzer window? I traced for recompiles as well...didn't see any.
>
> Thanks,
>
> Van
| |
|
| Yes, I've also tried doing an sp_recomplile on the SP and running it a few
times. No change.
"Bud Y. Zer" wrote:
> Did you try it *with* recompile?
> You are probably not recompiling and therefore using a bad cached plan.
>
> "Van" <Van@discussions.microsoft.com> wrote in message
> news:0B3AD70A-7428-44A4-BF9C- 2FECD4AB599A@microso
ft.com...
>
>
>
|
|
|
|