Posted by Hugo Kornelis on 11/15/07 23:40

On Thu, 15 Nov 2007 13:42:36 -0800 (PST), codefragment@googlemail.com
wrote:

>> There is some truth here. When the value of the parameters is
>> available to the optimizer at compile time
>
>but what I mean is not the parameters, but something like this
>
>declare @var int
>set @var = 1
>
>select * from table where somecolumn=@var
>
>
>as opposed to a stored procedure where @var is a parameter

Hi,

If @var is a locally declared variable, its value is not yet known at
execution time (since the whole batch is compiled at once, before
execution starts). So the optimization will be based on general
statistics on the distribution of somecolumn.

If @var is a parameter to a stored procedure, its value is known at
execution time. The proc is compiled the first time it is called, and at
that time the value for the parameter is known. The optimizer will
create a plan that is optimized for the specific value. Note that in a
large majority of cases, the end result will be the same plan - but not
always.

In both cases, the plan is retained in the procedure cache, and reused
when an identical batch is executed in the first case, or when the same
proc is executed in the second case. The former is not a problem, the
latter *usually* neither - but in some cases, the execution plan that is
optimal for @var = 1 might be very slow for @var = 2. In those cases,
you'll see extremely slow execution if the proc happens to be called
with @var = 1 first, and with @var = 2 later.

This feature is called parameter sniffing. You'll find lots more info
when you search for it on google. And though I doon't have my copy of
Inside SQL Server at hand, I think that this is what you are referring
to.

> I'd be interested in knowing what
>Oracle\mySQL do.

I guess you'll have to ask that in another group.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]