February 15, 2017 at 6:44 pm
Dear all,
We have a query which results all the records which are unpaid as of transaction effective date provided by user.
SELECT dbo.LRLedger.LedgerName, dbo.Account.AccountName, dbo.LRTransaction.AccountNumber, sum(dbo.LRTransaction.Amount) AS Amount FROM dbo.LRLedger,dbo.LRTransaction LEFT OUTER JOIN dbo.Account ON dbo.LRTransaction.AccountNumber = dbo.Account.AccountNumber WHERE dbo.LRLedger.LedgerId = dbo.LRTransaction.LedgerId AND dbo.LRTransaction.TransactionEffDate <= '01/31/2018' AND dbo.LRTransaction.IsClosed = 0 GROUP BY dbo.LRLedger.LedgerName, dbo.Account.AccountName, dbo.LRTransaction.AccountNumber
This query takes about less than one second to run . We will use this result set to populate an excel sheet and present to user in the online application.
Now I am trying to modify this query in such a way that it breaks down the unpaid amount based on number of aging days like below.
1. Records which are between 0 and 30 days which are unpaid since the transaction effective date.
2. Records which are between 30 and 60 days which are unpaid since the transaction effective date
3. Between 60 and 90 , 90 and 120 , 120 and 150 and finally 150 +.
Basically its just a breakdown of the existing report.
To achieve this I have written a stored procedure which will give me the breakdown as expected.
But when i run this stored procedure, it takes more than 30 seconds to complete.
Not sure what is the mistake because of which this query is running for this much time.
Please guide me on improving the performance of this stored procedure.
My sp is below :
CREATE PROCEDURE [dbo].[Pmcsdcorrection] @TransactionEffDate DATETIME2(3)
AS
BEGIN
SET nocount ON
SET ansi_warnings OFF
DECLARE @thirty DATETIME2(3),
@Sixty DATETIME2(3),
@Ninety DATETIME2(3),
@onetwenty DATETIME2(3),
@onefifty DATETIME2(3),
@ActNumber CHAR(10),
@LedgerNme CHAR(30),
@NinetyBucket MONEY,
@ThirtyBucket MONEY,
@SixtyBucket MONEY,
@OneTwentyBucke MONEY,
@OneFiftyBucket MONEY,
@OneFiftyPlusBucket MONEY,
@LedgerId NUMERIC (18, 0)
CREATE TABLE #tmpledger
(
[ledgername] [CHAR](20) NULL,
[ledgerid] [NUMERIC](18, 0) NULL,
[accountname] [CHAR](30) NULL,
[accountnumber] [CHAR](10) NOT NULL,
[thirtybucket] [MONEY] NULL,
[sixtybucket] [MONEY] NULL,
[ninetybucket] [MONEY] NULL,
[onetwentybucket] [MONEY] NULL,
[onefiftybucket] [MONEY] NULL,
[onefiftyplusbucket] [MONEY] NULL
)
SELECT dbo.lrledger.ledgername,
dbo.lrtransaction.ledgerid,
dbo.account.accountname,
dbo.lrtransaction.accountnumber
FROM dbo.lrledger,
dbo.lrtransaction
LEFT OUTER JOIN dbo.account
ON dbo.lrtransaction.accountnumber =
dbo.account.accountnumber
WHERE dbo.lrledger.ledgerid = dbo.lrtransaction.ledgerid
AND dbo.lrtransaction.transactioneffdate <= '04/30/2017'
AND dbo.lrtransaction.isclosed = 0
GROUP BY dbo.lrledger.ledgername,
dbo.account.accountname,
dbo.lrtransaction.accountnumber,
dbo.lrtransaction.ledgerid;
INSERT INTO #tmpledger
(ledgername,
ledgerid,
accountname,
accountnumber)
SELECT dbo.lrledger.ledgername,
dbo.lrtransaction.ledgerid,
dbo.account.accountname,
dbo.lrtransaction.accountnumber
FROM dbo.lrledger,
dbo.lrtransaction
LEFT OUTER JOIN dbo.account
ON dbo.lrtransaction.accountnumber =
dbo.account.accountnumber
WHERE dbo.lrledger.ledgerid = dbo.lrtransaction.ledgerid
AND dbo.lrtransaction.transactioneffdate <= '04/30/2017'
AND dbo.lrtransaction.isclosed = 0
GROUP BY dbo.lrledger.ledgername,
dbo.account.accountname,
dbo.lrtransaction.ledgerid,
dbo.lrtransaction.accountnumber
DECLARE get_aging_buckets CURSOR FOR
SELECT ledgername,
ledgerid,
accountnumber
FROM #tmpledger
OPEN get_aging_buckets
FETCH next FROM get_aging_buckets INTO @LedgerNme, @LedgerId, @ActNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @thirty = Dateadd(day, -30, @TransactionEffDate)
SELECT @Sixty = Dateadd(day, -30, @TransactionEffDate)
SELECT @Ninety = Dateadd(day, -60, @TransactionEffDate)
SELECT @onetwenty = Dateadd(day, -90, @TransactionEffDate)
SELECT @onefifty = Dateadd(day, -150, @TransactionEffDate)
SELECT @ThirtyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
Amount
FROM dbo.lrledger,
dbo.lrtransaction
WHERE dbo.lrtransaction.accountnumber =
@ActNumber
AND dbo.lrledger.ledgerid = @LedgerId
AND transactioneffdate BETWEEN
@thirty AND @TransactionEffDate
AND dbo.lrtransaction.isclosed = 0);
SELECT @SixtyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
Amount
FROM dbo.lrledger,
dbo.lrtransaction
WHERE dbo.lrtransaction.accountnumber =
@ActNumber
AND dbo.lrledger.ledgerid = @LedgerId
AND transactioneffdate BETWEEN
@Sixty AND @thirty
AND dbo.lrtransaction.isclosed = 0);
SELECT @NinetyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
Amount
FROM dbo.lrledger,
dbo.lrtransaction
WHERE dbo.lrtransaction.accountnumber =
@ActNumber
AND dbo.lrledger.ledgerid = @LedgerId
AND transactioneffdate BETWEEN
@Ninety AND @Sixty
AND dbo.lrtransaction.isclosed = 0);
SELECT @OneTwentyBucke = (SELECT Sum(dbo.lrtransaction.amount) AS
Amount
FROM dbo.lrledger,
dbo.lrtransaction
WHERE dbo.lrtransaction.accountnumber =
@ActNumber
AND dbo.lrledger.ledgerid =
@LedgerId
AND transactioneffdate BETWEEN
@onetwenty AND @Ninety
AND dbo.lrtransaction.isclosed = 0)
;
SELECT @OneFiftyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
Amount
FROM dbo.lrledger,
dbo.lrtransaction
WHERE dbo.lrtransaction.accountnumber =
@ActNumber
AND dbo.lrledger.ledgerid =
@LedgerId
AND transactioneffdate BETWEEN
@onefifty AND @onetwenty
AND dbo.lrtransaction.isclosed = 0)
;
SELECT @OneFiftyPlusBucket = (SELECT Sum(dbo.lrtransaction.amount)
AS
Amount
FROM dbo.lrledger,
dbo.lrtransaction
WHERE dbo.lrtransaction.accountnumber
=
@ActNumber
AND dbo.lrledger.ledgerid =
@LedgerId
AND transactioneffdate >
@onefifty
AND dbo.lrtransaction.isclosed
=
0);
UPDATE #tmpledger
SET thirtybucket = @ThirtyBucket,
sixtybucket = @SixtyBucket,
ninetybucket = @NinetyBucket,
onetwentybucket = @OneTwentyBucke,
onefiftybucket = @OneFiftyBucket,
onefiftyplusbucket = @OneFiftyPlusBucket
WHERE accountnumber = @ActNumber
AND ledgerid = @LedgerId
FETCH next FROM get_aging_buckets INTO @LedgerNme, @LedgerId,
@ActNumber
END
CLOSE get_aging_buckets
DEALLOCATE get_aging_buckets
SELECT *
FROM #tmpledger
DROP TABLE #tmpledger
SET nocount OFF
SET ansi_warnings ON
END
go
February 16, 2017 at 12:37 am
Table definitions please & index definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2017 at 7:22 am
Your problem is the cursor.
It is difficult to write a set based approach without DDL and comsumable test data.
Try something like the following:
--declare @TransactionEffDate DATETIME2(3) = SYSDATETIME();
SET @TransactionEffDate = DATEADD(day, DATEDIFF(day, 0, @TransactionEffDate), 1);
WITH Buckets(Bucket, MinDate, MaxDate)
AS
(
SELECT Bucket
,COALESCE(DATEADD(DAY, V.MinVal, @TransactionEffDate), '19000101')
,COALESCE(DATEADD(DAY, V.MaxVal, @TransactionEffDate), @TransactionEffDate)
FROM
(
VALUES ('30', -30, NULL),('60', -60, -30),('90', -90, -60),('120', -120, -90)
,('150', -150, -120), ('Rest', NULL, -150)
) V (Bucket, MinVal, MaxVal)
)
--select * from Buckets
SELECT L.ledgername, T.ledgerid, A.accountname, T.accountnumber
,ThirtyBucket = SUM(CASE WHEN B.Bucket = '30' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
,SixtyBucket = SUM(CASE WHEN B.Bucket = '60' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
,NinetyBucket = SUM(CASE WHEN B.Bucket = '90' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
,OneTwentyBucket = SUM(CASE WHEN B.Bucket = '120' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
,OneFiftyBucket = SUM(CASE WHEN B.Bucket = '150' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
,OneFiftyPlusBucket = SUM(CASE WHEN B.Bucket = 'Rest' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
FROM dbo.lrledger L
CROSS JOIN Buckets B
JOIN dbo.lrtransaction T
ON L.ledgerid = T.ledgerid
AND T.transactioneffdate < @TransactionEffDate
AND T.isclosed = 0
LEFT OUTER JOIN dbo.account A
ON L.accountnumber = A.accountnumber
GROUP BY L.ledgername, T.ledgerid, A.accountname, T.accountnumber;
February 16, 2017 at 7:47 am
I took a slightly different approach, but for the same reasons. Cursors are well known for being potential performance problems, and what it appears you did was to approach the problem as a procedural problem instead of a set-based one. SQL Server and relational databases do best by working with sets. The key here was simply to recognize how to group your data in such a way that the SUMs you wanted were done according to various 30 days ranges from the date passed in to your sproc. Look at each query element and see how it contributes to the process. Taking this apart may do more to help your understanding.
CREATE PROCEDURE dbo.Pmcsdcorrection (
@TransactionEffDate DATETIME2(3)
)
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
WITH AGE_RANGES AS (
SELECT '0 - 30 days' AS RANGE_NAME, 0 AS RANGE_LOW, 31 AS RANGE_HIGH, 1 AS SORT_VALUE
UNION ALL
SELECT '31 - 60 days', 31, 61, 2
UNION ALL
SELECT '61 - 90 days', 61, 91, 3
UNION ALL
SELECT '91 - 120 days', 91, 121, 4
UNION ALL
SELECT '121 - 150 days', 121, 151, 5
UNION ALL
SELECT '150+ days', 151, 2147483647, 6
),
AGING AS (
SELECT LRL.ledgername, LRT.ledgerid, A.accountname, LRT.accountnumber, LRT.amount,
DATEDIFF(day, LRT.transactioneffdate, @TransactionEffDate) AS ACCOUNT_AGE
FROM dbo.lrledger AS LRL,
dbo.lrtransaction AS LRT
LEFT OUTER JOIN dbo.account AS A
ON LRT.accountnumber = A.accountnumber
WHERE LRL.ledgerid = LRT.ledgerid
AND LRT.transactioneffdate <= '04/30/2017'
AND LRT.isclosed = 0
),
RANGE_ASSIGNMENT AS (
SELECT R.RANGE_NAME, A.ledgername, A.ledgerid, A.accountname, A.accountnumber, SUM(ISNULL(A.amount,0)) AS amount, R.SORT_VALUE
FROM AGE_RANGES AS R
LEFT OUTER JOIN AGING AS A
ON A.ACCOUNT_AGE >= R.RANGE_LOW
AND A.ACCOUNT_AGE < R.RANGE_HIGH
GROUP BY R.RANGE_NAME, A.ledgername, A.ledgerid, A.accountname, A.accountnumber
)
SELECT [0 - 30 days], [31 - 60 days], [61 - 90 days], [91 - 120 days], [121 - 150 days], [150+ days]
FROM (
SELECT SORT_VALUE, [0 - 30 days], [31 - 60 days], [61 - 90 days], [91 - 120 days], [121 - 150 days], [150+ days]
FROM RANGE_ASSIGNMENT
PIVOT (SUM(amount) FOR RANGE_NAME IN ([0 - 30 days], [31 - 60 days], [61 - 90 days], [91 - 120 days], [121 - 150 days], [150+ days]) AS PVT
) AS X;
END
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 8:39 am
Just a basic approach using cross tabs to avoid massaging the data for pivot.
I also changed the old-syntax join to keep the code consistent and used table alias to prevent 3-part column names which are deprecated.
CREATE PROCEDURE [dbo].[Pmcsdcorrection] @TransactionEffDate DATETIME2(3)
AS
SET nocount ON ;
SET ansi_warnings OFF ;
DECLARE @thirty DATETIME2(3),
@Sixty DATETIME2(3),
@Ninety DATETIME2(3),
@onetwenty DATETIME2(3),
@onefifty DATETIME2(3);
SELECT l.ledgername,
t.ledgerid,
a.accountname,
t.accountnumber
FROM dbo.lrledger l
JOIN dbo.lrtransaction t ON l.ledgerid = t.ledgerid
LEFT OUTER JOIN dbo.account a ON t.accountnumber = a.accountnumber
WHERE t.transactioneffdate <= '04/30/2017'
AND t.isclosed = 0
GROUP BY l.ledgername,
a.accountname,
t.accountnumber,
t.ledgerid;
SELECT @thirty = Dateadd(day, -30, @TransactionEffDate)
,@Sixty = Dateadd(day, -30, @TransactionEffDate)
,@Ninety = Dateadd(day, -60, @TransactionEffDate)
,@onetwenty = Dateadd(day, -90, @TransactionEffDate)
,@onefifty = Dateadd(day, -150, @TransactionEffDate);
SELECT l.ledgername,
t.ledgerid,
a.accountname,
t.accountnumber,
thirtybucket = SUM( CASE WHEN transactioneffdate BETWEEN @thirty AND @TransactionEffDate THEN t.amount ELSE 0 END),
sixtybucket = SUM( CASE WHEN transactioneffdate BETWEEN @Sixty AND @thirty THEN t.amount ELSE 0 END),
ninetybucket = SUM( CASE WHEN transactioneffdate BETWEEN @Ninety AND @Sixty THEN t.amount ELSE 0 END),
onetwentybucket = SUM( CASE WHEN transactioneffdate BETWEEN @onetwenty AND @Ninety THEN t.amount ELSE 0 END),
onefiftybucket = SUM( CASE WHEN transactioneffdate BETWEEN @onefifty AND @onetwenty THEN t.amount ELSE 0 END),
onefiftyplusbucket = SUM( CASE WHEN transactioneffdate < @onefifty THEN t.amount ELSE 0 END)
FROM dbo.lrledger l
JOIN dbo.lrtransaction t ON l.ledgerid = t.ledgerid
LEFT JOIN dbo.account a ON t.accountnumber = a.accountnumber
WHERE t.transactioneffdate <= '04/30/2017'
AND t.isclosed = 0
GROUP BY l.ledgername,
a.accountname,
t.accountnumber,
t.ledgerid;
SET nocount OFF ;
SET ansi_warnings ON ;
go
February 16, 2017 at 12:19 pm
GilaMonster - Thursday, February 16, 2017 12:37 AMTable definitions please & index definitions.
Hi Gila,
Thanks very much for checking this .. I have attached DDL for reference. Please check it.
February 16, 2017 at 12:22 pm
muralikrishna2489 - Thursday, February 16, 2017 12:19 PMHiGilaMonster - Thursday, February 16, 2017 12:37 AMTable definitions please & index definitions.Hi Gila,
Thanks for checking this .. I have attached DDL for reference. Please check it.
You're missing one table (accounts) and sample data that can be used for testing purposes.
February 16, 2017 at 12:29 pm
Thanks very much for explaining the current problem and providing solution for it.
I am going through the new stored procedures and trying to understand it. When i run these SPs with test data, it completes within two seconds !!!!!.
As i am not familiar with the concepts, it is taking some time for me to understand it. I will post here shortly if i have any doubts . Thanks again 🙂
February 16, 2017 at 12:46 pm
muralikrishna2489 - Thursday, February 16, 2017 12:29 PMHi Luis , Sgmunson, Ken MckelveyThanks very much for explaining the current problem and providing solution for it.
I am going through the new stored procedures and trying to understand it. When i run these SPs with test data, it completes within two seconds !!!!!.
As i am not familiar with the concepts, it is taking some time for me to understand it. I will post here shortly if i have any doubts . Thanks again 🙂
Take a look at this article which explains the method that I used.
http://www.sqlservercentral.com/articles/T-SQL/63681/
February 16, 2017 at 1:13 pm
Hi Luis,
I am having one small doubt in the SP, Sorry i didn't realize this earlier. I would also like to show one last column where it shows the sum of all six aging buckets.
I have tried to modify your SP like below but it is throwing error like i cannot use the variable which is not declared.
SELECT l.LedgerName,
t.LedgerId,
a.AccountName,
t.AccountNumber,
thirtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @thirty AND @TransactionEffDate THEN t.Amount ELSE 0 END),
sixtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Sixty AND @thirty THEN t.Amount ELSE 0 END),
ninetybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Ninety AND @Sixty THEN t.Amount ELSE 0 END),
onetwentybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onetwenty AND @Ninety THEN t.Amount ELSE 0 END),
onefiftybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onefifty AND @onetwenty THEN t.Amount ELSE 0 END),
onefiftyplusbucket= SUM( CASE WHEN TransactionEffDate < @onefifty THEN t.Amount ELSE 0 END),
SumofAmount = SUM (thirtybucket+sixtybucket+ninetybucket+onetwentybucket+onefiftybucket+onefiftyplusbucket)
[/code]
Could you please tell me on how to use the last column as sum of all six aging buckets.
February 16, 2017 at 1:25 pm
Hi Luis,
I have got it 🙂 ...
I have used below query to get the sum of aging bucket.
Please tell me if it is wrong Luis... Thanks in Advance...
SELECT l.LedgerName,
t.LedgerId,
a.AccountName,
t.AccountNumber,
thirtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @thirty AND @TransactionEffDate THEN t.Amount ELSE 0 END),
sixtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Sixty AND @thirty THEN t.Amount ELSE 0 END),
ninetybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Ninety AND @Sixty THEN t.Amount ELSE 0 END),
onetwentybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onetwenty AND @Ninety THEN t.Amount ELSE 0 END),
onefiftybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onefifty AND @onetwenty THEN t.Amount ELSE 0 END),
onefiftyplusbucket= SUM( CASE WHEN TransactionEffDate < @onefifty THEN t.Amount ELSE 0 END),
SumofAmount = SUM (t.Amount)
FROM dbo.LRLedger l
JOIN dbo.LRTransaction t ON l.LedgerId = t.LedgerId
LEFT JOIN dbo.Account a ON t.AccountNumber = a.AccountNumber
WHERE t.TransactionEffDate <= '04/30/2017'
AND t.IsClosed = 0
GROUP BY l.LedgerName,
a.AccountName,
t.AccountNumber,
t.LedgerId;
February 16, 2017 at 1:28 pm
muralikrishna2489 - Thursday, February 16, 2017 1:25 PMHi Luis,I have got it 🙂 ...
I have used below query to get the sum of aging bucket.
Please tell me if it is wrong Luis... Thanks in Advance...
SELECT l.LedgerName,
t.LedgerId,
a.AccountName,
t.AccountNumber,
thirtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @thirty AND @TransactionEffDate THEN t.Amount ELSE 0 END),
sixtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Sixty AND @thirty THEN t.Amount ELSE 0 END),
ninetybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Ninety AND @Sixty THEN t.Amount ELSE 0 END),
onetwentybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onetwenty AND @Ninety THEN t.Amount ELSE 0 END),
onefiftybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onefifty AND @onetwenty THEN t.Amount ELSE 0 END),
onefiftyplusbucket= SUM( CASE WHEN TransactionEffDate < @onefifty THEN t.Amount ELSE 0 END),
SumofAmount = SUM (t.Amount)
FROM dbo.LRLedger l
JOIN dbo.LRTransaction t ON l.LedgerId = t.LedgerId
LEFT JOIN dbo.Account a ON t.AccountNumber = a.AccountNumber
WHERE t.TransactionEffDate <= '04/30/2017'
AND t.IsClosed = 0
GROUP BY l.LedgerName,
a.AccountName,
t.AccountNumber,
t.LedgerId;
That's exactly the way to code it. As you can see, it's also the simplest way.
February 16, 2017 at 1:34 pm
muralikrishna2489 - Thursday, February 16, 2017 1:25 PMThat's exactly the way to code it. As you can see, it's also the simplest way.
Thanks very much Luis !!!
February 17, 2017 at 2:56 am
Hello muralikrishna2489,
Did you notice that in your orginal query as well as in some of the answers, three intervals are set incorrect? @Sixty is set to 30 days, @Ninety is set to 60 days and @onetwenty is set to 90 days. This might be on purpose, but I guess they are just typos, because @thirty and @onefifty are set exactly according to their names. Hope this helps, good luck with this procedure.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply