Производительность Sql Server Delete и Merge

Хорошо, вот еще один подход. Для аналогичной проблемы (большое сканирование, ЕСЛИ НЕ СООТВЕТСТВУЕТ ИСТОЧНИК, затем УДАЛИТЬ) я уменьшил время выполнения MERGE с 806 мс до 6 мс!

Одна из проблем, связанных с описанной выше проблемой, заключается в том, что предложение «WHEN NOT MATCHED BY SOURCE» сканирует всю таблицу TARGET.

Это не так очевидно, но Microsoft позволяет фильтровать таблицу TARGET (с помощью CTE) ПЕРЕД выполнением слияния. Таким образом, в моем случае количество строк TARGET было уменьшено с 250 000 до менее 10 строк. Большая разница.

Если предположить, что описанная выше проблема работает с ЦЕЛЬЮ, отфильтрованной @itemid и @platform, тогда код MERGE будет выглядеть следующим образом. Приведенные выше изменения в индексах также помогут этой логике.

WITH Transactions_CTE (itemId
                        ,dt
                        ,count
                        ,price
                        ,platform
                        )
AS
-- Define the CTE query that will reduce the size of the TARGET table.  
(  
    SELECT itemId
        ,dt
        ,count
        ,price
        ,platform
    FROM Transactions  
    WHERE itemId = @itemId
      AND platform = @platform  
)  
MERGE Transactions_CTE AS TARGET
USING @Table AS SOURCE
    ON (
        TARGET.[itemId] = SOURCE.[itemId]
        AND TARGET.[platform] = SOURCE.[platform]
        AND TARGET.[dt] = SOURCE.[dt]
        AND TARGET.[count] = SOURCE.[count]
        AND TARGET.[price] = SOURCE.[price]
        )
WHEN NOT MATCHED BY TARGET  THEN
        INSERT
        VALUES (
            SOURCE.[itemId]
            ,SOURCE.[dt]
            ,SOURCE.[count]
            ,SOURCE.[price]
            ,SOURCE.[platform]
            )
WHEN NOT MATCHED BY SOURCE THEN
        DELETE;

Использование поля BIT для IsDeleted (или IsActive, как делают многие люди) допустимо, но требует изменения всего кода, а также создания отдельного задания SQL для периодического прохождения и удаления «удаленных» записей. Это может быть путь, но есть что-то менее навязчивое, чтобы попробовать сначала.

Я заметил в вашем наборе из двух индексов, что ни один из них не является CLUSTERED. Можно предположить, что поле IDENTITY есть? Вы можете рассмотреть возможность сделать индекс [IX2] UNIQUE CLUSTERED и изменить PK (опять же, я предполагаю, что поле IDENTITY является CLUSTERED PK) на NONCLUSTERED. Я бы также переупорядочил поля IX2, чтобы сначала поставить [Platform] и [ItemID]. Поскольку ваша основная операция ищет [Platform] и [ItemID] как набор, может помочь их физическое упорядочивание таким образом. И поскольку этот индекс уникален, он является хорошим кандидатом на КЛАСТЕРИЗАЦИЯ. Это, безусловно, стоит протестировать, так как это повлияет на все запросы к таблице.

Кроме того, если изменение индексов, как я предложил, помогает, все же, возможно, стоит попробовать обе идеи и, следовательно, также использовать поле IsDeleted, чтобы увидеть, повысит ли это производительность еще больше.

РЕДАКТИРОВАТЬ: я забыл упомянуть, сделав индекс IX2 CLUSTERED и переместив поле [Platform] наверх, вы должны избавиться от индекса IX1.

РЕДАКТИРОВАТЬ2:

Просто чтобы быть очень ясным, я предлагаю что-то вроде:

CREATE UNIQUE CLUSTERED  INDEX [IX2]
(
[ItemId] DESC,
[platform] ASC,
[count] ASC,
[dt] DESC,
[price] ASC
)

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

См. это https://stackoverflow.com/questions/3685141/how-to-improve-performance-when-deleting-entities-from-database/3685275#3685275

будет ли обновление стоить столько же, сколько и удаление? Нет. Обновление было бы намного более легкой операцией, особенно если бы у вас был индекс в ПК (э-э-э, это guid, а не int). Дело в том, что обновление битового поля обходится гораздо дешевле. (Массовое) удаление приведет к перетасовке данных.

В свете этой информации ваша идея использовать битовое поле очень актуальна.