SQL Script taking too long due to two joins

EugeneRequilet · January 6, 2023, 10:26am

Hi There,
I have a script that is taking too long to run due to two joins. I have marked the two joins.
Please help to make it faster. I think it should be done in two parts, perhaps with a view and a second pass or a cursor.
Thanks for helping me with other scripts previously.

This is the script:

Declare @Today Date, @30DaysAgo Date, @120DaysAgo Date, @150DaysAgo Date, @180DaysAgo Date, @StatusChangedDays Date

Set @30DaysAgo = DATEADD(day, -30, GETDATE())
Set @120DaysAgo = DATEADD(day, -120, GETDATE())
Set @150DaysAgo = DATEADD(day, -150, GETDATE())
Set @180DaysAgo = DATEADD(day, -180, GETDATE())
Set @StatusChangedDays = DATEADD(day, -90, GETDATE())

Select --Top 100
xm.ID As 'Excalibur Reference'
, LTrim(IsNull(xd.D_FullNames,'') + ' ' + IsNull(xd.D_Surname,'')) As 'Debtor'
, ISNULL(xd.D_DebtorIdentityNo,'') As 'Debtor ID'
, CONVERT(varchar, xm.M_IntructionReceived, 103) 'Instructed'
, xdt.DP_Name as 'Department'
, Case When Format(XPC.LastPhoneCall,'yyyy-MM-dd') Is Null Then '' Else Format(XPC.LastPhoneCall,'yyyy-MM-dd') End As 'Last Phone Call'
, Isnull(XPC.LastPhoneNo,'') As 'Last Number Called'
, Isnull(XPC.WhoAnswered,'No Selection') As 'Who Answered'
--, Isnull(XMP.PN_Number,'') As 'RPC Number'
--, Isnull(Format(XMP.PN_RPC_Date,'yyyy-MM-dd'),'') As 'RPC Date'
, Isnull(XDB.DebtorBalance,0) As 'Debtors Balance'
, Isnull(Format(XDBR.DB_Date,'yyyy/MM/dd'),'') As 'Last Receipt'
, IsNull(XDBR.DB_Credit,0) As 'L/R Amount'
, Isnull(Format(xmfs.M_ActionChanged,'yyyy/MM/dd'),'') As 'Status Changed'
, Isnull(xfs.St_Status,'') As 'Status'
, Isnull(xfss.FSS_SubStatusName,'') As 'Sub Status'
, Isnull(CONVERT(varchar, XTR.BackFromTrace, 111),'') As 'Back From Trace'
, Case When xmfs.M_FileStatus = 4 Then 1 Else 0 End As 'PTPs 0=Attempting, 1=Broken'
, Case Isnull(XTR.TraceResult,0)
When 50003 Then 0 -- 50003 = RPC Trace Successful, 50004 = RPC Trace unsuccessful
When 50004 Then
Case When CONVERT(varchar, XTR.BackFromTrace, 111) > CONVERT(varchar, @StatusChangedDays, 111) Then
1 -- 1 = No Successful Trace < 90 Days
Else
2 -- 2 = No Successful Trace > 90 Days
End
Else
3 -- No Trace Done
End As '0=Successful Trace, 1=No Successful Trace, 2=No Successful Trace - Status Changed > 90 Days, 3=No Trace Done'

-- Attempting

,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When CONVERT(varchar, xm.M_IntructionReceived, 111) > CONVERT(varchar, @30DaysAgo, 111) Then 1 Else 0 End
	Else 
		''
	End 'Attempting < 30 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When CONVERT(varchar, xm.M_IntructionReceived, 111) <= CONVERT(varchar, @30DaysAgo, 111) And 
				CONVERT(varchar, xm.M_IntructionReceived, 111) > CONVERT(varchar, @120DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Attempting 30-120 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When CONVERT(varchar, xm.M_IntructionReceived, 111) <= CONVERT(varchar, @120DaysAgo, 111) And 
				CONVERT(varchar, xm.M_IntructionReceived, 111) > CONVERT(varchar, @150DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Attempting 120-150 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When CONVERT(varchar, xm.M_IntructionReceived, 111) <= CONVERT(varchar, @150DaysAgo, 111) And 
				CONVERT(varchar, xm.M_IntructionReceived, 111) > CONVERT(varchar, @180DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Attempting 150-180 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When CONVERT(varchar, xm.M_IntructionReceived, 111) <= CONVERT(varchar, @180DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Attempting > 180 Days Ago'

-- Broken

,	Case When xmfs.M_FileStatus = 4 Then 
		Case When CONVERT(varchar, xmfs.M_ActionChanged, 111) > CONVERT(varchar, @30DaysAgo, 111) Then 1 Else 0 End
	Else 
		''
	End 'Broken < 30 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When CONVERT(varchar, xmfs.M_ActionChanged, 111) <= CONVERT(varchar, @30DaysAgo, 111) And 
				CONVERT(varchar, xmfs.M_ActionChanged, 111) > CONVERT(varchar, @120DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Broken 30-120 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When CONVERT(varchar, xmfs.M_ActionChanged, 111) <= CONVERT(varchar, @120DaysAgo, 111) And 
				CONVERT(varchar, xmfs.M_ActionChanged, 111) > CONVERT(varchar, @150DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Broken 120-150 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When CONVERT(varchar, xmfs.M_ActionChanged, 111) <= CONVERT(varchar, @150DaysAgo, 111) And 
				CONVERT(varchar, xmfs.M_ActionChanged, 111) > CONVERT(varchar, @180DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Broken 150-180 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When CONVERT(varchar, xmfs.M_ActionChanged, 111) <= CONVERT(varchar, @180DaysAgo, 111) Then 1 Else 0 End 
	Else 
		''
	End 'Broken > 180 Days Ago'

From Matter xm With (Nolock)
Inner Join MatterFileStatus xmfs With (Nolock)on xmfs.IDX = xm.M_IDX
Inner join FileStatus xfs with (nolock) on xfs.ID = xmfs.M_FileStatus
Inner join FileSubStatus xfss with (Nolock) on xmfs.M_FileSubStatus = xfss.ID
Inner join Department xdt with (Nolock) on xmfs.M_DepartmentID = xdt.ID
Inner Join BookListView xb with (Nolock) on xb.ID = xm.M_ClientID
Left Join Debtor xd With (Nolock) on xd.D_IDX = xm.M_IDX
Left Join (
Select H_IDX ,H_DateTime, H_HCRS_ID As AnsweredBy,
ROW_NUMBER() Over(Partition By H_IDX Order by H_IDX, H_DateTime Desc) rowNum
From History with (nolock)
Where H_HA_ID = 6 -- 6 = Call
And H_HCRS_ID = 1 -- 1 = Debtor Answered
) XPCNC On XPCNC.H_IDX = xm.M_IDX And XPCNC.rowNum = 1
Left Join ( -- UP TO HERE IT TAKES 6 SECONDS TO LOAD
Select PN_PL_TblID , PN_Number, PN_RPC_Date,
ROW_NUMBER() Over(Partition By PN_PL_TblID Order by PN_PL_TblID, PN_RPC_Date Desc) rowNum
From Phone with (nolock)
Where PN_RPC > 0 -- > 0 = RPC
) XMP On XMP.PN_PL_TblID = xm.M_IDX And XMP.rowNum = 1
Left Join ( -- ADDS 1 MIN TO SCRIPT
Select DB_IDX , DB_Date, DB_Credit, DB_Transaction_TypeID, ROW_NUMBER() Over(Partition By DB_IDX Order by DB_IDX, DB_Date Desc) rowNum
From Debtor_Balance with (nolock)
Where DB_Transaction_TypeID = 1 -- 1 = Receipt
) XDBR On XDBR.DB_IDX = xm.M_IDX And XDBR.rowNum = 1
Left Join ( -- UP TO HERE IT TAKES 1:25 MINUTES TO LOAD
Select H_IDX ,H_DateTime As BackFromTrace, H_SubSt_ID As TraceResult, ROW_NUMBER() Over(Partition By H_IDX Order by H_IDX, H_DateTime Desc) rowNum
From History with (nolock)
Where H_HA_ID = 11 -- 11 = Status Changed
And H_SubSt_ID In (50003,50004) -- 50003 - Trace Successful, 50004 - Trace Unsucessful
) XTR On XTR.H_IDX = xm.M_IDX And XTR.rowNum = 1

-- THE NEXT TWO JOINS SLOW THE SCRIPT DOWN VERY BADLY - IF I DON'T LOAD THE NEXT TWO JOINS IT TAKES 1:25 MINUTES TO LOAD.
-- HOW DO I FIX THIS?
-- I THOUGHT OF A SCRIPT WITHIN A SCRIPT MIGHT SPEED IT UP. I.E. A CURSOR OR MAYBE A VIEW AND THEN ADD THE BOTTOM TWO JOINS?


Left Join	(	-- ADDS 6:30 MINUTES TO SCRIPT; 8:03 MINUTES; 10:58 MINUTES; 7:43 MINUTES 
				Select DB_IDX, SUM(IsNull(DB_Debit,0)) - SUM(IsNull(DB_Credit,0)) As DebtorBalance			
				From Debtor_Balance with (nolock) 
				Group By DB_IDX
			) As xdb On xdb.DB_IDX = xm.M_IDX

Left Join	(	-- ADDS 3 HOURS TO SCRIPT
				Select H_IDX ,H_DateTime As LastPhoneCall, hcsr.HCSR_Desc As WhoAnswered, H_PhoneNumber As LastPhoneNo,  
						ROW_NUMBER() Over(Partition By H_IDX Order by H_IDX, H_DateTime Desc) rowNum
				From History with (nolock) 
					Left Join HistoryCallSelectionResult hcsr on hcsr.ID = H_HCRS_ID
				Where H_HA_ID = 6																		-- 6 = Call
			) XPC On XPC.H_IDX = xm.M_IDX And XPC.rowNum = 1

Where xb.Name Not Like '%TEST%' -- Test Matters -- EXCLUDE
And xb.Name Not Like '%ACME%' -- Test Matters -- EXCLUDE
And xb.Name Not Like '%Ithala%' -- Ithala Matters -- EXCLUDE
And xb.Name Not Like '%First National%' -- First National Matters -- EXCLUDE
And xm.ID Not Like '%TEST%' -- Test Matters -- EXCLUDE
And xm.ID Not Like '%ACME%' -- ACME Test Matters -- EXCLUDE
And xb.ID Not In (901305, 901499, 901507) -- Outsourced Matters -- EXCLUDE

And xmfs.M_FileStatus Not In (2,5,6,10,11,16,25,29,40,51,52,65,70,71,106,111,112,10000)
And xmfs.M_DepartmentID = 2			-- Call Centre
And Isnull(XPCNC.AnsweredBy,0) <> 1		-- 1 = Debtor Answered
And Isnull(XMP.PN_Number,'') = ''

ORDER BY xm.ID

Ifor · January 6, 2023, 11:05am

The extra joins are probably pushing the optimizer over the edge.
Here are some observations:

You should google nolock to see what it actually does.
Never specify just varchar, always specify the length. While varchar generally returns around varchar(30) it is not gaurenteed so can cause problems. As far as I can see all the varchar should be char(10) anyway.
Why are you converting dates to strings before comparing them. Casting is expensive so just compare dates.
BookListView is probably doing more than is needed for this query so expand it out to the underlying tables.
It would be useful to know the number of rows in the following:

BookListView
Debtor
Debtor_Balance
Department
FileStatus
FileSubStatus
History
HistoryCallSelectionResult
Matter
MatterFileStatus
Phone

EugeneRequilet · January 6, 2023, 11:21am

These are the numbers as requested:

BookListView - 1906
Debtor - 169842
Debtor_Balance - 8784197
Department - 6
FileStatus - 112
FileSubStatus - 1036
History - 34218330
HistoryCallSelectionResult - 15
Matter - 169932
MatterFileStatus - 169842
Phone - 543754

EugeneRequilet · January 6, 2023, 11:52am

After googling (nolock) info I still think I must use it because it will be faster and it is just a reporting script. Am I correct or should I remove them?
I changed all the varchar's to char(10) and I am not converting the dates into strings for comparisons.
I don't know what " 1. BookListView is probably doing more than is needed for this query so expand it out to the underlying tables." means. BookListView is just a view of Client with just the code and name.

EugeneRequilet · January 6, 2023, 11:54am

I have simplified it to this:

Declare @Today Date, @30DaysAgo Date, @120DaysAgo Date, @150DaysAgo Date, @180DaysAgo Date, @StatusChangedDays Date

Set @30DaysAgo = DATEADD(day, -30, GETDATE())
Set @120DaysAgo = DATEADD(day, -120, GETDATE())
Set @150DaysAgo = DATEADD(day, -150, GETDATE())
Set @180DaysAgo = DATEADD(day, -180, GETDATE())
Set @StatusChangedDays = DATEADD(day, -90, GETDATE())

Select --Top 100
xm.ID As 'Excalibur Reference'
, LTrim(IsNull(xd.D_FullNames,'') + ' ' + IsNull(xd.D_Surname,'')) As 'Debtor'
, ISNULL(xd.D_DebtorIdentityNo,'') As 'Debtor ID'
, CONVERT(char(10), xm.M_IntructionReceived, 103) 'Instructed'
, xdt.DP_Name as 'Department'
, Case When Format(XPC.LastPhoneCall,'yyyy-MM-dd') Is Null Then '' Else Format(XPC.LastPhoneCall,'yyyy-MM-dd') End As 'Last Phone Call'
, Isnull(XPC.LastPhoneNo,'') As 'Last Number Called'
, Isnull(XPC.WhoAnswered,'No Selection') As 'Who Answered'
--, Isnull(XMP.PN_Number,'') As 'RPC Number'
--, Isnull(Format(XMP.PN_RPC_Date,'yyyy-MM-dd'),'') As 'RPC Date'
, Isnull(XDB.DebtorBalance,0) As 'Debtors Balance'
, Isnull(Format(XDBR.DB_Date,'yyyy/MM/dd'),'') As 'Last Receipt'
, IsNull(XDBR.DB_Credit,0) As 'L/R Amount'
, Isnull(Format(xmfs.M_ActionChanged,'yyyy/MM/dd'),'') As 'Status Changed'
, Isnull(xfs.St_Status,'') As 'Status'
, Isnull(xfss.FSS_SubStatusName,'') As 'Sub Status'
, Isnull(CONVERT(char(10), XTR.BackFromTrace, 111),'') As 'Back From Trace'
, Case When xmfs.M_FileStatus = 4 Then 1 Else 0 End As 'PTPs 0=Attempting, 1=Broken'
, Case Isnull(XTR.TraceResult,0)
When 50003 Then 0 -- 50003 = RPC Trace Successful, 50004 = RPC Trace unsuccessful
When 50004 Then
Case When XTR.BackFromTrace > @StatusChangedDays Then
1 -- 1 = No Successful Trace < 90 Days
Else
2 -- 2 = No Successful Trace > 90 Days
End
Else
3 -- No Trace Done
End As '0=Successful Trace, 1=No Successful Trace, 2=No Successful Trace - Status Changed > 90 Days, 3=No Trace Done'

-- Attempting

,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When xm.M_IntructionReceived > @30DaysAgo Then 1 Else 0 End
	Else 
		''
	End 'Attempting < 30 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When xm.M_IntructionReceived <= @30DaysAgo And xm.M_IntructionReceived > @120DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Attempting 30-120 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When xm.M_IntructionReceived <= @120DaysAgo And xm.M_IntructionReceived > @150DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Attempting 120-150 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When xm.M_IntructionReceived <= @150DaysAgo And xm.M_IntructionReceived > @180DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Attempting 150-180 Days Ago'
,	Case When xmfs.M_FileStatus <> 4 Then 
		Case When xm.M_IntructionReceived <= @180DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Attempting > 180 Days Ago'

-- Broken

,	Case When xmfs.M_FileStatus = 4 Then 
		Case When xmfs.M_ActionChanged > @30DaysAgo Then 1 Else 0 End
	Else 
		''
	End 'Broken < 30 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When xmfs.M_ActionChanged <= @30DaysAgo And xmfs.M_ActionChanged > @120DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Broken 30-120 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When xmfs.M_ActionChanged <= @120DaysAgo And xmfs.M_ActionChanged > @150DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Broken 120-150 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When xmfs.M_ActionChanged <= @150DaysAgo And xmfs.M_ActionChanged > @180DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Broken 150-180 Days Ago'
,	Case When xmfs.M_FileStatus = 4 Then 
		Case When xmfs.M_ActionChanged <= @180DaysAgo Then 1 Else 0 End 
	Else 
		''
	End 'Broken > 180 Days Ago'

From Matter xm With (Nolock)
Inner Join MatterFileStatus xmfs With (Nolock)on xmfs.IDX = xm.M_IDX
Inner join FileStatus xfs with (nolock) on xfs.ID = xmfs.M_FileStatus
Inner join FileSubStatus xfss with (Nolock) on xmfs.M_FileSubStatus = xfss.ID
Inner join Department xdt with (Nolock) on xmfs.M_DepartmentID = xdt.ID
Inner Join BookListView xb with (Nolock) on xb.ID = xm.M_ClientID
Left Join Debtor xd With (Nolock) on xd.D_IDX = xm.M_IDX
Left Join (
Select H_IDX ,H_DateTime, H_HCRS_ID As AnsweredBy,
ROW_NUMBER() Over(Partition By H_IDX Order by H_IDX, H_DateTime Desc) rowNum
From History with (nolock)
Where H_HA_ID = 6 -- 6 = Call
And H_HCRS_ID = 1 -- 1 = Debtor Answered
) XPCNC On XPCNC.H_IDX = xm.M_IDX And XPCNC.rowNum = 1
Left Join ( -- UP TO HERE IT TAKES 6 SECONDS TO LOAD
Select PN_PL_TblID , PN_Number, PN_RPC_Date,
ROW_NUMBER() Over(Partition By PN_PL_TblID Order by PN_PL_TblID, PN_RPC_Date Desc) rowNum
From Phone with (nolock)
Where PN_RPC > 0 -- > 0 = RPC
) XMP On XMP.PN_PL_TblID = xm.M_IDX And XMP.rowNum = 1
Left Join ( -- ADDS 1 MIN TO SCRIPT
Select DB_IDX , DB_Date, DB_Credit, DB_Transaction_TypeID, ROW_NUMBER() Over(Partition By DB_IDX Order by DB_IDX, DB_Date Desc) rowNum
From Debtor_Balance with (nolock)
Where DB_Transaction_TypeID = 1 -- 1 = Receipt
) XDBR On XDBR.DB_IDX = xm.M_IDX And XDBR.rowNum = 1
Left Join ( -- UP TO HERE IT TAKES 1:25 MINUTES TO LOAD
Select H_IDX ,H_DateTime As BackFromTrace, H_SubSt_ID As TraceResult, ROW_NUMBER() Over(Partition By H_IDX Order by H_IDX, H_DateTime Desc) rowNum
From History with (nolock)
Where H_HA_ID = 11 -- 11 = Status Changed
And H_SubSt_ID In (50003,50004) -- 50003 - Trace Successful, 50004 - Trace Unsucessful
) XTR On XTR.H_IDX = xm.M_IDX And XTR.rowNum = 1

-- THE NEXT TWO JOINS SLOW THE SCRIPT DOWN VERY BADLY - IF I DON'T LOAD THE NEXT TWO JOINS IT TAKES 1:25 MINUTES TO LOAD.
-- HOW DO I FIX THIS?
-- I THOUGHT OF A SCRIPT WITHIN A SCRIPT MIGHT SPEED IT UP. I.E. A CURSOR OR MAYBE A VIEW AND THEN ADD THE BOTTOM TWO JOINS?


Left Join	(	-- ADDS 6:30 MINUTES TO SCRIPT; 8:03 MINUTES; 10:58 MINUTES; 7:43 MINUTES 
				Select DB_IDX, SUM(IsNull(DB_Debit,0)) - SUM(IsNull(DB_Credit,0)) As DebtorBalance			
				From Debtor_Balance with (nolock) 
				Group By DB_IDX
			) As xdb On xdb.DB_IDX = xm.M_IDX

Left Join	(	-- ADDS 3 HOURS TO SCRIPT
				Select H_IDX ,H_DateTime As LastPhoneCall, hcsr.HCSR_Desc As WhoAnswered, H_PhoneNumber As LastPhoneNo,  
						ROW_NUMBER() Over(Partition By H_IDX Order by H_IDX, H_DateTime Desc) rowNum
				From History with (nolock) 
					Left Join HistoryCallSelectionResult hcsr on hcsr.ID = H_HCRS_ID
				Where H_HA_ID = 6																		-- 6 = Call
			) XPC On XPC.H_IDX = xm.M_IDX And XPC.rowNum = 1

Where xb.Name Not Like '%TEST%' -- Test Matters -- EXCLUDE
And xb.Name Not Like '%ACME%' -- Test Matters -- EXCLUDE
And xb.Name Not Like '%Ithala%' -- Ithala Matters -- EXCLUDE
And xb.Name Not Like '%First National%' -- First National Matters -- EXCLUDE
And xm.ID Not Like '%TEST%' -- Test Matters -- EXCLUDE
And xm.ID Not Like '%ACME%' -- ACME Test Matters -- EXCLUDE
And xb.ID Not In (901305, 901499, 901507) -- Outsourced Matters -- EXCLUDE

And xmfs.M_FileStatus Not In (2,5,6,10,11,16,25,29,40,51,52,65,70,71,106,111,112,10000)
And xmfs.M_DepartmentID = 2			-- Call Centre
And Isnull(XPCNC.AnsweredBy,0) <> 1		-- 1 = Debtor Answered
And Isnull(XMP.PN_Number,'') = ''

ORDER BY xm.ID

ScottPletcher · January 6, 2023, 2:25pm

For the first LJ (left join), create an index:

Debtor_Balance ( DB_IDX ) INCLUDE ( DB_Debit, DB_Credit )

The second LJ will likely need an index too. How is History table clustered?

Ifor · January 6, 2023, 3:41pm

Try replacing the FROM clause with the following. It is an attempt to nudge the optimizer to concentrate it's limited time and resources on the tables with more rows.

FROM Matter xm /*WITH (NOLOCK)*/
INNER JOIN MatterFileStatus xmfs /*WITH (NOLOCK)*/ ON xmfs.IDX = xm.M_IDX
INNER JOIN FileStatus xfs /*WITH (NOLOCK)*/ ON COALESCE(xfs.ID, xfs.ID) = xmfs.M_FileStatus
INNER JOIN FileSubStatus xfss /*WITH (NOLOCK)*/ ON xmfs.M_FileSubStatus = COALESCE(xfss.ID, xfss.ID)
INNER JOIN Department xdt /*WITH (NOLOCK)*/ ON xmfs.M_DepartmentID = COALESCE(xdt.ID, xdt.ID)
INNER JOIN BookListView xb /*WITH (NOLOCK)*/ ON COALESCE(xb.ID, xb.ID) = xm.M_ClientID
LEFT JOIN Debtor xd /*WITH (NOLOCK)*/ ON xd.D_IDX = xm.M_IDX
LEFT JOIN (
	SELECT H_IDX
		,H_DateTime
		,H_HCRS_ID AS AnsweredBy
		,ROW_NUMBER() OVER (
			PARTITION BY H_IDX ORDER BY H_IDX
				,H_DateTime DESC
			) rowNum
	FROM History /*WITH (NOLOCK)*/
	WHERE H_HA_ID = 6 -- 6 = Call
		AND H_HCRS_ID = 1 -- 1 = Debtor Answered
	) XPCNC ON XPCNC.H_IDX = xm.M_IDX
	AND XPCNC.rowNum = 1
LEFT JOIN (
	-- UP TO HERE IT TAKES 6 SECONDS TO LOAD
	SELECT PN_PL_TblID
		,PN_Number
		,PN_RPC_Date
		,ROW_NUMBER() OVER (
			PARTITION BY PN_PL_TblID ORDER BY PN_PL_TblID
				,PN_RPC_Date DESC
			) rowNum
	FROM Phone /*WITH (NOLOCK)*/
	WHERE PN_RPC > 0 -- > 0 = RPC
	) XMP ON XMP.PN_PL_TblID = xm.M_IDX
	AND XMP.rowNum = 1
LEFT JOIN (
	-- ADDS 1 MIN TO SCRIPT
	SELECT DB_IDX
		,DB_Date
		,DB_Credit
		,DB_Transaction_TypeID
		,ROW_NUMBER() OVER (
			PARTITION BY DB_IDX ORDER BY DB_IDX
				,DB_Date DESC
			) rowNum
	FROM Debtor_Balance /*WITH (NOLOCK)*/
	WHERE DB_Transaction_TypeID = 1 -- 1 = Receipt
	) XDBR ON XDBR.DB_IDX = xm.M_IDX
	AND XDBR.rowNum = 1
LEFT JOIN (
	-- UP TO HERE IT TAKES 1:25 MINUTES TO LOAD
	SELECT H_IDX
		,H_DateTime AS BackFromTrace
		,H_SubSt_ID AS TraceResult
		,ROW_NUMBER() OVER (
			PARTITION BY H_IDX ORDER BY H_IDX
				,H_DateTime DESC
			) rowNum
	FROM History /*WITH (NOLOCK)*/
	WHERE H_HA_ID = 11 -- 11 = Status Changed
		AND H_SubSt_ID IN (
			50003
			,50004
			) -- 50003 - Trace Successful, 50004 - Trace Unsucessful
	) XTR ON XTR.H_IDX = xm.M_IDX
	AND XTR.rowNum = 1
-- THE NEXT TWO JOINS SLOW THE SCRIPT DOWN VERY BADLY - IF I DON'T LOAD THE NEXT TWO JOINS IT TAKES 1:25 MINUTES TO LOAD.
-- HOW DO I FIX THIS?
-- I THOUGHT OF A SCRIPT WITHIN A SCRIPT MIGHT SPEED IT UP. I.E. A CURSOR OR MAYBE A VIEW AND THEN ADD THE BOTTOM TWO JOINS?
LEFT JOIN (
	-- ADDS 6:30 MINUTES TO SCRIPT; 8:03 MINUTES; 10:58 MINUTES; 7:43 MINUTES 
	SELECT DB_IDX
		,SUM(IsNull(DB_Debit, 0)) - SUM(IsNull(DB_Credit, 0)) AS DebtorBalance
	FROM Debtor_Balance /*WITH (NOLOCK)*/
	GROUP BY DB_IDX
	) AS xdb ON xdb.DB_IDX = xm.M_IDX
LEFT JOIN (
	-- ADDS 3 HOURS TO SCRIPT
	SELECT H_IDX
		,H_DateTime AS LastPhoneCall
		,hcsr.HCSR_Desc AS WhoAnswered
		,H_PhoneNumber AS LastPhoneNo
		,ROW_NUMBER() OVER (
			PARTITION BY H_IDX ORDER BY H_IDX
				,H_DateTime DESC
			) rowNum
	FROM History /*WITH (NOLOCK)*/
	LEFT JOIN HistoryCallSelectionResult hcsr ON COALESCE(hcsr.ID, hcsr.ID) = H_HCRS_ID
	WHERE H_HA_ID = 6 -- 6 = Call
	) XPC ON XPC.H_IDX = xm.M_IDX
	AND XPC.rowNum = 1

EugeneRequilet · January 10, 2023, 8:36am

Ifor:

FROM Matter xm /*WITH (NOLOCK)*/
INNER JOIN MatterFileStatus xmfs /*WITH (NOLOCK)*/ ON xmfs.IDX = xm.M_IDX
INNER JOIN FileStatus xfs /*WITH (NOLOCK)*/ ON COALESCE(xfs.ID, xfs.ID) = xmfs.M_FileStatus
INNER JOIN FileSubStatus xfss /*WITH (NOLOCK)*/ ON xmfs.M_FileSubStatus = COALESCE(xfss.ID, xfss.ID)
INNER JOIN Department xdt /*WITH (NOLOCK)*/ ON xmfs.M_DepartmentID = COALESCE(xdt.ID, xdt.ID)
INNER JOIN BookListView xb /*WITH (NOLOCK)*/ ON COALESCE(xb.ID, xb.ID) = xm.M_ClientID
LEFT JOIN Debtor xd /*WITH (NOLOCK)*/ ON xd.D_IDX = xm.M_IDX
LEFT JOIN (
	SELECT H_IDX
		,H_DateTime
		,H_HCRS_ID AS AnsweredBy
		,ROW_NUMBER() OVER (
			PARTITION BY H_IDX ORDER BY H_IDX
				,H_DateTime DESC
			) rowNum
	FROM History /*WITH (NOLOCK)*/
	WHERE H_HA_ID = 6 -- 6 = Call
		AND H_HCRS_ID = 1 -- 1 = Debtor Answered
	) XPCNC ON XPCNC.H_IDX = xm.M_IDX
	AND XPCNC.rowNum = 1
LEFT JOIN (
	-- UP TO HERE IT TAKES 6 SECONDS TO LOAD
	SELECT PN_PL_TblID
		,PN_Number
		,PN_RPC_Date
		,ROW_NUMBER() OVER (
			PARTITION BY PN_PL_TblID ORDER BY PN_PL_TblID
				,PN_RPC_Date DESC
			) rowNum
	FROM Phone /*WITH (NOLOCK)*/
	WHERE PN_RPC > 0 -- > 0 = RPC
	) XMP ON XMP.PN_PL_TblID = xm.M_IDX
	AND XMP.rowNum = 1
LEFT JOIN (
	-- ADDS 1 MIN TO SCRIPT
	SELECT DB_IDX
		,DB_Date
		,DB_Credit
		,DB_Transaction_TypeID
		,ROW_NUMBER() OVER (
			PARTITION BY DB_IDX ORDER BY DB_IDX
				,DB_Date DESC
			) rowNum
	FROM Debtor_Balance /*WITH (NOLOCK)*/
	WHERE DB_Transaction_TypeID = 1 -- 1 = Receipt
	) XDBR ON XDBR.DB_IDX = xm.M_IDX
	AND XDBR.rowNum = 1
LEFT JOIN (
	-- UP TO HERE IT TAKES 1:25 MINUTES TO LOAD
	SELECT H_IDX
		,H_DateTime AS BackFromTrace
		,H_SubSt_ID AS TraceResult
		,ROW_NUMBER() OVER (
			PARTITION BY H_IDX ORDER BY H_IDX
				,H_DateTime DESC
			) rowNum
	FROM History /*WITH (NOLOCK)*/
	WHERE H_HA_ID = 11 -- 11 = Status Changed
		AND H_SubSt_ID IN (
			50003
			,50004
			) -- 50003 - Trace Successful, 50004 - Trace Unsucessful
	) XTR ON XTR.H_IDX = xm.M_IDX
	AND XTR.rowNum = 1
-- THE NEXT TWO JOINS SLOW THE SCRIPT DOWN VERY BADLY - IF I DON'T LOAD THE NEXT TWO JOINS IT TAKES 1:25 MINUTES TO LOAD.
-- HOW DO I FIX THIS?
-- I THOUGHT OF A SCRIPT WITHIN A SCRIPT MIGHT SPEED IT UP. I.E. A CURSOR OR MAYBE A VIEW AND THEN ADD THE BOTTOM TWO JOINS?
LEFT JOIN (
	-- ADDS 6:30 MINUTES TO SCRIPT; 8:03 MINUTES; 10:58 MINUTES; 7:43 MINUTES 
	SELECT DB_IDX
		,SUM(IsNull(DB_Debit, 0)) - SUM(IsNull(DB_Credit, 0)) AS DebtorBalance
	FROM Debtor_Balance /*WITH (NOLOCK)*/
	GROUP BY DB_IDX
	) AS xdb ON xdb.DB_IDX = xm.M_IDX
LEFT JOIN (
	-- ADDS 3 HOURS TO SCRIPT
	SELECT H_IDX
		,H_DateTime AS LastPhoneCall
		,hcsr.HCSR_Desc AS WhoAnswered
		,H_PhoneNumber AS LastPhoneNo
		,ROW_NUMBER() OVER (
			PARTITION BY H_IDX ORDER BY H_IDX
				,H_DateTime DESC
			) rowNum
	FROM History /*WITH (NOLOCK)*/
	LEFT JOIN HistoryCallSelectionResult hcsr ON COALESCE(hcsr.ID, hcsr.ID) = H_HCRS_ID
	WHERE H_HA_ID = 6 -- 6 = Call
	) XPC ON XPC.H_IDX = xm.M_IDX
	AND XPC.rowNum = 1

Hi Ifor,

I made these changes but it still took a very long time to run.

However by mistake I left out the Where clause at the bottom and it took 2 minutes to run.
When I added the where clause back I stopped it at 10 minutes.

Why would that be the case?
I am fiddling with the where clauses now.

EugeneRequilet · January 10, 2023, 10:34am

Hi There,

I have discovered that it is not the join that is slow it is in the "Where" clause.

These two lines is what is making the seach slow for some reason:

And Isnull(XPCNC.AnsweredBy,0) <> 1		-- 1 = Debtor Answered
And Isnull(XMP.PN_Number,'') = ''

EugeneRequilet · January 10, 2023, 12:24pm

Hi Guys,

I fixed the script by changing one script into an Inner Join from a Left Join.

	Inner Join	(
					Select PN_PL_TblID , PN_Number, PN_RPC_Date, 
							ROW_NUMBER() Over(Partition By PN_PL_TblID Order by PN_PL_TblID, PN_RPC_Date Desc) rowNum
					From ExcaliburV4_WH.dbo.Phone with (nolock) 
					Where PN_RPC > 0																		-- > 0 = RPC			
				) XMP On XMP.PN_PL_TblID = xm.M_IDX And XMP.rowNum = 1

And leaving out the the following:
And Isnull(XMP.PN_Number,'') = ''

Thanks for all your help.

harishgg1 · January 10, 2023, 4:19pm

There are numerous things you can do

One idea

is to the problem joins individually

EugeneRequilet · January 18, 2023, 10:42am

Hi There,

This helped a lot.

I now know how to use temp tables in my scripts and it works well.

Thank you.