SQL Server DELETE работает медленнее с индексами

У меня есть база данных SQL Server 2005, и я попытался поставить индексы на соответствующие поля, чтобы ускорить DELETE записей из таблицы с миллионами строк (big_table имеет только 3 столбца), но теперь DELETE время выполнения даже < em>длиннее! (например, 1 час против 13 минут)

У меня есть связь между таблицами, и столбец, по которому я фильтрую свой DELETE, находится в другой таблице. Например

DELETE FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)

Кстати, я также пробовал:

DELETE FROM big_table
WHERE EXISTS
(SELECT 1 FROM small_table
WHERE small_table.id_product = big_table.id_product
AND small_table.id_category = 1)

и хотя кажется, что он работает немного быстрее, чем первый, он все же намного медленнее с индексами, чем без них.

Я создал индексы для этих полей:

  1. big_table.id_product
  2. small_table.id_product
  3. small_table.id_category

Мой файл .ldf сильно разросся во время DELETE.

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

ОБНОВЛЕНИЕ

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

Из прочитанного у меня сложилось впечатление, что индексы ускорят DELETE, ускорив поиск полей в предложении WHERE.

Odetocode.com сообщает:

«Индексы работают так же хорошо при поиске записи в командах DELETE и UPDATE, как и в операторах SELECT».

Но позже в статье говорится, что слишком много индексов может снизить производительность.

Ответы на вопросы bobs:

  1. 55 миллионов строк в таблице
  2. 42 миллиона строк удаляются
  3. Аналогичный оператор SELECT не будет выполняться (было выдано исключение типа «System.OutOfMemoryException»).

Я попробовал следующие 2 запроса:

SELECT * FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)

SELECT * FROM big_table
INNER JOIN small_table
ON small_table.id_product = big_table.id_product
WHERE small_table.id_category = 1

Оба сбой после работы в течение 25 минут с этим сообщением об ошибке от SQL Server 2005:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Сервер базы данных представляет собой старую двухъядерную машину Xeon с оперативной памятью 7,5 ГБ. Это моя игрушечная тестовая база данных :), поэтому она больше ничего не запускает.

Нужно ли мне делать что-то особенное с моими индексами после того, как я их CREATE заработал, чтобы они работали правильно?


sql sql-server sql-delete subquery
person JohnB    schedule 10.08.2010    source источник
comment
Сколько строк в таблице? Сколько строк удаляется? Сколько времени потребуется для выполнения аналогичного оператора SELECT? Знание того, как быстро выполняется оператор SELECT, может дать некоторое представление о том, как индексы влияют на DELETE.   -  person bobs    schedule 11.08.2010
comment
Это занимает больше времени, потому что когда вы выполняете удаление, индексы, которые ссылаются на вашу таблицу, также должны быть обновлены.   -  person WOPR    schedule 11.08.2010
comment
55 млн строк, 42 удалено, не завершено, подробнее см. выше.   -  person JohnB    schedule 12.08.2010

Ответы (5)


arrow_upward
29
arrow_downward

Индексы ускоряют поиск — как индекс в конце книги.

Операции по изменению данных (такие как DELETE) выполняются медленнее, так как требуют манипулирования индексами. Рассмотрим тот же указатель в конце книги. У вас будет больше работы, если вы добавите, удалите или измените страницы, потому что вам также нужно обновить индекс.

person brabster    schedule 10.08.2010

arrow_upward
3
arrow_downward

Я согласен с комментарием Боба выше - если вы удаляете большие объемы данных из больших таблиц, удаление индексов может занять некоторое время, помимо удаления данных, это стоимость ведения бизнеса. Поскольку он удаляет все данные, вы вызываете события переиндексации.

Что касается роста файла журнала; если вы ничего не делаете со своими файлами журнала, вы можете переключиться на Простое ведение журнала ; но я призываю вас прочитать о влиянии, которое может оказать на ваш ИТ-отдел, прежде чем менять.

Если вам нужно выполнить удаление в режиме реального времени; Часто бывает полезно пометить данные как неактивные либо непосредственно в таблице, либо в другой таблице и исключить эти данные из запросов; затем вернитесь позже и удалите данные, когда пользователи не смотрят на песочные часы. Есть и вторая причина для этого; если вы удаляете много данных из таблицы (это то, что я предполагаю, основываясь на вашей проблеме с файлом журнала), то вы, вероятно, захотите выполнить indexdefrag для реорганизации индекса; делать это в нерабочее время - это то, что нужно, если вам не нравятся пользователи, разговаривающие по телефону!

person u07ch    schedule 11.08.2010

arrow_upward
1
arrow_downward

JohnB удаляет около 75% данных. Я думаю, что следующее решение было бы возможным и, вероятно, одним из самых быстрых. Вместо удаления данных создайте новую таблицу и вставьте данные, которые необходимо сохранить. Создайте индексы в этой новой таблице после вставки данных. Теперь удалите старую таблицу и переименуйте новую в то же имя, что и старая.

Вышеизложенное, конечно, предполагает, что на диске имеется достаточно места для временного хранения дублированных данных.

person Valentino Vranken    schedule 25.03.2013

arrow_upward
0
arrow_downward

Вы также можете попробовать расширение TSQL для синтаксиса DELETE и проверить, улучшает ли оно производительность:

DELETE FROM big_table
FROM big_table AS b
INNER JOIN small_table AS s ON (s.id_product = b.id_product)
WHERE s.id_category  =1
person a1ex07    schedule 10.08.2010
comment
Это совсем не помогло; это заняло столько же времени, сколько delete from big_table where exists (select 1 from small_table where small_table.id_product = big_table.id_product and small_table.id_category = 1) - person JohnB; 12.08.2010

arrow_upward
0
arrow_downward

Попробуйте что-то подобное, чтобы избежать массового удаления (и тем самым избежать увеличения файла журнала)

declare @continue bit = 1

-- delete all ids not between starting and ending ids
while @continue = 1
begin

    set @continue = 0

    delete top (10000) u
    from    <tablename> u WITH (READPAST)
    where   <condition>

    if @@ROWCOUNT > 0
        set @continue = 1 

end
person user2608613    schedule 14.01.2015