CODEWITHSUNDEEP
sql-servert-sqlmerge
user3282300
user3282300

Reputation: 3

SQL Server 2014-Run SQL merge statement in loop/batches for 80M rows

Need some help in query optimization for below merge statement. This query takes max time during execution of our process because of number of rows and merge does operation in one shot. I would like to modify it little bit so I can run it in loop or batch process.

    MERGE Target_DB.[dbo].[target_tbl] A
    USING   Source_DB].[DBO].[source_tbl] B
    INNER JOIN (SELECT DISTINCT [IndividualEligibility_IDNO],[Case_NUMB],[MCI_NUMB],[Program_CODE],[Subprogram_CODE],[AGSequence_NUMB],[CategorySequence_NUMB] 
FROM staging_tbl  
where [IndividualEligibility_IDNO] is not null) C
ON C.[Case_NUMB] = B.[CASE_NUM] AND B.[PROGRAM_CD]= C.[Program_CODE] AND B.[SUBPROGRAM_CD]=  C.[Subprogram_CODE] AND B.[AG_SEQ_NUM]= C.[AGSequence_NUMB]AND C.[CategorySequence_NUMB]= B.[CAG_ELIG_SEQ_NUM]AND C.[MCI_NUMB] = B.[MCI_NUM] 
ON (A.[IndividualEligibility_IDNO] = C.[IndividualEligibility_IDNO])
WHEN MATCHED THEN  
UPDATE
SET 
WHEN NOT MATCHED THEN
INSERT
(
)
VALUES
(
);

Upvotes: 0

Views: 4459

Answers (1)

Steve Mangiameli
Steve Mangiameli

Reputation: 688

Batching DML will require some type of key or range that can help you with a known batch size - Date, Id, etc. With that you can add a filter to your merge so it only works with a certain number of records. Loop that until you've managed all the records and you are good to go.

Pseudo code assuming an identity Key; assuming Case_NUMB from the query above. Obviously don't know your data, but this will give you an idea.

   DECLARE 
      @batchSize=10000
    , @curBatch=0
    , @lastBatch=0
    , @maxRow

   SELECT @maxRow=MAX([Case_NUMB])
   FROM [Source_DB].[DBO].[T0826_AG_IN_GRS_I]


   SET @curBatch+=@batchSize

   WHILE @curBatch < @maxRow
   BEGIN

    MERGE Target_DB.[dbo].[GrossIncomeIndividualEligibility_T] A
       USING   [Source_DB].[DBO].[T0826_AG_IN_GRS_I] B
        INNER JOIN (
            SELECT DISTINCT [IndividualEligibility_IDNO],[Case_NUMB],[MCI_NUMB],[Program_CODE],[Subprogram_CODE],[AGSequence_NUMB],[CategorySequence_NUMB] 
            FROM Staging_DB.dbo.STG_CaseNUM_ID_Holder  
            where [IndividualEligibility_IDNO] is not null
                AND [Case_NUMB] BETWEEN @lastBatch AND @curBatch -->ADD THIS LINE
        ) C ON C.[Case_NUMB] = B.[CASE_NUM] 
            AND B.[PROGRAM_CD]= C.[Program_CODE] 
            AND B.[SUBPROGRAM_CD]=  C.[Subprogram_CODE] 
            AND B.[AG_SEQ_NUM]= C.[AGSequence_NUMB]
            AND C.[CategorySequence_NUMB]= B.[CAG_ELIG_SEQ_NUM]
            AND C.[MCI_NUMB] = B.[MCI_NUM] 
    ON (A.[IndividualEligibility_IDNO] = C.[IndividualEligibility_IDNO])
    WHEN MATCHED THEN  
    UPDATE
        SET 
    WHEN NOT MATCHED THEN
        INSERT
        (
        )
        VALUES
        (
        );

    SET @lastBatch=@curBatch+1              
    SET @curBatch+=@batchSize
   END

Upvotes: 1

Related Questions