Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

proc args vs. local variables

4 posts in Performance and Tuning Last posting was on 2008-06-26 14:06:09.0Z
sybfan Posted on 2008-06-25 20:23:50.0Z
Sender: 660e.4862a2fd.1804289383@sybase.com
From: sybfan
Newsgroups: sybase.public.ase.performance+tuning
Subject: proc args vs. local variables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4862a955.675f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Jun 2008 13:23:50 -0700
X-Trace: forums-1-dub 1214425430 10.22.241.41 (25 Jun 2008 13:23:50 -0700)
X-Original-Trace: 25 Jun 2008 13:23:50 -0700, 10.22.241.41
Lines: 4
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10975
Article PK: 89586

Is it still true in ase 15.x & 12.5.x, that in stored procs,
the optimizer will have a better chance of coming up with a
good perforimg query plan, if the queries uses the stored
proc's arguments instead of local variables as SARGS ?


"Mark A. Parsons" <iron_horse Posted on 2008-06-26 10:54:23.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: proc args vs. local variables
References: <4862a955.675f.1681692777@sybase.com>
In-Reply-To: <4862a955.675f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4863755f$1@forums-1-dub>
Date: 26 Jun 2008 03:54:23 -0700
X-Trace: forums-1-dub 1214477663 10.22.241.152 (26 Jun 2008 03:54:23 -0700)
X-Original-Trace: 26 Jun 2008 03:54:23 -0700, vip152.sybase.com
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10979
Article PK: 89589

In 12.5.x? yes

In 15.x? depends on the version as well as traceflag settings

Prior to 15.0.2 the optimizer works best when it can use the stored proc's input parameters. (Same as with 12.5.x)

With 15.0.2 there's a new feature called 'Deferred Optimization' that's suppose to allow the optimizer to take local
variables into consideration when compiling queries within a stored proc.

There's a small 2-paragraph blurb in the What's New manual for 15.0.2 (pg 135).

From what I've been able to ascertain Deferred Optimization is on by default in ASE 15.0.2, but can be disabled by
enabling traceflag 7730.

I've had to enable traceflag 7730 in a few dataservers because of a bug in Deferred Optimization ... stored procs
defined 'with recompile' will *not* recompile when they are run.

When traceflag 7730 is not enabled (ie, Deferred Compilation is enabled) I've seen mixed results coming out of the
optimizer ... sometimes it works as described, sometimes it doesn't ... ymmv.

And obviously (?) you still have the issue of the re-use of stored proc query plans. The first time the stored proc is
executed the optimizer will build a new query plan based on local variable values. Follow-on invocations of the same
stored proc will re-use this query plan (unless of course the proc is forced to recompile during follow-on invocations).
This is 'normal' stored procedure behaviour.

sybfan wrote:
> Is it still true in ase 15.x & 12.5.x, that in stored procs,
> the optimizer will have a better chance of coming up with a
> good perforimg query plan, if the queries uses the stored
> proc's arguments instead of local variables as SARGS ?


sybfan Posted on 2008-06-26 14:06:09.0Z
Sender: 660e.4862a2fd.1804289383@sybase.com
From: sybfan
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: proc args vs. local variables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4863a251.776.1681692777@sybase.com>
References: <4863755f$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Jun 2008 07:06:09 -0700
X-Trace: forums-1-dub 1214489169 10.22.241.41 (26 Jun 2008 07:06:09 -0700)
X-Original-Trace: 26 Jun 2008 07:06:09 -0700, 10.22.241.41
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10980
Article PK: 89590

Thanks for both Cory and Mark for looking into this for me.

I've also posted a question about how not in ( ) compares
with not ( ... in ( ) ) as where conds in a query. Would
either of you gentlemen have some thoughts on that ?

> In 12.5.x? yes
>
> In 15.x? depends on the version as well as traceflag
> settings
>
> Prior to 15.0.2 the optimizer works best when it can use
> the stored proc's input parameters. (Same as with 12.5.x)
>
> With 15.0.2 there's a new feature called 'Deferred
> Optimization' that's suppose to allow the optimizer to
> take local variables into consideration when compiling
> queries within a stored proc.
>
> There's a small 2-paragraph blurb in the What's New manual
> for 15.0.2 (pg 135).
>
> From what I've been able to ascertain Deferred
> Optimization is on by default in ASE 15.0.2, but can be
> disabled by enabling traceflag 7730.
>
> I've had to enable traceflag 7730 in a few dataservers
> because of a bug in Deferred Optimization ... stored procs
> defined 'with recompile' will *not* recompile when they
> are run.
>
> When traceflag 7730 is not enabled (ie, Deferred
> Compilation is enabled) I've seen mixed results coming out
> of the optimizer ... sometimes it works as described,
> sometimes it doesn't ... ymmv.
>
> And obviously (?) you still have the issue of the re-use
> of stored proc query plans. The first time the stored
> proc is executed the optimizer will build a new query
> plan based on local variable values. Follow-on
> invocations of the same stored proc will re-use this
> query plan (unless of course the proc is forced to
> recompile during follow-on invocations).
> This is 'normal' stored procedure behaviour.
>
> sybfan wrote:
> > Is it still true in ase 15.x & 12.5.x, that in stored
> > procs, the optimizer will have a better chance of coming
> > up with a good perforimg query plan, if the queries uses
> > the stored proc's arguments instead of local variables
> as SARGS ?


Cory Sane Posted on 2008-06-26 03:16:42.0Z
From: "Cory Sane" <cory!=sane>
Newsgroups: sybase.public.ase.performance+tuning
References: <4862a955.675f.1681692777@sybase.com>
In-Reply-To: <4862a955.675f.1681692777@sybase.com>
Subject: Re: proc args vs. local variables
Lines: 20
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48630a1a$1@forums-1-dub>
Date: 25 Jun 2008 20:16:42 -0700
X-Trace: forums-1-dub 1214450202 10.22.241.152 (25 Jun 2008 20:16:42 -0700)
X-Original-Trace: 25 Jun 2008 20:16:42 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10978
Article PK: 89591

I've spent 20 minutes trying to find documents for this @ sybooks online -
The section in the 12.5.1 manual that mentions this was rewritten for the
15.0 release and those statements are not mentioned.

I believe that it is still true because the ASE folks say that the
statistics are more important than ever in ASE 15x.

If someone can show that it is not true, please supply a little
documentation.

Cory Sane
[TeamSybase]

"sybfan" wrote in message news:4862a955.675f.1681692777@sybase.com...
> Is it still true in ase 15.x & 12.5.x, that in stored procs,
> the optimizer will have a better chance of coming up with a
> good perforimg query plan, if the queries uses the stored
> proc's arguments instead of local variables as SARGS ?