Here is a bit of good news. While in Razzak's Super Advanced eXtreme 9.0 class I performed the trigger test below using V9. It is lightning fast in V9. There was little difference in speed when the trigger was active or inactive.
Also, the new enhanced DBGrid in V9 is awesome. It has many features found in a DBGrid, ListView and a report all rolled into one object.. VERY POWERFUL and USEFUL! John From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer Sent: Tuesday, August 04, 2009 3:36 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Triggers and performance Thanks all for your input.. Here is what I have found. The following test proved to me that triggers add significant overhead to processing. I tested an update of 100000 records with a trigger and the same update without a trigger. The only code contained within the trigger was a RETURN. The update that fired the "no code" trigger took 20 times longer to complete. That explains why my update of more than a million records toke so long, even though I have a shunt to bypass the trigger code. I also noticed that, when a trigger is active, the memory usage nearly pegs at max memory and the CPU cycles stay at 50%. I have a Dell PC running Vista Business, Dual core Intel processor and 4 gig of memory (32 bit OS can only use 3 gig). My conclusion is that you pay a big price in system performance if you use a trigger on a large table that is updated frequently. Here is the code that I used for my test. --Trigger test 08/04/09 clear all var --without using trigger alter table UPC_MASTER drop trigger SET VAR vno_trigger_start TIME = .#TIME UPDATE UPC_MASTER SET mfgcode = mfgcode WHERE COUNT < 100000 SET VAR vno_trigger_end TIME = .#TIME SET VAR vno_trigger_total = (.vno_trigger_end - .vno_trigger_start) --Run time just under 3 Seconds --With trigger, The trigger has no code except for a RETURN alter table UPC_MASTER add trigger update before TRIG_UPC_AUDIT_BF_UPDATE SET VAR vtrigger_start TIME = .#TIME UPDATE UPC_MASTER SET mfgcode = mfgcode WHERE COUNT < 100000 SET VAR vtrigger_end TIME = .#TIME SET VAR vtrigger_total = (.vtrigger_end - .vtrigger_start) --Run time 60+ seconds RETURN John
