Dan I think that I will need to revise my code and eliminate triggers on large tables. The triggers are nice and clean but they come with a big hit on performance.
John From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Dan Goldberg Sent: Tuesday, September 09, 2008 11:13 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Triggers Based on our requirements I use audit tables but do not use triggers. I just use a command in my application or form when I want to capture audit data. Something simple like this. if datachanged = 'true' then insert into audittable values ((cval('netuser')),.....) endif I do realize this is more work because you have to put it in everywhere you want to audit data. Dan Goldberg _____ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Engwer Sent: Tuesday, September 09, 2008 7:51 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Triggers Thanks for your input Larry. I have been using triggers for a long time and I am aware that there is overhead involved. I never realized how much overhead until I applied them to a very large table and did updates of large quantities of data. When I drop the trigger the update is lighting fast (just as you pointed out). The problem with dropping the trigger, is that it is dropped for other users also. This means that other users will not be writing audit files when the trigger is disabled. This is a serious problem for me. John From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Lawrence Lustig Sent: Tuesday, September 09, 2008 9:45 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Triggers << The problem that I have is that the trigger adds an enormous amount of processing time (10 times longer), even when the trigger is being bypassed. As a work around, I drop the trigger when the trigger when it is not needed, instead of using the bypass. Is this behavior normal or do I have something wrong with my code or my process. >> This behavior is normal and expected as far as I know. The bulk of the overhead with triggers is in detecting the existence of the trigger, finding the stored procedure code, and executing it. The fact that you skip over most of the code in your trigger doesn't eliminate that basic overhead. R:Base is extraordinarily fast at basic UPDATE operations, but when you stick an interpreted code process in the middle of update to each row, you're invariably going to slow down the operation substantially. Even if RBTI were to go all out optimizing trigger performance, I'm sure that using a trigger will slow down an update by a factor of several hundred percent. You're left with dropping and re-adding the trigger, which is not a great solution (the purpose of a trigger, after all, is to become an unavoidable part of the database structure), accepting the performance hit, or refactoring your database in some way to separate out the "trigger" and "non-trigger" rows into separate tables. -- Larry
