Dynamic SQL in SP Faster than Compiled SQL??!!! 
Author Message
 Dynamic SQL in SP Faster than Compiled SQL??!!!

We are doing some load testing and loaded up the FMStocks application with a
bunch of data.  After that some of the SPs ran very slowly.  After some
playing around, we discovered that building the SQL string dynamically and

The procedure is listed below.  The stocks table has a clustered index on
the ticker field and has 11 million rows.  The commented code is the
original.  This code causes an index scan and takes 28 seconds when the
parameter is at the end of the index.  The dynamic SQL does a index seek and
returns instantly.  This does not seem right.  Everything I have read tells
me that dynamic SQL will be slower.  WHAT IS GOING ON HERE??????

CREATE PROCEDURE Ticker_ListByTicker
(

)

WITH RECOMPILE
AS


/*
 select ticker, company, exchange
 from stocks  --WITH (NOLOCK)

*/






Mon, 26 Aug 2002 03:00:00 GMT
 Dynamic SQL in SP Faster than Compiled SQL??!!!

Because of the '+ "%"' added to the parameter value, you turn the comparison
into a comparison with an expression rather than a comparison with a
constant or a parameter.  In such a case, the optimizer doesn't know what
the value of the expression will be at execution time and can only guess at
what you are trying to do. That can lead to sub-optimal plans.  In your
dynamic query example, the optimizer has the constant and can figure out
what plan would work best for your specific query.  So this is a case where
the cost of dynamic SQL (ie, the cost of compiling each time the query is
executed) is lower than the cost of pre-compiled SQL (ie, where the
optimizer picked the most generic plan available instead of one for a
"typical" value.

By the way, this problem is at its worst when you are using LIKE.  That's
because unless the optimizer can examine the string you pass in, to see if
there are any leading wildcards, it must assume the worst and thus generates
a very poor plan for the actual value of the parameter.

--
All opinions expressed here are mine and mine alone

-----------
Show support for SQL Server and support the American Red Cross, order your
books from barnesandnoble.com at
http://bn.bfast.com/bfast/click/mid1349732?siteid=6710312&bfpage=h


Quote:
> We are doing some load testing and loaded up the FMStocks application with
a
> bunch of data.  After that some of the SPs ran very slowly.  After some
> playing around, we discovered that building the SQL string dynamically and

> The procedure is listed below.  The stocks table has a clustered index on
> the ticker field and has 11 million rows.  The commented code is the
> original.  This code causes an index scan and takes 28 seconds when the
> parameter is at the end of the index.  The dynamic SQL does a index seek
and
> returns instantly.  This does not seem right.  Everything I have read
tells
> me that dynamic SQL will be slower.  WHAT IS GOING ON HERE??????

> CREATE PROCEDURE Ticker_ListByTicker
> (

> )

> WITH RECOMPILE
> AS


> /*
>  select ticker, company, exchange
>  from stocks  --WITH (NOLOCK)

> */







Mon, 26 Aug 2002 03:00:00 GMT
 Dynamic SQL in SP Faster than Compiled SQL??!!!
So in this instance, assuming Jim would rather use a stored procedure, would
it be appropriate to add optimizer hints to the SP?

Blake


Quote:
> Because of the '+ "%"' added to the parameter value, you turn the
comparison
> into a comparison with an expression rather than a comparison with a
> constant or a parameter.  In such a case, the optimizer doesn't know what
> the value of the expression will be at execution time and can only guess
at
> what you are trying to do. That can lead to sub-optimal plans.  In your
> dynamic query example, the optimizer has the constant and can figure out
> what plan would work best for your specific query.  So this is a case
where
> the cost of dynamic SQL (ie, the cost of compiling each time the query is
> executed) is lower than the cost of pre-compiled SQL (ie, where the
> optimizer picked the most generic plan available instead of one for a
> "typical" value.

> By the way, this problem is at its worst when you are using LIKE.  That's
> because unless the optimizer can examine the string you pass in, to see if
> there are any leading wildcards, it must assume the worst and thus
generates
> a very poor plan for the actual value of the parameter.

> --
> All opinions expressed here are mine and mine alone

> -----------
> Show support for SQL Server and support the American Red Cross, order your
> books from barnesandnoble.com at
> http://bn.bfast.com/bfast/click/mid1349732?siteid=6710312&bfpage=h



> > We are doing some load testing and loaded up the FMStocks application
with
> a
> > bunch of data.  After that some of the SPs ran very slowly.  After some
> > playing around, we discovered that building the SQL string dynamically
and
> > executing it was faster than using a standard select ... WHERE Fld =

> > The procedure is listed below.  The stocks table has a clustered index
on
> > the ticker field and has 11 million rows.  The commented code is the
> > original.  This code causes an index scan and takes 28 seconds when the
> > parameter is at the end of the index.  The dynamic SQL does a index seek
> and
> > returns instantly.  This does not seem right.  Everything I have read
> tells
> > me that dynamic SQL will be slower.  WHAT IS GOING ON HERE??????

> > CREATE PROCEDURE Ticker_ListByTicker
> > (

> > )

> > WITH RECOMPILE
> > AS


> > /*
> >  select ticker, company, exchange
> >  from stocks  --WITH (NOLOCK)

> > */







Mon, 26 Aug 2002 03:00:00 GMT
 Dynamic SQL in SP Faster than Compiled SQL??!!!
I would not use hints except as a last resort.

--
All opinions expressed here are mine and mine alone

-----------
Show support for SQL Server and support the American Red Cross, order your
books from barnesandnoble.com at
http://bn.bfast.com/bfast/click/mid1349732?siteid=6710312&bfpage=h


Quote:
> So in this instance, assuming Jim would rather use a stored procedure,
would
> it be appropriate to add optimizer hints to the SP?

> Blake



> > Because of the '+ "%"' added to the parameter value, you turn the
> comparison
> > into a comparison with an expression rather than a comparison with a
> > constant or a parameter.  In such a case, the optimizer doesn't know
what
> > the value of the expression will be at execution time and can only guess
> at
> > what you are trying to do. That can lead to sub-optimal plans.  In your
> > dynamic query example, the optimizer has the constant and can figure out
> > what plan would work best for your specific query.  So this is a case
> where
> > the cost of dynamic SQL (ie, the cost of compiling each time the query
is
> > executed) is lower than the cost of pre-compiled SQL (ie, where the
> > optimizer picked the most generic plan available instead of one for a
> > "typical" value.

> > By the way, this problem is at its worst when you are using LIKE.
That's
> > because unless the optimizer can examine the string you pass in, to see
if
> > there are any leading wildcards, it must assume the worst and thus
> generates
> > a very poor plan for the actual value of the parameter.

> > --
> > All opinions expressed here are mine and mine alone

> > -----------
> > Show support for SQL Server and support the American Red Cross, order
your
> > books from barnesandnoble.com at
> > http://bn.bfast.com/bfast/click/mid1349732?siteid=6710312&bfpage=h



> > > We are doing some load testing and loaded up the FMStocks application
> with
> > a
> > > bunch of data.  After that some of the SPs ran very slowly.  After
some
> > > playing around, we discovered that building the SQL string dynamically
> and
> > > executing it was faster than using a standard select ... WHERE Fld =

> > > The procedure is listed below.  The stocks table has a clustered index
> on
> > > the ticker field and has 11 million rows.  The commented code is the
> > > original.  This code causes an index scan and takes 28 seconds when
the
> > > parameter is at the end of the index.  The dynamic SQL does a index
seek
> > and
> > > returns instantly.  This does not seem right.  Everything I have read
> > tells
> > > me that dynamic SQL will be slower.  WHAT IS GOING ON HERE??????

> > > CREATE PROCEDURE Ticker_ListByTicker
> > > (

> > > )

> > > WITH RECOMPILE
> > > AS


> > > /*
> > >  select ticker, company, exchange
> > >  from stocks  --WITH (NOLOCK)

> > > */


ticker





Mon, 26 Aug 2002 03:00:00 GMT
 Dynamic SQL in SP Faster than Compiled SQL??!!!
Have a look a back a couple of days, this was asked recently.

Basically as the variable is being modified the query optimiser can't
make a sensible decision regarding the potential value/distribution of
the column so it excludes it from the index choice.

The suggested workaround was the one you have of putting the statement
into an exec'd variable.

Regards

Paul Hatcher, MCSD
Principal Consultant
Graduate Associates Ltd



Tue, 27 Aug 2002 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Is Dynamic SQL String Compiled?

2. Error Compiling Dynamic SQL

3. Help with compile and link Dynamic SQL routines

4. SQL SP compiling anomaly

5. Stored Procs faster than Dynamic SQL?

6. CLI vs dynamic SQL - which is faster?

7. SQL faster than SP in QA

8. T-SQL fast sp VERY slow

9. alternative to Dynamic SQL SP

10. Dynamic SQL string building withinin SP

11. Using dynamic SQL better than using an sp?

12. Converting SP to Dynamic SQL


 
Powered by phpBB® Forum Software