Frage Ist SQL Server DRI (ON DELETE CASCADE) langsam?


Ich habe einen wiederkehrenden "Bugreport" (Perf-Problem) in einem unserer Systeme im Zusammenhang mit einer besonders langsamen Löschoperation analysiert. Lange Rede, kurzer Sinn: Es scheint, dass die CASCADE DELETE Schlüssel waren größtenteils verantwortlich, und ich würde gerne wissen (a) wenn das Sinn macht, und (b) warum das der Fall ist.

Wir haben ein Schema von, sagen wir, Widgets, die die Wurzel eines großen Graphen verwandter Tabellen und verwandter Tabellen und so weiter sind. Um ganz klar zu sein, wird aktiv von der Löschung aus dieser Tabelle abgeraten; es ist die "nukleare Option" und die Nutzer machen sich keine Illusionen über das Gegenteil. Trotzdem muss es manchmal einfach gemacht werden.

Das Schema sieht ungefähr so ​​aus:

Widgets
   |
   +--- Anvils [1:1]
   |    |
   |    +--- AnvilTestData [1:N]
   |
   +--- WidgetHistory (1:N)
        |
        +--- WidgetHistoryDetails (1:N)

Spaltendefinitionen sehen wie folgt aus:

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, ...)

Nichts zu gruselig, wirklich. EIN Widget kann verschiedene Arten sein, ein Anvil ist ein spezieller Typ, so dass die Beziehung 1: 1 (oder genauer 1: 0..1) ist. Dann gibt es eine große Menge an Daten - vielleicht Tausende von Zeilen AnvilTestData pro Anvil gesammelt im Laufe der Zeit, Umgang mit Härte, Korrosion, genaues Gewicht, Hammer-Kompatibilität, Usability-Probleme und Aufpralltests mit Cartoon-Köpfe.

Dann alle Widget hat eine lange, langweilige Geschichte der verschiedenen Arten von Transaktionen - Produktion, Lagerbewegungen, Verkäufe, Mängeluntersuchungen, RMAs, Reparaturen, Kundenbeschwerden, etc. Es kann 10-20k Details für ein einzelnes Widget oder gar keine geben, abhängig von sein Alter.

Es ist also nicht überraschend, dass es ein CASCADE DELETE Beziehung auf jeder Ebene hier. Wenn ein Widget muss gelöscht werden, es bedeutet, dass etwas schrecklich falsch gelaufen ist und wir alle Datensätze des Widgets löschen müssen, die jemals existierten, einschließlich seiner Historie, Testdaten usw. Wiederum nukleare Option.

Beziehungen sind alle indiziert, Statistiken sind aktuell. Normale Abfragen sind schnell. Das System tendiert dazu, für alles außer Deletes ziemlich reibungslos zu summen.

Um hier auf den Punkt zu kommen, können wir aus verschiedenen Gründen nur ein Widget nach dem anderen löschen, sodass eine Löschanweisung wie folgt aussehen würde:

DELETE FROM Widgets
WHERE WidgetID = @WidgetID

Ziemlich einfach, harmlos suchen löschen ... das dauert mehr als 2 Minuten, für ein Widget mit keine Daten!

Nachdem ich die Ausführungspläne durchgespielt hatte, konnte ich endlich die AnvilTestData und WidgetHistoryDetails löscht als die Unteroperationen mit den höchsten Kosten. Also experimentierte ich damit, die CASCADE (aber die tatsächliche FK beibehalten, nur auf NO ACTION) und das Skript als etwas ähnlich dem folgenden umschreiben:

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

Beide dieser "Optimierungen" führten zu erheblichen Beschleunigungen, die sich jeweils um fast eine volle Minute von der Ausführungszeit abhoben, so dass die ursprüngliche 2-Minuten-Löschung nun ungefähr 5-10 Sekunden dauert - zumindest für Neu Widgets, ohne viel Geschichte oder Testdaten.

Nur um ganz klar zu sein, gibt es noch eine CASCADE von WidgetHistory zu WidgetHistoryDetails, wo der Fanout am höchsten ist, habe ich nur den Ursprung entfernt Widgets.

Eine weitere "Verflachung" der Kaskadenbeziehungen führte zu immer weniger dramatischen, aber immer noch spürbaren Beschleunigungen bis hin zum Löschen eines Neu Widget war fast augenblicklich, sobald alle Kaskadenlöschungen zu größeren Tabellen entfernt und durch explizite Löschungen ersetzt wurden.

Ich benutze DBCC DROPCLEANBUFFERS und DBCC FREEPROCCACHE vor jedem Test. Ich habe alle Auslöser deaktiviert, die weitere Verlangsamungen verursachen könnten (obwohl diese sowieso im Ausführungsplan auftauchen würden). Und ich teste auch gegen ältere Widgets und bemerke auch dort eine deutliche Beschleunigung; Löschungen, die früher 5 Minuten dauerten, dauern nun 20-40 Sekunden.

Jetzt bin ich ein begeisterter Anhänger der Philosophie "SELECT is not broken", aber es scheint einfach keine logische Erklärung für dieses Verhalten zu geben, außer der vernichtenden, verblüffenden Ineffizienz der CASCADE DELETE Beziehungen.

Meine Fragen sind also:

  • Ist dies ein bekanntes Problem bei DRI in SQL Server? (Ich konnte bei Google oder hier in SO keine Hinweise auf solche Dinge finden; ich vermute, die Antwort ist nein.)

  • Wenn nicht, gibt es eine andere Erklärung für das Verhalten, das ich sehe?

  • Wenn es ein bekanntes Problem ist, warum ist es ein Problem, und gibt es bessere Workarounds, die ich verwenden könnte?


7
2018-03-26 17:10


Ursprung


Antworten:


SQL Server ist am besten bei Set-basierten Operationen, während CASCADE Löschungen sind ihrer Natur nach datensatzbasiert.

SQL Serverversucht im Gegensatz zu den anderen Servern die unmittelbaren set-basierten Operationen zu optimieren, arbeitet jedoch nur eine Ebene tief. Die Datensätze müssen in den übergeordneten Tabellen gelöscht werden, um sie in den untergeordneten Tabellen zu löschen.

Mit anderen Worten: Kaskadierungsoperationen werden von oben nach unten ausgeführt, während Ihre Lösung nach unten arbeitet, was auf Set-Based basiert und effizienter ist.

Hier ist ein Beispielschema:

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)

, diese Abfrage:

DELETE
FROM    t_g
WHERE   id > 50000

und sein Plan:

  |--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)

Zuerst, SQL Server Löscht Datensätze aus t_g, verbindet dann die mit gelöschten Datensätze t_p und löscht aus letzterem schließlich Datensätze, die aus gelöscht wurden t_p mit t_c und löscht von t_c.

Eine einzelne Join mit drei Tabellen wäre in diesem Fall viel effizienter, und das ist, was Sie mit Ihrer Problemumgehung tun.

Wenn es dich besser fühlen lässt, Oracle optimiert Kaskadenoperationen in keiner Weise: Sie sind immer NESTED LOOPS und Gott hilft Ihnen, wenn Sie vergessen haben, einen Index für die Referenzspalte zu erstellen.


8
2018-03-26 17:25