Creating a trigger causes SQLite to insert a row into the sqlite_master table which contains the text you supply for the trigger. No code is generated at this time. You can verify this by creating a trigger that references undefined fields. It willl succeed. But entering a statement that references the trigger will fail with an undefined field error.
When preparing a statement, SQLite will check for any triggers defined on the referenced tables, prepare the appropriate "trigger programs" and insert calls to these in the original "statement program". A simple UPDATE <table> set <field>=<value> where <key_condition> will translate into about 40 instructions; adding your trigger adds an additional estimated 400 instructions of trigger program. Basically, triggers are compiled into the statements that MAY cause them to fire; the WHEN clause is evaluated within the context of the trigger, even if the trigger does not fire at all. -----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 11:29 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Trigger slowness even when it's not fired Hi there, > How many times are you preparing the update statement? Maybe you are just > measuring the effort required to prepare 55000 UPDATE statements. OK, this sounds plausible. But given the trigger doesn’t even fire, why does the statement need to be prepared? Is it not prepared just before running? > 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. In the specific test scenario, all those tables are empty. It’s simply a way of ensuring that this trigger doesn’t fire for this test. I’ve just tried it with the following code, and it’s still slow, until I remove the update statements, when it’s faster. 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 1=0 BEGIN INSERT INTO TriggerLog( TriggerLog_Name ) VALUES ('ContactCalcUpdate8'); … as before ... > > -----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 _______________________________________________ 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
