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.

Delete Trigger TOO SLOW !!!

8 posts in Trigger Last posting was on 2006-05-09 15:13:13.0Z
Rinaldi Bruno Posted on 2006-04-20 07:14:45.0Z
Date: Thu, 20 Apr 2006 09:14:45 +0200
From: Rinaldi Bruno <bruno.rinaldi@cibaelectric.it>
User-Agent: Mozilla Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Delete Trigger TOO SLOW !!!
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 85.18.14.43
Message-ID: <4447341b@solutions.advantagedatabase.com>
X-Trace: 20 Apr 2006 01:11:23 -0700, 85.18.14.43
Lines: 61
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!85.18.14.43
Xref: solutions.advantagedatabase.com Advantage.Trigger:216
Article PK: 1136282

Hi,
I have the necessity to create a TRIGGER that deletes the data on a
series of tables of Details if the record of the table Master is
deleted. For other motives tied to the origin of the data I cannot use
the RI.
As before test I have written the following instructions but the result
it is a delete really Very slow:

CREATE TRIGGER DELETE_S_TRECIPE_BY1
ON S_TRecipe_BY1
BEFORE
DELETE
BEGIN
DELETE FROM S_DB1B_BY1
WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
DELETE FROM S_DB1G_BY1
WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
DELETE FROM S_DB1N_BY1
WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
DELETE FROM S_DB1O_BY1
WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
DELETE FROM S_DB1Z_BY1
WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
DELETE FROM S_DMIXER_BY1
WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
END
PRIORITY 1;

Have I committed some error?
Do you have suggestions to increase the speed in the execution of the
Trigger?

Regards.
--
Rinaldi Bruno
Analista Programmatore

Please, consider your environmental responsibility.
Before printing this e-mail ask yourself: "Do I need a hard copy?"

Nota di riservatezza : Il presente messaggio, corredato dei relativi
allegati, contiene informazioni da considerarsi strettamente riservate,
ed ? destinato esclusivamente al destinatario sopra indicato, il quale ?
l'unico autorizzato ad usarlo, copiarlo e, sotto la propria
responsabilit?, diffonderlo. Chiunque ricevesse questo messaggio per
errore o comunque lo leggesse senza esserne legittimato ? avvertito che
trattenerlo, copiarlo, divulgarlo, distribuirlo a persone diverse dal
destinatario ? severamente proibito, ed ? pregato di rinviarlo
immediatamente al mittente distruggendone l'originale.
Grazie.

Confidentiality Notice : This message, together with its annexes,
contains information to be deemed strictly confidential and is destined
only to the addressee(s) identified above who only may use, copy and,
under his/their responsibility, further disseminate it. If anyone
received this message by mistake or reads it without entitlement is
forewarned that keeping, copying, disseminating or distributing this
message to persons other than the addressee(s) is strictly forbidden and
is asked to transmit it immediately to the sender and to erase the
original message received.
Thank you.


Thomas Steinmaurer Posted on 2006-04-20 12:49:16.0Z
Date: Thu, 20 Apr 2006 14:49:16 +0200
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam@upscene.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Delete Trigger TOO SLOW !!!
References: <4447341b@solutions.advantagedatabase.com>
In-Reply-To: <4447341b@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 193.186.173.108
Message-ID: <4447827f@solutions.advantagedatabase.com>
X-Trace: 20 Apr 2006 06:45:51 -0700, 193.186.173.108
Lines: 43
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!193.186.173.108
Xref: solutions.advantagedatabase.com Advantage.Trigger:217
Article PK: 1136284


> I have the necessity to create a TRIGGER that deletes the data on a
> series of tables of Details if the record of the table Master is
> deleted. For other motives tied to the origin of the data I cannot use
> the RI.
> As before test I have written the following instructions but the result
> it is a delete really Very slow:
>
> CREATE TRIGGER DELETE_S_TRECIPE_BY1
> ON S_TRecipe_BY1
> BEFORE
> DELETE
> BEGIN
> DELETE FROM S_DB1B_BY1
> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
> DELETE FROM S_DB1G_BY1
> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
> DELETE FROM S_DB1N_BY1
> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
> DELETE FROM S_DB1O_BY1
> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
> DELETE FROM S_DB1Z_BY1
> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
> DELETE FROM S_DMIXER_BY1
> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
> END
> PRIORITY 1;
>
> Have I committed some error?
> Do you have suggestions to increase the speed in the execution of the
> Trigger?

The following is totally trigger-independent. Do you have indices on the
PROG_STORICO fields in your tables?


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


Rinaldi Bruno Posted on 2006-04-20 14:01:34.0Z
Date: Thu, 20 Apr 2006 16:01:34 +0200
From: Rinaldi Bruno <bruno.rinaldi@cibaelectric.it>
User-Agent: Mozilla Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Delete Trigger TOO SLOW !!!
References: <4447341b@solutions.advantagedatabase.com> <4447827f@solutions.advantagedatabase.com>
In-Reply-To: <4447827f@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 85.18.14.43
Message-ID: <44479373@solutions.advantagedatabase.com>
X-Trace: 20 Apr 2006 07:58:11 -0700, 85.18.14.43
Lines: 66
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!85.18.14.43
Xref: solutions.advantagedatabase.com Advantage.Trigger:218
Article PK: 1136283

Thomas Steinmaurer ha scritto:

>> I have the necessity to create a TRIGGER that deletes the data on a
>> series of tables of Details if the record of the table Master is
>> deleted. For other motives tied to the origin of the data I cannot use
>> the RI.
>> As before test I have written the following instructions but the
>> result it is a delete really Very slow:
>>
>> CREATE TRIGGER DELETE_S_TRECIPE_BY1
>> ON S_TRecipe_BY1
>> BEFORE
>> DELETE
>> BEGIN
>> DELETE FROM S_DB1B_BY1
>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>> DELETE FROM S_DB1G_BY1
>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>> DELETE FROM S_DB1N_BY1
>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>> DELETE FROM S_DB1O_BY1
>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>> DELETE FROM S_DB1Z_BY1
>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>> DELETE FROM S_DMIXER_BY1
>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>> END
>> PRIORITY 1;
>>
>> Have I committed some error?
>> Do you have suggestions to increase the speed in the execution of the
>> Trigger?
>
> The following is totally trigger-independent. Do you have indices on the
> PROG_STORICO fields in your tables?
>
>

Yes I have an index and is part of the PRIMARY KEY

--
Rinaldi Bruno
Analista Programmatore

Please, consider your environmental responsibility.
Before printing this e-mail ask yourself: "Do I need a hard copy?"

Nota di riservatezza : Il presente messaggio, corredato dei relativi
allegati, contiene informazioni da considerarsi strettamente riservate,
ed ? destinato esclusivamente al destinatario sopra indicato, il quale ?
l'unico autorizzato ad usarlo, copiarlo e, sotto la propria
responsabilit?, diffonderlo. Chiunque ricevesse questo messaggio per
errore o comunque lo leggesse senza esserne legittimato ? avvertito che
trattenerlo, copiarlo, divulgarlo, distribuirlo a persone diverse dal
destinatario ? severamente proibito, ed ? pregato di rinviarlo
immediatamente al mittente distruggendone l'originale.
Grazie.

Confidentiality Notice : This message, together with its annexes,
contains information to be deemed strictly confidential and is destined
only to the addressee(s) identified above who only may use, copy and,
under his/their responsibility, further disseminate it. If anyone
received this message by mistake or reads it without entitlement is
forewarned that keeping, copying, disseminating or distributing this
message to persons other than the addressee(s) is strictly forbidden and
is asked to transmit it immediately to the sender and to erase the
original message received.
Thank you.


Thomas Steinmaurer Posted on 2006-04-21 06:55:10.0Z
Date: Fri, 21 Apr 2006 08:55:10 +0200
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam@upscene.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Delete Trigger TOO SLOW !!!
References: <4447341b@solutions.advantagedatabase.com> <4447827f@solutions.advantagedatabase.com> <44479373@solutions.advantagedatabase.com>
In-Reply-To: <44479373@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 193.186.173.108
Message-ID: <44488101@solutions.advantagedatabase.com>
X-Trace: 21 Apr 2006 00:51:45 -0700, 193.186.173.108
Lines: 53
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!193.186.173.108
Xref: solutions.advantagedatabase.com Advantage.Trigger:219
Article PK: 1136286

Hi Bruno,

>>> I have the necessity to create a TRIGGER that deletes the data on a
>>> series of tables of Details if the record of the table Master is
>>> deleted. For other motives tied to the origin of the data I cannot
>>> use the RI.
>>> As before test I have written the following instructions but the
>>> result it is a delete really Very slow:
>>>
>>> CREATE TRIGGER DELETE_S_TRECIPE_BY1
>>> ON S_TRecipe_BY1
>>> BEFORE
>>> DELETE
>>> BEGIN
>>> DELETE FROM S_DB1B_BY1
>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>> DELETE FROM S_DB1G_BY1
>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>> DELETE FROM S_DB1N_BY1
>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>> DELETE FROM S_DB1O_BY1
>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>> DELETE FROM S_DB1Z_BY1
>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>> DELETE FROM S_DMIXER_BY1
>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>> END
>>> PRIORITY 1;
>>>
>>> Have I committed some error?
>>> Do you have suggestions to increase the speed in the execution of the
>>> Trigger?
>>
>> The following is totally trigger-independent. Do you have indices on
>> the PROG_STORICO fields in your tables?
>>
>>
> Yes I have an index and is part of the PRIMARY KEY

My last wild guess. Is this a compound index with PROG_STORICO not being
on first position of the index definition? If it isn't a compound index
or PROG_STORICO is on the first position then the performance penalty
possibly is trigger related. Can't help you there. Sorry.


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


Jared Sherman Posted on 2006-05-02 20:04:17.0Z
From: "Jared Sherman" <Jared.Sherman@Per-Se.com>
Newsgroups: Advantage.Trigger
References: <4447341b@solutions.advantagedatabase.com> <4447827f@solutions.advantagedatabase.com> <44479373@solutions.advantagedatabase.com> <44488101@solutions.advantagedatabase.com>
Subject: Re: Delete Trigger TOO SLOW !!!
Date: Tue, 2 May 2006 13:04:17 -0700
Lines: 65
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 63.175.205.139
Message-ID: <4457ba72@solutions.advantagedatabase.com>
X-Trace: 2 May 2006 14:00:50 -0700, 63.175.205.139
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!63.175.205.139
Xref: solutions.advantagedatabase.com Advantage.Trigger:224
Article PK: 1136290

You might also make sure the implicit transactions are turned off. Those
have slowed my triggers down in the past.

What verison of the server are you running. I'm seeing a drastic speed
increase with Advantage 8.0 over Advantage 7.1 when performing SQL
statements.

"Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam@upscene.com> wrote in
message news:44488101@solutions.advantagedatabase.com...
> Hi Bruno,
>
>>>> I have the necessity to create a TRIGGER that deletes the data on a
>>>> series of tables of Details if the record of the table Master is
>>>> deleted. For other motives tied to the origin of the data I cannot use
>>>> the RI.
>>>> As before test I have written the following instructions but the result
>>>> it is a delete really Very slow:
>>>>
>>>> CREATE TRIGGER DELETE_S_TRECIPE_BY1
>>>> ON S_TRecipe_BY1
>>>> BEFORE
>>>> DELETE
>>>> BEGIN
>>>> DELETE FROM S_DB1B_BY1
>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>> DELETE FROM S_DB1G_BY1
>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>> DELETE FROM S_DB1N_BY1
>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>> DELETE FROM S_DB1O_BY1
>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>> DELETE FROM S_DB1Z_BY1
>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>> DELETE FROM S_DMIXER_BY1
>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>> END
>>>> PRIORITY 1;
>>>>
>>>> Have I committed some error?
>>>> Do you have suggestions to increase the speed in the execution of the
>>>> Trigger?
>>>
>>> The following is totally trigger-independent. Do you have indices on the
>>> PROG_STORICO fields in your tables?
>>>
>>>
>> Yes I have an index and is part of the PRIMARY KEY
>
> My last wild guess. Is this a compound index with PROG_STORICO not being
> on first position of the index definition? If it isn't a compound index or
> PROG_STORICO is on the first position then the performance penalty
> possibly is trigger related. Can't help you there. Sorry.
>
>
> --
> 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


Rinaldi Bruno Posted on 2006-05-03 07:08:04.0Z
Date: Wed, 03 May 2006 09:08:04 +0200
From: Rinaldi Bruno <bruno.rinaldi@cibaelectric.it>
User-Agent: Mozilla Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Delete Trigger TOO SLOW !!!
References: <4447341b@solutions.advantagedatabase.com> <4447827f@solutions.advantagedatabase.com> <44479373@solutions.advantagedatabase.com> <44488101@solutions.advantagedatabase.com> <4457ba72@solutions.advantagedatabase.com>
In-Reply-To: <4457ba72@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 85.18.14.43
Message-ID: <44585609@solutions.advantagedatabase.com>
X-Trace: 3 May 2006 01:04:41 -0700, 85.18.14.43
Lines: 72
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!85.18.14.43
Xref: solutions.advantagedatabase.com Advantage.Trigger:225
Article PK: 1136291

Hi,
How can I be sure that the implicit transactions are turned off?
Can you correct me the example that I have posted for excluding the
implicit transactions?
Unfortunately I am using various versions (from the 6.2 to the 8.0) with
the various clients...

Tahks Bruno
Jared Sherman ha scritto:

> You might also make sure the implicit transactions are turned off. Those
> have slowed my triggers down in the past.
>
> What verison of the server are you running. I'm seeing a drastic speed
> increase with Advantage 8.0 over Advantage 7.1 when performing SQL
> statements.
>
>
> "Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam@upscene.com> wrote in
> message news:44488101@solutions.advantagedatabase.com...
>> Hi Bruno,
>>
>>>>> I have the necessity to create a TRIGGER that deletes the data on a
>>>>> series of tables of Details if the record of the table Master is
>>>>> deleted. For other motives tied to the origin of the data I cannot use
>>>>> the RI.
>>>>> As before test I have written the following instructions but the result
>>>>> it is a delete really Very slow:
>>>>>
>>>>> CREATE TRIGGER DELETE_S_TRECIPE_BY1
>>>>> ON S_TRecipe_BY1
>>>>> BEFORE
>>>>> DELETE
>>>>> BEGIN
>>>>> DELETE FROM S_DB1B_BY1
>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>> DELETE FROM S_DB1G_BY1
>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>> DELETE FROM S_DB1N_BY1
>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>> DELETE FROM S_DB1O_BY1
>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>> DELETE FROM S_DB1Z_BY1
>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>> DELETE FROM S_DMIXER_BY1
>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>> END
>>>>> PRIORITY 1;
>>>>>
>>>>> Have I committed some error?
>>>>> Do you have suggestions to increase the speed in the execution of the
>>>>> Trigger?
>>>> The following is totally trigger-independent. Do you have indices on the
>>>> PROG_STORICO fields in your tables?
>>>>
>>>>
>>> Yes I have an index and is part of the PRIMARY KEY
>> My last wild guess. Is this a compound index with PROG_STORICO not being
>> on first position of the index definition? If it isn't a compound index or
>> PROG_STORICO is on the first position then the performance penalty
>> possibly is trigger related. Can't help you there. Sorry.
>>
>>
>> --
>> 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
>
>


Jared Sherman Posted on 2006-05-05 16:12:41.0Z
From: "Jared Sherman" <Jared.Sherman@Per-Se.com>
Newsgroups: Advantage.Trigger
References: <4447341b@solutions.advantagedatabase.com> <4447827f@solutions.advantagedatabase.com> <44479373@solutions.advantagedatabase.com> <44488101@solutions.advantagedatabase.com> <4457ba72@solutions.advantagedatabase.com> <44585609@solutions.advantagedatabase.com>
Subject: Re: Delete Trigger TOO SLOW !!!
Date: Fri, 5 May 2006 09:12:41 -0700
Lines: 96
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 63.175.205.139
Message-ID: <445b78ab@solutions.advantagedatabase.com>
X-Trace: 5 May 2006 10:09:15 -0700, 63.175.205.139
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!63.175.205.139
Xref: solutions.advantagedatabase.com Advantage.Trigger:226
Article PK: 1136293

CREATE TRIGGER <trigger name>
ON <table name>
BEFORE
UPDATE
FUNCTION <function name>
IN LIBRARY [.\<dllname>.dll] ;can also do SQL
NO TRANSACTION
PRIORITY 1;

The no transaction is what turns them off. You can open the dictionary and
check the system.triggers using SQL to see if they are set to have implicit
transactions.

You can also do the following:
1. Open the .add file
2. Log in to the dictionary
3. Select a table that has a trigger
4. Select one of its triggers
5. Check and see if the implicit transactions is selected.

"Rinaldi Bruno" <bruno.rinaldi@cibaelectric.it> wrote in message
news:44585609@solutions.advantagedatabase.com...
> Hi,
> How can I be sure that the implicit transactions are turned off?
> Can you correct me the example that I have posted for excluding the
> implicit transactions?
> Unfortunately I am using various versions (from the 6.2 to the 8.0) with
> the various clients...
>
> Tahks Bruno
> Jared Sherman ha scritto:
>> You might also make sure the implicit transactions are turned off. Those
>> have slowed my triggers down in the past.
>>
>> What verison of the server are you running. I'm seeing a drastic speed
>> increase with Advantage 8.0 over Advantage 7.1 when performing SQL
>> statements.
>>
>>
>> "Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam@upscene.com> wrote
>> in message news:44488101@solutions.advantagedatabase.com...
>>> Hi Bruno,
>>>
>>>>>> I have the necessity to create a TRIGGER that deletes the data on a
>>>>>> series of tables of Details if the record of the table Master is
>>>>>> deleted. For other motives tied to the origin of the data I cannot
>>>>>> use the RI.
>>>>>> As before test I have written the following instructions but the
>>>>>> result it is a delete really Very slow:
>>>>>>
>>>>>> CREATE TRIGGER DELETE_S_TRECIPE_BY1
>>>>>> ON S_TRecipe_BY1
>>>>>> BEFORE
>>>>>> DELETE
>>>>>> BEGIN
>>>>>> DELETE FROM S_DB1B_BY1
>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>> DELETE FROM S_DB1G_BY1
>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>> DELETE FROM S_DB1N_BY1
>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>> DELETE FROM S_DB1O_BY1
>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>> DELETE FROM S_DB1Z_BY1
>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>> DELETE FROM S_DMIXER_BY1
>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>> END
>>>>>> PRIORITY 1;
>>>>>>
>>>>>> Have I committed some error?
>>>>>> Do you have suggestions to increase the speed in the execution of the
>>>>>> Trigger?
>>>>> The following is totally trigger-independent. Do you have indices on
>>>>> the PROG_STORICO fields in your tables?
>>>>>
>>>>>
>>>> Yes I have an index and is part of the PRIMARY KEY
>>> My last wild guess. Is this a compound index with PROG_STORICO not being
>>> on first position of the index definition? If it isn't a compound index
>>> or PROG_STORICO is on the first position then the performance penalty
>>> possibly is trigger related. Can't help you there. Sorry.
>>>
>>>
>>> --
>>> 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
>>


Rinaldi Bruno Posted on 2006-05-09 15:13:13.0Z
Date: Tue, 09 May 2006 17:13:13 +0200
From: Rinaldi Bruno <bruno.rinaldi@cibaelectric.it>
User-Agent: Mozilla Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Delete Trigger TOO SLOW !!!
References: <4447341b@solutions.advantagedatabase.com> <4447827f@solutions.advantagedatabase.com> <44479373@solutions.advantagedatabase.com> <44488101@solutions.advantagedatabase.com> <4457ba72@solutions.advantagedatabase.com> <44585609@solutions.advantagedatabase.com> <445b78ab@solutions.advantagedatabase.com>
In-Reply-To: <445b78ab@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 85.18.14.43
Message-ID: <4460b086@solutions.advantagedatabase.com>
X-Trace: 9 May 2006 09:08:54 -0700, 85.18.14.43
Lines: 128
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!85.18.14.43
Xref: solutions.advantagedatabase.com Advantage.Trigger:227
Article PK: 1136292

OK I have found the implicit transaction and that was set to TRUE...
Thanks....

Jared Sherman ha scritto:

> CREATE TRIGGER <trigger name>
> ON <table name>
> BEFORE
> UPDATE
> FUNCTION <function name>
> IN LIBRARY [.\<dllname>.dll] ;can also do SQL
> NO TRANSACTION
> PRIORITY 1;
>
> The no transaction is what turns them off. You can open the dictionary and
> check the system.triggers using SQL to see if they are set to have implicit
> transactions.
>
> You can also do the following:
> 1. Open the .add file
> 2. Log in to the dictionary
> 3. Select a table that has a trigger
> 4. Select one of its triggers
> 5. Check and see if the implicit transactions is selected.
>
>
> "Rinaldi Bruno" <bruno.rinaldi@cibaelectric.it> wrote in message
> news:44585609@solutions.advantagedatabase.com...
>> Hi,
>> How can I be sure that the implicit transactions are turned off?
>> Can you correct me the example that I have posted for excluding the
>> implicit transactions?
>> Unfortunately I am using various versions (from the 6.2 to the 8.0) with
>> the various clients...
>>
>> Tahks Bruno
>> Jared Sherman ha scritto:
>>> You might also make sure the implicit transactions are turned off. Those
>>> have slowed my triggers down in the past.
>>>
>>> What verison of the server are you running. I'm seeing a drastic speed
>>> increase with Advantage 8.0 over Advantage 7.1 when performing SQL
>>> statements.
>>>
>>>
>>> "Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam@upscene.com> wrote
>>> in message news:44488101@solutions.advantagedatabase.com...
>>>> Hi Bruno,
>>>>
>>>>>>> I have the necessity to create a TRIGGER that deletes the data on a
>>>>>>> series of tables of Details if the record of the table Master is
>>>>>>> deleted. For other motives tied to the origin of the data I cannot
>>>>>>> use the RI.
>>>>>>> As before test I have written the following instructions but the
>>>>>>> result it is a delete really Very slow:
>>>>>>>
>>>>>>> CREATE TRIGGER DELETE_S_TRECIPE_BY1
>>>>>>> ON S_TRecipe_BY1
>>>>>>> BEFORE
>>>>>>> DELETE
>>>>>>> BEGIN
>>>>>>> DELETE FROM S_DB1B_BY1
>>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>>> DELETE FROM S_DB1G_BY1
>>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>>> DELETE FROM S_DB1N_BY1
>>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>>> DELETE FROM S_DB1O_BY1
>>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>>> DELETE FROM S_DB1Z_BY1
>>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>>> DELETE FROM S_DMIXER_BY1
>>>>>>> WHERE PROG_STORICO = (SELECT PROG FROM __OLD);
>>>>>>> END
>>>>>>> PRIORITY 1;
>>>>>>>
>>>>>>> Have I committed some error?
>>>>>>> Do you have suggestions to increase the speed in the execution of the
>>>>>>> Trigger?
>>>>>> The following is totally trigger-independent. Do you have indices on
>>>>>> the PROG_STORICO fields in your tables?
>>>>>>
>>>>>>
>>>>> Yes I have an index and is part of the PRIMARY KEY
>>>> My last wild guess. Is this a compound index with PROG_STORICO not being
>>>> on first position of the index definition? If it isn't a compound index
>>>> or PROG_STORICO is on the first position then the performance penalty
>>>> possibly is trigger related. Can't help you there. Sorry.
>>>>
>>>>
>>>> --
>>>> 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
>

--
Rinaldi Bruno
Analista Programmatore

Please, consider your environmental responsibility.
Before printing this e-mail ask yourself: "Do I need a hard copy?"

Nota di riservatezza : Il presente messaggio, corredato dei relativi
allegati, contiene informazioni da considerarsi strettamente riservate,
ed ? destinato esclusivamente al destinatario sopra indicato, il quale ?
l'unico autorizzato ad usarlo, copiarlo e, sotto la propria
responsabilit?, diffonderlo. Chiunque ricevesse questo messaggio per
errore o comunque lo leggesse senza esserne legittimato ? avvertito che
trattenerlo, copiarlo, divulgarlo, distribuirlo a persone diverse dal
destinatario ? severamente proibito, ed ? pregato di rinviarlo
immediatamente al mittente distruggendone l'originale.
Grazie.

Confidentiality Notice : This message, together with its annexes,
contains information to be deemed strictly confidential and is destined
only to the addressee(s) identified above who only may use, copy and,
under his/their responsibility, further disseminate it. If anyone
received this message by mistake or reads it without entitlement is
forewarned that keeping, copying, disseminating or distributing this
message to persons other than the addressee(s) is strictly forbidden and
is asked to transmit it immediately to the sender and to erase the
original message received.
Thank you.