Trigger - Once Again

C

cmm

New Member
Hello to everybody,

This is my first question in this forum, and I sincerely apologies for my bad English.

We use allot of triggers in the database (Write and Delete).
These triggers are writing in programs .P and we put them in the database with NO path:

Ex:
Event Procedure
Delete tr040-sn.p

We put the triggers in a library and we use the parameter -trigg in the client .pf to put the path to the library, in the library indicated by the -trigg we only have the triggers use in the application.

Normally we have the database in Linux or Solaris, and the programs and triggers in the W2000.
We notes that when override the DataBase triggers and put them in the source code of the programs (we passed the source code from the triggers and put them in the programs) that improves the performance in more the 300% a program who takes 10 minutes passed to 3 minutes.

Can somebody explain the best way to work with triggers?

Ex:

1 - In the programs o write or delete the records
2 - In the Data Base, directly write the source code in them
3 - In External programs but associated with the database.

:confused:
 
I haven't really seen any issues like this before. As far as I know, there is no right or wrong way to utilise triggers. Whether from the DB schema, or from the 4GL code, there are advantages to both.

For example, some might argue against using schema triggers because functionality is hidden. But DB triggers are very powerful. On the other hand, 4GL triggers are more easily seen by developers, and facilitate better debugging. It's also easier to enable or disable 4GL triggers to during development.

In our application we do the same as you. All of our triggers are precompiled and stored in procedure libraries, and when the database is connected we specify the name of the library in which the triggers are stored.

But 300%!! Something doesn't seem right here. But I suppose there could be a number of possible causes. There are a couple of suggestions why this might happen below, but I'm just guessing really.

1) When you use DB triggers, are the triggers being session-compiled each time the triggers is called. Are your triggers pre-compiled?

2) There is an overhead in loading procedures into memory.Whether this applies to trigger procedures I'm not sure, but I don't see why not. Perhaps you have a really slow disk drive . If you define a trigger in your 4GL code, it is perhaps loaded from disk into memory less times that if the trigger was defined in the DB. But I would guess that Progress is pretty smart in this area, and the loading and re-loading of 4GL files from disk is already highly optimized.

Apart from that I can think of no other reason for such a huge difference in performance. Perhaps somone else can make some suggestions.
 
Hello,

Tanks for your answer, I agree with you this is very strange, but now I talk with one of my programmers and the time he told me before wasn’t correct. The real time is:

Triggers in Database: 9 minutes and 45 seconds
Triggers in Programs: 7 minutes and 12 seconds

The triggers are compiled and now I think that this difference is more acceptable in 1800 records write???
 
Well, that's better although I still can't think of a good enough reason for such a difference, apart from those I've already suggested.

But when you think about it perhaps my second suggestion does make sense. Regardless of whether a procedure is already loaded into memory, a procedure must be (re)initialized whenever it is called. But when the trigger is in the code, the procedure that contains it is already initialized. So in theory the trigger should work faster when called repeatedly, because there is no procedure initialization to perform.

If this is normal behaviour, it will make me think twice about using DB triggers in future.
 
You must log in or register to reply here.
Back
Top