Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Minor
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.7.14
-
Fix Version/s: 4.7.31
-
Component/s: CiviContribute
-
Labels:
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Contributed Code
-
Verified?:No
Description
The query to determine the median for CiviCRM summary pages takes longer than it used to due to the financial_type ACLS, even when financial type acls are enabled.
Removing the financial_type acls & the line item join knocks this query down to a quarter of the time taken. I don't know if the query can be improved for sites that do use ACLs but for those that don't, adding an extra conditional around the query removes the ACL related part, and speeds it up
SELECT civicrm_contribution.total_amount as median
FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.
contact_id = contact_a.id LEFT JOIN civicrm_line_item li
ON civicrm_contribution.id = li.contribution_id AND
li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN (18,20,17,9,15,23,26,21,25,11,24,13,12,14,16,10,22,19) WHERE (
( civicrm_contribution.receive_date >= '20161210000000' ) AND
( civicrm_contribution.receive_date <= '20161211235959' )
AND civicrm_contribution.is_test = 0 AND civicrm_contribution.trxn_id LIKE "x %" ) AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND contact_a.is_deleted = 0 AND civicrm_contribution.financial_type_id IN (18,20,17,9,15,23,26,21,25,11,24,13,12,14,16,10,22,19) AND li.id IS NULL AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution.currency = 'USD'
ORDER BY median LIMIT 27,1;
Attachments
Issue Links
- supplements
-
CRM-19820 performance impact from ft-ACLs
- Open
- links to