УДАЛИТЬ SQL с коррелированным подзапросом для таблицы с 42 миллионами строк?

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

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

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

Последний пункт часто может быть самым важным. Выполните команду 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

Если вы используете 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

Как уже упоминалось, когда вы удаляете 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% все еще будут существовать.

Возможно, стоит попробовать MERGE, например,

MERGE INTO cats 
   USING owner_cats
      ON cats.id_cat = owner_cats.id_cat
         AND owner_cats.id_owner = 1
WHEN MATCHED THEN DELETE;

‹Edit› (28.09.2011)
Мой ответ работает в основном так же, как и решение Томаса (6 августа 2010 г.). Я пропустил это, когда опубликовал свой ответ, потому что он использует настоящий КУРСОР, поэтому я подумал про себя «плохо» из-за количества задействованных записей. Однако, когда я только что перечитал его ответ, я понял, что способ, которым он использует курсор, на самом деле «хорош». Очень умный. Я только что проголосовал за его ответ и, вероятно, воспользуюсь его подходом в будущем. Если вы не понимаете почему, взгляните на это еще раз. Если вы все еще не видите его, оставьте комментарий к этому ответу, и я вернусь и постараюсь подробно объяснить. Я решил оставить свой ответ, потому что у кого-то может быть администратор баз данных, который отказывается позволить им использовать настоящий КУРСОР, независимо от того, насколько он «хорош». :-)
‹/Edit›

Я понимаю, что этому вопросу уже год, но недавно у меня была похожая ситуация. Я пытался выполнить «массовое» обновление большой таблицы с присоединением к другой таблице, также довольно большой. Проблема заключалась в том, что объединение приводило к такому количеству «объединенных записей», что обработка занимала слишком много времени и могла привести к конфликтам. Поскольку это было разовое обновление, я придумал следующий «хак». Я создал WHILE LOOP, который прошел по таблице для обновления и выбрал 50 000 записей для обновления за раз. Выглядело это примерно так:

DECLARE @RecId bigint
DECLARE @NumRecs bigint
SET @NumRecs = (SELECT MAX(Id) FROM [TableToUpdate])
SET @RecId = 1
WHILE @RecId < @NumRecs
BEGIN
    UPDATE [TableToUpdate]
    SET UpdatedOn = GETDATE(),
        SomeColumn = t2.[ColumnInTable2]
    FROM    [TableToUpdate] t
    INNER JOIN [Table2] t2 ON t2.Name = t.DBAName 
        AND ISNULL(t.PhoneNumber,'') = t2.PhoneNumber 
        AND ISNULL(t.FaxNumber, '') = t2.FaxNumber
    LEFT JOIN [Address] d ON d.AddressId = t.DbaAddressId 
        AND ISNULL(d.Address1,'') = t2.DBAAddress1
        AND ISNULL(d.[State],'') = t2.DBAState
        AND ISNULL(d.PostalCode,'') = t2.DBAPostalCode
    WHERE t.Id BETWEEN @RecId AND (@RecId + 49999)
    SET @RecId = @RecId + 50000
END

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

‹Edit› (28.09.2011)
Есть ОГРОМНАЯ оговорка к предложению, которое упоминалось здесь более одного раза и публикуется повсюду в Интернете относительно копирования «хорошие» записи в другую таблицу, выполнение TRUNCATE (или DROP и reCREATE, или DROP и переименование), а затем повторное заполнение таблицы.

Вы не можете сделать это, если таблица является таблицей PK в отношении PK-FK (или другом ОГРАНИЧЕНИИ). Конечно, вы можете УБРАТЬ связь, выполнить очистку и восстановить связь, но вам также придется очистить таблицу FK. Вы можете сделать это ПЕРЕД восстановлением связи, что означает большее «время простоя», или вы можете выбрать НЕ ПРИМЕНЯТЬ ОГРАНИЧЕНИЕ при создании и очистить его после. Я думаю, вы также можете очистить таблицу FK ПЕРЕД очисткой таблицы PK. Суть в том, что вам нужно явно очистить таблицу FK, так или иначе.

Мой ответ - это гибридный процесс на основе SET / квази-КУРСОРА. Еще одним преимуществом этого метода является то, что если отношение PK-FK настроено на КАСКАДНОЕ УДАЛЕНИЕ, вам не нужно выполнять очистку, о которой я упоминал выше, потому что сервер позаботится об этом за вас. Если ваша компания или администратор баз данных не одобряют каскадное удаление, вы можете попросить включить его только во время выполнения этого процесса, а затем отключить, когда он будет завершен. В зависимости от уровней разрешений учетной записи, которая запускает очистку, операторы ALTER для включения / отключения каскадного удаления могут быть прикреплены к началу и концу оператора SQL. ‹/Edit›

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

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

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

«В автономном режиме и при удалении большого% может иметь смысл просто создать новую таблицу с данными для хранения, отбросить старую таблицу и переименовать».

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

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