Очень медленный запрос DELETE

У меня проблемы с производительностью SQL. По внезапной причине следующие запросы очень медленные:

У меня есть два списка, содержащий Id определенной таблицы. Мне нужно удалить все записи из первого списка, если Id уже существует во втором списке:

DECLARE @IdList1 TABLE(Id INT)
DECLARE @IdList2 TABLE(Id INT)

-- Approach 1
DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id

-- Approach 2
DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)

Возможно, что в двух списках содержится более 10.000 записей. В этом случае оба запроса выполняются каждые 20 секунд.

План выполнения также показал то, что я не понимаю. Может быть, это объясняет, почему это так медленно:

Я заполнил оба списка с 10.000 последовательными целыми числами, поэтому оба списка содержали значение 1-10 000 в качестве отправной точки.

Как вы можете видеть, оба запроса отображаются для @IdList2. Фактическое количество строк - 50.005.000!!. @IdList1 является правильным (фактическое количество строк - 10.000)

Я знаю, что есть другие способы решения этой проблемы. Как заполнение третьего списка, установленного для удаления из первого списка. Но мой вопрос:

Почему эти запросы на удаление так медленно и почему я вижу эти странные планы запросов?

Ответ 1

Добавьте первичный ключ к переменным таблицы и посмотрите их крик

DECLARE @IdList1 TABLE(Id INT primary Key not null)
DECLARE @IdList2 TABLE(Id INT primary Key not null)

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

Ответ 2

SQL Server компилирует план, когда переменная таблицы пуста и не перекомпилирует ее при добавлении строк. Попробуйте

DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)
OPTION (RECOMPILE)

Это будет учитывать фактическое количество строк, содержащихся в переменной таблицы, и избавиться от плана вложенных циклов

Конечно, создание индекса на Id через ограничение может быть полезно для других запросов, используя также переменную таблицы.

Ответ 3

В таблицах переменных таблицы могут быть первичные ключи, поэтому, если ваши данные поддерживают уникальность для этих Id s, вы можете повысить производительность, перейдя на

DECLARE @IdList1 TABLE(Id INT PRIMARY KEY)
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY)

Ответ 4

Возможные решения:

1) Попробуйте создать индексы, таким образом

1.1) Если столбец List {1 | 2}.Id имеет уникальные значения, тогда вы можете определить уникальный кластерный индекс, используя ограничение PK следующим образом:

DECLARE @IdList1 TABLE(Id INT PRIMARY KEY);
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY);

1.2) Если столбец List {1 | 2}.Id может иметь повторяющиеся значения, тогда вы можете определить уникальный кластерный индекс, используя ограничение PK, используя столбец IDENTITY, как это:

DECLARE @IdList1 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );
DECLARE @IdList2 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );

2) Попробуйте добавить подсказку HASH JOIN так:

DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id
OPTION (HASH JOIN);

Ответ 5

Вы используете Table Variables, либо добавьте первичный ключ в таблицу, либо измените их на Temporary Tables и добавьте INDEX. Это приведет к значительно большей производительности. Как правило, если таблица мала, используйте Table Variables, однако, если таблица расширяется и содержит много данных, либо используйте временную таблицу.

Ответ 6

У меня возникнет соблазн попробовать

DECLARE @IdList3 TABLE(Id INT);

INSERT @IdList3
SELECT Id FROM @IDList1 ORDER BY Id
EXCEPT
SELECT Id FROM @IDList2 ORDER BY Id

Не требуется удаление.

Ответ 7

Попробуйте этот альтернативный синтаксис:

DELETE deleteAlias
FROM @IdList1 deleteAlias
WHERE EXISTS (
        SELECT NULL
        FROM @IdList2 innerList2Alias
        WHERE innerList2Alias.id=deleteAlias.id
    )

ИЗМЕНИТЬ.....................

Попробуйте вместо этого использовать таблицы #temp с индексами.

Вот общий пример, где "DepartmentKey" - это PK и FK.

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end


CREATE TABLE #Department 
( 
    DepartmentKey int , 
    DepartmentName  varchar(12)
)



CREATE INDEX IX_TEMPTABLE_Department_DepartmentKey ON #Department (DepartmentKey)




IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end


CREATE TABLE #Employee 
( 
    EmployeeKey int , 
    DepartmentKey int ,
    SSN  varchar(11)
)



CREATE INDEX IX_TEMPTABLE_Employee_DepartmentKey ON #Employee (DepartmentKey)


Delete deleteAlias 
from #Department deleteAlias
where exists ( select null from #Employee innerE where innerE.DepartmentKey = deleteAlias.DepartmentKey )





IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end