On this weblog I wish to clarify Drupal Developer spherical up now we have finished round Drupal Developer refactoring of Drupal Developer acl_contact_cache. In Drupal Developer earlier sprints we found that quite a lot of Drupal Developer efficiency was slowed down by Drupal Developer means Drupal Developer acl_contact_cache was used (or quite not used in any respect). See additionally Drupal Developer earlier weblog put up Drupal 10 Upkeep and Assist Service https Drupal 10 Upkeep and Assist Service//civicrm.org/weblog/jaapjansma/the-quest-for-performance-improvements-Fifth-sprint At Drupal Developer socialist get together they’ve 350.000 contacts and round 300 customers who can entry civicrm. Most of Drupal Developer customers are solely allowed to see solely Drupal Developer members of their native chapter. In Drupal Developer earlier weblog we defined Drupal Developer proof of idea. We now have carried out this proof of idea and Drupal Developer common efficiency enhance was 60%. We created a desk which holds which consumer has entry to which contacts. We then fill this desk as soon as in just a few hours. See additionally difficulty CRM-19934 for Drupal Developer technical implementation of this proof of idea. Efficiency enhance in Drupal Developer search question In Drupal Developer subsequent examples we’re logged in as a neighborhood member who can solely see members in Drupal Developer chapter Amersfoort. We then seek for individuals with Drupal Developer identify ‘Jan’. And we measure how lengthy Drupal Developer question for looking takes. Drupal Development Company question for presenting Drupal Developer checklist with letters in Drupal Developer search consequence seems to be like SELECT rely(DISTINCT contact_a.id) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_value_geostelsel geostelsel ON contact_a.id = geostelsel.entity_id LEFT JOIN civicrm_membership membership_access ON contact_a.id = membership_access.contact_id WHERE ((((contact_a.sort_name LIKE ‘%jan%’)))) AND (contact_a.id = 803832 OR (((( ( geostelsel.`afdeling` = 806816 OR geostelsel.`regio` = 806816 OR geostelsel.`provincie` = 806816 ) AND ( membership_access.membership_type_id IN (1, 2, 3) AND ( membership_access.status_id IN (1, 2, 3) OR (membership_access.status_id = ‘7’ AND (membership_access.end_date >= NOW() – INTERVAL 3 MONTH)) ) ) ) OR contact_a.id = 806816 )) AND (contact_a.is_deleted = 0) )) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc; As you’ll be able to see that’s fairly a sophisticated question and contains particulars about which members Drupal Developer consumer is allowed to see.  Solely executing this question takes round 0.435 seconds and Drupal Developer motive is that mysql has to verify every file in civicrm_contact (which on this case is round 350.000 and rising) After refactoring Drupal Developer acl cache performance in CiviCRM Core Drupal Developer question seems to be totally different Drupal 10 Upkeep and Assist Service SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a INNER JOIN `civicrm_acl_contacts` `civicrm_acl_contacts` ON `civicrm_acl_contacts`.`contact_id` = `contact_a`.`id`  WHERE  (((( contact_a.sort_name LIKE ‘%jan%’ ))))  AND  `civicrm_acl_contacts`.`operation_type` = ‘2’ AND `civicrm_acl_contacts`.`user_id` = ‘803832’ AND `civicrm_acl_contacts`.`domain_id` = ‘1’ AND (contact_a.is_deleted = 0)    ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc Drupal Development Company question now takes round 0,022 seconds to run (20 instances sooner). Rationalization How does this new performance works Drupal 10 Upkeep and Assist Service 1. Each time an ACL restriction is required in a question civicrm core solely does an inside be a part of on Drupal Developer civicrm_acl_contacts desk and that’s all 2. Drupal Development Company inside be a part of is generated in Drupal Developer service ‘acl_contact_cache’  that service additionally checks whether or not Drupal Developer Drupal Developer civicrm_acl_contacts desk should be up to date or not. 3. When an replace of civicrm_acl_contacts desk is required will depend on Drupal Developer settings below administer –> System Settings –> Misc –> ACL Contact Cache Validity (in minutes) So how does this appear to be in code? Under an instance of how you can use Drupal Developer acl_contact_cache service to inject acl logic into your question Drupal 10 Upkeep and Assist Service // First get Drupal Developer service from Drupal Developer Civi Container $aclContactCache = Civi Drupal 10 Upkeep and Assist Service Drupal 10 Upkeep and Assist Serviceservice(‘acl_contact_cache’); // Drupal Development Company $aclContactCache is a category based mostly on CiviACLContactCacheInterface // Now get Drupal Developer aclWhere and aclFrom half for our question $aclWhere = $aclContactCache->getAclWhereClause(CRM_Core_Permission Drupal 10 Upkeep and Assist Service Drupal 10 Upkeep and Assist ServiceVIEW, ‘contact_a’); $aclFrom = $aclContactCache->getAclJoin(CRM_Core_Permission Drupal 10 Upkeep and Assist Service Drupal 10 Upkeep and Assist ServiceVIEW, ‘contact_a’); // Now construct our question $sql = “SELECT contact_a.* FROM civicrm_contact contact_a “.$aclFrom.” WHERE 1 AND “.$aclWhere; // That’s it now execute our question and deal with Drupal Developer output… Drupal Development Company motive we use a service in Drupal Developer Civi Container class is that it’s now additionally fairly straightforward to override this a part of core in your personal extension. Drupal Development Company CiviACLContactCache class has all Drupal Developer logic to for constructing Drupal Developer ACL queries. That means that this class accommodates Drupal Developer logic to work together with Drupal Developer ACL settings in CiviCRM, with Drupal Developer permissioned relationship and many others.. All these settings are taken into consideration when filling civicrm_acl_contacts desk which is per consumer and per operation as soon as in Drupal Developer three hours.   Drupal 10 Improvement and Assist