> 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)

> */

> 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)

> > */

> 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