Re: tuning stored procedure, variables and different optimisers

On Thu, 15 Nov 2007 13:42:36 -0800 (PST), codefragment@xxxxxxxxxxxxxx
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
.

Relevant Pages

  • Re: index bloat?
    ... execution plan is, how can I get the query optimizer to generate it - *without the hints*? ... Because some users of the DB are using report designing software that will just generate the join query with no hints, and I don't want them sitting there for hours when they don't have to. ... Does the optimizer not choose the merge join because it requires a bookmark lookup? ...
    (comp.databases.ms-sqlserver)
  • Re: jslint doesnt like my syntax for conditional object creation
    ... Section 10.1.3) to create properties of the Variable object, ... the execution of code for the execution context. ... var a = null; ... the parser fetching all var operators and pre-declaring them. ...
    (comp.lang.javascript)
  • Re: tuning stored procedure, variables and different optimisers
    ... declare @var int ... It's not very odd once you know how that the optimizer builds the ... plan for entire procedure or a batch. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: setTimeout and an objects methods
    ... instance property at the time of its execution. ... There is also the question of how often - trigger - will be called. ... var f1 = new Foo; ... What is also happening is that a closure is formed by the ...
    (comp.lang.javascript)
  • Re: javascript debugger
    ... "unused" identifiers (as it could get quite tedious to manually check all of the possible branches of execution with breakpoints). ... Too bad that initialized, yet unused variables can be quite daunting for an application, specially when "caught" in a closure. ... var a = Array; ...
    (comp.lang.javascript)