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.

Update Trigger resulting sub optimal plan

3 posts in General Discussion Last posting was on 2009-07-31 12:19:57.0Z
Matt Posted on 2009-07-29 12:28:32.0Z
Sender: 6749.4a703ef8.1804289383@sybase.com
From: Matt
Newsgroups: sybase.public.ase.general
Subject: Update Trigger resulting sub optimal plan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a70406f.6881.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 29 Jul 2009 05:28:32 -0700
X-Trace: forums-3-dub.sybase.com 1248870512 10.22.241.188 (29 Jul 2009 05:28:32 -0700)
X-Original-Trace: 29 Jul 2009 05:28:32 -0700, forums-3-dub.sybase.com
Lines: 23
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28067
Article PK: 77316

Hi Everyone,

There is a update trigger, which is being fired by a point
query and this trigger updates the table in another
database. Now if I run update query several time, first time
it runs fine using indexes etc. and then it goes for
tablescan in other execution with the same parameters etc.

When I run sp_recompile on base table before executing the
update statemment then I get optimal plan for the update
statement including trigger.

Sybase Version 12.5.4 on Linux

Index selectivity is at .33 and range selectivity as .25
with histogram evenly distributed. Index stats updated and
cluster ratio close to 1.0 so no fragmentation.


Any Idea where are things going wrong..

Regards,
Mat


"Mark A. Parsons" <iron_horse Posted on 2009-07-29 13:19:48.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Update Trigger resulting sub optimal plan
References: <4a70406f.6881.1681692777@sybase.com>
In-Reply-To: <4a70406f.6881.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090727-0, 07/27/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a704c74$1@forums-3-dub.sybase.com>
Date: 29 Jul 2009 06:19:48 -0700
X-Trace: forums-3-dub.sybase.com 1248873588 10.22.241.152 (29 Jul 2009 06:19:48 -0700)
X-Original-Trace: 29 Jul 2009 06:19:48 -0700, vip152.sybase.com
Lines: 72
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28068
Article PK: 77313

In your description you state that you ran the UPDATE statement several times.

Did you update the same exact records each time?

Is there any chance some other process could have also fired a copy of the trigger while you were running your tests?
I'm wondering if you could be picking up different query plans when you run your UPDATE. You could run the following to
see if you have more than 1 copy of the trigger stored in procedure cache:

======================
use <dbname>
go
dbcc traceon(3604)
go
declare @dbid int,
@trigid int

select @dbid = db_id(),
@trigid = object_id('<trigger_name>')

dbcc procbuf(@dbid,@trigid)
go
======================

-------------

You state that after running sp_recompile on the base table that the trigger runs with the good query plan.

Do repeated updates also run with the good query plan?

-------------

While you're obviously (?) seeing a difference in index usage, I would also want to look at the join order of tables in
the parent UPDATE as well as the trigger's queries.

Can you verify that the parent processes that perform the UPDATE are not running with 'set forceplan on'? I've found
that a 'set forceplan on' in the parent process will trickle down to the queries in the trigger code - which may not
provide the best query plan for queries in the trigger.

Does the trigger have any if/then/else logic which causes different queries to fire based on the attributes of the
updated record(s)?

How many records are affected by the UPDATE in both cases (good vs bad query plan)?

Does the trigger query in question contain a join between the 'inserted' and 'deleted' tables?

Matt wrote:
> Hi Everyone,
>
> There is a update trigger, which is being fired by a point
> query and this trigger updates the table in another
> database. Now if I run update query several time, first time
> it runs fine using indexes etc. and then it goes for
> tablescan in other execution with the same parameters etc.
>
> When I run sp_recompile on base table before executing the
> update statemment then I get optimal plan for the update
> statement including trigger.
>
> Sybase Version 12.5.4 on Linux
>
> Index selectivity is at .33 and range selectivity as .25
> with histogram evenly distributed. Index stats updated and
> cluster ratio close to 1.0 so no fragmentation.
>
>
> Any Idea where are things going wrong..
>
> Regards,
> Mat


Matt Posted on 2009-07-31 12:19:57.0Z
Sender: 69a2.4a7041d2.1804289383@sybase.com
From: Matt
Newsgroups: sybase.public.ase.general
Subject: Re: Update Trigger resulting sub optimal plan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a72e16d.303.1681692777@sybase.com>
References: <4a704c74$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 31 Jul 2009 05:19:57 -0700
X-Trace: forums-3-dub.sybase.com 1249042797 10.22.241.188 (31 Jul 2009 05:19:57 -0700)
X-Original-Trace: 31 Jul 2009 05:19:57 -0700, forums-3-dub.sybase.com
Lines: 100
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28076
Article PK: 77323

Thanks Mark, As you suggested there was not join in the
update statement it caused trigger to fire.

I have noticed it uses unexpected query plans in triggers
presumably because they contain joins to syslogs and size of
syslogs constantly changing.

I had to force the plan in the trigger and It worked.

Thanks a lot for your help.

Cheers
Matt

> In your description you state that you ran the UPDATE
> statement several times.
>
> Did you update the same exact records each time?
>
> Is there any chance some other process could have also
> fired a copy of the trigger while you were running your
> tests? I'm wondering if you could be picking up different
> query plans when you run your UPDATE. You could run the
> following to see if you have more than 1 copy of the
> trigger stored in procedure cache:
>
> ======================
> use <dbname>
> go
> dbcc traceon(3604)
> go
> declare @dbid int,
> @trigid int
>
> select @dbid = db_id(),
> @trigid = object_id('<trigger_name>')
>
> dbcc procbuf(@dbid,@trigid)
> go
> ======================
>
> -------------
>
> You state that after running sp_recompile on the base
> table that the trigger runs with the good query plan.
>
> Do repeated updates also run with the good query plan?
>
> -------------
>
> While you're obviously (?) seeing a difference in index
> usage, I would also want to look at the join order of
> tables in the parent UPDATE as well as the trigger's
> queries.
>
> Can you verify that the parent processes that perform the
> UPDATE are not running with 'set forceplan on'? I've
> found that a 'set forceplan on' in the parent process
> will trickle down to the queries in the trigger code -
> which may not provide the best query plan for queries in
> the trigger.
>
> Does the trigger have any if/then/else logic which causes
> different queries to fire based on the attributes of the
> updated record(s)?
>
> How many records are affected by the UPDATE in both cases
> (good vs bad query plan)?
>
> Does the trigger query in question contain a join between
> the 'inserted' and 'deleted' tables?
>
>
>
>
> Matt wrote:
> > Hi Everyone,
> >
> > There is a update trigger, which is being fired by a
> > point query and this trigger updates the table in
> > another database. Now if I run update query several time
> > , first time it runs fine using indexes etc. and then it
> > goes for tablescan in other execution with the same
> > parameters etc.
> > When I run sp_recompile on base table before executing
> > the update statemment then I get optimal plan for the
> > update statement including trigger.
> >
> > Sybase Version 12.5.4 on Linux
> >
> > Index selectivity is at .33 and range selectivity as .25
> > with histogram evenly distributed. Index stats updated
> > and cluster ratio close to 1.0 so no fragmentation.
> >
> >
> > Any Idea where are things going wrong..
> >
> > Regards,
> > Mat