Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-19934

Performance improvement on civicrm_acl_contact_cache

    Details

    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      User and Admin Doc
    • Funding Source:
      Needs Funding
    • Verified?:
      No

      Description

      At the socialist party local chapter members could login and see their local members. This done by using the hook hook_civicrm_aclWhereClause in this hook we lookup the chapter of the local user and then we add a part to the query to lookup people living in the same area and who have an active membership.

      The query resulting is a query with a lot of subqueries which makes it a bad performing query. For example when a user does a search the search query self is altered with the hook_civicrm_aclWhereClauses from the hook. During a search at least three queries are altered by this hook: the total amount of records, the paging per first letter of the name, and the actual contacts in the search. So when a user does a search the bad performing query runs three times.
      The bad performance is caused by the fact that a search query has to go through all contact records in the database (which are at the socialist party around 360.000)

      Proposed solution

      • Make a table which stores the user ID and then the contact id a user is allowed to see/edit.
      • When a search/report etc is done only use an inner join on this table (inner join perform faster because MySQL will only check the records in the subset)
      • Update this table once in a while (for example after 24 hours); This update is done by deleting all records for that user and insert the records based on the query generated by hook_civicrm_aclWhereClause. This is possibly done when a user logs in.
      • A user interface to change the validity period (default 24 hours)
      • Also refactor/remove the current civicrm_acl_contact_cache as that one is probably obsolete with the above code.

        Attachments

          Activity

            People

            • Assignee:
              grapio Jaap Jansma
              Reporter:
              grapio Jaap Jansma
            • Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated: