Return to Question

CREATE PROCEDURE [dbo].[SingleCandidateView_IdentifyCandidateChanges]  
(  
@MoreToProcess BIT = 0 OUTPUT  
)  
AS  

SET NOCOUNT ON  

DECLARE @BatchSize INT  

SELECT  
@MoreToProcess = 0  
, @BatchSize = 200000  

DECLARE @Changes TABLE  
(  
RSACandidateNo VARCHAR(10) NOT NULL  
, CentreNo CHAR(5) NOT NULL  
, CandidateModifiedDate DATETIME NOT NULL  
, ChangeStatus CHAR(1) NOT NULL  
)  

-- If this is the first run (or the CandidateSync table is empty for another reason) then just take the hit now  
-- and process the entire set of candidates in one go.  

IF EXISTS(SELECT TOP 1 1 FROM CandidateSync)  
SET ROWCOUNT @BatchSize  

INSERT INTO @Changes  
(  
RSACandidateNo  
, CentreNo  
, CandidateModifiedDate  
, ChangeStatus  
)  
SELECT  
COALESCE(RTRIM(Known.RSACandidateNo), CandidateSync.RSACandidateNo) AS RSACandidateNo  
, COALESCE(Known.CentreNo, CandidateSync.CentreNo) AS CentreNo  
, COALESCE(Candidate.ModifiedDate, CandidateSync.CandidateModifiedDate) AS CandidateModifiedDate  
, CASE   
  WHEN CandidateSync.RSACandidateNo IS NULL THEN   
   'I'   
  WHEN Known.RSACandidateNo IS NULL THEN   
   'D'   
  ELSE   
   'U'   
END AS ChangeStatus  
FROM  
Candidate WITH(INDEX(IX_Candidate))  
INNER MERGE JOIN  
(  
SELECT  
  Registration.RSACandidateNo  
, Registration.CentreNo  
FROM  
  Registration WITH(INDEX(IX_Registration))  
UNION SELECT  
  AssessHeaderValid.CandidateNo  
, AssessHeaderValid.CentreNo  
FROM  
  AssessHeaderValid WITH(INDEX(IX_AssessHeaderValid))  
WHERE  
  AssessHeaderValid.Deleted = 0  
UNION SELECT  
  CandAnsHeaderValid.CandidateNo  
, CandAnsHeaderValid.CentreNo  
FROM  
  CandAnsHeaderValid WITH(INDEX(IX_CandAnsHeaderValid))  
) AS Known  
ON  
Known.RSACandidateNo = Candidate.RSACandidateNo  
FULL OUTER MERGE JOIN  
CandidateSync WITH(INDEX(PK_CandidateSync))  
ON  
CandidateSync.RSACandidateNo = Known.RSACandidateNo  
AND CandidateSync.CentreNo = Known.CentreNo  
WHERE  
CandidateSync.RSACandidateNo IS NULL  
OR Known.RSACandidateNo IS NULL  
OR CandidateSync.CandidateModifiedDate < Candidate.ModifiedDate  
OR CandidateSync.ChangeStatus = 'D' -- Always include changes to pending deletions so that the scenario where a record has been resurrected is handled correctly.  
OPTION  
(  
MERGE UNION  
)  

IF @@ROWCOUNT = @BatchSize  
SET @MoreToProcess = 1  

SET ROWCOUNT 0  

INSERT INTO CandidateSync WITH (PAGLOCK)  
(  
RSACandidateNo  
, CentreNo  
, CandidateModifiedDate  
, ChangeStatus  
)  
SELECT  
Changes.RSACandidateNo  
, Changes.CentreNo  
, Changes.CandidateModifiedDate  
, CASE Changes.ChangeStatus WHEN 'D' THEN 'D' ELSE 'U' END  
FROM  
@Changes Changes  
WHERE  
Changes.ChangeStatus = 'I'  

UPDATE  
CandidateSync  
WITH  
(ROWLOCK)  
SET  
CandidateModifiedDate = Changes.CandidateModifiedDate  
, ChangeStatus = CASE Changes.ChangeStatus WHEN 'D' THEN 'D' ELSE 'U' END  
, ProcessingStatus = 'U'  
FROM  
@Changes Changes  
WHERE  
CandidateSync.RSACandidateNo = Changes.RSACandidateNo  
AND CandidateSync.CentreNo = Changes.CentreNo  
AND CandidateSync.ProcessingStatus = 'S'  
AND Changes.ChangeStatus <> 'I'  

The approximate count of rows in the tables are as:

The actual execution plan:

PasteThePlan

The tables Candidate, Registration, CandAnsHeaderValid and AssessHeaderValid are updated twice daily by an SSIS job run, which need to be synced to the CandidateSync table, as it keeps record of the records which have been already processed.

The issue is that this query is taking almost a minute to complete and is completing massive table scans with sorts spilling to tempdb and millions of rows being pumped into table variables. Is there any way I can reduce the load on the server?

CREATE PROCEDURE [dbo].[IdentifyCandidateChanges]  
(  
@MoreToProcess BIT = 0 OUTPUT  
)  
AS  

SET NOCOUNT ON  

DECLARE @BatchSize INT  

SELECT  
@MoreToProcess = 0  
, @BatchSize = 200000  

DECLARE @Changes TABLE  
(  
  CandidateNo VARCHAR(10) NOT NULL  
, CentreNo CHAR(5) NOT NULL  
, CandidateModifiedDate DATETIME NOT NULL  
, ChangeStatus CHAR(1) NOT NULL  
)  

-- If this is the first run (or the CandidateSync table is empty for another reason) then just take the hit now  
-- and process the entire set of candidates in one go.  

IF EXISTS(SELECT TOP 1 1 FROM CandidateSync)  
SET ROWCOUNT @BatchSize  

INSERT INTO @Changes  
(  
  CandidateNo  
, CentreNo  
, CandidateModifiedDate  
, ChangeStatus  
)  
SELECT  
COALESCE(RTRIM(Known.CandidateNo), CandidateSync.CandidateNo) AS CandidateNo  
, COALESCE(Known.CentreNo, CandidateSync.CentreNo) AS CentreNo  
, COALESCE(Candidate.ModifiedDate, CandidateSync.CandidateModifiedDate) AS CandidateModifiedDate  
, CASE   
  WHEN CandidateSync.CandidateNo IS NULL THEN   
   'I'   
  WHEN Known.CandidateNo IS NULL THEN   
   'D'   
  ELSE   
   'U'   
END AS ChangeStatus  
FROM  
Candidate WITH(INDEX(IX_Candidate))  
INNER MERGE JOIN  
(  
SELECT  
  Registration.CandidateNo  
, Registration.CentreNo  
FROM  
  Registration WITH(INDEX(IX_Registration))  
UNION SELECT  
  AssessHeaderValid.CandidateNo  
, AssessHeaderValid.CentreNo  
FROM  
  AssessHeaderValid WITH(INDEX(IX_AssessHeaderValid))  
WHERE  
  AssessHeaderValid.Deleted = 0  
UNION SELECT  
  CandAnsHeaderValid.CandidateNo  
, CandAnsHeaderValid.CentreNo  
FROM  
  CandAnsHeaderValid WITH(INDEX(IX_CandAnsHeaderValid))  
) AS Known  
ON  
Known.CandidateNo = Candidate.CandidateNo  
FULL OUTER MERGE JOIN  
CandidateSync WITH(INDEX(PK_CandidateSync))  
ON  
CandidateSync.CandidateNo = Known.CandidateNo  
AND CandidateSync.CentreNo = Known.CentreNo  
WHERE  
CandidateSync.CandidateNo IS NULL  
OR Known.CandidateNo IS NULL  
OR CandidateSync.CandidateModifiedDate < Candidate.ModifiedDate  
OR CandidateSync.ChangeStatus = 'D'  
OPTION  
(  
MERGE UNION  
)  

IF @@ROWCOUNT = @BatchSize  
SET @MoreToProcess = 1  

SET ROWCOUNT 0  

INSERT INTO CandidateSync WITH (PAGLOCK)  
(  
CandidateNo  
, CentreNo  
, CandidateModifiedDate  
, ChangeStatus  
)  
SELECT  
Changes.CandidateNo  
, Changes.CentreNo  
, Changes.CandidateModifiedDate  
, CASE Changes.ChangeStatus WHEN 'D' THEN 'D' ELSE 'U' END  
FROM  
@Changes Changes  
WHERE  
Changes.ChangeStatus = 'I'  

UPDATE  
CandidateSync  
WITH  
(ROWLOCK)  
SET  
CandidateModifiedDate = Changes.CandidateModifiedDate  
, ChangeStatus = CASE Changes.ChangeStatus WHEN 'D' THEN 'D' ELSE 'U' END  
, ProcessingStatus = 'U'  
FROM  
@Changes Changes  
WHERE  
CandidateSync.CandidateNo = Changes.CandidateNo  
AND CandidateSync.CentreNo = Changes.CentreNo  
AND CandidateSync.ProcessingStatus = 'S'  
AND Changes.ChangeStatus <> 'I'  

The tables Candidate, Registration, CandAnsHeaderValid and AssessHeaderValid are updated twice daily by an SSIS job run, which need to be synced to the CandidateSync table, as it keeps record of the records which have been already processed.

The issue is that this query is taking almost a minute to complete. Is there any way I can reduce the load on the server?