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 Extremly Slow

6 posts in Trigger Last posting was on 2006-01-12 17:12:37.0Z
Nathan Robeson Posted on 2005-12-23 16:33:33.0Z
From: "Nathan Robeson" <nathan@voicecue.com>
Newsgroups: advantage.trigger
Subject: Triggers Extremly Slow
Date: Fri, 23 Dec 2005 10:33:33 -0600
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
NNTP-Posting-Host: 68.20.62.36
Message-ID: <43ac274b@solutions.advantagedatabase.com>
X-Trace: 23 Dec 2005 09:35:23 -0700, 68.20.62.36
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.20.62.36
Xref: solutions.advantagedatabase.com Advantage.Trigger:181
Article PK: 1136247

I am performance testing ADS 8.0 using VO OLE DB Vo2ADO under Windows Server
2003.

Appending 100,000 records is very fast: 18 seconds (Oracle took 40 using a
Sequence and Trigger to populate the Account Number since Oracle does not
have an AutoInc data type)

However using a trigger instead of an autoinc i.e. to allow for a large char
field is extremely slow. Using and INSTEAD OF INSERT trigger takes 450
seconds while using an AFTER INSERT trigger takes 400 seconds.

Why are triggers so darn slow? They will not be viable for high performance
work..

No Triggers: 18 seconds
Trigger: 400 seconds

Below is the AFTER INSERT example. No rocket science here. Notice that
Account_Number_PK was defaulted to a space to temporarily pass the
NULL_CONSTRAINT.

CREATE TABLE Dc_Account
(
Account_Number_PK Char( 12 ) CONSTRAINT NOT NULL DEFAULT ' '
);
----------------------------------------------------------------------------
------------------------------------
CREATE TABLE Dc_Account_Account_Number_SEQ(
Account_Number Double
);

INSERT INTO Dc_Account_Account_Number_SEQ( Account_Number ) VALUES (
100000000000 );
----------------------------------------------------------------------------
------------------------------------
CREATE TRIGGER Dc_Account_Insert_TRG
ON Dc_Account
AFTER INSERT
BEGIN
UPDATE Dc_Account_Account_Number_SEQ SET Account_Number=Account_Number+1;
UPDATE Dc_Account SET Dc_Account.Account_Number_PK=CAST(
Dc_Account_Account_Number_SEQ.Account_Number AS SQL_CHAR( 12 ) ) FROM
Dc_Account_Account_Number_SEQ WHERE Dc_Account.Account_Number_PK = ' ';
END
NO VALUES
NO MEMOS
PRIORITY 1;

Nathan


Jeremy Mullin Posted on 2006-01-03 15:20:37.0Z
Message-ID: <886edc156f338c7de5622c9f933@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Triggers Extremly Slow
Newsgroups: Advantage.Trigger
References: <43ac274b@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 671.6
NNTP-Posting-Host: 198.102.102.44
Date: 3 Jan 2006 08:20:37 -0700
X-Trace: 3 Jan 2006 08:20:37 -0700, 198.102.102.44
Lines: 63
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.44
Xref: solutions.advantagedatabase.com Advantage.Trigger:182
Article PK: 1136251

Hi Nathan,

Did you try disabling the implicit transactions on the trigger? That should
help quite a bit. When using the transactions a tps log file has to be created
and destroyed for each insert, which is very expensive.

J.D. Mullin
Advantage R&D

> I am performance testing ADS 8.0 using VO OLE DB Vo2ADO under Windows
> Server 2003.
>
> Appending 100,000 records is very fast: 18 seconds (Oracle took 40
> using a Sequence and Trigger to populate the Account Number since
> Oracle does not have an AutoInc data type)
>
> However using a trigger instead of an autoinc i.e. to allow for a
> large char field is extremely slow. Using and INSTEAD OF INSERT
> trigger takes 450 seconds while using an AFTER INSERT trigger takes
> 400 seconds.
>
> Why are triggers so darn slow? They will not be viable for high
> performance work..
>
> No Triggers: 18 seconds
> Trigger: 400 seconds
> Below is the AFTER INSERT example. No rocket science here. Notice that
> Account_Number_PK was defaulted to a space to temporarily pass the
> NULL_CONSTRAINT.
>
> CREATE TABLE Dc_Account
> (
> Account_Number_PK Char( 12 ) CONSTRAINT NOT NULL DEFAULT ' '
> );
> ----------------------------------------------------------------------
> ------
> ------------------------------------
> CREATE TABLE Dc_Account_Account_Number_SEQ(
> Account_Number Double
> );
> INSERT INTO Dc_Account_Account_Number_SEQ( Account_Number ) VALUES (
> 100000000000 );
> ----------------------------------------------------------------------
> ------
> ------------------------------------
> CREATE TRIGGER Dc_Account_Insert_TRG
> ON Dc_Account
> AFTER INSERT
> BEGIN
> UPDATE Dc_Account_Account_Number_SEQ SET
> Account_Number=Account_Number+1;
> UPDATE Dc_Account SET Dc_Account.Account_Number_PK=CAST(
> Dc_Account_Account_Number_SEQ.Account_Number AS SQL_CHAR( 12 ) ) FROM
> Dc_Account_Account_Number_SEQ WHERE Dc_Account.Account_Number_PK = '
> ';
> END
> NO VALUES
> NO MEMOS
> PRIORITY 1;
> Nathan
>


Nathan Robeson Posted on 2006-01-06 15:39:05.0Z
From: "Nathan Robeson" <nathan@voicecue.com>
Newsgroups: Advantage.Trigger
References: <43ac274b@solutions.advantagedatabase.com> <886edc156f338c7de5622c9f933@devzone.advantagedatabase.com>
Subject: Re: Triggers Extremly Slow
Date: Fri, 6 Jan 2006 09:39:05 -0600
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
NNTP-Posting-Host: 68.20.62.36
Message-ID: <43be8faf@solutions.advantagedatabase.com>
X-Trace: 6 Jan 2006 08:41:35 -0700, 68.20.62.36
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.20.62.36
Xref: solutions.advantagedatabase.com Advantage.Trigger:183
Article PK: 1136248

Jeremy,

Yes I did. It made little difference.

BTW, the benchmark was for 10,000 not 100, 000 records.

Nathan

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc156f338c7de5622c9f933@devzone.advantagedatabase.com...
> Hi Nathan,
>
> Did you try disabling the implicit transactions on the trigger? That
should
> help quite a bit. When using the transactions a tps log file has to be
created
> and destroyed for each insert, which is very expensive.
>
> J.D. Mullin
> Advantage R&D
>
> > I am performance testing ADS 8.0 using VO OLE DB Vo2ADO under Windows
> > Server 2003.
> >
> > Appending 100,000 records is very fast: 18 seconds (Oracle took 40
> > using a Sequence and Trigger to populate the Account Number since
> > Oracle does not have an AutoInc data type)
> >
> > However using a trigger instead of an autoinc i.e. to allow for a
> > large char field is extremely slow. Using and INSTEAD OF INSERT
> > trigger takes 450 seconds while using an AFTER INSERT trigger takes
> > 400 seconds.
> >
> > Why are triggers so darn slow? They will not be viable for high
> > performance work..
> >
> > No Triggers: 18 seconds
> > Trigger: 400 seconds
> > Below is the AFTER INSERT example. No rocket science here. Notice that
> > Account_Number_PK was defaulted to a space to temporarily pass the
> > NULL_CONSTRAINT.
> >
> > CREATE TABLE Dc_Account
> > (
> > Account_Number_PK Char( 12 ) CONSTRAINT NOT NULL DEFAULT ' '
> > );
> > ----------------------------------------------------------------------
> > ------
> > ------------------------------------
> > CREATE TABLE Dc_Account_Account_Number_SEQ(
> > Account_Number Double
> > );
> > INSERT INTO Dc_Account_Account_Number_SEQ( Account_Number ) VALUES (
> > 100000000000 );
> > ----------------------------------------------------------------------
> > ------
> > ------------------------------------
> > CREATE TRIGGER Dc_Account_Insert_TRG
> > ON Dc_Account
> > AFTER INSERT
> > BEGIN
> > UPDATE Dc_Account_Account_Number_SEQ SET
> > Account_Number=Account_Number+1;
> > UPDATE Dc_Account SET Dc_Account.Account_Number_PK=CAST(
> > Dc_Account_Account_Number_SEQ.Account_Number AS SQL_CHAR( 12 ) ) FROM
> > Dc_Account_Account_Number_SEQ WHERE Dc_Account.Account_Number_PK = '
> > ';
> > END
> > NO VALUES
> > NO MEMOS
> > PRIORITY 1;
> > Nathan
> >
>
>


Jeremy Mullin Posted on 2006-01-06 16:43:18.0Z
Message-ID: <886edc1577668c7e0bd2f97f7a2@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Triggers Extremly Slow
Newsgroups: Advantage.Trigger
References: <43be8faf@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 671.6
NNTP-Posting-Host: 198.102.102.44
Date: 6 Jan 2006 09:43:18 -0700
X-Trace: 6 Jan 2006 09:43:18 -0700, 198.102.102.44
Lines: 89
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.44
Xref: solutions.advantagedatabase.com Advantage.Trigger:184
Article PK: 1136249

Can you send your benchmark app to advantage@extendedsystems.com, and attn:
JD

I'll try to take a look at it.

J.D. Mullin
Advantage R&D

> Jeremy,
>
> Yes I did. It made little difference.
>
> BTW, the benchmark was for 10,000 not 100, 000 records.
>
> Nathan
>
> "Jeremy Mullin" <no@email.com> wrote in message
> news:886edc156f338c7de5622c9f933@devzone.advantagedatabase.com...
>> Hi Nathan,
>>
>> Did you try disabling the implicit transactions on the trigger? That
>>
> should
>
>> help quite a bit. When using the transactions a tps log file has to
>> be
>>
> created
>
>> and destroyed for each insert, which is very expensive.
>>
>> J.D. Mullin
>> Advantage R&D
>>> I am performance testing ADS 8.0 using VO OLE DB Vo2ADO under
>>> Windows Server 2003.
>>>
>>> Appending 100,000 records is very fast: 18 seconds (Oracle took 40
>>> using a Sequence and Trigger to populate the Account Number since
>>> Oracle does not have an AutoInc data type)
>>>
>>> However using a trigger instead of an autoinc i.e. to allow for a
>>> large char field is extremely slow. Using and INSTEAD OF INSERT
>>> trigger takes 450 seconds while using an AFTER INSERT trigger takes
>>> 400 seconds.
>>>
>>> Why are triggers so darn slow? They will not be viable for high
>>> performance work..
>>>
>>> No Triggers: 18 seconds
>>> Trigger: 400 seconds
>>> Below is the AFTER INSERT example. No rocket science here. Notice
>>> that
>>> Account_Number_PK was defaulted to a space to temporarily pass the
>>> NULL_CONSTRAINT.
>>> CREATE TABLE Dc_Account
>>> (
>>> Account_Number_PK Char( 12 ) CONSTRAINT NOT NULL DEFAULT ' '
>>> );
>>> --------------------------------------------------------------------
>>> --
>>> ------
>>> ------------------------------------
>>> CREATE TABLE Dc_Account_Account_Number_SEQ(
>>> Account_Number Double
>>> );
>>> INSERT INTO Dc_Account_Account_Number_SEQ( Account_Number ) VALUES (
>>> 100000000000 );
>>> --------------------------------------------------------------------
>>> --
>>> ------
>>> ------------------------------------
>>> CREATE TRIGGER Dc_Account_Insert_TRG
>>> ON Dc_Account
>>> AFTER INSERT
>>> BEGIN
>>> UPDATE Dc_Account_Account_Number_SEQ SET
>>> Account_Number=Account_Number+1;
>>> UPDATE Dc_Account SET Dc_Account.Account_Number_PK=CAST(
>>> Dc_Account_Account_Number_SEQ.Account_Number AS SQL_CHAR( 12 ) )
>>> FROM
>>> Dc_Account_Account_Number_SEQ WHERE Dc_Account.Account_Number_PK = '
>>> ';
>>> END
>>> NO VALUES
>>> NO MEMOS
>>> PRIORITY 1;
>>> Nathan


Jeremy Mullin Posted on 2006-01-10 22:23:39.0Z
Message-ID: <886edc157de08c7e41165fb6a95@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Triggers Extremly Slow
Newsgroups: Advantage.Trigger
References: <43be8faf@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 671.6
NNTP-Posting-Host: 198.102.102.44
Date: 10 Jan 2006 15:23:39 -0700
X-Trace: 10 Jan 2006 15:23:39 -0700, 198.102.102.44
Lines: 107
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.44
Xref: solutions.advantagedatabase.com Advantage.Trigger:185
Article PK: 1136252

Hi Nathan,

I looked into this some today and Parker from our tech services group is
going to be contacting you with my findings and a couple of possible work-arounds.
This is a known performance issue Yves Moreau reported a while back and is
scheduled to be fixed in the 8.1 release.

Basically the statement for the trigger script is not cached, so the trigger
has to parse and semantic check the trigger script every time it fires. The
semantic check involves opening the table. Since you are opening a table
other than the one the trigger lives on (Dc_Account_Account_Number_SEQ) this
table is getting opened and closed 1000 times.

There are two workarounds. The simplest is to open an instance of the Dc_Account_Account_Number_SEQ
table and leave it open. In my testing this brought the time from 45 seconds
down to 7.6 seconds.

The second work-around is to use the vo driver or ACE to get your autoinc
on the client side. Basically you would open an instance of Dc_Account_Account_Number_SEQ
and leave it open. When you need a new autoinc you would lock the record,
update the value, unlock the record, and use the new value in your insert
statement, eliminating the trigger all together.

J.D. Mullin
Advantage R&D

> Jeremy,
>
> Yes I did. It made little difference.
>
> BTW, the benchmark was for 10,000 not 100, 000 records.
>
> Nathan
>
> "Jeremy Mullin" <no@email.com> wrote in message
> news:886edc156f338c7de5622c9f933@devzone.advantagedatabase.com...
>> Hi Nathan,
>>
>> Did you try disabling the implicit transactions on the trigger? That
>>
> should
>
>> help quite a bit. When using the transactions a tps log file has to
>> be
>>
> created
>
>> and destroyed for each insert, which is very expensive.
>>
>> J.D. Mullin
>> Advantage R&D
>>> I am performance testing ADS 8.0 using VO OLE DB Vo2ADO under
>>> Windows Server 2003.
>>>
>>> Appending 100,000 records is very fast: 18 seconds (Oracle took 40
>>> using a Sequence and Trigger to populate the Account Number since
>>> Oracle does not have an AutoInc data type)
>>>
>>> However using a trigger instead of an autoinc i.e. to allow for a
>>> large char field is extremely slow. Using and INSTEAD OF INSERT
>>> trigger takes 450 seconds while using an AFTER INSERT trigger takes
>>> 400 seconds.
>>>
>>> Why are triggers so darn slow? They will not be viable for high
>>> performance work..
>>>
>>> No Triggers: 18 seconds
>>> Trigger: 400 seconds
>>> Below is the AFTER INSERT example. No rocket science here. Notice
>>> that
>>> Account_Number_PK was defaulted to a space to temporarily pass the
>>> NULL_CONSTRAINT.
>>> CREATE TABLE Dc_Account
>>> (
>>> Account_Number_PK Char( 12 ) CONSTRAINT NOT NULL DEFAULT ' '
>>> );
>>> --------------------------------------------------------------------
>>> --
>>> ------
>>> ------------------------------------
>>> CREATE TABLE Dc_Account_Account_Number_SEQ(
>>> Account_Number Double
>>> );
>>> INSERT INTO Dc_Account_Account_Number_SEQ( Account_Number ) VALUES (
>>> 100000000000 );
>>> --------------------------------------------------------------------
>>> --
>>> ------
>>> ------------------------------------
>>> CREATE TRIGGER Dc_Account_Insert_TRG
>>> ON Dc_Account
>>> AFTER INSERT
>>> BEGIN
>>> UPDATE Dc_Account_Account_Number_SEQ SET
>>> Account_Number=Account_Number+1;
>>> UPDATE Dc_Account SET Dc_Account.Account_Number_PK=CAST(
>>> Dc_Account_Account_Number_SEQ.Account_Number AS SQL_CHAR( 12 ) )
>>> FROM
>>> Dc_Account_Account_Number_SEQ WHERE Dc_Account.Account_Number_PK = '
>>> ';
>>> END
>>> NO VALUES
>>> NO MEMOS
>>> PRIORITY 1;
>>> Nathan


Thomas Steinmaurer Posted on 2006-01-12 17:12:37.0Z
Date: Thu, 12 Jan 2006 18:12:37 +0100
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam_@upscene.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Triggers Extremly Slow
References: <43be8faf@solutions.advantagedatabase.com> <886edc157de08c7e41165fb6a95@devzone.advantagedatabase.com>
In-Reply-To: <886edc157de08c7e41165fb6a95@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 86.56.177.91
Message-ID: <43c68d45@solutions.advantagedatabase.com>
X-Trace: 12 Jan 2006 10:09:25 -0700, 86.56.177.91
Lines: 40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!86.56.177.91
Xref: solutions.advantagedatabase.com Advantage.Trigger:186
Article PK: 1136250

Hello Jeremy,

> I looked into this some today and Parker from our tech services group is
> going to be contacting you with my findings and a couple of possible
> work-arounds. This is a known performance issue Yves Moreau reported a
> while back and is scheduled to be fixed in the 8.1 release.
>
> Basically the statement for the trigger script is not cached, so the
> trigger has to parse and semantic check the trigger script every time it
> fires. The semantic check involves opening the table. Since you are
> opening a table other than the one the trigger lives on
> (Dc_Account_Account_Number_SEQ) this table is getting opened and closed
> 1000 times.
> There are two workarounds. The simplest is to open an instance of the
> Dc_Account_Account_Number_SEQ table and leave it open. In my testing
> this brought the time from 45 seconds down to 7.6 seconds.
>
> The second work-around is to use the vo driver or ACE to get your
> autoinc on the client side. Basically you would open an instance of
> Dc_Account_Account_Number_SEQ and leave it open. When you need a new
> autoinc you would lock the record, update the value, unlock the record,
> and use the new value in your insert statement, eliminating the trigger
> all together.

Thanks a lot for the explanation. This explains why our SQL script based
log triggers are damn slow. ;-)

Looking forward to version 8.1.



--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com