3

The activity tab on the contact summary page takes much time to load and list the activities.

Below is the time-consuming query which takes around 20-25 sec each for the contacts having ~50 activity records.

Query 1

INSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted )
SELECT     ac.activity_id,
       ac.contact_id,
       ac.record_type_id,
       c.sort_name,
       c.is_deleted
FROM       {$activityTempTable}
INNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id )
INNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id )
INNER JOIN civicrm_contact c ON c.id = ac.contact_id
WHERE ac.record_type_id != %1

followed by

INSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter )
{$select}, count(ac.contact_id)
FROM       {$activityTempTable}
INNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id )
INNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id )
INNER JOIN civicrm_contact c ON c.id = ac.contact_id
WHERE ac.record_type_id = %1
GROUP BY ac.activity_id

from https://github.com/civicrm/civicrm-core/blob/master/CRM/Activity/BAO/Activity.php#L1022-L1048

There is an open issue CRM-20481 which seems to have added these queries. If I revert those changes, the loading time is even worse, as it will then call getActivities() function twice to retrieve the count and list of activities.

Anyone else experienced the same? i,e, slow loading of activity tab after upgrade?

Our MySQL is on 5.5, incase slow queries relates to only this version.

UPDATE - The temp table is created with the following query -

CREATE TABLE temp_table (  fixed_sort_order INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,activity_id int unsigned,
activity_date_time datetime,
source_record_id int unsigned,
status_id int unsigned,
subject varchar(255),
source_contact_name varchar(255),
activity_type_id int unsigned,
activity_type varchar(128),
case_id int unsigned,
case_subject varchar(255),
campaign_id int unsigned,   UNIQUE KEY ( activity_id )
    ) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

which has an index for fixed_sort_order and activity_id

enter image description here

Your Answer

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

Browse other questions tagged or ask your own question.