Answers.Body: <p>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 <a href="https://github.com/systopia/de.systopia.fastactivity" rel="nofollow noreferrer">here</a>.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-07T19:05:38.873Z
Answers.Text: Thank you very much for mentioning this. we will explore.
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-07T07:59:51.940Z
Answers.LastActivityDate: 2018-02-07T07:59:51.940Z
Answers.UserDisplayName: Fabian
Body: <p>Digging in to why one of our larger clients is suffering more from slowness of Activity searches or loading of Activity tab for contacts.</p>
  
  <p>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</p>
  
  <p>| db | Activities | Activity Contacts | average contacts per activity |</p>
  
  <p>| A | 626,145 | 22,270,065 | 36 |</p>
  
  <p>| B | 180,000 | 5,680,000 | 32 |</p>
  
  <p>| C | 3,718,634 | 7,722,346 | 2 |</p>
  
  <p>And it is DB 'A' that is seriously slow running Activity related queries.</p>
  
  <p>Most of the Activity Contacts are on 'bulk mail' Activities</p>
  
  <p>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.</p>
  
  <pre><code>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 &lt;&gt; 5)  
      AND  (activity_type.component_id IS NULL OR activity_type.component_id &lt;&gt; 7)
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-02-06T23:34:24.593Z
Comments.Text: 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?
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-02-07T04:06:16.687Z
Comments.Text: 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
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-02-07T05:28:39.420Z
Comments.Text: 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 https://github.com/civicrm/civicrm-core/blob/master/CRM/Activity/BAO/Activity.php 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 https://github.com/systopia/de.systopia.fastactivity).
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-02-07T19:07:59.943Z
Comments.Text: thanks. fabian just mentioned the same. will def look at it. have added query to Q.
Comments.UserDisplayName: petednz - fuzion
Tags: <activities><performance>
Title: What can be done to improve loading of Activity searches and tabs on databases with gazillion civicrm_activity_contact rows
ThreadText: # What can be done to improve loading of Activity searches and tabs on databases with gazillion civicrm_activity_contact rows
  
  (Asked by: username_0 on 2018-02-02)
  
  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)
  
  **Tags:** activities, performance
  
  > **Comments:**
  > **username_1**: 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?  
  > **username_0**: 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  
  > **username_1**: 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 https://github.com/civicrm/civicrm-core/blob/master/CRM/Activity/BAO/Activity.php 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 https://github.com/systopia/de.systopia.fastactivity).  
  > **username_0**: thanks. fabian just mentioned the same. will def look at it. have added query to Q.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2018-02-07. Score: 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](https://github.com/systopia/de.systopia.fastactivity).
  
  > **Comments:**
  > **username_0**: Thank you very much for mentioning this. we will explore.  
  
  ---
  
  