Large Transaction Mode

[SBarnes]

Hi Mike

Is there any update on the plugin for the following https://forums.jiwa.com.au/viewtopic.php?f=26&t=859&p=3131&hilit=large+transaction+mode#p3131?

[Mike.Sheen]

Hi Stuart,

Sorry - not yet. Will get to it when I can.

Mike

[SBarnes]

Hi Mike,

I had a quick look at this today and discovered something that you may want to know Jiwa 7.1 is calling the stored procedure usp_Jiwa_Debtor_Transactions which is shown below on the customer database that is taking a long time to produce the debtor transactions.

This procedure takes well over a minute to execute in SQL explorer and for the account in question @DebtorIsBranchAccount would be equal to 1 i.e. its a child account, it would appear that part of the issue may be coming from the sub queries that are being executed when there are large volumes of transaction data.

I also noticed that the stored procedure use table variables instead of temporary tables and considered this may be an issue given the volume of data but converting it to use temp tables in this case made no performance difference so this can be ruled out.

Jiwa is taking over three minutes to list the transactions so whilst what I discovered is part of the problem, paging in the grid is going to still be needed through a plugin.

I hope this is of some help.

Code: Select all

ALTER PROC [dbo].[usp_Jiwa_Debtor_Transactions] @DebtorID CHAR(20), @AsAtDate DATETIME, @AgedOrUnagedOrAll TINYINT
AS
SET NOCOUNT ON

--@AgedOrUnagedOrAll
--Aged = 0
--Unaged=1
--All=2

DECLARE @Results TABLE
(
   TransID CHAR(20) COLLATE DATABASE_DEFAULT NOT NULL,
   DebitCredit BIT NOT NULL,
   Source VARCHAR(255) NULL,
   Ref VARCHAR(200) NULL,
   Remark VARCHAR(200) NULL,
   TranDate DATETIME NOT NULL,
   InvRemitNo VARCHAR(50) NOT NULL,
   Amount DECIMAL(19,6) NOT NULL,
   AgedOut BIT NOT NULL,
   SourceID CHAR(36) COLLATE DATABASE_DEFAULT NULL,
   AllocatedAmount DECIMAL(19,6) NOT NULL,
   GSTAmount DECIMAL(19,6) NOT NULL,
   DueDate DATETIME NOT NULL,
   DebtorID CHAR(20) COLLATE DATABASE_DEFAULT NOT NULL,
   AccountNo VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL,
   SubType SMALLINT NOT NULL,
   FXAmount DECIMAL(19,6) NOT NULL,
   FXAllocAmount DECIMAL(19,6) NOT NULL,
   CurrencyID CHAR(36) COLLATE DATABASE_DEFAULT NULL,
   CurrencyRateUsed DECIMAL(19,6) NOT NULL,
   RealisedGainLoss DECIMAL(19,6) NOT NULL,
   UnRealisedGainLoss DECIMAL(19,6) NOT NULL,
   DecimalPlaces SMALLINT NOT NULL,
   FXDecimalPlaces SMALLINT NULL,
   ShortName VARCHAR(50) NULL,
   Note VARCHAR(MAX) NULL,
   Parent_DebtorID CHAR(36) COLLATE DATABASE_DEFAULT NULL,
   Parent_AccountNo VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL,
   PeriodNo INT NOT NULL
)

DECLARE @DebtorIsBranchAccount BIT

SELECT @DebtorIsBranchAccount = CASE DB_Main.DebtorID WHEN DB_Main.ParentDebtorID THEN 0 ELSE 1 END FROM DB_Main WHERE DebtorID = @DebtorID

PRINT @DebtorIsBranchAccount

IF @DebtorIsBranchAccount = 0
   INSERT INTO @Results
   SELECT DB_Trans.TransID, DebitCredit, Source, Ref, Remark, TranDate, InvRemitNo, Amount, AgedOut, SourceID, AllocatedAmount,
      GSTAmount, DueDate, DB_Main.DebtorID, DB_Main.AccountNo, SubType, FXAmount, FXAllocAmount, DB_Trans.CurrencyID, DB_Trans.CurrencyRateUsed, RealisedGainLoss,
      UnRealisedGainLoss, DB_Trans.DecimalPlaces, FXDecimalPlaces, FX_Currency.ShortName, Note, DB_Trans.DebtorID, DB_Main.AccountNo,
      dbo.ufn_JIWA_Debtors_TransactionPeriodNo(TranDate, DB_Main.DebtorID, @AsAtDate) [PeriodNo]
   FROM DB_Trans
   JOIN DB_Main ON (DB_Main.DebtorID = DB_Trans.ChildDebtorID)
   LEFT JOIN FX_Currency ON (FX_Currency.RecID = DB_Trans.CurrencyID)
   WHERE DB_Trans.DebtorID = @DebtorID
   AND (@AgedOrUnagedOrAll = 2 OR (@AgedOrUnagedOrAll = 0 AND AgedOut = 1) OR (@AgedOrUnagedOrAll = 1 AND AgedOut = 0))
ELSE
   INSERT INTO @Results
   SELECT DB_Trans.TransID, DebitCredit, Source, Ref, Remark, TranDate, InvRemitNo, Amount, AgedOut, SourceID, AllocatedAmount,
      GSTAmount, DueDate, DB_Main.DebtorID, DB_Main.AccountNo, SubType, FXAmount, FXAllocAmount, DB_Trans.CurrencyID, DB_Trans.CurrencyRateUsed, RealisedGainLoss,
      UnRealisedGainLoss, DB_Trans.DecimalPlaces, FXDecimalPlaces, FX_Currency.ShortName, Note, DB_Trans.DebtorID, DB_Main.AccountNo,
      dbo.ufn_JIWA_Debtors_TransactionPeriodNo(TranDate, DB_Main.DebtorID, @AsAtDate) [PeriodNo]
   FROM DB_Trans
   JOIN DB_Main ON (DB_Main.DebtorID = DB_Trans.ChildDebtorID)
   LEFT JOIN FX_Currency ON (FX_Currency.RecID = DB_Trans.CurrencyID)
   WHERE (DB_Trans.ChildDebtorID = @DebtorID OR DB_Trans.DebtorID = @DebtorID)
   OR DB_Trans.TransID IN (SELECT DebitTransID FROM DB_TransAlloc WHERE CreditTransID IN (SELECT TransID FROM DB_Trans WHERE (DB_Trans.ChildDebtorID = @DebtorID OR DB_Trans.DebtorID = @DebtorID)))
   OR DB_Trans.TransID IN (SELECT CreditTransID FROM DB_TransAlloc WHERE DebitTransID IN (SELECT TransID FROM DB_Trans WHERE (DB_Trans.ChildDebtorID = @DebtorID OR DB_Trans.DebtorID = @DebtorID)))
   AND (@AgedOrUnagedOrAll = 2 OR (@AgedOrUnagedOrAll = 0 AND AgedOut = 1) OR (@AgedOrUnagedOrAll = 1 AND AgedOut = 0))

SELECT * FROM @Results ORDER BY TranDate

[Mike.Sheen]

Hi Stuart,

Thanks for that analysis - we have a customer on 07.00.175.00 with the same performance issue relating to that same stored procedure.

We're in the process of troubleshooting that now, should know more tomorrow.

What I found really interesting was that when I ran SQL Profiler and grabbed the call to the stored procedure and ran in SQL Query Analyser, it was still taking minutes - but copying and pasting the stored proc contents and refactoring the parameters to be just declared variables, the results came back in less than 1 second (some 1,200 rows). Looking at the execution plan showed nothing to explain this. This made no sense to me, so we're rebuilding their indexes and updating statistics on that database tonight and we'll see what results we get tomorrow.

Mike

[SBarnes]

The customer database I have has 1,347,093 rows in DB_Trans so if you need me to test anything against that volume, let me know.

[Mike.Sheen]

I've narrowed this down to be caused by parameter sniffing.

The attached script will alter the stored procedure usp_Jiwa_Debtor_Transactions to put the parameter values into local variables and use those in the query instead of the passed in parameters. The result is that SQL Server will no longer make assumptions about the best query plan based on the parameter values passed in - which in my test case solved the issue.

Have a go with the attached and let us know if that resolves your issue also.

Mike

[SBarnes]

Hi Mike,

The procedure executes in 8 seconds for the debtor it was taking over a minute to execute for before, this is returning 27,619 rows.

Jiwa itself however to show the transactions is still taking a long time albeit a shorter time.

One interesting thing though on the same database and server a plugin I wrote with a custom screen can display over 207,000 rows in a jiwa grid in about a minute, admittedly its pulling that data from one table though.

If there is anything else you want me to test against this database just let me know

[DannyC]

This has reared it's head at another of our favourite clients. It'd be really great to have Large Transaction Mode available somehow in Jiwa 7.

We already have the tweaked stored proc installed usp_JIWA_Debtor_Transactions at this client as linked above.
I can see that your declaring local variables & assigning the passed in parameters to the local variables. I didn't think too much about it until I watched this video recently.

For context, you'll need to watch the whole thing, but the interesting bit relevant to the adjusted stored proc is at 37:15.
https://www.youtube.com/watch?v=pd7xqLT_-2k

[Mike.Sheen]

I can see that your declaring local variables & assigning the passed in parameters to the local variables. I didn't think too much about it until I watched this video recently.

Ok, so the advice is to not use the "trick" of creating a local variable, stuffing the parameter values into them and using the local variables because it looks wasteful / pointless, and to use the OPTIMIZE FOR UNKNOWN hint instead.

I did somewhat address the drawback of the local variable strategy by commenting that bit within the stored proc:

Code: Select all

-- Because of Parameter Sniffing, we take the parameters and put them into local variables and use those instead.
DECLARE @local_DebtorID CHAR(20) = @DebtorID
DECLARE @local_AsAtDate DATETIME = @AsAtDate
DECLARE @local_AgedOrUnagedOrAll TINYINT = @AgedOrUnagedOrAll

But, ok, I'll make a mental note to address parameter sniffing issues in the future with OPTIMIZE FOR UNKNOWN hints instead.

[Scott.Pearce]

Don't forget that hints are just that - hints. There is no guarantee that the SQL engine will use the hints.