DELETE SQL with correlated subquery for table with 42 million rows?

Практического порога нет. Это зависит от того, какой тайм-аут вашей команды установлен в вашем соединении.

Имейте в виду, что время, необходимое для удаления всех этих строк, зависит от:

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

Последний пункт часто может быть наиболее важным. Выполните команду sp_who2 в другом окне запроса, чтобы убедиться, что не происходит конфликта блокировок, препятствующего выполнению вашей команды.

Неправильно настроенные серверы SQL не справятся с этим типом запросов. Журналы транзакций, которые слишком малы и / или находятся на тех же дисках, что и файлы данных, часто приводят к серьезным потерям производительности при работе с большими строками.

Что касается решения, ну, как и все, это зависит от обстоятельств. Вы собираетесь часто этим заниматься? В зависимости от того, сколько строк у вас осталось, самым быстрым способом может быть перестроение таблицы с другим именем, а затем ее переименование и воссоздание ограничений внутри транзакции. Если это просто специальная вещь, убедитесь, что ваш ADO CommandTimeout установлен достаточно высоким, и вы можете просто нести расходы на это большое удаление.

Если удаление приведет к удалению «значительного количества» строк из таблицы, это может быть альтернативой DELETE: поместить записи в другое место, обрезать исходную таблицу, вернуть «хранителей». Что-то вроде:

SELECT *
INTO #cats_to_keep
FROM cats
WHERE cats.id_cat NOT IN (    -- note the NOT
SELECT owner_cats.id_cat FROM owner_cats
WHERE owner_cats.id_owner = 1)

TRUNCATE TABLE cats

INSERT INTO cats
SELECT * FROM #cats_to_keep

Вы пробовали не использовать подзапрос и вместо этого использовали соединение?

DELETE cats 
FROM
 cats c
 INNER JOIN owner_cats oc
 on c.id_cat = oc.id_cat
WHERE
   id_owner =1

И если да, то вы также пробовали разные подсказки соединения, например

DELETE cats 
FROM
 cats c
 INNER HASH JOIN owner_cats oc
 on c.id_cat = oc.id_cat
WHERE
   id_owner =1

Как уже упоминалось, когда вы удаляете 42 миллиона строк, база данных должна регистрировать 42 миллиона удалений в базе данных. Таким образом, журнал транзакций должен существенно увеличиться. Что вы можете попробовать, так это разбить удаление на части. В следующем запросе я использую функцию ранжирования NTile, чтобы разбить строки на 100 сегментов. Если это слишком медленно, вы можете увеличить количество сегментов, чтобы каждое удаление было меньше. Очень поможет, если есть индекс на owner_cats.id_owner , owner_cats.id_cats и cats.id_cat (который я принял за первичный ключ и числовой).

Declare @Cats Cursor
Declare @CatId int  --assuming an integer PK here
Declare @Start int
Declare @End int
Declare @GroupCount int

Set @GroupCount = 100

Set @Cats = Cursor Fast_Forward For
    With CatHerd As
        (
        Select cats.id_cat
            , NTile(@GroupCount) Over ( Order By cats.id_cat ) As Grp
        From cats
            Join owner_cats
                On owner_cats.id_cat = cats.id_cat
        Where owner_cats.id_owner = 1
        )
        Select Grp, Min(id_cat) As MinCat, Max(id_cat) As MaxCat
        From CatHerd
        Group By Grp
Open @Cats
Fetch Next From @Cats Into @CatId, @Start, @End

While @@Fetch_Status = 0
Begin
    Delete cats
    Where id_cat Between @Start And @End

    Fetch Next From @Cats Into @CatId, @Start, @End
End 

Close @Cats
Deallocate @Cats

Примечательная загвоздка описанного выше подхода заключается в том, что он не является транзакционным. Таким образом, если произойдет сбой на 40-м блоке, вы удалите 40% строк, а остальные 60% все еще будут существовать.

Если вы используете EXISTS, а не IN, вы должны получить гораздо лучшую производительность. Попробуйте так:

DELETE
  FROM cats c
 WHERE EXISTS (SELECT 1
                 FROM owner_cats o
                WHERE o.id_cat = c.id_cat
                  AND o.id_owner = 1)

Как такового порога нет - вы можете УДАЛИТЬ все строки из любой таблицы , учитывая достаточно места в журнале транзакций - именно здесь ваш запрос, скорее всего, не выдержит . Если вы получаете какие-то результаты от вашего DELETE TOP (n) PERCENT FROM cats WHERE ..., вы можете заключить его в цикл, как показано ниже:

SELECT 1
WHILE @@ROWCOUNT <> 0
BEGIN
 DELETE TOP (somevalue) PERCENT FROM cats
 WHERE cats.id_cat IN (
 SELECT owner_cats.id_cat FROM owner_cats
 WHERE owner_cats.id_owner = 1)
END

Ответ Билла Карвина на другой вопрос применим и к моей ситуации:

«Если ваш DELETE предназначен для удаления подавляющего большинства строк в этом table, люди часто делают то, что копируют только те строки, которые вы хотите сохранить, в дублирующую таблицу, а затем используют DROP TABLE или TRUNCATE , чтобы стереть исходную таблицу гораздо быстрее . "

Мэтт в этом ответе говорит об этом так:

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

ammoQ в этом ответе (из того же вопроса) рекомендует (перефразировано):

• блокировать таблицу при удалении большого количества строк
• помещать индексы в любые столбцы внешнего ключа