dumb question?

dumb question?

Post by Aaro » Thu, 01 Nov 2001 15:10:58



hello

I have a fairly complicated query, containing 4 inner joins. I made a stored
procedure out of it like so:


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

 
 
 

dumb question?

Post by Tony Rogerso » Thu, 01 Nov 2001 16:28:41


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