tuning stored procedure, variables and different optimisers

[codefragment@googlemail.com]

Hi
I've heard 2 things recently, can I confirm if their true/false?

(1) If you have a stored procedure and you want to optimise it you can
call exec proc1,
you could also use define/set for each of the variables and copy the
code into query analyser,
this then makes it easier to tune. However the optimiser works
differently for these variables than it does for variables passed into
the query via exec and will produce a less optimal
plan

(2) There is a different optimiser used in query analyser than that
used otherwise? A colleague
had a problem where a stored procedure called from dotnet code was
running slowly but
one run from query analyser via exec, with exactly the same arguments,
was running quickly

ta

[Hugo Kornelis]

Re: tuning stored procedure, variables and different optimisers

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