Answers.Body: <p>Your reasoning is good. The one thing to watch out for is ACL clauses, but I don't see any being used in that function.</p>
  <p>Perhaps the way to go here would be to modify the <code>getCaseActivityCountQuery()</code> function to use APIv4 instead of calling <code>getCaseActivityQuery()</code>.</p>
  <p>You can submit a pull-request to <a href="https://github.com/civicrm/civicrm-core" rel="nofollow noreferrer">CiviCRM on GitHub</a>, either by checking out the <code>master</code> branch locally and pushing your changes to a fork, or by using the online editor here: <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Case/BAO/Case.php#L454" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/blob/master/CRM/Case/BAO/Case.php#L454</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-18T22:26:26.020Z
Answers.Text: Thanks! I will do that.
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-18T22:37:08.270Z
Answers.Text: Seems like the function is only called in one place, line 625 of the same file: Civi::$statics[__CLASS__]['totalCount'][$type] = $totalCount = CRM_Core_DAO::singleValueQuery(self::getCaseActivityCountQuery($type, $userID, $condition));
  
  So I will replace that with a call to api4 to get the count of all cases on the system
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-19T03:14:28.810Z
Answers.Text: Sounds good. Thanks.
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-18T18:24:03.150Z
Answers.LastActivityDate: 2024-11-18T18:24:03.150Z
Answers.UserDisplayName: Coleman
Body: <p>Doing some performance tuning so I turned on the slow query log and found this absolute beast taking 3 minutes:</p>
  <pre><code>SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 19099, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id, ANY_VALUE(case_status.label) AS case_status, ANY_VALUE(civicrm_case_type.title) AS case_type       FROM civicrm_case
      INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
      INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id
      LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
      LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' )
      LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value
        AND option_group_case_status.id = case_status.option_group_id )
      LEFT JOIN civicrm_case_activity ca4
        ON civicrm_case.id = ca4.case_id
      LEFT JOIN civicrm_activity t_act
        ON t_act.id = ca4.activity_id
        AND t_act.is_current_revision = 1        LEFT JOIN civicrm_phone
        ON civicrm_phone.contact_id = civicrm_contact.id
          AND civicrm_phone.is_primary = 1
      LEFT JOIN civicrm_relationship case_relationship
        ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 19099)
        OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 19099))
          AND case_relationship.is_active
          AND case_relationship.case_id = civicrm_case.id
      LEFT JOIN civicrm_relationship_type case_relation_type
        ON case_relation_type.id = case_relationship.relationship_type_id
          AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted &lt;&gt; 1  GROUP BY case_id  ORDER BY activity_date_time ASC)
  </code></pre>
  <p>It seems that all this is actually doing is returning a count of all non-deleted cases on the system! The contact ID is irrelevant and all the left joins do not affect the final count,because left joins can't decrease the row count and the <code>group by case_id</code> ensures that they do not increase it either.</p>
  <p>I ran this: <code>select count(*) from civicrm_case where is_deleted &lt;&gt; 1;</code>and I got the same result.</p>
  <p>I traced the source of the query to this file <code>vendor/civicrm/civicrm-core/CRM/Case/BAO/Case.php</code> line 454:</p>
  <pre><code>  public static function getCaseActivityCountQuery($type, $userID, $condition = NULL) 
    {
      return sprintf(&quot; SELECT COUNT(*) FROM (%s) temp &quot;, self::getCaseActivityQuery($type, $userID, $condition));
    }
  </code></pre>
  <p>The query returned by <code>getCaseActivityQuery</code> no doubt has its place but wrapping it in a <code>count(*)</code> means that all its hard work is for naught - all the left joins can simply be removed and the count will be the same.</p>
  <p>The only bits that I could imagine might possibly affect the count are the inner joins on <code>civicrm_case_contact</code> and <code>civicrm_contact</code> to ensure that the target contact has not been deleted. However, if Civi is working correctly then a contact's cases  are deleted when the contact is deleted so this should not make any difference. But even leaving in these two joins, the query runs thousands of times faster than the original one.</p>
  <p>Can someone who is familiar with this part of the code please advise on whether my reasoning is correct?</p>
  <p>And if it is, please can someone advise on how I can submit this change to core?</p>
  
Tags: <civicase><core><sql><performance>
Title: Is my reasoning correct in regard to speeding up this core query, and if so how can I contribute the change to core?
ThreadText: # Is my reasoning correct in regard to speeding up this core query, and if so how can I contribute the change to core?
  
  (Asked by: username_0 on 2024-11-18)
  
  Doing some performance tuning so I turned on the slow query log and found this absolute beast taking 3 minutes:
  
  ```
  SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 19099, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id, ANY_VALUE(case_status.label) AS case_status, ANY_VALUE(civicrm_case_type.title) AS case_type       FROM civicrm_case
      INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
      INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id
      LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
      LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' )
      LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value
        AND option_group_case_status.id = case_status.option_group_id )
      LEFT JOIN civicrm_case_activity ca4
        ON civicrm_case.id = ca4.case_id
      LEFT JOIN civicrm_activity t_act
        ON t_act.id = ca4.activity_id
        AND t_act.is_current_revision = 1        LEFT JOIN civicrm_phone
        ON civicrm_phone.contact_id = civicrm_contact.id
          AND civicrm_phone.is_primary = 1
      LEFT JOIN civicrm_relationship case_relationship
        ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 19099)
        OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 19099))
          AND case_relationship.is_active
          AND case_relationship.case_id = civicrm_case.id
      LEFT JOIN civicrm_relationship_type case_relation_type
        ON case_relation_type.id = case_relationship.relationship_type_id
          AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1  GROUP BY case_id  ORDER BY activity_date_time ASC)
  
  ```
  
  It seems that all this is actually doing is returning a count of all non-deleted cases on the system! The contact ID is irrelevant and all the left joins do not affect the final count,because left joins can't decrease the row count and the `group by case_id` ensures that they do not increase it either.
  
  I ran this: `select count(*) from civicrm_case where is_deleted <> 1;`and I got the same result.
  
  I traced the source of the query to this file `vendor/civicrm/civicrm-core/CRM/Case/BAO/Case.php` line 454:
  
  ```
    public static function getCaseActivityCountQuery($type, $userID, $condition = NULL) 
    {
      return sprintf(" SELECT COUNT(*) FROM (%s) temp ", self::getCaseActivityQuery($type, $userID, $condition));
    }
  
  ```
  
  The query returned by `getCaseActivityQuery` no doubt has its place but wrapping it in a `count(*)` means that all its hard work is for naught - all the left joins can simply be removed and the count will be the same.
  
  The only bits that I could imagine might possibly affect the count are the inner joins on `civicrm_case_contact` and `civicrm_contact` to ensure that the target contact has not been deleted. However, if Civi is working correctly then a contact's cases are deleted when the contact is deleted so this should not make any difference. But even leaving in these two joins, the query runs thousands of times faster than the original one.
  
  Can someone who is familiar with this part of the code please advise on whether my reasoning is correct?
  
  And if it is, please can someone advise on how I can submit this change to core?
  
  **Tags:** civicase, core, sql, performance
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2024-11-18. Score: 3 | Accepted answer
  
  Your reasoning is good. The one thing to watch out for is ACL clauses, but I don't see any being used in that function.
  
  Perhaps the way to go here would be to modify the `getCaseActivityCountQuery()` function to use APIv4 instead of calling `getCaseActivityQuery()`.
  
  You can submit a pull-request to [CiviCRM on GitHub](https://github.com/civicrm/civicrm-core), either by checking out the `master` branch locally and pushing your changes to a fork, or by using the online editor here: <https://github.com/civicrm/civicrm-core/blob/master/CRM/Case/BAO/Case.php#L454>
  
  > **Comments:**
  > **username_0**: Thanks! I will do that.  
  > **username_0**: Seems like the function is only called in one place, line 625 of the same file: Civi::$statics[__CLASS__]['totalCount'][$type] = $totalCount = CRM_Core_DAO::singleValueQuery(self::getCaseActivityCountQuery($type, $userID, $condition));  So I will replace that with a call to api4 to get the count of all cases on the system  
  > **username_1**: Sounds good. Thanks.  
  
  ---
  
  