This depends on your query.
Usings physical constants rather than variables could quite possibly result
in a better plan which could result in a massive performance improvement.
Case 1
SELECT * FROM mytable WHERE dfdf = 'xyd'
Case 2
In the first Case (1) the optimiser knows what value you are looking for, so
it can use the distribution stats efficiently to determine where about the
row is and if using an index is better...
In the second Case (2) the optimiser only knows your accessing the table
view the column dfdf.
Try using sp_executesql instead of EXEC.
Also, parameterise the query...
I'd be interested to know if that makes a difference.
When using sp_executesql the plan resulting from the dynamic sql can be
reused (EXEC won't).
--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
Mobile: +44 (0) 796 816 0362
Office: +44 (0) 1582 768450
Fax: +44 (0) 845 458 8646
> hello
> I have a fairly complicated query, containing 4 inner joins. I made a
stored
> procedure out of it like so:
> create procedure sp_big_query
> as
> select .... blah blah ..p1...blah blah...
> then I made a dummy stored procedure that uses 'exec' with a particular
> param value hardcoded..
> create procedure sp_big_query2
> as
> exec ( ' select .... blah blah ')
> what I was trying to do was run the identical query with the same
parameter
> value, but in two different ways... one static and the other dynamic
(using
> exec)
> the results were staggering... the exec was so much quicker !!
> Q: why? I thought stored procedures were supposed to be faster than
dynamic
> SQL ?
> thanks
> A
by