Query Analyser running a stored procedure

[Tracey]

Having a strange problem and would really like some advice
on how to go about solving it.

I have a stored procedure which has parameter values which
then goes onto do a select statement. The execution of
this stored procedure takes a long time (18 mins!).
However, when I run just the select statement from the
stored procedure in SQL Query Analyser this is executed
within 4 secs.

I have viewed both the execution plans and they aren't the
same. The stored procedure's execution plan is not
utilising indexes where as the select statement execution
plan is utilising the indexes. I have tried running the
stored procedure with 'with recompile'. Have also used the
command 'update statistics' and have dropped and recreated
the views and stored procedures. None of these have made
any difference to the stored procedure.

Any ideas on what I can do to get this stored procedure to
improve to same as the select statement????
Thanks
Tracey

[BP Margolin]

Tracey,

I'm going to assume that when you execute the SELECT in QA that you replace
the parameter with the actual value of the parameter..

If that assumption is correct, then keep in mind that the query optimizer
has more information when you provide a hard-coded value than when you
supply a parameter. The query optimizer can, and will, do "parameter
sniffing" when creating a query plan for a stored procedure, but it still
must take into account that the next invocation of the stored procedure
might have a very different value for the parameter, and so the query
optimizer has to be at least a little conservative when generating a query
plan using a parameter. Using a constant allows the query optimizer to
optimize specifically for the value specified.

If you have the option to use dynamic SQL in your stored procedures, then
try creating the query as a string, with the value of the parameter replaced
by it's actual value ... something like replacing:

select ... from ... where c1 = @c1

with

exec ('select ... from ... where c1 = ' + @c1)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Tracey" <tracey.v...@rtix.co.uk> wrote in message
news:03e901c2a5dc$e6986390$cef82ecf@TK2MSFTNGXA08...