John,
You are using the correlated sub-select on the first update. This may seem oddball, but for the second update try: UPDATE upc_master SET in_stock_qty = t2.inv_count, + last_inv_date = .#DATE + FROM upc_master t1,tinventory t2 + WHERE t1.upc_code IN (SELECT upccode FROM tinventory) AND + t1.upc_code = t2.upc_code This assumes that upc_code is indexed in both tables. 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 11:55 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Triggers and performance Emmitt, I am not clear on how you recommend that I use the correlated sub-select. When the trigger fires, it only acts on one row of data. Below is the code that I use to update the UPC_MASTER table (code that causes the trigger to fire). The column UPC_CODE is a primary key in the UPC_MASTER table. The column UPC_CODE and MFGCODE in the temporary table tINVENTORY are indexed. UPDATE upc_master SET in_stock_qty = 0,last_inv_date = .#DATE + WHERE mfgcode IN (SELECT mfgcode FROM Inventory) UPDATE upc_master SET in_stock_qty = t2.inv_count, + last_inv_date = .#DATE + FROM upc_master t1,tinventory t2 + WHERE t1.upc_code = t2.upc_code If I set the bypass to null vTrace_Off, the performance improves but the performance is still degraded significantly. I will do some more testing this afternoon. I must be overlooking something. Thanks for your help. John From: [email protected] [mailto:[email protected]] On Behalf Of Emmitt Dove Sent: Tuesday, August 04, 2009 11:12 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Triggers and performance John, It isn't a million rows, but our perpetual inventory tables may have 250000-350000 rows. As part of an "inventory rollover" process after end-of-month processing is complete, we will do a mass update to the table to null some columns that were previously set at month end. The trigger fires for each row, but uses the signal method to determine that it should immediately RETURN. This is very fast, but the catch is that a) it uses a correlated sub-select to force index usage on the table's primary key, and b) is operating only on a subset of the table - maybe 5000 rows. Look at your mass update to determine if a) you can limit the number of rows to be processed by determining only those that need to be updated, and b) you can speed the update using a correlated sub-select. Your trigger is going to be fired unless you drop it before the mass update and recreate it afterwards (or, more simply, replace the stored procedure with a one-liner that just says "RETURN 0" then put the original back after the update.) The disadvantage to that is that if anyone performs an auditable activity while the mass update is in progress the audit is lost. 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 10:38 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Triggers and performance Hi Doug, It is true that the Computer and Mac do not change but the user and form name can change. The master product table is used throughout the application. Also, none of that code is accessed if vTRIG_OFF is NULL. Even when vTRIG_OFF is NULL the performance is impacted. I even commented out the code and it still impacted performance when there is a large scale update after a physical inventory. These businesses do inventory cycle counts several times a week (sometimes daily). John From: [email protected] [mailto:[email protected]] On Behalf Of Doug Hamilton Sent: Tuesday, August 04, 2009 10:18 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Triggers and performance John, since the computer, user, formname and macadddr aren't going to change during an R:Base session, it might help performance to declare them when an app starts or put them in an OnBeforeStart form EEP. I've found that on some machines (mine) those CVALs take a little longer to evaluate. Doug John Engwer wrote: 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
