Over the years, I've had cause to optimise a few JIWA queries, with some success. I thought I'd post a bit about my experiences, which mostly involve more 'advanced' techniques like using table-valued functions (including CROSS APPLY joins) and common table expressions to improve the way SQL accesses the data.
DISCLAIMER #1: The work I describe below was for a specific JIWA database; while it is probably applicable to other databases, my intent is to demonstrate the techniques, not to provide general-purpose replacements for standard JIWA code. Furthermore, my code is not as well tested as JIWA's, so while you may feel free to use and adapt any code samples, they come with no guarantees, and you shouldn't use them unless you first understand them and thoroughly test them.
DISCLAIMER #2: samples come with no guarantees, and you shouldn't use them unless you first understand them.
DISCLAIMER #3: samples come with no guarantees, and you shouldn't deploy any code based on them without LOTS OF TESTING.
The challenge
To start with, the ones I've done most recently, and so the ones I can find easily: "Creditor/GL reconciliation" (CRAUD020 - CR GL Reconciliation.rpt), which uses "Creditor Balance As At" and "GL Balance As At".
One of my customers has a huge active supplier list and so their 'Creditor/GL Reconciliation' report takes (took) a while to run. Last year, they reached a 'tipping point' where it became unusable, seemingly stalling when it was running (I aborted it after 40 minutes). After a few days of battling with it, there were a fair few changes I made to the stored procedures, related functions and table indexes, but I ended up with the report running in seconds (as in the customer didn't believe the results at first, because it couldn't possibly be that quick!).
This work was done on JIWA 6, but the queries are largely unchanged for JIWA 7 (I may post a JIWA 7 update in a few weeks).
I don't think any single change in an of itself magically fixed things, it was the combination of many small changes. At the end of the process, there was no value in further analysing which bits made the most difference, since there would be no-one paying for said analysis, so we ended up just leaving all the changes, including some which I strongly suspect had very little impact.
Sidebar on SQL functions
When I'm optimising SQL, which I've done for many years on many different databases (JIWA only makes up a small part of my professional career), there are a few things I've learnt to look for. A big one is the scalar-valued function or SVF. Up until SQL 2017, using an SVF was a sure-fire way to crush the performance of a query. On the other hand, a properly written inline table-valued function or iTVF will always be 'inlined' by SQL. Fortunately, with common table expressions (CTEs), almost any SVF can be converted to an iTVF. So you will see several JIWA functions converted from scalar-valued to table-valued.
In SQL Server 2019, Microsoft have finally made it so that scalar-valued functions can be 'inlined' (subject to some limitations) into the query that is calling them, so this will become less of an issue. Having said that; looking at the documentation and testing by SQL Server MVPs, a well written TVF will remain preferable to an SVF; retaining a (reduced) performance advantage.
Sidebar on SQL cursors
After scalar-valued functions, one of the next big things that can cause problems are cursors. Note, they are not inherently a problem, and can, under specific conditions, even produce better performance than pure set-based logic. But on the whole their use is more likely to impair the performance of a complex query, so I also like to get rid of them if I can.
Back to the topic at hand
Firstly (and this is easy). Our customer doesn't do variable periods - all creditors and debtors are monthly. So I replaced JIWA's scalar-valued function (SVF) ufn_JIWA_Creditors_TransactionPeriodNo with a very simple table-valued function (TVF). I left @CreditorID as a parameter, to make it consistent with the original, and to allow me to change it later if necessary. When it gets called, the CreditorID is supplied :
- Code: Select all
CREATE FUNCTION [dbo].[AERP_Get_Creditors_TransactionPeriodNo] (
@TranDate DATETIME
, @CreditorID CHAR(20)
, @AsAtDate DATETIME
)
RETURNS TABLE
with schemabinding
AS
RETURN
SELECT PeriodNo = datediff(month, @TranDate, dateadd(month, datediff(month, @AsAtDate, 0), 0))
For flexible creditor periods, the following version should work, but I couldn't really test it, because I don't have data that can use it:
- Code: Select all
CREATE FUNCTION [dbo].[AERP_Get_Creditors_TransactionPeriodNo] (
@TranDate DATETIME
, @CreditorID CHAR(20)
, @AsAtDate DATETIME
)
RETURNS TABLE
with schemabinding
AS
RETURN
WITH PSD AS ( -- PeriodStartDate
SELECT PeriodType = COALESCE(PeriodType, - 1)
, Period1StartDate = CASE COALESCE(PeriodType, - 1)
WHEN 2
THEN -- set Period1StartDate to be the start of the month we are in
(DATEADD(month, datediff(month, 0, @AsAtDate), 0))
WHEN 1
THEN -- set @Period1StartDate to be the start of the fornight we are currently in.
(DATEADD(day, (DATEPART(weekday, @AsAtDate) - 1) * - 1, @AsAtDate))
ELSE -- set @Period1StartDate to be the start of the week.
(DATEADD(day, (DATEPART(weekday, @AsAtDate) - 1) * - 1, @AsAtDate))
END
FROM dbo.CR_Main WITH(NOLOCK)
WHERE CreditorID = @CreditorID
)
SELECT PeriodNo = CASE PSD.PeriodType
WHEN 2
THEN -- set Period1StartDate to be the start of the month we are in
datediff(m, @TranDate, Period1StartDate)
WHEN 1
THEN -- set @Period1StartDate to be the start of the fortnight we are currently in.
CASE
WHEN (
SELECT datediff(d, @TranDate, Period1StartDate)
) < 1
THEN 0
ELSE (
SELECT (datediff(d, @TranDate, Period1StartDate) / 14) + 1
)
END
ELSE -- type == 0 set @Period1StartDate to be the start of the week.
CASE
WHEN (
SELECT datediff(d, @TranDate, Period1StartDate)
) < 1
THEN 0
ELSE (datediff(d, @TranDate, Period1StartDate) / 7) + 1
END
END
FROM PSD
Similarly, I converted the [ufn_JIWA_GL_YearNo] and [ufn_JIWA_GL_PeriodStartDate] SVFs to TVFs.
Since my customer's financial year aligns with calendar months, I was also able to simplify my Get_GL_YearNo to use a simple date calculation:
- Code: Select all
CREATE FUNCTION [dbo].[AERP_Get_GL_YearNo] (@TranDate DATETIME)
RETURNS TABLE
with schemabinding
AS
RETURN
-- My simplified logic:
-- a) find the start of the "Next year" by adding one year to YearStartingDate
-- b) determine if @TranDate >= YearStartingDate and < NextYearStartingDate
-- This will break if the year is not defined in GL_Config (as would the standard scalar UDF)
-- This will break if your financial years don't align with calendar months (in which case, this will need to look more like the standard JIWA function).
SELECT TOP (1) YearNo
FROM dbo.GL_Config
WHERE @TranDate >= YearStartingDate
AND @TranDate < dateadd(year, 1, YearStartingDate)
ORDER BY YearStartingDate ASC;
- Code: Select all
CREATE FUNCTION [dbo].[AERP_Get_GL_PeriodStartDate] (@SP_YearNo AS SMALLINT, @SP_PeriodNo AS TINYINT)
RETURNS TABLE
with schemabinding
AS
RETURN
SELECT StartDate = CASE @SP_PeriodNo
WHEN 1
THEN PeriodStart1
WHEN 2
THEN PeriodStart2
WHEN 3
THEN PeriodStart3
WHEN 4
THEN PeriodStart4
WHEN 5
THEN PeriodStart5
WHEN 6
THEN PeriodStart6
WHEN 7
THEN PeriodStart7
WHEN 8
THEN PeriodStart8
WHEN 9
THEN PeriodStart9
WHEN 10
THEN PeriodStart10
WHEN 11
THEN PeriodStart11
WHEN 12
THEN PeriodStart12
END
FROM dbo.GL_Config
WHERE YearNo = @SP_YearNo
Then I re-implemented the stored procedures usp_JIWA_GL_GetBalanceAsAt and usp_JIWA_Creditors_CreditorBalanceAsAt and the SVF ufn_JIWA_Creditors_TransactionGainLossAsAt as TVFs as well:
- Code: Select all
CREATE FUNCTION [dbo].[AERP_Get_GL_BalanceAsAt] (
@SP_GLLedgerID AS VARCHAR(20)
, @SP_MyDate AS DATETIME
)
RETURNS TABLE
AS
RETURN
WITH asAt AS (
SELECT DATE = convert(DATE, @SP_MyDate)
)
, actType1 AS (
SELECT AccountType = AccType
FROM dbo.GL_Ledger WITH (NOLOCK)
WHERE GLLedgerID = @SP_GLLedgerID
)
, actType2 AS (
SELECT AccountType = COALESCE(AccountType, 0)
FROM actType1
)
, ysd AS (
SELECT YearStartDate = StartDate
from
dbo.[AERP_Get_GL_YearNo](@SP_MyDate) GlYear
cross apply dbo.[AERP_Get_GL_PeriodStartDate](GlYear.YearNo, 1)
-- dbo.ufn_JIWA_GL_PeriodStartDate(dbo.ufn_JIWA_GL_YearNo(@SP_MyDate), 1)
)
, combined AS (
SELECT Balance = SUM(CASE
WHEN COALESCE(DebitCredit, 0) = 0
THEN COALESCE(Amount, 0)
ELSE - COALESCE(Amount, 0)
END)
FROM asAt
, ysd
, actType2
, dbo.GL_Transactions
INNER JOIN dbo.GL_Sets ON GL_Sets.GLSetID = GL_Transactions.GLSetID
WHERE AccountType = 0
AND (TransPostDateTime >= YearStartDate)
AND TransPostDateTime < asAt.DATE
AND GLLedgerID = @SP_GLLedgerID
AND SetType = 0
UNION
SELECT Balance = SUM(CASE
WHEN COALESCE(DebitCredit, 0) = 0
THEN COALESCE(Amount, 0)
ELSE - COALESCE(Amount, 0)
END)
FROM asAt
, ysd
, actType2
, dbo.GL_Transactions
INNER JOIN dbo.GL_Sets ON GL_Sets.GLSetID = GL_Transactions.GLSetID
WHERE AccountType = 1
AND TransPostDateTime < asAt.DATE
AND GLLedgerID = @SP_GLLedgerID
AND SetType = 0
)
SELECT Balance
FROM combined
WHERE Balance IS NOT NULL
- Code: Select all
CREATE FUNCTION [dbo].[AERP_Get_Creditors_TransactionGainLossAsAt] (
@TransID VARCHAR(20)
, @AsAtDate DATETIME
)
RETURNS TABLE
WITH schemabinding
AS
RETURN
WITH asAt AS (
SELECT AsAtDate = convert(DATE, @AsAtDate)
)
, combined AS (
-- force existence of at least one record.
SELECT TransID = @TransID
, GainLoss = 0
UNION
SELECT TransID
, GainLoss = - (
SELECT COALESCE(SUM(CreditAmount - DebitAmount), 0)
FROM dbo.CR_TransAlloc TA1
, asAt
WHERE TA1.CreditTransID = T1.TransID
AND (TA1.DateAlloc < AsAtDate)
AND TA1.Version = (
SELECT MIN(Version)
FROM dbo.CR_TransAlloc TA2, asAt
WHERE TA2.DebitTransID = TA1.DebitTransID
AND TA2.CreditTransID = TA1.CreditTransID
AND (TA2.DateAlloc < AsAtDate)
)
)
FROM dbo.CR_Trans T1
WHERE TransID = @TransID
AND DebitCredit = 0
AND (TranDate < @AsAtDate)
UNION
SELECT TransID
, GainLoss = (
SELECT COALESCE(SUM(CreditAmount - DebitAmount), 0)
FROM dbo.CR_TransAlloc TA3
, asAt
WHERE TA3.DebitTransID = T2.TransID
AND (TA3.DateAlloc < AsAtDate)
AND TA3.Version = (
SELECT MIN(Version)
FROM dbo.CR_TransAlloc TA4, asAt
WHERE TA4.DebitTransID = TA3.DebitTransID
AND TA4.CreditTransID = TA3.CreditTransID
AND (TA4.DateAlloc < AsAtDate)
)
)
FROM dbo.CR_Trans T2
WHERE TransID = @TransID
AND DebitCredit = 1
AND (TranDate < @AsAtDate)
)
SELECT TransID
, GainLoss = sum(GainLoss)
FROM combined
GROUP BY TransID
- Code: Select all
CREATE FUNCTION [dbo].[AERP_Get_Creditors_CreditorBalanceAsAt] (@SP_Date DATETIME
, @SP_CreditorID CHAR(36)
)
RETURNS TABLE
AS
RETURN
------Get Transaction data
WITH CreditorTransactionsTable as (
SELECT T.TransID
, T.Amount
, AllocatedAmount = (
SELECT COALESCE(SUM(CreditAmount), 0)
FROM dbo.CR_TransAlloc TA1
WHERE TA1.DebitTransID = T.TransID
AND DateAlloc < @SP_Date
AND Version = (
SELECT MIN(Version)
FROM dbo.CR_TransAlloc TA2
WHERE TA2.CreditTransID = TA1.CreditTransID
AND TA2.DebitTransID = T.TransID
AND TA2.DateAlloc < @SP_Date
)
)
, CrPeriod.PeriodNo
, DebitCredit
, T.FXAmount
, FXAllocatedAmount = (
SELECT COALESCE(SUM(FXAmount), 0)
FROM dbo.CR_TransAlloc TA3
WHERE TA3.DebitTransID = T.TransID
AND DateAlloc < @SP_Date
AND Version = (
SELECT MIN(Version)
FROM dbo.CR_TransAlloc TA4
WHERE TA4.CreditTransID = TA3.CreditTransID
AND TA4.DebitTransID = T.TransID
AND TA4.DateAlloc < @SP_Date
)
)
, RealisedGainLoss = GainLossAt.GainLoss
FROM dbo.CR_Trans T
cross apply [dbo].[AERP_Get_Creditors_TransactionGainLossAsAt](T.TransID, @SP_Date) GainLossAt
cross apply [dbo].[AERP_Get_Creditors_TransactionPeriodNo](T.TranDate, CreditorID, @SP_Date) CrPeriod
WHERE T.CreditorID = COALESCE(@SP_CreditorID, T.CreditorID)
AND T.TranDate < @SP_Date
AND DebitCredit = 1
UNION ALL
SELECT T.TransID
, T.Amount
, (
SELECT COALESCE(SUM(DebitAmount), 0)
FROM dbo.CR_TransAlloc TA5
WHERE TA5.CreditTransID = T.TransID
AND DateAlloc < @SP_Date
AND Version = (
SELECT MIN(Version)
FROM dbo.CR_TransAlloc TA6
WHERE TA6.DebitTransID = TA5.DebitTransID
AND TA6.CreditTransID = T.TransID
AND TA6.DateAlloc < @SP_Date
)
)
, CrPeriod.PeriodNo
, DebitCredit
, T.FXAmount
, (
SELECT COALESCE(SUM(FXAmount), 0)
FROM dbo.CR_TransAlloc TA7
WHERE TA7.CreditTransID = T.TransID
AND DateAlloc < @SP_Date
AND Version = (
SELECT MIN(Version)
FROM dbo.CR_TransAlloc TA8
WHERE TA8.DebitTransID = TA7.DebitTransID
AND TA8.CreditTransID = T.TransID
AND TA8.DateAlloc < @SP_Date
)
)
, RealisedGainLoss = GainLossAt.GainLoss
FROM dbo.CR_Trans T
cross apply [dbo].[AERP_Get_Creditors_TransactionGainLossAsAt](T.TransID, @SP_Date) GainLossAt
cross apply [dbo].[AERP_Get_Creditors_TransactionPeriodNo](T.TranDate, CreditorID, @SP_Date) CrPeriod
WHERE T.CreditorID = COALESCE(@SP_CreditorID, T.CreditorID)
AND T.TranDate < @SP_Date
AND DebitCredit = 0
)
SELECT
Balance = COALESCE(SUM(CASE
WHEN DebitCredit = 1
THEN Amount - (AllocatedAmount - RealisedGainLoss)
WHEN DebitCredit = 0
THEN -(Amount - (AllocatedAmount - RealisedGainLoss))
ELSE 0
END), 0)
, CurrentBalance =
COALESCE(SUM(CASE
WHEN PeriodNo = 0
AND DebitCredit = 1
THEN Amount - (AllocatedAmount - RealisedGainLoss)
WHEN PeriodNo = 0
AND DebitCredit = 0
THEN -(Amount - (AllocatedAmount - RealisedGainLoss))
ELSE 0
END), 0)
, P1Balance = COALESCE(SUM(CASE
WHEN PeriodNo = 1
AND DebitCredit = 1
THEN Amount - (AllocatedAmount - RealisedGainLoss)
WHEN PeriodNo = 1
AND DebitCredit = 0
THEN - (Amount - (AllocatedAmount - RealisedGainLoss))
ELSE 0
END), 0)
, P2Balance = COALESCE(SUM(CASE
WHEN PeriodNo = 2
AND DebitCredit = 1
THEN Amount - (AllocatedAmount - RealisedGainLoss)
WHEN PeriodNo = 2
AND DebitCredit = 0
THEN -(Amount - (AllocatedAmount - RealisedGainLoss))
ELSE 0
END), 0)
, P3Balance = COALESCE(SUM(CASE
WHEN PeriodNo = 3
AND DebitCredit = 1
THEN Amount - (AllocatedAmount - RealisedGainLoss)
WHEN PeriodNo = 3
AND DebitCredit = 0
THEN -(Amount - (AllocatedAmount - RealisedGainLoss))
ELSE 0
END), 0)
, FXCurrentBalance = COALESCE(SUM(CASE
WHEN PeriodNo = 0
AND DebitCredit = 1
THEN FXAmount - FXAllocatedAmount
WHEN PeriodNo = 0
AND DebitCredit = 0
THEN -(FXAmount - FXAllocatedAmount)
ELSE 0
END), 0)
, FXP1Balance = COALESCE(SUM(CASE
WHEN PeriodNo = 1
AND DebitCredit = 1
THEN FXAmount - FXAllocatedAmount
WHEN PeriodNo = 1
AND DebitCredit = 0
THEN -(FXAmount - FXAllocatedAmount)
ELSE 0
END), 0)
, FXP2Balance = COALESCE(SUM(CASE
WHEN PeriodNo = 2
AND DebitCredit = 1
THEN FXAmount - FXAllocatedAmount
WHEN PeriodNo = 2
AND DebitCredit = 0
THEN -(FXAmount - FXAllocatedAmount)
ELSE 0
END), 0)
, FXP3Balance = COALESCE(SUM(CASE
WHEN PeriodNo = 3
AND DebitCredit = 1
THEN FXAmount - FXAllocatedAmount
WHEN PeriodNo = 3
AND DebitCredit = 0
THEN -(FXAmount - FXAllocatedAmount)
ELSE 0
END), 0)
FROM CreditorTransactionsTable
Then, the big one. I don't recall why, but we needed to retain the original name for the reconciliation stored procedure. This is less than ideal, because JIWA updates have more than a fighting chance of resetting it. But anyway, while comparing this to the original with a file comparing tool will make it appear vastly different, it's not really so different. The overall structure and steps are the same, just with calls to stored procedures replaced with calls to user-defined functions, and all use of cursors replaced with set-based logic. Even some of the original comments are still in there!
- Code: Select all
ALTER PROCEDURE [dbo].[usp_JIWA_Creditors_CreditorsAndGeneralLedgerReconciliation] @SP_Year AS TINYINT = NULL
, @SP_StartPeriodNo AS TINYINT = NULL
, @SP_EndPeriodNo AS TINYINT = NULL
AS
BEGIN
SET NOCOUNT ON
--Used by:
--CRAUD020 - CR GL Reconciliation.rpt
--
--@SP_Year
--1 = Last
--2 = Current
--3 = Next
DECLARE @StartDate DATETIME
DECLARE @OpeningBalanceDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @YearNo SMALLINT
DECLARE @GeneralLedgerOpeningBalance DECIMAL(19, 6)
DECLARE @GLLedgerID CHAR(20)
DECLARE @ExpSign BIT
DECLARE @CreditorLedgerOpeningBalance DECIMAL(19, 6)
DECLARE @CreditorLedgerTransactionsTotal DECIMAL(19, 6)
DECLARE @GeneralLedgerTransactionsTotal DECIMAL(19, 6)
DECLARE @RealisedGainLoss DECIMAL(19, 6)
DECLARE @timestamp VARCHAR(50);
--NULL Protections
SET @SP_StartPeriodNo = ISNULL(@SP_StartPeriodNo, 1)
SET @SP_EndPeriodNo = ISNULL(@SP_EndPeriodNo, 12)
SET @SP_Year = ISNULL(@SP_Year, 2)
--Check for valid passed in values
IF (
@SP_StartPeriodNo < 1
OR @SP_StartPeriodNo > 12
)
SET @SP_StartPeriodNo = 1
IF (
@SP_EndPeriodNo < 1
OR @SP_EndPeriodNo > 12
)
SET @SP_EndPeriodNo = 12
IF (
@SP_Year < 1
OR @SP_Year > 3
)
SET @SP_Year = 2
--Resolve SP_Year (Last/Current/Next) to a yearno
--then get start/end dates for limiting transaction list with
IF @SP_Year = 1
BEGIN
--Last Year
EXEC usp_JIWA_GL_GetLastYearYearNo @YearNo OUTPUT
IF @YearNo IS NULL
BEGIN
--Last Year does not exist in the system. Try again using Current Year period
--configuration, but with 1 year subtracted.
EXEC usp_JIWA_GL_GetCurrentYearYearNo @YearNo OUTPUT
SELECT @StartDate = DATEADD("y", - 1, StartDate)
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_StartPeriodNo)
SELECT @EndDate = DATEADD("y", - 1, DATEADD("m", 1, StartDate))
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_EndPeriodNo)
END
ELSE
BEGIN
SELECT @StartDate = StartDate
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_StartPeriodNo)
SELECT @EndDate = DATEADD("m", 1, StartDate)
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_EndPeriodNo)
END
END
ELSE
BEGIN
IF @SP_Year = 2
BEGIN
--Current Year
EXEC usp_JIWA_GL_GetCurrentYearYearNo @YearNo OUTPUT
SELECT @StartDate = StartDate
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_StartPeriodNo)
SELECT @EndDate = DATEADD("m", 1, StartDate)
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_EndPeriodNo)
END
ELSE
BEGIN
IF @SP_Year = 3
BEGIN
--Next Year
EXEC usp_JIWA_GL_GetNextYearYearNo @YearNo OUTPUT
IF @YearNo IS NULL
BEGIN
--Next Year does not exist in the system. Try again using Current Year period
--configuration, but with 1 year added.
EXEC usp_JIWA_GL_GetCurrentYearYearNo @YearNo OUTPUT
SELECT @StartDate = DATEADD("y", 1, StartDate)
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_StartPeriodNo)
SELECT @EndDate = DATEADD("y", 1, DATEADD("m", 1, StartDate))
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_EndPeriodNo)
END
ELSE
BEGIN
SELECT @StartDate = StartDate
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_StartPeriodNo)
SELECT @EndDate = DATEADD("m", 1, StartDate)
FROM dbo.AERP_Get_GL_PeriodStartDate(@YearNo, @SP_EndPeriodNo)
END
END
ELSE
BEGIN
--Out of range parameter value passed in
RAISERROR ('Error: Invalid @SP_Year passed in. Year must be 1 (Last Year), 2 (Current Year), or 3 (Next Year)', 12, 1);
END
END
END
--Get Creditor Ledger Opening Balance
SET @OpeningBalanceDate = @StartDate --(SELECT DATEADD("ms", -1, @StartDate))
DECLARE @UseOpeningBalanceTable BIT
DECLARE @CROpeningBalanceDate DATETIME
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: getting creditor balance', 0, 0, @timestamp) WITH NOWAIT;
SELECT @CreditorLedgerOpeningBalance = Balance
FROM [dbo].[AERP_Get_Creditors_CreditorBalanceAsAt](@OpeningBalanceDate, NULL);
CREATE TABLE #TempTable (
LedgerType VARCHAR(20) NOT NULL DEFAULT ''
, CreditorLedgerOpeningBalance DECIMAL(19, 6) NOT NULL DEFAULT 0
, Source VARCHAR(255) NOT NULL DEFAULT ''
, SourceTotal DECIMAL(19, 6) NOT NULL DEFAULT 0
, CreditorLedgerTransactionsTotal DECIMAL(19, 6) NOT NULL DEFAULT 0
, CreditorLedgerClosingBalance DECIMAL(19, 6) NOT NULL DEFAULT 0
, GeneralLedgerOpeningBalance DECIMAL(19, 6) NOT NULL DEFAULT 0
, GeneralLedgerTransactionsTotal DECIMAL(19, 6) NOT NULL DEFAULT 0
, GeneralLedgerClosingBalance DECIMAL(19, 6) NOT NULL DEFAULT 0
, MovementVariance DECIMAL(19, 6) NOT NULL DEFAULT 0
, ClosingBalanceVariance DECIMAL(19, 6) NOT NULL DEFAULT 0
, GLLedgerID VARCHAR(20) NOT NULL DEFAULT ''
, GeneralLedgerAccountNo VARCHAR(50) NOT NULL DEFAULT ''
, GeneralLedgerAccountDescription VARCHAR(200) NOT NULL DEFAULT ''
)
CREATE TABLE #TempTable2 (
MyDescription VARCHAR(255)
, MyValue DECIMAL(19, 6)
, MyValue2 INT
)
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: getting creditor control balances', 0, 0, @timestamp) WITH NOWAIT;
WITH controlIds
AS (
SELECT DISTINCT LedgerIDCreditorControl
FROM CR_Main
)
INSERT INTO #TempTable2
SELECT MyDescription = LedgerIDCreditorControl
, MyValue = coalesce(baa.Balance, 0)
, MyValue2 = ExpSign
FROM controlIds
INNER JOIN GL_Ledger
ON controlIds.LedgerIDCreditorControl = GL_Ledger.GLLedgerID
CROSS APPLY dbo.[AERP_Get_GL_BalanceAsAt](controlIds.LedgerIDCreditorControl, @StartDate) baa
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: got creditor control balances', 0, 0, @timestamp) WITH NOWAIT;
SELECT @GeneralLedgerOpeningBalance = SUM(CASE MyValue2
WHEN 0
THEN MyValue
ELSE - MyValue
END)
FROM #TempTable2;
IF @GeneralLedgerOpeningBalance IS NULL
SELECT @GeneralLedgerOpeningBalance = 0;
DECLARE @openBalance BIGINT = @GeneralLedgerOpeningBalance
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: got GL overall opening balance: %I64d', 0, 0, @timestamp, @openBalance) WITH NOWAIT;
RAISERROR ('%s: getting creditor transaction values', 0, 0, @timestamp) WITH NOWAIT;
INSERT INTO #TempTable(LedgerType, CreditorLedgerOpeningBalance, Source, SourceTotal, GeneralLedgerOpeningBalance)
SELECT 'Creditor Ledger'
, @CreditorLedgerOpeningBalance
, CR_Trans.Source
, SUM(CASE CR_Trans.DebitCredit
WHEN 0
THEN - CR_Trans.Amount
ELSE CR_Trans.Amount
END) + (X.GainLoss)
, @GeneralLedgerOpeningBalance
FROM dbo.CR_Trans
OUTER APPLY [dbo].[AERP_Get_Creditors_GainLossAsAtBySource](Source, @StartDate, @EndDate) X
WHERE TranDate >= @StartDate
AND TranDate < @EndDate
GROUP BY Source
, X.GainLoss
HAVING SUM((
CR_Trans.Amount + (
CASE CR_Trans.DebitCredit
WHEN 0
THEN 0
ELSE CR_Trans.RealisedGainLoss
END
)
) * (CR_Trans.DebitCredit * 2 - 1)) <> 0
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: getting temporary allocations', 0, 0, @timestamp) WITH NOWAIT;
-- NOTE: We may have to use the ufn_JIWA_GainLossAsAtBySource function (as used in the above query)
-- in the query below. This has NOT BEEN DONE!
-- Do the Gain/Losses which are incurred but were not included in the above because
-- there was no movement in the range (ie:those that were just allocations)
WITH TempAllocationsTable
AS (
SELECT Source = 'Gain/Loss' --CR_Trans.Source
, Amount = COALESCE((DebitAmount - CreditAmount) * - 1, 0) - COALESCE((
SELECT COALESCE(DebitAmount - CreditAmount, 0) * - 1
FROM CR_TransAlloc A
WHERE A.DebitTransID = CR_TransAlloc.DebitTransID
AND A.CreditTransID = CR_TransAlloc.CreditTransID
AND (A.DateAlloc < @StartDate)
AND A.Version = (
SELECT MIN(Version)
FROM CR_TransAlloc B
WHERE B.DebitTransID = A.DebitTransID
AND B.CreditTransID = A.CreditTransID
AND B.DateAlloc < @StartDate
)
), 0)
FROM CR_TransAlloc
JOIN CR_Trans
ON CR_Trans.TransID = CR_TransAlloc.DebitTransID
WHERE DateAlloc >= @StartDate
AND DateAlloc < @EndDate
AND CR_TransAlloc.Version = (
SELECT MIN(Version)
FROM CR_TransAlloc C
WHERE C.DebitTransID = CR_TransAlloc.DebitTransID
AND C.CreditTransID = CR_TransAlloc.CreditTransID
AND (
C.DateAlloc >= @StartDate
AND C.DateAlloc < @EndDate
)
)
AND CR_Trans.DebitCredit = 1
AND CR_Trans.TransID NOT IN (
SELECT TransID
FROM CR_Trans
WHERE TranDate >= @StartDate
AND TranDate < @EndDate
)
)
INSERT INTO #TempTable(LedgerType, CreditorLedgerOpeningBalance, Source, SourceTotal, GeneralLedgerOpeningBalance)
SELECT 'Creditor Ledger'
, @CreditorLedgerOpeningBalance
, 'Gain/Loss' -- + A.Source
, SUM(A.Amount)
, @GeneralLedgerOpeningBalance
FROM TempAllocationsTable A
GROUP BY A.Source
HAVING COALESCE(SUM(A.Amount), 0) <> 0
IF @@RowCount = 0
BEGIN
INSERT INTO #TempTable(LedgerType, CreditorLedgerOpeningBalance, GeneralLedgerOpeningBalance)
SELECT 'Creditor Ledger'
, @CreditorLedgerOpeningBalance
, @GeneralLedgerOpeningBalance
END
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: getting creditor allocation batches', 0, 0, @timestamp) WITH NOWAIT;
--Get General Ledger Transaction summaries
INSERT INTO #TempTable(LedgerType, CreditorLedgerOpeningBalance, Source, SourceTotal, GeneralLedgerOpeningBalance, GLLedgerID, GeneralLedgerAccountNo, GeneralLedgerAccountDescription)
SELECT 'General Ledger'
, @CreditorLedgerOpeningBalance
, CASE -- Bug 3796 calls for the description to be 'Gain/Loss' rather than the CR_Trans.Source
WHEN MAX(GL_Sets.Source) = 'Creditor Allocation Batch'
THEN 'Gain/Loss'
ELSE MAX(GL_Sets.Source)
END
, SUM(Amount * ((DebitCredit * - 2) + 1) * ((ExpSign * - 2) + 1))
, @GeneralLedgerOpeningBalance
, GL_Ledger.GLLedgerID
, MAX(GL_Ledger.AccountNo)
, MAX(GL_Ledger.Description)
FROM GL_Ledger
INNER JOIN GL_Transactions
ON GL_Transactions.GLLedgerID = GL_Ledger.GLLedgerID
INNER JOIN GL_Sets
ON GL_Transactions.GLSetID = GL_Sets.GLSetID
WHERE GL_Ledger.GLLedgerID IN (
SELECT DISTINCT LedgerIDCreditorControl
FROM CR_Main
)
AND GL_Sets.SetType = 0
AND TransPostDateTime >= @StartDate
AND TransPostDateTime < @EndDate
GROUP BY GL_Sets.Source
, GL_Ledger.GLLedgerID
-- Ensure there's at least one record in the result set.
IF @@RowCount = 0
BEGIN
INSERT INTO #TempTable(LedgerType, CreditorLedgerOpeningBalance, GeneralLedgerOpeningBalance)
SELECT 'General Ledger'
, @CreditorLedgerOpeningBalance
, @GeneralLedgerOpeningBalance
END
--Get Creditor Ledger Transactions Total
SELECT @CreditorLedgerTransactionsTotal = coalesce((
SELECT SUM(SourceTotal)
FROM #TempTable
WHERE LedgerType = 'Creditor Ledger'
), 0)
UPDATE #TempTable
SET CreditorLedgerTransactionsTotal = @CreditorLedgerTransactionsTotal
, CreditorLedgerClosingBalance = CreditorLedgerOpeningBalance + @CreditorLedgerTransactionsTotal
-- Get General Ledger Transactions Total
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: getting General Ledger Transactions Total', 0, 0, @timestamp) WITH NOWAIT;
DELETE
FROM #TempTable2
INSERT INTO #TempTable2
SELECT GLLedgerID
, SUM(COALESCE(SourceTotal, 0))
, 0
FROM #TempTable
WHERE LedgerType = 'General Ledger'
GROUP BY GLLedgerID
--Get General Ledger Closing Balance
--Get Movement Variance
UPDATE t1
SET GeneralLedgerTransactionsTotal = t2.MyValue
, GeneralLedgerClosingBalance = GeneralLedgerOpeningBalance + (
SELECT SUM(COALESCE(MyValue, 0))
FROM #TempTable2
)
, MovementVariance = (
SELECT SUM(COALESCE(MyValue, 0))
FROM #TempTable2
) - CreditorLedgerTransactionsTotal
FROM #TempTable t1
INNER JOIN #TempTable2 t2
ON t1.GLLedgerID = t2.MyDescription
WHERE t1.LedgerType = 'General Ledger'
--Get Closing Balance Variance
UPDATE #TempTable
SET ClosingBalanceVariance = GeneralLedgerClosingBalance - CreditorLedgerClosingBalance
SELECT *
FROM #TempTable
ORDER BY LedgerType
, [Source]
SET @timestamp = convert(VARCHAR(50), sysdatetime(), 126);
RAISERROR ('%s: finished', 0, 0, @timestamp) WITH NOWAIT;
DROP TABLE #TempTable;
DROP TABLE #TempTable2;
END
Further thoughts
It would be possible to rewrite the stored procedure to get rid of the temporary tables, and instead use one big query. However, this is a case where the temporary table provides better performance. If you tried to write one big monster query, you would introduce some curly locking problems and you'd almost certainly make it slower, because the same tables are being hit multiple times, and from different angles. To preserve database integrity, SQL will end up making copies of data that it doesn't need to. Using temporary tables allows us to manage SQL's 'expectations' so that it doesn't waste resources dealing with potential problems that we know won't happen.
For this query, I don't believe you'd notice any difference between #temporary tables and table @variables. For me, I think because the temporary tables are relatively 'long lived' (as in they're used all over the stored procedure, not just in one specific place), the #temporary tables seem a better fit, but that's a purely subjective aesthetic thing.
Indexes
I haven't discussed the indexes I changed; since I think that's something that most JIWA techs probably have a bit of experience with. For me, mostly they come from recommendations in SSMS - I run a query with "Include Actual Execution Plan" enabled and see what index recommendations come up. Frequently, creating an index and re-running the query will end up with a recommendation for a similar, but slightly different index. In these scenarios, I make an educated guess about the best combination and try again - too many similar indexes is self-defeating. Sometimes, you get recommendations for several different indexes on the same table, and that's also OK. This is an iterative process, and very data-specific, so be prepared to spend hours getting it right for a complicated query. But make sure to watch the impact rating from SSMS - if the index is offering a 17% improvement on a query that's got a relative cost within the batch of 5%, then it may not be worth the hassle.
Ryan Price
Advanced ERP Limited (NZ)
