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.

Triggers Performance

2 posts in PB Infomaker Report Writer Last posting was on 1999-01-21 18:24:32.0Z
Pat Wright Posted on 1999-01-21 18:23:15.0Z
Newsgroups: sybase.public.infomaker.general
From: "Pat Wright" <Pwright@easydental.com>
Subject: Triggers Performance
Date: Thu, 21 Jan 1999 11:23:15 -0700
Lines: 59
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_EopE1sWR#GA.189@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6609
Article PK: 241595

I am having a problem with some triggers that are causing performance
problems on my SQLA5.5 # 1666, I have one table that has about 13,000
records and about 250 columns. I know there is a lot of columns for this
table but Due to the front end application I use this is how it is done. I
have two triggers on the table. They are both basically the same but one
updates if something changes and one is after an insert. here is what
happens. there is five fields that need to be added together and then the
total is placed into another field.
invamount (float)
invamount2(float)
invamount3(float)
invamount4(float)
latefeeamt(float)
Total(float)

The trigger takes all fields add's them and then places the amount in the
total. here are the two triggers I created one for Insert and one for
update.
Insert

Create trigger ConfigBill after insert
on table.config
referencing old as old_config new as new_config
for each row
begin
update table.config set

rma11amtc=isnull(invamount,0)+isnull(invamount2,0)+isnull(invamount3,0)+isnu
ll(invamount4,0)+isnull(latefeeamt,0)
where config.configtype='Billing'
end

Update

Create trigger Configbillup after update of invamount,
invamount2,invamount3,invamount4,latefeeamt on heat.config
referencing old as old_config new as new_config
for each row
begin
update heat.config set

rma11amtc=isnull(invamount,0)+isnull(invamount2,0)+isnull(invamount3,0)+isnu
ll(invamount4,0)+isnull(latefeeamt,0)
where config.configtype='Billing'
end

Any suggestions? to speed this up? I am new to triggers and tuning them
but I need something it is taking a long time to insert a row into the
database. Can I use a column list on the insert trigger like i did with the
update? If so I cannot find anything in the manual on it. :) Thanks in
advance.,


Pat Wright
Easy Dental Systems
Heat Database Administrator


Pat Wright Posted on 1999-01-21 18:24:32.0Z
Newsgroups: sybase.public.infomaker.general
From: "Pat Wright" <Pwright@easydental.com>
Subject: Re: Triggers Performance
Date: Thu, 21 Jan 1999 11:24:32 -0700
Lines: 66
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_18rsitWR#GA.209@forums.powersoft.com>
References: <348_EopE1sWR#GA.189@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6608
Article PK: 241594

I'm sorry I posted this in the wrong forum. I have added it to the
sqlanywhere forum. Sorry bout that. :)

Pat Wright wrote in message ...
> I am having a problem with some triggers that are causing performance
>problems on my SQLA5.5 # 1666, I have one table that has about 13,000
>records and about 250 columns. I know there is a lot of columns for this
>table but Due to the front end application I use this is how it is done. I
>have two triggers on the table. They are both basically the same but one
>updates if something changes and one is after an insert. here is what
>happens. there is five fields that need to be added together and then the
>total is placed into another field.
>invamount (float)
>invamount2(float)
>invamount3(float)
>invamount4(float)
>latefeeamt(float)
>Total(float)
>
>The trigger takes all fields add's them and then places the amount in the
>total. here are the two triggers I created one for Insert and one for
>update.
>Insert
>
>Create trigger ConfigBill after insert
>on table.config
>referencing old as old_config new as new_config
>for each row
>begin
> update table.config set
>
>rma11amtc=isnull(invamount,0)+isnull(invamount2,0)+isnull(invamount3,0)+isn
u
>ll(invamount4,0)+isnull(latefeeamt,0)
> where config.configtype='Billing'
>end
>
>Update
>
>Create trigger Configbillup after update of invamount,
>invamount2,invamount3,invamount4,latefeeamt on heat.config
>referencing old as old_config new as new_config
>for each row
>begin
> update heat.config set
>
>rma11amtc=isnull(invamount,0)+isnull(invamount2,0)+isnull(invamount3,0)+isn
u
>ll(invamount4,0)+isnull(latefeeamt,0)
> where config.configtype='Billing'
>end
>
>Any suggestions? to speed this up? I am new to triggers and tuning them
>but I need something it is taking a long time to insert a row into the
>database. Can I use a column list on the insert trigger like i did with the
>update? If so I cannot find anything in the manual on it. :) Thanks in
>advance.,
>
>
>Pat Wright
>Easy Dental Systems
>Heat Database Administrator
>
>
>