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;')