3

Digging in to why one of our larger clients is suffering more from slowness of Activity searches or loading of Activity tab for contacts.

Comparing it to other dbs what stands out for this database is the sheer number of rows in civicrm_activity_contact - and while they also have a lot of Activities, they have no where near as many as other databases. A sample of data below where A is the db that is grinding slowly

| db | Activities | Activity Contacts | average contacts per activity |

| A | 626,145 | 22,270,065 | 36 |

| B | 180,000 | 5,680,000 | 32 |

| C | 3,718,634 | 7,722,346 | 2 |

And it is DB 'A' that is seriously slow running Activity related queries.

Most of the Activity Contacts are on 'bulk mail' Activities

The activity search makes use of multiple joins of civicrm_activity, civicrm_activity_contact and civicrm_contact table. It leads to millions and millions of rows again and again which causes an indefinite loop of the query which never finishes or takes much longer time to complete. A sample query which is executed by hitting the search button on Find Activity form.

SELECT count( DISTINCT ( civicrm_activity.id ) ) as rowCount  

FROM civicrm_contact contact_a
  LEFT JOIN civicrm_activity_contact ON ( civicrm_activity_contact.contact_id = contact_a.id )  

  LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
     AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) 

  INNER JOIN civicrm_contact ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 ) 

  LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') 

  LEFT JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value 
      AND option_group_activity_type.id = activity_type.option_group_id ) 

  LEFT JOIN civicrm_activity_contact ac ON ( ac.activity_id = civicrm_activity_contact.activity_id AND ac.record_type_id = 2)

  INNER JOIN civicrm_contact source_contact ON (ac.contact_id = source_contact.id) 

WHERE  (  civicrm_activity_contact.record_type_id = 3 
    AND civicrm_activity.is_test = 0 
    AND civicrm_activity.status_id IN ("1", "2") )  
    AND  ( 1 )  
    AND  (activity_type.component_id IS NULL OR activity_type.component_id <> 5)  
    AND  (activity_type.component_id IS NULL OR activity_type.component_id <> 7)
  • To clarify do you mean A and C are ok but B is slow? Does B have a higher % of activities with is_deleted=1? – Demerit Feb 6 '18 at 23:34
  • sorry for missing out the key letter, edited now. A is the 'problem' site, so it is not the largest in terms of Activities by a long shot, but it is way the biggest in terms of Activity Contacts and that is what seems to be dragging it under – petednz - fuzion Feb 7 '18 at 4:06
  • Can you see the actual query that is slow (using your favourite method - lately mine is SHOW FULL PROCESSLIST in mysql while the slowness is happening). Just browsing github.com/civicrm/civicrm-core/blob/master/CRM/Activity/BAO/… in deprecatedGetActivities(), which is where for example the activity tab on a contact happens, it could definitely be that large activity_contacts is a drag. If so then I don't see a quick workaround other than editing that directly. Or providing your own tab/page (e.g. for 4.6 there is github.com/systopia/de.systopia.fastactivity). – Demerit Feb 7 '18 at 5:28
  • thanks. fabian just mentioned the same. will def look at it. have added query to Q. – petednz - fuzion Feb 7 '18 at 19:07
2

I do not know why this is so slow but we developed an extension together with Matthew Wire that improves some of those problems. Information can be found here.

  • Thank you very much for mentioning this. we will explore. – petednz - fuzion Feb 7 '18 at 19:05

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.