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
