The quest for performance improvements - 3rd sprint

Last week we had our third sprint at the socialist party to improve the performance.  In the previous blogs I have explained what we have done so far. You can read them here and here.

Since the previous sprint a guy with a lot of database knowledge has done some analysis of the queries and he came up with the following observations. Most queries are build in CRM_Contact_BAO_Query class and that class adds a join on the tables civicrm_group_contact and civicrm_group_contact_cache and a where clause with an or on both tables.  See example query below.

SELECT contact_a.id AS contact_id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-2304` ON (contact_a.id = `civicrm_group_contact-2304`.contact_id AND `civicrm_group_contact-2304`.status IN ("Added"))   LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_2304` ON contact_a.id = `civicrm_group_contact_cache_2304`.contact_id   WHERE  ( ( ( `civicrm_group_contact-2304`.group_id IN ( 2304 ) ) OR ( `civicrm_group_contact_cache_2304`.group_id IN (2304) ) ) )   GROUP BY contact_a.id;

Such a query is performing slow because it is looking up all records in civicrm_contact, all records in civicrm_group_contact_cache and all records civicrm_group_contact. Rebuilding the query to use an IN statement will increase the performance. See the query below:

SELECT contact_a.id AS contact_id  FROM civicrm_contact contact_a 
WHERE contact_a.id IN (SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (2304) AND status IN ("Added"))
OR contact_a.id IN (SELECT contact_id FROM civicrm_group_contact_cache ON groupId IN (2304))
GROUP BY contact_a.id;

Luckily we could do this in an extension because CRM_Contact_BAO_Query provides functionality to change and tweak the query.  The extension we have developed could be found on github (https://github.com/CiviCooP/org.civicoop.groupperformance)

In our test environment the extension has a visible effect on the performance. Searching for contact went down from 15 seconds to 3 seconds. We have also installed the extension in the production environment and we are waiting on feedback from end users whether they experience a noticble increase in performance.

Read more

• https://civicrm.org/blog/jaapjansma/the-quest-for-performance-improvements
• https://civicrm.org/blog/jaapjansma/the-quest-for-performance-improvements-2nd-sprint
• https://civicrm.org/blog/jaapjansma/the-quest-for-performance-improvements-4th-sprint