5

While looking for the root cause of a performance issue for a client, I tracked it down to a couple of triggers performing an inefficient UPDATE statement:

civicrm_activity_before_update civicrm_activity_before_delete

Original SQL (~4 seconds):

UPDATE civicrm_case SET modified_date = CURRENT_TIMESTAMP WHERE id IN (SELECT ca.case_id FROM civicrm_case_activity ca WHERE ca.activity_id = OLD.id);

~1000x faster SQL to do the same thing:

UPDATE civicrm_case, civicrm_case_activity SET civicrm_case.modified_date = CURRENT_TIMESTAMP WHERE civicrm_case.id = civicrm_case_activity.case_id AND civicrm_case_activity.activity_id = OLD.id;

I tracked this down to the following PHP file: sites/all/modules/civicrm/Civi/Core/Container.php

The patch to fix the SQL payload in the file:

--- Container.php.orig  2021-04-12 11:30:35.039270421 +0100
+++ Container.php   2021-04-12 11:32:00.485650901 +0100
@@ -286,7 +286,7 @@
             'table' => 'civicrm_activity',
             'when' => 'BEFORE',
             'event' => ['UPDATE', 'DELETE'],
-            'sql' => "\nUPDATE civicrm_case SET modified_date = CURRENT_TIMESTAMP WHERE id IN (SELECT ca.case_id FROM civicrm_case_activity ca WHERE ca.activity_id = OLD.id);\n",
+            'sql' => "\nUPDATE civicrm_case, civicrm_case_activity SET civicrm_case.modified_date = CURRENT_TIMESTAMP WHERE civicrm_case.id = civicrm_case_activity.case_id AND civicrm_case_activity.activity_id = OLD.id;\n",
           ],
         ],
       ]

The bug doesn't manifest if you rewrite the UPDATE as a SELECT, but in the UPDATE form it takes an absurdly long time when a sub-select is used. Tested on MariaDB 10.4 and 10.5.

Any thoughts on this? Functionally the two queries are identical. What does it take to get this merged upstream?

4
4

Nice find. Thanks for posting. Yes the inner join is generally better than a subquery.

To answer your secondary question, to get it merged you need to create a ticket at https://lab.civicrm.org/dev/core/-/issues/new and then if you're able to also make an associated pull request at https://github.com/civicrm/civicrm-core against master. See also https://docs.civicrm.org/dev/en/latest/tools/git/#contributing. Then someone needs to confirm and test it.

When you do post your ticket, it would help to also describe the parameters (e.g. # of activities in the database, any other relevant variables...) so that others can reproduce/evaluate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.