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

Reply via email to