SQL Server лучший способ перебирать миллионы строк

Я считаю, что следующий запрос поможет вам начать, и это очень эффективный способ приблизиться к вашей цели.

Он был создан для хранения исторической информации о серверах SQL в центральном расположении и выполняет следующие действия: вы должны включить/заменить структуры таблиц в соответствующих блоках скрипта.

1. Создает temp таблицу
2. Собирает информацию с нескольких серверов, используя OPENQUERY через Lined Servers (источник), и загружает в Temp таблицу.
3. Создает индексы для Temp таблиц
4. Загружает данные в центральную таблицу (назначение) с 3 сценариями (как указано в сценарии)

Примечание. Сценарий заменен в соответствии с вашим сценарием

BEGIN
    Create Table #SrcTemp
                    (   AENAM nvarchar(12),
                        AUTYP nvarchar(2),
                        AWART nvarchar(4),
                        BELNR nvarchar(10),
                        CATSHOURS decimal(7, 3),
                        CATSQUANTITY decimal(18, 3),
                        CHARGE_HOLD nvarchar(24),
                        [COUNTER] nvarchar(12),
                        ERNAM nvarchar(12),
                        ERSDA nvarchar(8),
                        ERSTM nvarchar(6),
                        HRCOSTASG nvarchar(1),
                        LAEDA nvarchar(8),
                        LSTAR nvarchar(6),
                        LTXA1 nvarchar(40),
                        MANDT nvarchar(3),
                        PERNR nvarchar(8),
                        RAPLZL nvarchar(8),
                        RAUFPL nvarchar(10),
                        REFCOUNTER nvarchar(12),
                        RNPLNR nvarchar(12),
                        SKOSTL nvarchar(10),
                        CATS_STATUS nvarchar(2),
                        SUPP3 nvarchar(10),
                        WORKDATE nvarchar(8),
                        ZZOH_ORDER nvarchar(24),
                        APDAT nvarchar(8),
                        APNAM nvarchar(12),
                        LAETM nvarchar(6),
                        APPR_STATUS nvarchar(2)
                    );

--    DECLARE @orig_counter nvarchar(12)
END
    UPDATE #SrcTemp SET REFCOUNTER = '0' WHERE  REFCOUNTER = '' or REFCOUNTER is null;
    CREATE Clustered Index CLU_SrvTemp on #SrcTemp ([COUNTER], REFCOUNTER);

BEGIN

        INSERT INTO #SrcTemp
        SELECT 
                AENAM,AUTYP,AWART,BELNR,CATSHOURS,CATSQUANTITY,CHARGE_HOLD,[COUNTER],ERNAM,ERSDA,ERSTM,HRCOSTASG,LAEDA,LSTAR,LTXA1,MANDT,
                PERNR,RAPLZL,RAUFPL,REFCOUNTER,RNPLNR,SKOSTL,CATS_STATUS,SUPP3,WORKDATE,ZZOH_ORDER,APDAT,APNAM,LAETM,APPR_STATUS
        FROM    
                CATSDB;
END

--BEGIN
--    OPEN curs
--END

-- Scope: UNCHANGED Records ==================================================================================================================================

    IF EXISTS 
        (select *
         from   (
                    SELECT ROW_NUMBER () OVER (PARTITION BY [COUNTER] ORDER BY COUNTER) AS RN
                    FROM    #SrcTemp
                    WHERE REFCOUNTER = '0'
                ) as t where t.RN > 1
         )
        BEGIN
            RAISERROR ('Primary key violation occurred in "UNCHANGED" records processing block', 16, 1) with NOWAIT;
        END
    ELSE 

    BEGIN
    -- When NON-CHANGED Records NOT Existed in SQL table -------------------------------------------
            BEGIN
                INSERT INTO TEMP  ([AENAM],[AUTYP],[AWART],[BELNR],[CATSHOURS],[CATSQUANTITY],[CHARGE_HOLD],[COUNTER],[ERNAM]
                                    ,[ERSDA],[ERSTM],[HRCOSTASG],[LAEDA],[LSTAR],[LTXA1],[MANDT],[PERNR],[RAPLZL],[RAUFPL]
                                    ,[REFCOUNTER],[RNPLNR],[SKOSTL],[CATS_STATUS],[SUPP3],[WORKDATE],[ZZOH_ORDER],[APDAT],[APNAM]
                                    ,[LAETM],[APPR_STATUS]
                                    )
                SELECT    s.[AENAM], s.[AUTYP], s.[AWART], s.[BELNR], s.[CATSHOURS], s.[CATSQUANTITY], s.[CHARGE_HOLD], s.[COUNTER], s.[ERNAM]
                        , s.[ERSDA], s.[ERSTM], s.[HRCOSTASG], s.[LAEDA], s.[LSTAR], s.[LTXA1], s.[MANDT], s.[PERNR], s.[RAPLZL], s.[RAUFPL]
                        , s.[REFCOUNTER], s.[RNPLNR], s.[SKOSTL], s.[CATS_STATUS], s.[SUPP3], s.[WORKDATE], s.[ZZOH_ORDER], s.[APDAT], s.[APNAM]
                        , s.[LAETM], s.[APPR_STATUS]
                FROM    #SrcTemp as S
                        LEFT JOIN
                            TEMP as D on s.COUNTER = d.COUNTER
                WHERE (S.REFCOUNTER = '0') and D.COUNTER is null ;
            END

        -- When NON-CHANGED Records Existed in SQL table -------------------------------------------
            BEGIN
                UPDATE S
                    SET [AENAM] = D.AENAM
                        ,[AUTYP] = D.AUTYP
                        ,[AWART] = D.AWART
                        ,[BELNR] = D.BELNR
                        ,[CATSHOURS] = D.CATSHOURS
                        ,[CATSQUANTITY] = D.CATSQUANTITY
                        ,[CHARGE_HOLD] = D.CHARGE_HOLD
                        ,[ERNAM] = D.ERNAM
                        ,[ERSDA] = D.ERSDA
                        ,[ERSTM] = D.ERSTM
                        ,[HRCOSTASG] = D.HRCOSTASG
                        ,[LAEDA] = D.LAEDA
                        ,[LSTAR] = D.LSTAR
                        ,[LTXA1] = D.LTXA1
                        ,[MANDT] = D.MANDT
                        ,[PERNR] = D.PERNR
                        ,[RAPLZL] = D.RAPLZL
                        ,[RAUFPL] = D.RAUFPL
                        ,[REFCOUNTER] = D.REFCOUNTER
                        ,[RNPLNR] = D.RNPLNR
                        ,[SKOSTL] = D.SKOSTL
                        ,[CATS_STATUS] = D.CATS_STATUS
                        ,[SUPP3] = D.SUPP3
                        ,[WORKDATE] = D.WORKDATE
                        ,[ZZOH_ORDER] = D.ZZOH_ORDER
                        ,[APDAT] = D.APDAT
                        ,[APNAM] = D.APNAM
                        ,[LAETM] = D.LAETM
                        ,[APPR_STATUS] = D.APPR_STATUS
                FROM    #SrcTemp as S
                        LEFT JOIN
                            TEMP as D on    (s.COUNTER = d.COUNTER and S.REFCOUNTER = D.REFCOUNTER)
                WHERE (S.REFCOUNTER = '0') and D.COUNTER is NOT null 
            END
    END

-- Scope: CHANGED Records ==================================================================================================================================

    IF EXISTS 
        (select *
         from   (
                    SELECT ROW_NUMBER () OVER (PARTITION BY [COUNTER], REFCOUNTER ORDER BY [COUNTER]) AS RN
                    FROM    #SrcTemp
                    WHERE not REFCOUNTER = '0' 
                ) as t where t.RN > 1
         )
        BEGIN
            RAISERROR ('Primary key violation occurred in "CHANGED" records processing block', 10, 1) with NOWAIT;
        END
    ELSE

    BEGIN
        -- When CHANGED Records NOT Existed in SQL table -------------------------------------------
            BEGIN
                INSERT INTO TEMP  ([AENAM],[AUTYP],[AWART],[BELNR],[CATSHOURS],[CATSQUANTITY],[CHARGE_HOLD],[COUNTER],[ERNAM]
                                    ,[ERSDA],[ERSTM],[HRCOSTASG],[LAEDA],[LSTAR],[LTXA1],[MANDT],[PERNR],[RAPLZL],[RAUFPL]
                                    ,[REFCOUNTER],[RNPLNR],[SKOSTL],[CATS_STATUS],[SUPP3],[WORKDATE],[ZZOH_ORDER],[APDAT],[APNAM]
                                    ,[LAETM],[APPR_STATUS]
                                    )
                SELECT    s.[AENAM], s.[AUTYP], s.[AWART], s.[BELNR], s.[CATSHOURS], s.[CATSQUANTITY], s.[CHARGE_HOLD], s.[COUNTER], s.[ERNAM]
                        , s.[ERSDA], s.[ERSTM], s.[HRCOSTASG], s.[LAEDA], s.[LSTAR], s.[LTXA1], s.[MANDT], s.[PERNR], s.[RAPLZL], s.[RAUFPL]
                        , s.[REFCOUNTER], s.[RNPLNR], s.[SKOSTL], s.[CATS_STATUS], s.[SUPP3], s.[WORKDATE], s.[ZZOH_ORDER], s.[APDAT], s.[APNAM]
                        , s.[LAETM], s.[APPR_STATUS]
                FROM    #SrcTemp as S
                        LEFT JOIN
                            TEMP as D on s.COUNTER = d.COUNTER and S.REFCOUNTER = D.REFCOUNTER
                WHERE (not S.REFCOUNTER = '0') and D.COUNTER is null 
            END

        -- When NON-CHANGED Records Existed in SQL table -------------------------------------------
            BEGIN
                UPDATE S
                    SET [AENAM] = D.AENAM
                        ,[AUTYP] = D.AUTYP
                        ,[AWART] = D.AWART
                        ,[BELNR] = D.BELNR
                        ,[CATSHOURS] = D.CATSHOURS
                        ,[CATSQUANTITY] = D.CATSQUANTITY
                        ,[CHARGE_HOLD] = D.CHARGE_HOLD
                        ,[ERNAM] = D.ERNAM
                        ,[ERSDA] = D.ERSDA
                        ,[ERSTM] = D.ERSTM
                        ,[HRCOSTASG] = D.HRCOSTASG
                        ,[LAEDA] = D.LAEDA
                        ,[LSTAR] = D.LSTAR
                        ,[LTXA1] = D.LTXA1
                        ,[MANDT] = D.MANDT
                        ,[PERNR] = D.PERNR
                        ,[RAPLZL] = D.RAPLZL
                        ,[RAUFPL] = D.RAUFPL
                        ,[REFCOUNTER] = D.REFCOUNTER
                        ,[RNPLNR] = D.RNPLNR
                        ,[SKOSTL] = D.SKOSTL
                        ,[CATS_STATUS] = D.CATS_STATUS
                        ,[SUPP3] = D.SUPP3
                        ,[WORKDATE] = D.WORKDATE
                        ,[ZZOH_ORDER] = D.ZZOH_ORDER
                        ,[APDAT] = D.APDAT
                        ,[APNAM] = D.APNAM
                        ,[LAETM] = D.LAETM
                        ,[APPR_STATUS] = D.APPR_STATUS
                FROM    #SrcTemp as S
                        LEFT JOIN
                        TEMP as D on s.COUNTER = d.COUNTER and S.REFCOUNTER = D.REFCOUNTER
                WHERE (not S.REFCOUNTER = '0' ) and D.COUNTER is NOT null 
            END
    END

Drop table #SrcTemp;

Похоже, это можно сделать с помощью простого рекурсивного запроса. Наличие подходящего индекса также важно.

Пример данных

Вот как ваши образцы данных должны выглядеть в вопросе. Только несколько соответствующих столбцов. Лучше включить несколько наборов/цепочек изменений, а не один. Имея только эти образцы данных, вам будет сложнее проверить правильность представленных решений.

+-----------+---------------------+-----------+------------+
|   BELNR   |     CHARGE_HOLD     |  COUNTER  | REFCOUNTER |
+-----------+---------------------+-----------+------------+
| 417548605 | T4-GS023ABC2 0150#* | 420202428 | NULL       |
| 417549506 | T4-GS023-ABC2       | 420203329 | 420202428  |
| 417553156 | JGS023001    0010#* | 420206979 | 420203329  |
| 417557221 | T4-GS023-ABC2       | 420211044 | 420206979  |
| 417581675 | JGS023001    0010#* | 420235498 | 420211044  |
| 417677969 | JGS023001    0010#* | 420331792 | 420235498  |
+-----------+---------------------+-----------+------------+

Основная рекурсивная часть запроса

WITH
CTE
AS
(
    SELECT
        1 AS Lvl,
        CATSDB.BELNR AS OriginalBELNR,
        CATSDB.CHARGE_HOLD AS OriginalCHARGE_HOLD,
        CATSDB.[COUNTER] AS OriginalCOUNTER,
        CATSDB.REFCOUNTER AS OrginalREFCOUNTER,
        CATSDB.BELNR AS NewBELNR,
        CATSDB.CHARGE_HOLD AS NewCHARGE_HOLD,
        CATSDB.[COUNTER] AS NewCOUNTER,
        CATSDB.REFCOUNTER AS NewREFCOUNTER
    FROM
        CATSDB
    WHERE
        REFCOUNTER IS NULL

    UNION ALL

    SELECT
        CTE.Lvl + 1 AS Lvl,
        CTE.OriginalBELNR,
        CTE.OriginalCHARGE_HOLD,
        CTE.OriginalCOUNTER,
        CTE.OrginalREFCOUNTER,
        CATSDB.BELNR AS NewBELNR,
        CATSDB.CHARGE_HOLD AS NewCHARGE_HOLD,
        CATSDB.[COUNTER] AS NewCOUNTER,
        CATSDB.REFCOUNTER AS NewREFCOUNTER
    FROM
        CATSDB
        INNER JOIN CTE ON CATSDB.REFCOUNTER = CTE.NewCOUNTER
)
SELECT * FROM CTE;

Промежуточный результат

+-----+---------------+---------------------+-----------------+-------------------+-----------+---------------------+------------+---------------+
| Lvl | OriginalBELNR | OriginalCHARGE_HOLD | OriginalCOUNTER | OrginalREFCOUNTER | NewBELNR  |   NewCHARGE_HOLD    | NewCOUNTER | NewREFCOUNTER |
+-----+---------------+---------------------+-----------------+-------------------+-----------+---------------------+------------+---------------+
|   1 |     417548605 | T4-GS023ABC2 0150#* |       420202428 | NULL              | 417548605 | T4-GS023ABC2 0150#* |  420202428 | NULL          |
|   2 |     417548605 | T4-GS023ABC2 0150#* |       420202428 | NULL              | 417549506 | T4-GS023-ABC2       |  420203329 | 420202428     |
|   3 |     417548605 | T4-GS023ABC2 0150#* |       420202428 | NULL              | 417553156 | JGS023001    0010#* |  420206979 | 420203329     |
|   4 |     417548605 | T4-GS023ABC2 0150#* |       420202428 | NULL              | 417557221 | T4-GS023-ABC2       |  420211044 | 420206979     |
|   5 |     417548605 | T4-GS023ABC2 0150#* |       420202428 | NULL              | 417581675 | JGS023001    0010#* |  420235498 | 420211044     |
|   6 |     417548605 | T4-GS023ABC2 0150#* |       420202428 | NULL              | 417677969 | JGS023001    0010#* |  420331792 | 420235498     |
+-----+---------------+---------------------+-----------------+-------------------+-----------+---------------------+------------+---------------+

Как видите, мы взяли начальную строку цепочки (где RefCounter is NULL) и перенесли ее на всю цепочку изменений.

Теперь нам просто нужно выбрать строки с последним изменением, то есть с наибольшим Lvl для каждой начальной строки. Один из способов сделать это — использовать функцию ROW_NUMBER с подходящим разделением.

Последний запрос

WITH
CTE
AS
(
    SELECT
        1 AS Lvl,
        CATSDB.BELNR AS OriginalBELNR,
        CATSDB.CHARGE_HOLD AS OriginalCHARGE_HOLD,
        CATSDB.[COUNTER] AS OriginalCOUNTER,
        CATSDB.REFCOUNTER AS OrginalREFCOUNTER,
        CATSDB.BELNR AS NewBELNR,
        CATSDB.CHARGE_HOLD AS NewCHARGE_HOLD,
        CATSDB.[COUNTER] AS NewCOUNTER,
        CATSDB.REFCOUNTER AS NewREFCOUNTER
    FROM
        CATSDB
    WHERE
        REFCOUNTER IS NULL

    UNION ALL

    SELECT
        CTE.Lvl + 1 AS Lvl,
        CTE.OriginalBELNR,
        CTE.OriginalCHARGE_HOLD,
        CTE.OriginalCOUNTER,
        CTE.OrginalREFCOUNTER,
        CATSDB.BELNR AS NewBELNR,
        CATSDB.CHARGE_HOLD AS NewCHARGE_HOLD,
        CATSDB.[COUNTER] AS NewCOUNTER,
        CATSDB.REFCOUNTER AS NewREFCOUNTER
    FROM
        CATSDB
        INNER JOIN CTE ON CATSDB.REFCOUNTER = CTE.NewCOUNTER
)
,CTE_rn
AS
(
    SELECT
        *
        ,ROW_NUMBER() OVER (PARTITION BY OriginalCOUNTER ORDER BY Lvl DESC) AS rn
    FROM CTE
)
SELECT *
FROM CTE_rn
WHERE rn = 1
--OPTION (MAXRECURSION 0)
;

Если у вас может быть цепочка длиннее 100, вы должны добавить OPTION (MAXRECURSION 0) к запросу, потому что по умолчанию SQL Server ограничивает глубину рекурсии до 100.

Результат

+-----+---------------+---------------------+-----------------+-------------------+-----------+---------------------+------------+---------------+----+
| Lvl | OriginalBELNR | OriginalCHARGE_HOLD | OriginalCOUNTER | OrginalREFCOUNTER | NewBELNR  |   NewCHARGE_HOLD    | NewCOUNTER | NewREFCOUNTER | rn |
+-----+---------------+---------------------+-----------------+-------------------+-----------+---------------------+------------+---------------+----+
|   6 |     417548605 | T4-GS023ABC2 0150#* |       420202428 | NULL              | 417677969 | JGS023001    0010#* |  420331792 |     420235498 |  1 |
+-----+---------------+---------------------+-----------------+-------------------+-----------+---------------------+------------+---------------+----+

Эффективность

Чтобы он работал эффективно, нам нужен индекс для столбца REFCOUNTER. Кроме того, запрос предполагает, что REFCOUNTER равно NULL, а не ''. Если у вас есть смесь NULL и пустых строк, унифицируйте свои данные, иначе индекс будет бесполезен. Этот индекс является минимумом того, что вам нужно иметь.

В идеале у вас должен быть CLUSTERED индекс для столбца REFCOUNTER, потому что запрос всегда выбирает все столбцы из таблицы.

CREATE CLUSTERED INDEX [IX_RefCounter] ON [dbo].[CATSDB]
(
    [REFCOUNTER] ASC
)

Если вы не можете изменить индексы своей исходной таблицы, я бы рекомендовал скопировать все миллионы строк во временную таблицу и создать этот кластеризованный индекс для этой временной таблицы.

У меня есть довольно хороший план с этим кластеризованным индексом.

Несколько вещей, которые вы можете сделать, чтобы улучшить производительность:

Преобразование COUNTER и REFCOUNTER в тип данных int из nvarchar, операции с int выполняются намного быстрее, чем с символами. Не используйте курсоры, вы все равно можете обрабатывать одну запись за раз, используя цикл while.

DECLARE @CCOUNTER int = 0
WHILE (1 = 1)
BEGIN
    /* SELECT @COUNTER = MIN(COUNTER) > @COUNTER FROM CATSDB */
    /* IF @@ROWCOUNT != 1 THEN BREAK OUT OF THE WHILE LOOP, WE ARE DONE */
    /* SELECT RECORD FOR THIS @COUNTER FROM CATSDB */
    /* DO THE PROCESSING FOR THIS RECORD */
END

Существует метод, называемый sql Bulk copy. Я не помогу в вашей проблеме, но попробуйте.

Самый эффективный способ сделать это через BCP. https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017.

Вы можете перенести все данные в промежуточную таблицу в SQL Server, а затем запустить вставки и обновления. Также при проверке на отсутствие записи определить, вставка это или обновление "ЕСЛИ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ * ИЗ ВРЕМЕННОГО ГДЕ [СЧЕТЧИК] = @СЧЕТЧИК)" очень накладно.

Пример более эффективного способа сделать это: (Имена таблиц TBL_SOURCE, TBL_DESTINATION, #TBL_UPDATES и #TBL_INSERTS. >)

SELECT * into #TBL_INSERTS
FROM TBL_SOURCE S
    left outer join TBL_DESTINATION D on S.COUNTER=D.COUNTER
WHERE D.Counter is null

SELECT * into #TBL_UPDATES
FROM TBL_SOURCE S
    left outer join TBL_DESTINATION D on S.COUNTER=D.COUNTER
WHERE D.Counter is not null

Обновления будут записываться в #tbl_updates и вставляться в #tbl_inserts.

Посмотрите на основе нескольких примеров данных и данных вывода, наш скрипт не может быть на 100% исправным и оптимизированным, когда речь идет об обновлении миллионов данных.

Я уверен в своем сценарии, что его можно улучшить в этом направлении после полного понимания требований.

Прежде всего, мне интересно, почему тип данных nvarchar, если возможно, сделайте его varchar,int,datetime.

Если вы можете изменить тип данных, то это удивит производительность.

Также нет столбца идентификаторов, который должен быть Clustered Index.

Эти два пункта имеют значение с точки зрения производительности.

Итак, в моем примере

CREATE TABLE CATSDB 
        (
            id int identity ,
            [COUNTER] nvarchar(12),
            REFCOUNTER nvarchar(12),
            PERNR nvarchar(8),
            WORKDATE nvarchar(8),
            CATSHOURS decimal(7, 3),
            APDAT nvarchar(8),
            LAETM nvarchar(6),
            CATS_STATUS nvarchar(2),
            APPR_STATUS nvarchar(2)
        )   

ALTER TABLE CATSDB
ADD CONSTRAINT PK_CATSDB_ID PRIMARY KEY CLUSTERED(ID)

CREATE NONCLUSTERED INDEX FICATSDB_REFCOUNTER ON CATSDB(REFCOUNTER,[COUNTER]);




IF OBJECT_ID('tempdb..#TEMP', 'U') IS NOT NULL
    DROP TABLE #TEMP;

CREATE TABLE #TEMP
(UpdateID      INT,
 FINDID        INT
 PRIMARY KEY,
 [COUNTER]     [NVARCHAR](12) NOT NULL,
 [REFCOUNTER]  [NVARCHAR](12) NULL,
 [PERNR]       [NVARCHAR](8) NULL,
 [WORKDATE]    [NVARCHAR](8) NULL,
 [CATSHOURS]   [DECIMAL](7, 3) NULL,
 [APDAT]       [NVARCHAR](8) NULL,
 [LAETM]       [NVARCHAR](6) NULL,
 [CATS_STATUS] [NVARCHAR](2) NULL,
 [APPR_STATUS] [NVARCHAR](2) NULL
);

WITH CTE
     AS (SELECT a.id,
                a.[COUNTER],
                a.REFCOUNTER,
                a.id AS Findid
         FROM dbo.CATSDB A

         UNION ALL
         SELECT b.id,
                a.[COUNTER],
                a.[refCOUNTER],
                a.id
         FROM dbo.CATSDB A
              INNER JOIN CTE b ON(a.REFCOUNTER = b.[COUNTER])
         WHERE a.id >= b.Findid),
     CTE1
     AS (SELECT id,
                MAX(Findid) Findid
         FROM CTE
         GROUP BY id)

     INSERT INTO #TEMP
     (UpdateID,
      FINDID,
      [COUNTER],
      [REFCOUNTER],
      [PERNR],
      [WORKDATE],
      [CATSHOURS],
      [APDAT],
      [LAETM],
      [CATS_STATUS],
      [APPR_STATUS]
     )
            SELECT c1.ID,
                   c1.FINDID,
                   a.COUNTER,
                   a.REFCOUNTER,
                   a.PERNR,
                   a.WORKDATE,
                   a.CATSHOURS,
                   a.APDAT,
                   a.LAETM,
                   a.CATS_STATUS,
                   a.APPR_STATUS
            FROM dbo.CATSDB A
                 INNER JOIN CTE1 c1 ON a.id = c1.Findid;

BEGIN TRY
    BEGIN TRAN;

    UPDATE A
      SET
          [REFCOUNTER] = b.REFCOUNTER,
          [PERNR] = b.PERNR,
          [WORKDATE] = b.WORKDATE,
          [CATSHOURS] = b.CATSHOURS,
          [APDAT] = b.APDAT,
          [LAETM] = b.LAETM,
          [CATS_STATUS] = b.CATS_STATUS,
          [APPR_STATUS] = b.APPR_STATUS
    FROM CATSDB A
         INNER JOIN #TEMP B ON a.id = b.UpdateID;

    -- this is only test query
    SELECT c1.UpdateID AS UpdateID,
           a.*
    FROM dbo.CATSDB A
         INNER JOIN #TEMP c1 ON a.id = c1.Findid;

    IF(@@trancount > 0)
        ROLLBACK; -- commit
END TRY
BEGIN CATCH
    IF(@@trancount > 0)
        ROLLBACK;
END CATCH;

#Temp should be permanent table.

IMO, вашей таблице очень нужен столбец идентификаторов, который должен быть идентификатором и кластерным индексом.

Вы можете попробовать, вы можете изменить его.

REFCOUNTER,COUNTER должен быть некластеризованным индексом.

После и только после оптимизации запроса и при правильном ПЛАНЕ выше индекс повысит производительность.

Правильный ПЛАН: следует ли вам использовать Recursive or RBAR и обновлять миллионы записей за один раз или мне следует Batch update?

Вы можете сначала протестировать сценарий с миллионами строк с помощью Rollback.