How many times are you preparing the update statement? Maybe you are just measuring the effort required to prepare 55000 UPDATE statements.
What is the purpose of counting all the rows of several tables before firing the trigger? If you are attempting to avoid running UPDATE on an empty table, then you are failing. The trigger will fire as soon as any one of the mentioned tables has at least one row, without guaranteeing that the other tables are not empty. -----Ursprüngliche Nachricht----- Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish Symington Gesendet: Montag, 04. Juli 2016 10:27 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] Trigger slowness even when it's not fired Hello, I have a curious situation involving a trigger, which I’m at a loss to explain. I’m wondering if someone who knows more about the insides of SQLite can tell me more about why it’s happening. I’m running SQLite 3.8.7. The trigger code is at the bottom of this email. It’s a straightforward AFTER UPDATE trigger, firing when any of three fields is updated and when the old value is not the same as the new value for any of those fields. There’s also a test I’ve put in there to make sure that some tables which I’m looking to update are not empty. The table TriggerLog is a table I’ve added for testing. If the trigger fires, a row is inserted into the log. I have tested that this works when the trigger fires. There are then a number of UPDATE statements to set flags on other tables. In the circumstances I’m running this in at the moment, there are no rows in QuoteCalc, SaleCalc, TxnCalc etc., so the trigger’s code won’t actually fire. I have verified this by inspecting TriggerLog after the text; there are no rows reporting that ContactCalcUpdate8 has fired. During the test, a row on ContactCalc has one of its ContactCalc_Phone, ContactCalc_Email or ContactCalc_Website fields updated. This occurs on various rows of ContactCalc approximately 5,000 times. This stage of the test takes approximately 22 seconds. If I remove all of the UPDATE rows from this trigger, and make no other changes, the test takes approximately 12 seconds. I don’t understand why removing code from the body of a trigger which doesn’t fire makes things faster. I have also tried replacing the WHERE clauses of all of the UPDATE statements with WHERE 1=0 to eliminate the possibility of the IN statement being the culprit; it’s still slow. I would very much like to understand what’s going on here; perhaps someone can enlighten me. Thanks, Hamish // When ContactCalc's phone, email or website changes, update the phone, email and website fields in // QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc. // Also update GroupHead phone, email and web fields for QuoteCalc, SaleCalc, PurchaseCalc, TxnCalc CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE OF ContactCalc_Phone, ContactCalc_Email, ContactCalc_Website ON ContactCalc WHEN ( Old.ContactCalc_Phone != New.ContactCalc_Phone OR Old.ContactCalc_Email != New.ContactCalc_Email OR Old.ContactCalc_Website != New.ContactCalc_Website ) AND ( ( SELECT COUNT(QuoteCalc_QuoteUUID) FROM QuoteCalc ) > 0 OR ( SELECT COUNT(SaleCalc_SaleUUID) FROM SaleCalc ) > 0 OR ( SELECT COUNT(PurchaseCalc_PurchaseUUID) FROM PurchaseCalc ) > 0 OR ( SELECT COUNT(TxnCalc_TxnUUID) FROM TxnCalc ) > 0 ) BEGIN INSERT INTO TriggerLog( TriggerLog_Name ) VALUES ('ContactCalcUpdate8'); UPDATE QuoteCalc SET QuoteCalc_UpdateContactFlag = 1 WHERE QuoteCalc_ContactUUID = New.ContactCalc_ContactUUID; UPDATE SaleCalc SET SaleCalc_UpdateBillingContactFlag = 1 WHERE SaleCalc_SaleUUID IN ( SELECT Sale_UUID FROM Sale WHERE Sale_BillingContactUUID = New.ContactCalc_ContactUUID ); UPDATE SaleCalc SET SaleCalc_UpdateShippingContactFlag = 1 WHERE SaleCalc_SaleUUID IN ( SELECT Sale_UUID FROM Sale WHERE Sale_ShippingContactUUID = New.ContactCalc_ContactUUID ); UPDATE TxnCalc SET TxnCalc_UpdateContactFlag = 1 WHERE TxnCalc_TxnUUID IN ( SELECT TxnCalc_TxnUUID FROM TxnCalc WHERE TxnCalc_ContactUUID = New.ContactCalc_ContactUUID ); UPDATE PurchaseCalc SET PurchaseCalc_UpdateContactFlag = 1 WHERE PurchaseCalc_PurchaseUUID IN ( SELECT PurchaseCalc_PurchaseUUID FROM PurchaseCalc WHERE PurchaseCalc_ContactUUID = New.ContactCalc_ContactUUID ); UPDATE ContactCalc SET ContactCalc_UpdateGroupHeadFlag = 1 WHERE ContactCalc_ContactUUID IN ( SELECT Contact_UUID FROM Contact WHERE Contact_LinkedContactUUID = New.ContactCalc_ContactUUID ); UPDATE QuoteCalc SET QuoteCalc_UpdateGroupHeadFlag = 1 WHERE QuoteCalc_QuoteUUID IN ( SELECT Quote_UUID FROM Quote, Contact WHERE Quote_ContactUUID = Contact_UUID AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID ); UPDATE SaleCalc SET SaleCalc_UpdateBillingGroupHeadFlag = 1 WHERE SaleCalc_SaleUUID IN ( SELECT Sale_UUID FROM Sale, Contact WHERE Sale_BillingContactUUID = Contact_UUID AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID ); UPDATE SaleCalc SET SaleCalc_UpdateShippingGroupHeadFlag = 1 WHERE SaleCalc_SaleUUID IN ( SELECT Sale_UUID FROM Sale, Contact WHERE Sale_ShippingContactUUID = Contact_UUID AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID ); UPDATE TxnCalc SET TxnCalc_UpdateGroupHeadFlag = 1 WHERE TxnCalc_TxnUUID IN ( SELECT Txn_UUID FROM Txn, Contact WHERE Txn_ContactUUID = Contact_UUID AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID ); UPDATE PurchaseCalc SET PurchaseCalc_UpdateGroupHeadFlag = 1 WHERE PurchaseCalc_PurchaseUUID IN ( SELECT Purchase_UUID FROM Purchase, Contact WHERE Purchase_ContactUUID = Contact_UUID AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID ); END; _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users