Tuning complex queries/reports

Support for Microsoft SQL Server in the context of Jiwa installations.

Tuning complex queries/reports

Postby pricerc » Tue Jan 14, 2020 12:24 pm

https://forums.jiwa.com.au/viewtopic.php?f=26&t=1234&start=10#p4972 refers.

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)
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron