[FINERACT-127] Journal entry performance improvement

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Auto Closed
• Labels: Cleanup2022 beginner scalability technical
• Assignee: Unassigned
• Reporter: Emmanuel Nnaa
• Created: 29/Mar/16 09:27
• Updated: 07/Nov/22 18:11
• Resolved: 07/Nov/22 18:11

Description

The improvements will make the "accounting running balance update" job more efficient in handling the update of journal entries.

If you reset the "is_running_balance_calculated" property to 0 for all journal entries, running the following SQL statement will update the running balances much faster than the "accounting running balance update" job:

SET @running_balance := 0;
SET @account_id := 0;
update acc_gl_journal_entry as je
SET
		organization_running_balance = if(@account_id = je.account_id,
			@running_balance := @running_balance + IF(type_enum = 1, IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount * -1, amount), IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount, amount * -1)),
			@running_balance :=  IF(type_enum = 1, IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount * -1, amount), IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount, amount * -1))),
			account_id = IF(@account_id <> je.account_id, @account_id:=account_id, account_id)
order by account_id, entry_date, id;

COMMIT;
UNLOCK TABLES;
SET @running_balance := 0;
SET @account_id := 0;
SET @office_id := 0;

UPDATE acc_gl_journal_entry as je SET office_running_balance =
if( @office_id = je.office_id AND @account_id = je.account_id,
@running_balance := @running_balance + IF(type_enum = 1, IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount * -1, amount), IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount, amount * -1)),
@running_balance :=  IF(type_enum = 1, IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount * -1, amount), IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount, amount * -1))),
account_id = IF(@account_id <> je.account_id, @account_id:=account_id, account_id),
office_id = IF(@office_id <> je.office_id, @office_id:=office_id, office_id)
order by office_id, account_id, entry_date, id;
COMMIT;
update acc_gl_journal_entry set is_running_balance_calculated = 1;

Issue Links

• blocks: FINERACT-867 Scalability & Performance Enhancements for Supporting Millions of Clients, High TPS, and Concurrent Users