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.

Another way to bypass triggers

2 posts in Trigger Last posting was on 2004-09-22 13:00:14.0Z
francois Posted on 2004-09-20 12:47:48.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
Subject: Another way to bypass triggers
Date: Mon, 20 Sep 2004 20:47:48 +0800
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <414ed0fa@solutions.advantagedatabase.com>
X-Trace: 20 Sep 2004 06:45:46 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:132
Article PK: 1136200

Hi
I want to bypass triggers for global updates.
Most of my triggers look like this:

UPDATE OtherTable
SET Total=(
SELECT SUM(amount)
FROM thistable
WHERE smonth='January 2004')
WHERE smonth='January 2004'

So a global change like

UPDATE thistable
SET InUse = True
WHERE (InUse IS NULL
OR InUse = False)

causes SUM(amount) to be evaluated
for each record affected.
A typical global update is 200+ times faster
without ths type of trigger (in my tests)

I want to set field Global in SysTable to True
and then modify the trigger like this

UPDATE OtherTable
SET Total=(
SELECT SUM(amount)
FROM thistable
WHERE smonth='January 2004')
WHERE (SELECT Global FROM SysTable) = False
AND smonth='January 2004'

Thus the trigger should not fully execute when
Global = true
However it must still evaluate Global for every
record affected.

Is there someone with a better idea?

TIA
Francois


francois Posted on 2004-09-22 13:00:14.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Trigger
References: <414ed0fa@solutions.advantagedatabase.com>
Subject: Re: Another way to bypass triggers
Date: Wed, 22 Sep 2004 21:00:14 +0800
Lines: 88
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 202.72.164.126
Message-ID: <41517700@solutions.advantagedatabase.com>
X-Trace: 22 Sep 2004 06:58:40 -0700, 202.72.164.126
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.164.126
Xref: solutions.advantagedatabase.com Advantage.Trigger:133
Article PK: 1136199

Hi Alex, JD, etc.
Sad to say that even though the trigger still functions it has slowed right
down to a crawl.
To summarize:
A) With Triggers in place a global modification takes about 1 sec per
record. I have not timed it perfectly because my test table has 8000
records and I cannot wait that long;)
B) If you are the only user on the system
Drop 16 triggers = 16 msec
Do global modifications < 500 msec
Create 16 Triggers = 32 msec
All over in less than 1 sec
C) As my application always opens up all tables during startup, the
triggers will stay active, even after you dropped them, until all users have
logged out.
If you are foolish enough to go ahead with the global update after dropping
the triggers but before everybody has logged out, the system will perform at
1 sec per operation, even if the other users log out.
D) Using an external value to bypass the trigger works only in that the
tigger modifications are not applied. It still goes through all the motions
and a global update seems to be even slower. What is more is that the
standard trigger execution has now also slowed down to a crawl as it has to
evaluate this external test for every Where clause in the trigger.

It seems that I have no other choice but to remove the triggers totally and
apply them manually from inside Delphi using a OnAfterPost (clientside)
eventhandler.:{

I still believe that a START TRANSACTION NO TRIGGERS command is the best way
to go since most actions of this type will be over within 1 second and the
trigger is only disabled for this specific script and automatically kicks
back in after the COMMIT WORK command.

Come on ESI! Please see what you can do about it.

Regards
Francois

"francois" <fransh_@westnet.com.au> wrote in message
news:414ed0fa@solutions.advantagedatabase.com...
> Hi
> I want to bypass triggers for global updates.
> Most of my triggers look like this:
>
> UPDATE OtherTable
> SET Total=(
> SELECT SUM(amount)
> FROM thistable
> WHERE smonth='January 2004')
> WHERE smonth='January 2004'
>
> So a global change like
>
> UPDATE thistable
> SET InUse = True
> WHERE (InUse IS NULL
> OR InUse = False)
>
> causes SUM(amount) to be evaluated
> for each record affected.
> A typical global update is 200+ times faster
> without ths type of trigger (in my tests)
>
> I want to set field Global in SysTable to True
> and then modify the trigger like this
>
> UPDATE OtherTable
> SET Total=(
> SELECT SUM(amount)
> FROM thistable
> WHERE smonth='January 2004')
> WHERE (SELECT Global FROM SysTable) = False
> AND smonth='January 2004'
>
> Thus the trigger should not fully execute when
> Global = true
> However it must still evaluate Global for every
> record affected.
>
> Is there someone with a better idea?
>
> TIA
> Francois
>
>