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

Ответ 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