Case dashboard speed optimization

On a system with roughly 25,000 activities, running on a dedicated server, the case dashboard would take over a minute to load. Other orgs have reported similar problems, and in at least two cases the consultants simply removed the upcoming/recent sections from the dashboard since it was just too slow.

Taking for example the upcoming section, on the server above the query would take about 26 seconds. With some optimization, it now takes less than 1 second.

In this query the issue is that we are trying to display some information from a specific activity (the next scheduled activity within 14 days) inline with case data, and you need to remove possible alternate possibilities from the results. Removing the duplicates means re-joining back onto the activity table. You also have to re-join a third time in order to get additional fields from the activity record, since if you try to do this at the same time as using group by, you potentially get fields returned from the other duplicate activities. When there's a lot of activities this creates a bottleneck. So this needed to be revisited. To reduce the number of activities in the query it was decided to use a view. You can't use a temporary table since they can't be joined to themselves.

Another optimization is that when using group by without order by, the engine will likely perform a filesort operation, which you want to avoid. In mysql the solution is that whenever you have group by without order by, add an "order by null" clause.

And yet one more optimization is that all installations will have either the same number or more entries in the activity table than in the case activity table, so in the innermost join we can rearrange to have case activity on the left side, so that there are less rows for the engine to scan.

Query before:

SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, civicrm_activity.activity_type_id, cov_type.label as case_type, cov_type.name as case_type_name, cov_status.label as case_status, cov_status.label as case_status_name, civicrm_activity.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, civicrm_activity.activity_date_time as case_scheduled_activity_date, civicrm_activity.id as case_scheduled_activity_id, aov.name as case_scheduled_activity_type_name, aov.label as case_scheduled_activity_type FROM civicrm_case INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id LEFT JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id LEFT JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1) LEFT JOIN civicrm_activity ON ( civicrm_case_activity.activity_id = civicrm_activity.id AND civicrm_activity.is_current_revision = 1 AND civicrm_activity.status_id = 1 AND civicrm_activity.activity_date_time <= DATE_ADD( NOW(), INTERVAL 14 DAY ) ) LEFT JOIN civicrm_option_group aog ON aog.name = 'activity_type' LEFT JOIN civicrm_option_value aov ON ( civicrm_activity.activity_type_id = aov.value AND aog.id = aov.option_group_id ) LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 1 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 ) LEFT JOIN civicrm_option_group cog_type ON cog_type.name = 'case_type' LEFT JOIN civicrm_option_value cov_type ON ( civicrm_case.case_type_id = cov_type.value AND cog_type.id = cov_type.option_group_id ) LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'case_status' LEFT JOIN civicrm_option_value cov_status ON ( civicrm_case.status_id = cov_status.value AND cog_status.id = cov_status.option_group_id ) LEFT JOIN civicrm_activity ca2 ON ( ca2.id IN ( SELECT cca.activity_id FROM civicrm_case_activity cca WHERE cca.case_id = civicrm_case.id ) AND ca2.is_current_revision = 1 AND ca2.is_deleted = 0 AND ca2.status_id = 1 AND ca2.activity_date_time <= DATE_ADD( NOW(), INTERVAL 14 DAY ) AND civicrm_activity.activity_date_time > ca2.activity_date_time ) WHERE ca2.id IS NULL AND civicrm_activity.is_deleted = 0 AND civicrm_case.is_deleted = 0 AND civicrm_case.status_id != 2 ORDER BY case_scheduled_activity_date ASC;

Creating a view:

create view smaller_act AS SELECT ca.case_id, a.id, a.activity_date_time, a.status_id, a.activity_type_id
 FROM civicrm_case_activity ca
 INNER JOIN civicrm_activity a ON ca.activity_id=a.id
 WHERE a.activity_date_time <= DATE_ADD( NOW(), INTERVAL 14 DAY )
 AND a.is_current_revision = 1 AND a.is_deleted=0 AND a.status_id=1;

Query after, with the relevant parts bolded:

SELECT
civicrm_case.id as case_id,
civicrm_case.subject as case_subject,
civicrm_contact.id as contact_id,
civicrm_contact.sort_name as sort_name,
civicrm_phone.phone as phone,
civicrm_contact.contact_type as contact_type,
civicrm_contact.contact_sub_type as contact_sub_type,
t_act.activity_type_id,
cov_type.label as case_type,
cov_type.name as case_type_name,
cov_status.label as case_status,
cov_status.label as case_status_name,
t_act.status_id,
civicrm_case.start_date as case_start_date,
case_relation_type.label_b_a as case_role,
t_act.desired_date as case_scheduled_activity_date,
t_act.id as case_scheduled_activity_id,
t_act.act_type_name as case_scheduled_activity_type_name,
t_act.act_type AS case_scheduled_activity_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  INNER JOIN
(
SELECT act3.case_id, act3.minid AS id, act_details.activity_date_time AS desired_date, act_details.activity_type_id,
act_details.status_id, aov.name AS act_type_name, aov.label AS act_type
FROM smaller_act act_details INNER JOIN
(
  SELECT t.case_id, MIN(act2.id) as minid FROM smaller_act act2 INNER JOIN
    (SELECT smaller_act.case_id, MIN(smaller_act.activity_date_time) AS mindate FROM smaller_act
     GROUP BY smaller_act.case_id ORDER BY NULL
    ) AS t
  ON act2.activity_date_time = t.mindate
  GROUP BY t.case_id ORDER BY NULL
) AS act3
ON act3.minid = act_details.id
LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
LEFT JOIN civicrm_option_value aov ON (aov.option_group_id = aog.id AND aov.value = act_details.activity_type_id)
) AS t_act
        ON t_act.case_id = civicrm_case.id
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 = 1
      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 )
LEFT JOIN civicrm_option_group cog_type
 ON cog_type.name = 'case_type'
LEFT JOIN civicrm_option_value cov_type
 ON ( civicrm_case.case_type_id = cov_type.value
      AND cog_type.id = cov_type.option_group_id )
LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'
LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
WHERE civicrm_case.is_deleted     = 0
AND civicrm_case.status_id != 2 ORDER BY case_scheduled_activity_date ASC