Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Trivial
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.6.5
-
Fix Version/s: 4.7
-
Component/s: CiviContribute
-
Labels:
-
Documentation Required?:None
-
Funding Source:Contributed Code
Description
I'm trying to use the financial 'accounting batches' exports functionality of civicrm to export contributions to the accounting software.
When using this with a lot of contributions i'm running on some extremely heavy SQL query's which are getting executed.
Especially the query to find the transactions which aren't in a batch yet keeps hanging on my system.
To be precise this makes the list of contributions which you can assign to a batch.
To give some scope, i'm testing this with +-75000 contributions, which are all but 3 added to a first batch.
Now i'm creating a second batch to which i wan't to add the other 3 contributions. The query to find those 3 contributions however keeps running. (+45 minutes on my system).
This is the query executed.
SELECT civicrm_financial_trxn.id , civicrm_financial_trxn.payment_instrument_id as payment_method , civicrm_contribution.contact_id as contact_id , civicrm_contribution.id as contributionID , contact_a.sort_name , civicrm_financial_trxn.total_amount as amount , civicrm_financial_trxn.trxn_id as trxn_id , contact_a.contact_type , contact_a.contact_sub_type , civicrm_financial_trxn.trxn_date as transaction_date , name , civicrm_contribution.currency as currency , civicrm_financial_trxn.status_id as status , civicrm_financial_trxn.check_number as check_number
FROM civicrm_financial_trxn
LEFT JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
and civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'
LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_id = civicrm_financial_trxn.id
LEFT JOIN civicrm_contribution ON civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id
LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id
LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id
LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id
WHERE ( civicrm_entity_batch.batch_id is null
AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')
ORDER BY civicrm_financial_trxn.id
The problem lies in the (over) use of the left joins, to find the values which don't have a batch assigned.
I can change the code to use a different more performant query, but i'm not sure how to make a more performant query.