[jira] [Updated] (FINERACT-127) Journal entry performance improvement

["Michael Vorburger (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/FINERACT-127?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Michael Vorburger updated FINERACT-127:
---------------------------------------
    Labels: beginner scalability technical  (was: beginner performance technical)

> Journal entry performance improvement
> -------------------------------------
>
>                 Key: FINERACT-127
>                 URL: https://issues.apache.org/jira/browse/FINERACT-127
>             Project: Apache Fineract
>          Issue Type: Improvement
>            Reporter: Emmanuel Nnaa
>            Priority: Major
>              Labels: beginner, scalability, technical
>
> 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:
> {code}
> 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;
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)