Улучшение ужасной производительности MERGE

На первый взгляд кажется, что MERGE не является причиной ухудшения производительности. Условие слияния всегда ложно (0=1), и вставка (в [security].[Identities]) является единственным возможным путем/путем вперед.

Сколько времени потребуется, чтобы вставить 2 миллиона строк в @temp в обход [security].[Identities] и MERGE ?

DECLARE @temp AS TABLE(
      [action] NVARCHAR(20)
     ,[GlobalId] BIGINT
     ,[Personnumber] NVARCHAR(100)
     ,[Firstname] NVARCHAR(100)
     ,[Lastname] NVARCHAR(100)
);

--is this fast?!?
INSERT INTO @temp(action, GlobalId, Personnumber, Firstname, LastName)
SELECT 'insert', 0, t.[Personnumber], t.[Firstname], t.[Lastname]
FROM [temp].[RawRoles] t
WHERE t.Personnumber NOT IN 
(
    SELECT i.Account FROM [security].[Accounts] i
);

Проверить:

1. Каков тип данных [temp].[RawRoles].Personnumber? is Personnumber nvarchar(100)?
   Нужно ли хранить иностранные символы в номере человека? Nchar в два раза больше char. varchar/char может быть лучшим выбором, если у вас есть буквенно-цифровые (обычные латинские символы) или цифры с ведущими нулями. Если ваши требования могут быть выполнены с числовым типом данных, то предпочтительнее будет int/bigint/decimal.

2. Есть ли индекс для [temp].[RawRoles].Personnumber? Без индекса проверка существования должна была бы сортировать [temp].[RawRoles].Personnumber или хешировать его. Это может быть дополнительными затратами на пропускную способность ресурса/dtu. Кластерный индекс для [temp].RawRoles может быть наиболее полезным, учитывая, что большинство temp.RawRoles будут окончательно обработаны/вставлены.

3. Каков тип данных [security].[Accounts].Account? Есть ли индекс в столбце? Два столбца [security].[Accounts].Account и [temp].[RawRoles].Personnumber должны иметь один и тот же тип данных и в идеале иметь индекс для обоих. Если [security].[Accounts] является конечным пунктом назначения обработанного [temp].[RawRoles], тогда таблица может содержать миллионы строк, и для любой будущей обработки требуется индекс столбца Account. Недостатком индекса является более медленная вставка. Если 2 миллиона — это самый первый объем/данные, было бы лучше не иметь индекса для учетной записи при вставке «массы» в security.Accounts (но создать его позже).

Подводить итоги:

--contemplate&decide whether a change of the Account datatype is needed. (a datatype change can have many implications, for applications using the db)

--change the data type of Personnumber to the datatype of Account(security.Accounts)
ALTER TABLE temp.RawRoles ALTER COLUMN Personnumber "datatype of security.Accounts.Account" NOT NULL; -- rows having undefined Personnumber?

--clustered index Personnumber
CREATE /*UNIQUE*/ CLUSTERED INDEX uclxPersonnumber ON temp.RawRoles(Personnumber); --unique preferred, if possible

--index on account (not needed[?] when security.Accounts is empty)
CREATE INDEX idxAccount ON [security].Accounts(Account);


--baseline, how fast can we do a straight forward insertion of 2 million rows?
DECLARE @tempbaseline AS TABLE(
      [action] NVARCHAR(20)
     ,[GlobalId] BIGINT
     ,[Personnumber] NVARCHAR(100) --ignore this for now
     ,[Firstname] NVARCHAR(100)
     ,[Lastname] NVARCHAR(100)
);

INSERT INTO @tempbaseline([action], GlobalId, Personnumber, Firstname, LastName)
SELECT 'INSERT', 0, t.[Personnumber], t.[Firstname], t.[Lastname]
FROM [temp].[RawRoles] t
    WHERE NOT EXISTS (SELECT * FROM [security].[Accounts] i WHERE i.Account = t.Personnumber)    
--if the execution time (baseline) is acceptable, proceed with the merge code
--"merge with output into" should be be "slightly"/s slower than the baseline.
--if the baseline is not acceptable (simple insertion takes too much time) then merge is futile

/*
DECLARE @temp....


MERGE [security].[Identities] AS tar
USING 
(
    SELECT --top 1
        t.[Personnumber]
        ,t.[Firstname]
        ,t.[Lastname]
    FROM [temp].[RawRoles] t
    WHERE NOT EXISTS (SELECT * FROM [security].[Accounts] i WHERE i.Account = t.Personnumber)
) AS src
ON 0 = 1 -- all rows from src need to be inserted, ive already filtered out in the USING Query.
WHEN NOT MATCHED THEN
   INSERT
   (
     [Created], [Updated]
   )
   VALUES
   (
        GETUTCDATE(), GETUTCDATE()
   )
OUTPUT 'INSERT' /** only insert is possible $action */, inserted.GlobalId, src.[Personnumber], src.[Firstname], src.[Lastname]  INTO @temp;   


--delete the index on Account (the process will insert 2mil)
DROP INDEX idxAccount ON [security].Accounts --review and create this index after the bulk of accounts is inserted.

...your process

*/

Мне кажется, что ваша таблица Identity просто используется в качестве генератора последовательности, потому что вы не вставляете в нее ничего, кроме временных меток. Рассматривали ли вы возможность использования ПОСЛЕДОВАТЕЛЬНОСТИ вместо таблицы для генерации ключей? Использование последовательности могло бы устранить этот процесс, потому что вы могли бы генерировать ключ всякий раз, когда он был необходим.

Вывод миллионов строк в табличную переменную вряд ли возможен. Табличные переменные обычно хороши для нескольких тысяч строк.

INSERT INTO security.Accounts (GlobalId, Account, Firstname, Lastname)
SELECT NEXT VALUE FOR AccountSeq, r.Personnumber, r.Firstname, r.Lastname
FROM temp.RawRoles AS r
LEFT JOIN security.Accounts AS a ON r.Personnumber = a.Account
WHERE a.Personnumber IS NULL;

INSERT INTO security.identities (GlobalId, Created, Updated)
SELECT a.GlobalId, GETUTCDATE() AS Created, GETUTCDATE() AS Updated
FROM security.Accounts AS a
LEFT JOIN security.identities AS i ON a.GlobalId = i.GlobalId
WHERE i.GlobalId IS NULL;