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.

Performance

3 posts in Trigger Last posting was on 2004-09-17 02:37:53.0Z
Yves Moreau Posted on 2004-09-03 22:40:32.0Z
Reply-To: "Yves Moreau" <ymoreau@sisym.com>
From: "Yves Moreau" <ymoreau@sisym.com>
Newsgroups: advantage.trigger
Subject: Performance
Date: Fri, 3 Sep 2004 18:40:32 -0400
Lines: 38
Organization: SYM
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 24.226.214.63
Message-ID: <4138f30c@solutions.advantagedatabase.com>
X-Trace: 3 Sep 2004 16:41:16 -0700, 24.226.214.63
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!24.226.214.63
Xref: solutions.advantagedatabase.com Advantage.Trigger:124
Article PK: 1136190

Hi,

Could someone look to this performance and give me some advise?

Our configuration:
Server: Netware 6.0 P4-2.4Ghz 1GigRAM ADS.NLM version 7.1.0.6
Client : Windows XP. P4-2.4Ghz

Time to insert 1000 records = 765 msec.

When we use a trigger:
Time to insert 1000 records = 2.6 sec
Time to insert 1000 records when implicit transaction to maintain data
integrity is ON = 6.844 sec.

We use the INSTEAD OF INSERT
Trigger is define :
update __new set clef =-clef_autoinc
where clef is null;
insert into TableName select * from __new

Is it a normal performance? Is there other way to do this?

In the past 10 years, we were using Pervasive Products...
The autoinc field with Pervasive is using a different behavior.
When the autoinc field is null or equal to 0 then the database engine
generate a value.
If the value of autoinc different of null or 0 then the datebase engine try
to use this value.

This feature is very useful because when you copy table the database keep
the same value for the autoinc field.

Yves Moreau


JD Mullin Posted on 2004-09-07 21:04:51.0Z
From: JD Mullin <no@spam.com>
Newsgroups: advantage.trigger
Subject: Re: Performance
Date: Tue, 7 Sep 2004 15:04:51 -0600
Message-ID: <MPG.1ba7d05efa75ddd9989680@devzone.advantagedatabase.com>
References: <4138f30c@solutions.advantagedatabase.com>
Organization: Extended Systems
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.60.2060
NNTP-Posting-Host: 198.102.102.75
X-Trace: 7 Sep 2004 15:06:07 -0700, 198.102.102.75
Lines: 58
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.75
Xref: solutions.advantagedatabase.com Advantage.Trigger:125
Article PK: 1136192

If you send the app and data you used to test I can try it here. You can
send it to advantage@extendsys.com, attn JD

The transaction time is going to be fairly large, especially if you
don't already have a transaction active when you call INSERT. Most of
the overhead will be from creating a transaction log on disk, then
deleteing it. This will happen for every update operation, which will be
expensive.

The time to insert 1000 records with a trigger (without transactions)
seems kind of high, but I can run it here and see what is going on. It
might be the general overhead of parsing and running the INSERT
statement twice for each update operation.

J.D. Mullin
Advantage R&D

In article <4138f30c@solutions.advantagedatabase.com>, ymoreau@sisym.com
says...

> Hi,
>
> Could someone look to this performance and give me some advise?
>
> Our configuration:
> Server: Netware 6.0 P4-2.4Ghz 1GigRAM ADS.NLM version 7.1.0.6
> Client : Windows XP. P4-2.4Ghz
>
> Time to insert 1000 records = 765 msec.
>
> When we use a trigger:
> Time to insert 1000 records = 2.6 sec
> Time to insert 1000 records when implicit transaction to maintain data
> integrity is ON = 6.844 sec.
>
> We use the INSTEAD OF INSERT
> Trigger is define :
> update __new set clef =-clef_autoinc
> where clef is null;
> insert into TableName select * from __new
>
> Is it a normal performance? Is there other way to do this?
>
> In the past 10 years, we were using Pervasive Products...
> The autoinc field with Pervasive is using a different behavior.
> When the autoinc field is null or equal to 0 then the database engine
> generate a value.
> If the value of autoinc different of null or 0 then the datebase engine try
> to use this value.
>
> This feature is very useful because when you copy table the database keep
> the same value for the autoinc field.
>
> Yves Moreau
>
>
>
>
>


Francois Posted on 2004-09-17 02:37:53.0Z
From: "francois" <francois@geedee.com.au>
Newsgroups: advantage.trigger
References: <4138f30c@solutions.advantagedatabase.com> <MPG.1ba7d05efa75ddd9989680@devzone.advantagedatabase.com>
Subject: Re: Performance
Date: Fri, 17 Sep 2004 10:37:53 +0800
Lines: 16
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: 202.72.180.220
Message-ID: <414a4cb0@solutions.advantagedatabase.com>
X-Trace: 16 Sep 2004 20:32:16 -0700, 202.72.180.220
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.180.220
Xref: solutions.advantagedatabase.com Advantage.Trigger:129
Article PK: 1136197

Hi
Any GLOBAL ACTION on tables with triggers will be SLOW.
You would do much better to have a script doing everything including the
trigger actions in a sequential order.
My guess is that the response would be at least a 1000 times faster.

UNFORTUNATELY to do that you'll have to disable the triggers for the GLOBAL
update which can only happen now if you DROP TRIGGERS before and REINSTATE
afterwards.
BUT if any other user has the trigger table open the DROP TRIGGER will be
ignored until he has closed his connection or you have kicked him off.

Regards
Francois