Hi There, Please help me change this Outer Apply to a Left Join because I have found them faster.
--LAST PAYMENT RECEIVED WITH OUTER APPLY - 2:53 Minutes
Select
xm.ID As 'ID'
, Isnull(Format(LastPaymentDate,'yyyy/MM/dd'),'') As 'Last Receipt'
, Isnull(XLP.DB_Credit,0) As 'Last Amount'
From Matter xm with (nolock)
Inner join MatterFileStatus xmfs with (nolock) On xmfs.IDX = xm.M_IDX And xmfs.M_FileStatus <> 5
Outer Apply(
Select Top 1 Pmt.ID, Pmt.DB_Date As LastPaymentDate, Pmt.DB_Credit
From Debtor_Balance Pmt with (nolock)
Left Join (
Select Rev.ID, Rev.DB_PaymentID
From Debtor_Balance Rev with (nolock)
Where Rev.DB_IDX = xm.M_IDX
And Rev.DB_Transaction_TypeID = 1
And Rev.DB_Transaction_ParticularsID = 2
) LRev On Pmt.ID = LRev.DB_PaymentID
Where Pmt.DB_IDX = xm.M_IDX
And Pmt.DB_Transaction_TypeID = 1
And Pmt.DB_Transaction_ParticularsID = 1
And LRev.ID is NULL
Order by Pmt.DB_Date desc
) XLP
Where XLP.ID is not NULL
And Isnull(CONVERT(varchar(10), LastPaymentDate, 111),'') < CONVERT(Varchar, DATEADD(DAY, -60, GETDATE()), 111)
Order By xm.ID
What I am looking for is the last receipt that has not been reversed for each xm.ID.
Your problem isn't just the OUTER APPLY - there are several issues with this query.
Using FORMAT - FORMAT has known and well-documented performance issues.
Your OUTER APPLY should be a CROSS APPLY - since you only want results when there is data returned. Since you only want data returned when there is both Pmt and Rev data - why do you need a LEFT JOIN? If there is no Rev (Revenue?) the payment data will be excluded.
If you want Pmt data only when both Pmt and Rev exists - the change that left join to an EXISTS - or use INNER JOIN.
Converting a date to a string - then comparing eliminates any usage of indexes on that date column. A better method would be:
WHERE XLP.LastPaymentDate < DATEADD(DAY, -60, CAST(GETDATE() AS DATE))
Hi Jeff,
Thanks for the info.
I changed the Format commands to "Isnull(CONVERT(varchar(10), LastPaymentDate, 111),'')".
The Left Join is nessary because this is part of a larger script and I need it to be blank if no payments exist.
What I am looking for is the last payment that did not have a reversal.
I also changed the Where clause as you suggested.
This however has not reduced the time the script takes to complete which is 3 minutes.
I have attempted a Left Join and it is much faster but it does not work correctly becaise the RowNum = 1 is not the row number for all correct results.
This is my attempt:
--LAST PAYMENT RECEIVED WITH LEFT JOIN - 6 Seconds
With CTE As (
Select *
From (
Select ID as 'PayID', DB_IDX, DB_Date As 'LastPaymentDate', DB_Credit As 'LastPaymentAmount'
, ROW_NUMBER() Over(Partition by DB_IDX Order by DB_DateImported desc) as RowNum
From Debtor_Balance with (nolock)
Where DB_Transaction_TypeID = 1 And DB_Transaction_ParticularsID = 1
) Payments
Left Join (
Select ID as 'RevID', DB_PaymentID
From Debtor_Balance with (nolock)
Where DB_Transaction_TypeID = 1 And DB_Transaction_ParticularsID = 2 And DB_PaymentID is not NULL
) Reversals on Payments.PayID = Reversals.DB_PaymentID
)
Select --Top 1
xm.ID As 'ID'
, Isnull(CONVERT(char(10), LastPaymentDate, 111),'') As 'Last Receipt'
, Isnull(LastPaymentAmount,0) As 'Last Amount'
, CTE.RowNum
From CTE
Inner Join Matter xm with (nolock) on xm.M_IDX = DB_IDX
Inner join MatterFileStatus xmfs with (nolock) on xm.M_IDX = xmfs.IDX And xmfs.M_FileStatus <> 5--Exclude closed
Where CTE.RevID is null --Only payments that are not reversed
And CTE.RowNum = 1 --Only the last payment THIS IS THE POBLEM LINE I THINK
And LastPaymentDate < DATEADD(DAY, -60, CAST(GETDATE() AS DATE))
Order By xm.ID, RowNum
-- 6 Seconds
This line does not work "And CTE.RowNum = 1 --Only the last payment" because on some accounts the 2nd or 3rd or 4th payment is the one without the reversal.
I hope this makes sense to you.
Thanks for trying to help.
Eugene
There are many problems here - so let's start with getting the latest payment with no revenue:
SELECT ...
FROM dbo.Debtor_Balance AS pmt
LEFT JOIN dbo.Debtor_Balance AS rev ON rev.DB_PaymentID = pmt.PayID
AND rev.DB_Transaction_TypeID = pmt.DB_Transaction_TypeID
AND rev.DB_Transaction_ParticularsID = 2
WHERE pmt.DB_Transaction_TypeID = 1
AND pmt.DB_Transaction_ParticularsID = 1
AND rev.RevID IS NULL -- No Revenue associated with this Payment
Now - to get the latest we can add a TOP (1)
SELECT TOP (1)
...
FROM dbo.Debtor_Balance AS pmt
LEFT JOIN dbo.Debtor_Balance AS rev ON rev.DB_PaymentID = pmt.PayID
AND rev.DB_Transaction_TypeID = pmt.DB_Transaction_TypeID
AND rev.DB_Transaction_ParticularsID = 2
WHERE pmt.DB_Transaction_TypeID = 1
AND pmt.DB_Transaction_ParticularsID = 1
AND rev.RevID IS NULL -- No Revenue associated with this Payment
ORDER BY pmt.LastPaymentDate DESC
Now - we can use that in an OUTER APPLY:
SELECT ...
FROM dbo.Matter AS xm
INNER JOIN dbo.MatterFileStatus AS xmfs ON xmfs.IDX = xm.M_IDX
AND xmfs.M_FileStatus <> 5 --This can be moved to the WHERE clause
OUTER APPLY (
SELECT TOP (1)
...
FROM dbo.Debtor_Balance AS pmt
LEFT JOIN dbo.Debtor_Balance AS rev ON rev.DB_IDX = pmt.DB_IDX
AND rev.DB_PaymentID = pmt.PayID
AND rev.DB_Transaction_TypeID = pmt.DB_Transaction_TypeID
AND rev.DB_Transaction_ParticularsID = 2
WHERE pmt.DB_IDX = xm.M_IDX -- Correlate to outer query
AND pmt.DB_Transaction_TypeID = 1
AND pmt.DB_Transaction_ParticularsID = 1
AND rev.RevID IS NULL -- No Revenue associated with this Payment
ORDER BY pmt.LastPaymentDate DESC
) AS xlp
WHERE pmt.LastPaymentDate < DATEADD(DAY, -60, CAST(GETDATE() AS DATE))
Since you have XLP.ID IS NOT NULL - that makes the assumption that you don't want any rows where the OUTER APPLY doesn't return a value. If that is the case then simply change the OUTER APPLY to CROSS APPLY and that will eliminate the rows.
I also removed WITH (NOLOCK) - if you must have that then you should use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the query. Much easier than putting nolock on every table.
If you are - in fact - returning only rows where there is a LastPaymentDate - then no need to use ISNULL or convert. Just return the date to the client and let the client format the date as needed.
Hi Jeff,
Thanks for taking time out to resolve my speed issues.
I changed the script to be like yours but unfortunately it does not speed up the process.
I still believe that if we make the Outer Apply a Left Join it would be much faster.
This is my new script with your changes:
SELECT
xm.ID As 'ID'
, Isnull(CONVERT(char(10), XLP.LastPaymentDate, 111),'') As 'Last Receipt'
, Isnull(XLP.LastPaymentAmt,0) As 'Last Amount'
FROM ExcaliburV4_WH.dbo.Matter AS xm
INNER JOIN ExcaliburV4_WH.dbo.MatterFileStatus AS xmfs ON xmfs.IDX = xm.M_IDX AND xmfs.M_FileStatus <> 5 --This can be moved to the WHERE clause
OUTER APPLY (
SELECT TOP (1) pmt.ID, pmt.DB_Date As LastPaymentDate, pmt.DB_Credit As LastPaymentAmt
FROM ExcaliburV4_WH.dbo.Debtor_Balance pmt
LEFT JOIN ExcaliburV4_WH.dbo.Debtor_Balance AS rev ON rev.DB_PaymentID = pmt.ID
AND rev.DB_Transaction_TypeID = pmt.DB_Transaction_TypeID
AND rev.DB_Transaction_ParticularsID = 2
WHERE pmt.DB_IDX = xm.M_IDX
And pmt.DB_Transaction_TypeID = 1
AND pmt.DB_Transaction_ParticularsID = 1
AND Rev.ID IS NULL
ORDER BY pmt.DB_Date desc
) AS XLP
WHERE CAST(XLP.LastPaymentDate As Date) < DATEADD(DAY, -60, CAST(GETDATE() AS DATE))
Order By xm.ID
-- 3:53 Minutes
I tried ChatGPT as well but its Left Join did not work either.
Thanks.
Eugene.
Hi Jeff,
I think I have the Left Join and it takes 1 second to run:
--LAST PAYMENT RECEIVED WITH LEFT JOIN - 1 Second
WITH CTE AS (
SELECT
xm.ID AS 'ID'
, ISNULL(CONVERT(char(10), XLP.LastPaymentDate, 111), '') AS 'Last Receipt'
, ISNULL(XLP.LastPaymentAmt, 0) AS 'Last Amount'
, ROW_NUMBER() OVER (PARTITION BY xm.ID ORDER BY xm.ID, XLP.LastPaymentDate desc) AS RowNum
FROM Matter AS xm
INNER JOIN MatterFileStatus AS xmfs ON xmfs.IDX = xm.M_IDX AND xmfs.M_FileStatus <> 5
LEFT JOIN (
SELECT
pmt.DB_IDX,
MAX(pmt.DB_Date) AS LastPaymentDate,
pmt.DB_Credit AS LastPaymentAmt
FROM (
SELECT
ID,
DB_IDX,
DB_Date,
DB_Credit,
DB_Transaction_TypeID,
ROW_NUMBER() OVER (PARTITION BY DB_IDX ORDER BY DB_Date DESC) AS rn
FROM Debtor_Balance
WHERE DB_Transaction_TypeID = 1
AND DB_Transaction_ParticularsID = 1
) AS pmt
LEFT JOIN Debtor_Balance AS rev ON rev.DB_PaymentID = pmt.ID
AND rev.DB_Transaction_TypeID = pmt.DB_Transaction_TypeID
AND rev.DB_Transaction_ParticularsID = 2
WHERE rev.ID IS NULL
-- AND pmt.rn = 1
GROUP BY pmt.DB_IDX, pmt.DB_Credit
) AS XLP ON XLP.DB_IDX = xm.M_IDX
)
SELECT
[ID]
, [Last Receipt]
, [Last Amount]
FROM
CTE
WHERE
RowNum = 1
And CAST(ISNULL([Last Receipt], '1900-01-01') AS DATE) < DATEADD(DAY, -60, CAST(GETDATE() AS DATE))
ORDER BY [ID];
-- 1 Second
Any comments would be appreciated.
Thanks for the help.
Eugene
Hi Ahmed,
What is an execution plan?
Please send me a link for training on this.
All I know is the Outer Apply takes 3 minutes and the Left Join takes 2 seconds.
Thanks
Eugene