Является SQL Server DRI (НА, УДАЛЯЮТ КАСКАД), медленный?
Я анализировал повторяющийся "отчет об ошибках" (проблема перфекта) в одной из наших систем, связанных с особенно медленным, удаляют операцию. Длинная короткая история: кажется что CASCADE DELETE ключи были в основном ответственны, и я хотел бы знать (a) если это имеет смысл и (b) почему это имеет место.
У нас есть схема, скажем, виджеты, при этом это в корне большого графика связанных таблиц и связанных-к-связанному таблиц и так далее. Быть совершенно ясным, удаляя из этой таблицы активно препятствуют; это - "возможность применения ядерного оружия", и пользователи не тешат себя никакими иллюзиями об обратном. Тем не менее, это иногда просто должно быть сделано.
Схема выглядит примерно так:
Widgets
|
+--- Anvils [1:1]
| |
| +--- AnvilTestData [1:N]
|
+--- WidgetHistory (1:N)
|
+--- WidgetHistoryDetails (1:N)
Определения столбца похожи на следующее:
Widgets (WidgetID int PK, WidgetName varchar(50))
Anvils (AnvilID int PK, WidgetID int FK/IX/UNIQUE, ...)
AnvilTestData (AnvilID int FK/IX, TestID int, ...Test Data...)
WidgetHistory (HistoryID int PK, WidgetID int FK/IX, HistoryDate datetime, ...)
WidgetHistoryDetails (HistoryID int FK/IX, DetailType smallint, ...)
Ничто слишком страшное, действительно. A Widget могут быть различные типы, Anvil специальный тип, так, чтобы отношения были 1:1 (или более точно 1:0.. 1). Затем существует большой объем данных - возможно, тысячи строк AnvilTestData на Anvil собираемый со временем, имея дело с твердостью, коррозия, точный вес, кует совместимость, проблемы удобства использования и испытания на удар с мультипликационными заголовками.
Затем каждый Widget имеет длинную, скучную историю различных типов транзакций - производство, перемещения материально-технических ресурсов, продажи, дефектные расследования, RMAs, восстановления, претензии клиента, и т.д. Могли бы быть 10-20k детали для единственного виджета или ни одного вообще, в зависимости от его возраста.
Так, неудивительно, существует a CASCADE DELETE отношения на каждом уровне здесь. Если a Widget потребности, которые будут удалены, это означает, что что-то пошло ужасно неправильно, и мы должны стереть любые записи того виджета, когда-либо существующего, включая его историю, данные тестирования, и т.д. Снова, возможность применения ядерного оружия.
Отношения все индексируются, статистические данные актуальны. Нормальные запросы быстры. Система имеет тенденцию гудеть вперед довольно гладко для всего кроме, удаляет.
Переходя к сути дела здесь, наконец, по различным причинам, мы только позволяем удалять один виджет за один раз, таким образом, оператор удаления был бы похож на это:
DELETE FROM Widgets
WHERE WidgetID = @WidgetID
Довольно простой, безвредный взгляд удаляет..., который принимает 2 минуты для выполнения для виджета без данных!
После сильного удара через планы выполнения мне наконец удалось выбирать AnvilTestData и WidgetHistoryDetails удаляет как подоперации с самой высокой стоимостью. Таким образом, я экспериментировал с выключением CASCADE (но хранение фактического FK, просто установив его на NO ACTION) и перезапись сценария как что-то очень как следующее:
DECLARE @AnvilID int
SELECT @AnvilID = AnvilID FROM Anvils WHERE WidgetID = @WidgetID
DELETE FROM AnvilTestData
WHERE AnvilID = @AnvilID
DELETE FROM WidgetHistory
WHERE HistoryID IN (
SELECT HistoryID
FROM WidgetHistory
WHERE WidgetID = @WidgetID)
DELETE FROM Widgets WHERE WidgetID = @WidgetID
Обе из этой "оптимизации" привела к значительным ускорениям, каждый бреющий почти целую минуту от времени выполнения, так, чтобы исходное 2-минутное удаление теперь заняло приблизительно 5-10 секунд - по крайней мере, для новых виджетов без больших ретроспективных данных или данных тестирования.
Только, чтобы быть абсолютно ясным, существует все еще a CASCADE от WidgetHistory кому: WidgetHistoryDetails, где разветвление на выходе является самым высоким, я только удалил тот, происходящий из Widgets.
Далее "выравнивание" каскадных отношений привело к прогрессивно менее поразительным, но все еще значимым ускорениям, до такой степени, когда удаление нового виджета было почти мгновенно, после того как весь каскад удаляет к большим таблицам, были удалены и заменены явным, удаляет.
Я использую DBCC DROPCLEANBUFFERS и DBCC FREEPROCCACHE перед каждым тестом. Я отключил все триггеры, которые могли бы вызывать дальнейшее замедление (хотя они обнаружатся в плане выполнения так или иначе). И я тестирую против более старых виджетов также и замечаю значительное ускорение там также; удаляет, который раньше занимал 5 минут, теперь занимают 20-40 секунд.
Теперь я - горячий сторонник "ВЫБОРА, не повреждается" философия, но просто, кажется, нет никакого логического объяснения этого поведения кроме сокрушительной, ошеломляющей неэффективности CASCADE DELETE отношения.
Так, мои вопросы:
Действительно ли это - известная проблема с DRI в SQL Server? (Я, могло казаться, не нашел ссылок на этот вид вещи на Google или здесь в ТАК; я подозреваю, что ответ нет.)
В противном случае есть ли другое объяснение поведения, которое я вижу?
Если это - известная проблема, почему это - проблема и является там лучшими обходными решениями, которые я мог использовать?
1 ответ
SQL Server лучше всего подходит для операций на основе наборов, в то время как удаления CASCADE по своей природе основаны на записях.
SQL Server , в отличие от других серверов, пытается оптимизировать немедленные операции на основе наборов, однако он работает только на один уровень. Чтобы удалить записи в таблицах нижнего уровня, необходимо удалить записи в таблицах верхнего уровня.
Другими словами, каскадные операции работают снизу вверх, а ваше решение работает снизу вверх, что более эффективно и основано на наборах.
Вот образец схемы:
CREATE TABLE t_g (id INT NOT NULL PRIMARY KEY)
CREATE TABLE t_p (id INT NOT NULL PRIMARY KEY, g INT NOT NULL, CONSTRAINT fk_p_g FOREIGN KEY (g) REFERENCES t_g ON DELETE CASCADE)
CREATE TABLE t_c (id INT NOT NULL PRIMARY KEY, p INT NOT NULL, CONSTRAINT fk_c_p FOREIGN KEY (p) REFERENCES t_p ON DELETE CASCADE)
CREATE INDEX ix_p_g ON t_p (g)
CREATE INDEX ix_c_p ON t_c (p)
, этот запрос:
DELETE
FROM t_g
WHERE id > 50000
и его план:
|--Sequence
|--Table Spool
| |--Clustered Index Delete(OBJECT:([test].[dbo].[t_g].[PK__t_g__176E4C6B]), WHERE:([test].[dbo].[t_g].[id] > (50000)))
|--Index Delete(OBJECT:([test].[dbo].[t_p].[ix_p_g]) WITH ORDERED PREFETCH)
| |--Sort(ORDER BY:([test].[dbo].[t_p].[g] ASC, [test].[dbo].[t_p].[id] ASC))
| |--Table Spool
| |--Clustered Index Delete(OBJECT:([test].[dbo].[t_p].[PK__t_p__195694DD]) WITH ORDERED PREFETCH)
| |--Sort(ORDER BY:([test].[dbo].[t_p].[id] ASC))
| |--Merge Join(Inner Join, MERGE:([test].[dbo].[t_g].[id])=([test].[dbo].[t_p].[g]), RESIDUAL:([test].[dbo].[t_p].[g]=[test].[dbo].[t_g].[id]))
| |--Table Spool
| |--Index Scan(OBJECT:([test].[dbo].[t_p].[ix_p_g]), ORDERED FORWARD)
|--Index Delete(OBJECT:([test].[dbo].[t_c].[ix_c_p]) WITH ORDERED PREFETCH)
|--Sort(ORDER BY:([test].[dbo].[t_c].[p] ASC, [test].[dbo].[t_c].[id] ASC))
|--Clustered Index Delete(OBJECT:([test].[dbo].[t_c].[PK__t_c__1C330188]) WITH ORDERED PREFETCH)
|--Table Spool
|--Sort(ORDER BY:([test].[dbo].[t_c].[id] ASC))
|--Hash Match(Inner Join, HASH:([test].[dbo].[t_p].[id])=([test].[dbo].[t_c].[p]))
|--Table Spool
|--Index Scan(OBJECT:([test].[dbo].[t_c].[ix_c_p]), ORDERED FORWARD)
Сначала SQL Server удаляет записи из t_g , затем присоединяется к ним удаляется с помощью t_p и удаляется из последнего, наконец, объединяются записи, удаленные из t_p , с помощью t_c и удаляются из t_c .
В этом случае гораздо эффективнее было бы объединение трех таблиц, и это то, что вы делаете со своим обходным путем.
Если вам станет легче, Oracle никоим образом не оптимизирует каскадные операции: это всегда ВЛОЖЕННЫЕ ЦИКЛЫ , и Бог поможет вам, если вы забыли создать индекс на ссылочный столбец.