The standard utilities for Exo are slow to recalculate GL Balances and movement. This is because:
- They delete records line by line, which gets really slow for the GLMovements table in most databases.
- They take forever to update the resulting data, because most Exo functions work on a line-by-line basis, rather than calculating as a set.
Based on this, we had a need to recalculate GL Balances faster than the standard functions. Behind the cut is a function that does this fast.
This is much faster, because it uses a TRUNCATE on the GLMovements table, and then repopulates using set based transforms. It can still take awhile for larger databases though.
if not exists (select 1 from sys.procedures where name='X_RECALC_GL') exec ('CREATE PROCEDURE [dbo].[X_RECALC_GL] AS --PURPOSE: Much faster GL Recalc procedure, general toolkit procedure to speed up any recalc of GLMovements/GLAccs Balances. BEGIN SET NOCOUNT ON TRUNCATE TABLE GLMOVEMENTS; INSERT INTO GLMOVEMENTS (ACCNO, SUBACCNO, BRANCHNO, COMPANYNO, PERIOD_SEQNO, AMOUNT, AMOUNT_FC) SELECT T.ACCNO, T.SUBACCNO, T.BRANCHNO, T.COMPANYNO, T.PERIOD_SEQNO, ISNULL(SUM(T.AMOUNT),0) AS AMOUNT, ISNULL(SUM(T.FCAMOUNT),0) AS FCAMOUNT FROM GLTRANS T GROUP BY T.ACCNO, T.SUBACCNO, T.BRANCHNO, T.COMPANYNO,T.PERIOD_SEQNO; WITH BAL AS ( SELECT A.ACCNO ,A.NAME ,A.SECTION ,BALANCE ,ISNULL(ROUND(SUM(CASE WHEN A.SECTION<>0 OR PS.YEARAGE=0 THEN AMOUNT ELSE 0 END),2),0) AS CALC_BALANCE ,ISNULL(ROUND(SUM(CASE WHEN A.SECTION<>0 OR PS.YEARAGE=0 THEN AMOUNT ELSE 0 END),2),0)-ROUND(A.BALANCE,2) AS BALANCE_DIFF FROM GLACCS AS A WITH (NOLOCK) LEFT OUTER JOIN GLTRANS AS T WITH (NOLOCK) ON A.ACCNO = T.ACCNO LEFT OUTER JOIN PERIOD_STATUS AS PS WITH (NOLOCK) ON PS.SEQNO = T.PERIOD_SEQNO GROUP BY A.ACCNO, A.NAME, A.SECTION, A.BALANCE, A.BALSHEETACCTOT HAVING ISNULL(ROUND(SUM(CASE WHEN A.SECTION<>0 OR PS.YEARAGE=0 THEN AMOUNT ELSE 0 END),2),0)<>ROUND(A.BALANCE,2) ) UPDATE A SET BALANCE=BAL.CALC_BALANCE FROM GLACCS AS A INNER JOIN BAL ON BAL.ACCNO=A.ACCNO END;')