I just a developer, so go easy on me.
I have a situation where I exec. a stored
procedure in query analyzer and it takes
41 seconds to complete.

Then when running the EXACT same query
hardcoding the parameters outside of the
stored procedure and it takes 7 seconds!

I don't think resultset caching is the because
I rerun both over and over and get the same
execution times.

The sql is selecting from a remote table, but
no fancy joins.

I ran the execution plan on both the query and the strored proc. and the only difference I see is the statistics on the remote query. In the stored proc. the ESTIMATED ROW COUNT and ROW COUNT are around 7 million, whereas the plain query's ESTIMATED ROW COUNT and ROW COUNT are around 7 thousand.

I hope people find this topic interesting, because I am in dire need of an answer!! I would hate to avoid using stored procs for perfomance reasons. Thank you.