Answers.Body: <p>Nice find. Thanks for posting. Yes the inner join is generally better than a subquery.</p>
  <p>To answer your secondary question, to get it merged you need to create a ticket at <a href="https://lab.civicrm.org/dev/core/-/issues/new" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/-/issues/new</a> and then if you're able to also make an associated pull request at <a href="https://github.com/civicrm/civicrm-core" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core</a> against master. See also <a href="https://docs.civicrm.org/dev/en/latest/tools/git/#contributing" rel="nofollow noreferrer">https://docs.civicrm.org/dev/en/latest/tools/git/#contributing</a>. Then someone needs to confirm and test it.</p>
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-14T04:41:59.613Z
Answers.LastActivityDate: 2021-04-14T04:41:59.613Z
Answers.UserDisplayName: Demerit
Body: <p>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:</p>
  <p>civicrm_activity_before_update
  civicrm_activity_before_delete</p>
  <p>Original SQL (~4 seconds):</p>
  <pre><code>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);
  </code></pre>
  <p>~1000x faster SQL to do the same thing:</p>
  <pre><code>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;
  
  </code></pre>
  <p>I tracked this down to the following PHP file:
  sites/all/modules/civicrm/Civi/Core/Container.php</p>
  <p>The patch to fix the SQL payload in the file:</p>
  <pre><code>--- 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' =&gt; 'civicrm_activity',
               'when' =&gt; 'BEFORE',
               'event' =&gt; ['UPDATE', 'DELETE'],
  -            'sql' =&gt; &quot;\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&quot;,
  +            'sql' =&gt; &quot;\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&quot;,
             ],
           ],
         ]
  </code></pre>
  <p>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.</p>
  <p>Any thoughts on this? Functionally the two queries are identical. What does it take to get this merged upstream?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-13T15:18:32.833Z
Comments.Text: Nice find. 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.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-13T19:40:17.127Z
Comments.Text: Demerit - i suggest you paste above as an answer since the OP question seems to be "What does it take to get this merged upstream?" which you have answered well
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-14T04:42:56.433Z
Comments.Text: Thanks Petednz-fuzion
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-05-06T13:05:54.740Z
Comments.Text: Bug filed here: https://lab.civicrm.org/dev/core/-/issues/2576
Comments.UserDisplayName: Gordan Bobić
Tags: <issues><core><performance><bug-reporting>
Title: Performance Bug in Trigger Causing ~1000x Slowdown
ThreadText: # Performance Bug in Trigger Causing ~1000x Slowdown
  
  (Asked by: username_0 on 2021-04-13)
  
  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?
  
  **Tags:** issues, core, performance, bug-reporting
  
  > **Comments:**
  > **username_1**: Nice find. 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.  
  > **username_2**: username_1 - i suggest you paste above as an answer since the OP question seems to be "What does it take to get this merged upstream?" which you have answered well  
  > **username_1**: Thanks username_2-username_2  
  > **username_0**: Bug filed here: https://lab.civicrm.org/dev/core/-/issues/2576  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-04-14. Score: 4 | Accepted answer
  
  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.
  
  
  ---
  
  