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