Optimieren der SQL Server Merge-Anweisung ausgeführt wird, die mit Millionen von Datensätzen

Verwende ich SQL Server 2014 und benötigen ein update, eine neue Hinzugefügt, die datetime-Typ-Spalte in einer Tabelle. Es gibt zwei Tabellen miteinander in Beziehung (beide haben > 30 Millionen Datensätze):

TableA: 

CategoryID, itemID, dataCreated, deleted, some other string properties.

Diese Tabelle enthält Vielfache Datensätze für jedes Element mit unterschiedlichen datecreated.

TableB:

CategoryID, itemID, LatestUpdatedDate (This is the new added column)

beide categoryID und itemID sind Teil des index dieser Tabelle.

Aktualisieren tableB ist LatestUpdatedDate aus der Tabelle Ein, die auf abgestimmt CategoryID und ItemID ich verwendet die folgenden merge-Anweisung:

merge [dbo].[TableB] with(HOLDLOCK) as t
using 
(
    select CategoryID,itemID, max(DateCreated) as LatestUpdatedDate 
    from dbo.TableA 
    where TableA.Deleted = 0
    group by CategoryID,itemID
) as s on t.CategoryID = s.CategoryID and t.itemID = s.itemID

when matched then
    update
    set t.LatestUpdatedDate = s.LatestUpdatedDate

when not matched then
    insert (CategoryID, itemID, LatestUpdatedDate)
    values (s.CategoryID, s.itemID)

Angesichts der Tatsache, dass Millionen Datensätze in der Tabelle, Wie kann ich diese optimieren Skript? Oder gibt es eine andere Möglichkeit um die Tabelle zu aktualisieren, mit mehr Leistung?

Hinweis: Dies ist ein one-off-script und DB ist am Leben, es wäre ein trigger Hinzugefügt, um tableA gegen einfügen, aktualisieren Sie das Datum im tableB in die Zukunft.

  • Die entscheidenden Fragen sind: haben die änderungen zu atomic? Gibt es eine Frist sperren Sie können diese Tabelle ausschließlich?Wenn es nicht atomar, dann würde ich aufteilen in separate update und insert Aussagen. Auf diesem Weg bist du brechen Ihre Arbeit in der Hälfte. Ich habe verbrannt worden, von merge vor, Leistung klug, so dass ich nun Steuern Weg von ihm. Sie können auch die set rowcount trick, um das update nur kleinere Mengen auf ein mal, anstatt sperren Sie die gesamte Tabelle in einem Rutsch.
  • Ich sollte erwähnen, dass dies eine live-Datenbank, aber ich kann sicherlich führen Sie das Skript während der Nacht/Wochenende, wenn es viel weniger Verkehr. was für eine Leistung Ausgabe, die Sie haben, bevor Sie mit Zusammenführen? Ich bin nicht sicher, wie lange es dauern wird, führen Sie diese auf dieser Ebene der Tabellen. Minuten, Stunden?
  • Mit dem Zusammenführen der Leistung wurde mit den when not matched Teil. Sie brauchen, um herauszufinden, ob die änderungen notwendig werden atomic. Das heißt: ist es OK, wenn die Tabelle bekommt nur graduelle Veränderungen vorgenommen, es immer sagen, ein paar Stunden, oder muss die Tabelle vollständig aktualisiert in einem Rutsch (für die Konsistenz). Ihre Anwendung brechen, wenn Zeile y wird aktualisiert, aber nicht die Zeile x? Beachten Sie, dass das zu langsam ist (egal auf welche Weise Sie es tun) wenn Sie nicht über Indizes für die join-Spalten. Das hinzufügen von Indizes ist nicht eine Tabelle ändern, die unterbrochen werden, eine Anwendung, und lassen Sie Sie anschließend.
  • Wie viele Zeilen gibt es in der Untertabelle s`? Wie viele in TableA? Es lohnt, sich fallenlassen, dass in eine Tabelle ersten. Auch sind Sie sich bewusst, wht HOLDOCK zu tun? Weißt du, ob es Auswirkungen auf die Anwendung über den Rand?
  • Vielen Dank Nick. Die änderung muss nicht atomar, da ich nicht aktivieren, der code im Zusammenhang, bis Daten bereit sind. Beide Tabellen habe > 30 Millionen Zeilen und tableA ist über zweimal als tableB. HOLDOCK wird hier verwendet, um zu verhindern, dass die Parallelität Problem wie beschrieben von hier: link. Ich Teste mit einigen lokalen gefälschte Daten mit rund 50 Millionen Zeilen und das Skript abgeschlossen innerhalb von 10 min. Scheint, es ist akzeptabel für ein one-off-Betrieb.
InformationsquelleAutor xingkong | 2017-02-12
  1. 2

    Als pro Optimierung der MERGE-Anweisung Leistung, das beste, was Sie tun können, ist:

    • Erstellen Sie einen index für die join-Spalten in der Quell-Tabelle, ist einzigartig und abdecken.
    • Erstellen eines eindeutigen gruppierten index für die join-Spalten in der Zieltabelle.

    Erhalten Sie möglicherweise eine Verbesserung der Leistung während MERGE1 durch das anlegen eines index auf TableA auf (Deleted, CategoryID, itemID) INCLUDE(DateCreated). Da dies jedoch ist ein einmalige Vorgang, den Ressourcen (Zeit, CPU, Speicherplatz), die erforderlich ist, um diesen index erstellen, wird wahrscheinlich nicht Offsetdruck die performance-Gewinne vis-a-vis die Abfrage ausgeführt ist und sich auf Ihrem bereits vorhandenen index.

    • ja, dies ist ein ein-aus-Betrieb, würde ich vermeiden, zu machen, jede änderung an der Tabelle selbst. wird es hilfreich sein, wenn ich es aufgeteilt in mehrere verschmelzen, Zustand wie categoryID<500, categoryID>500...
    • test mit Tisch >50M Zeilen, die index bauen, und es dauert etwa 2min zu vervollständigen. scheint in Ordnung. Danke.
    InformationsquelleAutor Serge
Schreibe einen Kommentar