2

I have a stored procedure which identifies any changes in candidate information from the last SSIS job run. Then it proceeds to save the changes in a sync table.

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 Indexes are setup as:

Name Type Keys
IX_Candidate    nonclustered located on PRIMARY     CandidateNo, ModifiedDate
IX_Registration     nonclustered located on PRIMARY     CandidateNo, CentreNo
IX_AssessHeaderValid     nonclustered located on PRIMARY     CandidateNo, CentreNo, Deleted
IX_CandAnsHeaderValid     nonclustered located on PRIMARY     CandidateNo, CentreNo
PK_CandidateSync     clustered, unique, primary key located on PRIMARY     CandidateNo, CentreNo

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?

0

2 Answers 2

6

problem 1

There are a few things going on here, but the main reason you have all table scans is that aside from AssessHeaderValid.Deleted = 0, you don't have a single where clause that's capable of being assessed when data is first read.

The where clause at the end is comprised of a series of OR predicates evaluating the results of the FULL OUTER MERGE JOIN between CandidateSync and Candidate.

WHERE  
   CandidateSync.RSACandidateNo IS NULL  
OR Known.RSACandidateNo IS NULL  
OR CandidateSync.CandidateModifiedDate < Candidate.ModifiedDate  
OR CandidateSync.ChangeStatus = 'D' 

Which yields a plan that looks like this:

NUTS

Note the details of the Filter operator. Since this is an outer join, the results have to be fully manifested before rows can be eliminated.

problem 2

The second problem is that you're using a @table variable, which means you can't get a parallel execution plan. SQL Server does not support parallel modifications to @table variables.

#temp tables do not have this restriction. Getting a parallel plan here would be useful so that you can spread the high-ish number of rows out across additional workers.

problem 3

You've hinted this thing to death. It's not obvious why you prefer merge operations over hash operations, but a parallel plan that's capable of exploring hash operations would likely be better for performance in this case. I only say that because merge operations in parallel plans can cause real performance headaches.

fixes

In order of easy to do, here's what I'd change and test:

  1. Use a #temp table instead of a @table variable
  2. Remove all the index and join hints
  3. Use EXISTS/NOT EXISTS instead of a join with several OR predicates
  4. If these don't resolve everything, separate the Known derived join into a #temp table

See my article here regarding the third item:

0
-2

I feel a lot of Oracle in your past... That is not how we do stored procedures in SQL Server.

First of all, split your queries into temp tables.

Remove all with(index) from your query - they more often hinder performance. Server will automatically choose appropriate index. You even have with(index) for a full-table queries (look for example on the first select of the subselect for Known table). What purpose this index supposed to have???

Do not include SET NOCOUNT ON, the numbers it will show are actually helpful - without this flag, you will always see how many rows are going into tables or updated.

Another problem is that you first doing a join of Candidate to Known on Known.RSACandidateNo = Candidate.RSACandidateNo, but later you filter all these records out in where clause by Known.RSACandidateNo IS NULL. What is the point of the join then??? You making server do unneeded work. Use NOT IN (subselect) instead of join.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.