John,
Why not in your first SELECT . WHERE CURRENT OF SYS_NEW retrieve all the values into variables then do the INSERT using the variables? As it is, you are doing two SELECTs from UPC_MASTER; you can reduce that to one, and the advantage is that the WHERE CURRENT OF SYS_NEW doesn't have to do a lookup on UPC_CODE, indexed or not. Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer Sent: Tuesday, August 04, 2009 9:49 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Triggers and performance Thanks Emmitt, Your comments are encouraging. Below is the code that I use. IF vTRIG_OFF IS NULL THEN RETURN 0 ENDIF SET VAR vCOMPUTER_NAME TEXT = (CVAL('COMPUTER')) SET VAR vID TEXT = (CTXT((CVAL('USER')))) SET VAR vMAC TEXT = (GETVAL('GETMACADDR','1')) SET VAR vRBTI_FORM_FORMNAME TEXT = .RBTI_FORM_FORMNAME sel UPC_CODE into vUPC_AUDIT indi isNULL + from UPC_MASTER whe CURRENT OF SYS_NEW INSERT INTO UPC_MASTER_AUDIT + (UPC_CODE,MFGCODE,MFG_STYLE,MFG_SIZE,MFG_WIDTH,MFG_COLOR,MFG_DESC,W_PRICE,MA RKUP,+ Uplift_Dol,In_Stock_QTY,AUTH_QTY,Min_Order,Last_Trans_DT,DEM,LAST_INV_QTY,+ LAST_INV_DATE,SALE_DISCO,COM_AMT,FORM_NAME_UPDATE,DT_UPC_UPDATE,USERID_UPC_U PDATE,MAC_UPC_UPDATE,COMP_NAME_UPC_UPDATE ) + SELECT UPC_CODE,MFGCODE,MFG_STYLE,MFG_SIZE,MFG_WIDTH,MFG_COLOR,MFG_DESC,W_PRICE,MAR KUP,+ Uplift_Dol,In_Stock_QTY,AUTH_QTY,Min_Order,Last_Trans_DT,DEM,LAST_INV_QTY,+ LAST_INV_DATE,SALE_DISCO,COM_AMT,.vRBTI_FORM_FORMNAME,.#NOW,.vID,.vMAC,.vCOM PUTER_NAME + FROM UPC_MASTER + whe UPC_CODE = .vUPC_AUDIT RETURN John From: [email protected] [mailto:[email protected]] On Behalf Of Emmitt Dove Sent: Tuesday, August 04, 2009 9:38 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Triggers and performance John, I can't see how the trigger itself could be causing performance issues. I assume you've streamlined the trigger itself. If it passes initial decisions about whether there is something to be audited, what does it do then - update another table, or insert? We have triggers on three inventory tables that are all large. Any auditable activity writes 1-2 records in one of three external databases via an Oterro DSN. No performance hits. Try this: At the top of the trigger code, before you do anything else: SET VAR texecute INTEGER IF texecute IS NOT NULL AND texecute = 0 THEN RETURN 0 ENDIF . rest of trigger code Now, whenever you want to avoid the trigger executing: SET VAR texecute INTEGER = 0 . do whatever CLEAR VAR texecute The trigger will fire, but do nothing (very quickly.) Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer Sent: Tuesday, August 04, 2009 9:20 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Triggers and performance A while ago I was fond of using triggers to trap and collect data until I drove my application to its knees with triggers. I found that using a trigger on a heavily accessed table can have a serious impact on performance. I have tried using bypass code to shunt the trigger but it still causes performance problems. Just the fact that the trigger is fired on every table update creates tremendous overhead in an active system. Is there a way to turn a trigger on and off for a specific user and process? I do not want to drop the trigger because I want the audit data for processes that other users are performing. Here is my scenario: A master table of all products (typically 1 - 2 million records). This table is heavily accessed. I want to create an audit file to track changes to the table (update trigger works well except for the performance issue) I would like to bypass the trigger during certain processes such as the update that occurs after a physical inventory. Any recommendations will be appreciated. Razzak - Schedule permitting, could we spend a little time discussing triggers in the advanced training next week? John
