The Potential Problem
At a quick glance at the execution plan and what the code is doing, it looks like one of the main issues is your JOIN clause to [maxims_prod].[dbo].core_patient_c_identifi:
LEFT JOIN [maxims_prod].[dbo].core_patient_c_identifi alt
ON cp.id = alt.id
AND alt.lkp_c_ty = CASE @SourceSystem WHEN 'Rio' THEN 6942
WHEN 'Trakcare' THEN 6999
END
Specifically the CASE statement, it overcomplicates the predicate, which appears to result in multiple index scans against a larger (+1 million rows) table.
One Possible Fix
One potential way to fix that, is by branching the code on the @SourceSystem variable, since this is a stored procedure anyway. Prior to your main query that joins in [maxims_prod].[dbo].core_patient_c_identifi, you can create the following branch to materialize the data to a temp table:
-- Don't use SELECT *, list out the columns explicitly instead. I'm only doing it because I don't have your table schema to reference
DROP TABLE IF EXISTS #core_patient_c_identifi;
SELECT TOP 0 *
INTO #core_patient_c_identifi
FROM [maxims_prod].[dbo].core_patient_c_identifi;
IF (@SourceSystem = `Rio`)
BEGIN
INSERT INTO #core_patient_c_identifi
SELECT *
FROM [maxims_prod].[dbo].core_patient_c_identifi
WHERE alt.lkp_c_ty = 6942
END
ELSE IF (@SourceSystem = `Trakcare`)
BEGIN
INSERT INTO #core_patient_c_identifi
SELECT *
FROM [maxims_prod].[dbo].core_patient_c_identifi
WHERE alt.lkp_c_ty = 6999
END
Then your JOIN clause in your main query just needs to be:
LEFT JOIN #core_patient_c_identifi alt
ON cp.id = alt.id
Another Dangerous Predicate
At the very end of your query you have the following predicate:
WHERE AlternativeLocalPtId = '' -- return all rows if no 3a match is found
OR AlternativeLocalPtId = Matched_LocalPtId
This is potentially problematic for two reasons. The first being ORs can also jam up predicates from being optimally seeked on. Although this one is not overly complicated, so is probably of less concern.
The bigger issue is the AlternativeLocalPtId column is an alias in you previous subquery for an inline correlated subquery with an XML parsing expression. This makes the aforementioned predicate more complex than the words I just used to describe it:
(
(SELECT
CASE WHEN Match_type LIKE '%3a%'
THEN Matched_LocalPtId END [text()]
FROM #MATCHED_RESULT MATCHTYPE2
WHERE MATCHTYPE.MessageControlID = MATCHTYPE2.MessageControlID
ORDER BY Match_type
FOR XML PATH (''),TYPE).value('.','NVARCHAR(200)')
) AlternativeLocalPtId
Possible Fix
Materialization is likely your friend again here. You can extract only the pertinent parts of the query (might be the whole main query itself) to build the expression for AlternativeLocalPtId, and insert it into a temp table. Then filter on the temp table by the materialized column AlternativeLocalPtId.
I tried to write out a code example of that solution but the query is too verbose for me to peel back and modify on here (I write my answers from my phone). But you should get the idea at this point.
