Thanks very much indeed for this. SQLite is like an onion; just when I think I’ve got to the last layer and understood it, I find there’s another layer to peel back and understand…
Best wishes, Hamish > On 4 Jul 2016, at 11:08 am, Hick Gunter <h...@scigames.at> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
