SP runs slow but code doesn't

I have an SP that I can run in query analyzer and it takes about 45secs.  Th
e
code copied from the SP runs in about 5 seconds in query analyzer.  There ar
e
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.  Ho
w
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

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... 
>
>
>