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.

inserted and deleted tables in trigger

2 posts in General Discussion Last posting was on 2009-06-16 21:23:18.0Z
Anna Posted on 2009-06-16 20:45:19.0Z
Sender: 6e8f.4a380041.1804289383@sybase.com
From: Anna
Newsgroups: sybase.public.ase.general
Subject: inserted and deleted tables in trigger
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a3803b6.7118.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-Trace: 16 Jun 2009 13:42:30 -0700, forums-3-dub.sybase.com
Lines: 8
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-Trace: 16 Jun 2009 13:42:31 -0700, forums-3-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 16 Jun 2009 13:45:19 -0700
X-Trace: forums-1-dub 1245185119 10.22.108.75 (16 Jun 2009 13:45:19 -0700)
X-Original-Trace: 16 Jun 2009 13:45:19 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27871
Article PK: 77120

Hi! I've always used inserted and deleted table but never
really thought about this until one of the triggers showed a
significant slow down.

the query plan showed total estimated I/O cost for statement
that selects from inserted or deleted table was 1477840. Is
there a way to fix this, can I index the inserted table? any
suggestions would be greatly appreciated!


"Mark A. Parsons" <iron_horse Posted on 2009-06-16 21:23:18.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: inserted and deleted tables in trigger
References: <4a3803b6.7118.1681692777@sybase.com>
In-Reply-To: <4a3803b6.7118.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090616-0, 06/16/2009), Outbound message
X-Antivirus-Status: Clean
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a380ca5$1@forums-3-dub.sybase.com>
X-Original-Trace: 16 Jun 2009 14:20:37 -0700, vip152.sybase.com
Lines: 48
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-Trace: 16 Jun 2009 14:20:38 -0700, forums-3-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 16 Jun 2009 14:23:18 -0700
X-Trace: forums-1-dub 1245187398 10.22.108.75 (16 Jun 2009 14:23:18 -0700)
X-Original-Trace: 16 Jun 2009 14:23:18 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27873
Article PK: 77123

Can you index the inserted/deleted tables? No.

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

As for your performance problem it's a little hard(er) to say what the problem could be without more details.

- are there a *LOT* of records in the inserted/deleted table? [For large volumes of records you may need to consider
reducing the number of records affected by the parent DML statement; alternatively look at other P&T concepts for
improving performance of queries that access the inserted/deleted tables.]

- are you joining the inserted/deleted table to other/permanent tables and if so, does the query plan show the
inserted/deleted table as driving the query? [Usually - but not always - you want to see the (smaller) inserted/deleted
table driving a query; this tends to insure that indexes can be utilized on the other tables. Also, since a 'set
forceplan on' at a higher level can affect join orders in trigger-based queries, consider writing your trigger-based
queries so that the tables in the FROM clause are in the desired join order - just in case the parent process has 'set
forceplan on' enabled; alternatively you may need to force the join order in the trigger to insure desired performance.]

- are you joining the inserted table to the deleted table in an UPDATE trigger? [For moderate numbers of records in the
inserted/updated tables this can lead to a poorly performing cartesian product-like query; best solution is to replace
the 'inserted' table with a reference to the permanent underlying table, and (usually) insure the deleted table is
driving the query; this should allow for use of the permanent table's PK/unique index.]

Obviously (?) other basic P&T concepts apply ... make sure join columns have matching/equivalent datatypes, make sure
usable indexes are in place (and stats are up-to-date), try to stick with ANSI-compliant queries (ASE 15's optimizer is
less forgiving of non-ANSI-compliant queries - which can lead to performance issues), etc.

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

As for the 'estimated I/O cost' ... these estimates are just that ... *estimates*.

I've seen estimates of 2+ billion IO's ... where the query ran in less than a second and required a few hundred IOs.

I've seen estimates of 2000 IO's ... where the query ran really, Really, REALLY long and required 100's of millions of IOs.

To get a better understanding of potential performance issues you'll want to look at the *actual* IOs for the query(s)
in question. You can obtain this by a) running the parent DML statement while 'set statistics io on' is enabled, or b)
by pulling the performance stats from the master..monSysStatement (MDA) table.

Anna wrote:
> Hi! I've always used inserted and deleted table but never
> really thought about this until one of the triggers showed a
> significant slow down.
>
> the query plan showed total estimated I/O cost for statement
> that selects from inserted or deleted table was 1477840. Is
> there a way to fix this, can I index the inserted table? any
> suggestions would be greatly appreciated!