So laden Sie Daten schneller mit Talend und SQL Server
Ich verwende Talend, um Daten in eine SQL-Server-Datenbank zu laden.
Es scheint, dass der schwächste Punkt meiner Arbeit nicht die Datenverarbeitung ist, sondern die effektive Last in meiner Datenbank, die nicht schneller ist als 17 Zeilen / Sek.
Der springende Punkt ist, dass ich 5 Jobs gleichzeitig starten kann und sie alle mit einer Geschwindigkeit von 17 Zeilen / Sek. laden.
Was könnte diese Langsamkeit erklären und wie könnte ich die Geschwindigkeit verbessern?
Danke
Neue Informationen:
Die Übertragungsgeschwindigkeit zwischen meinem Desktop und dem Server beträgt ungefähr 1 MByte
Mein Job verpflichtet sich alle 10 000
Ich benutze SQL Server 2008 R2
Und das Schema, das ich für meine Jobs verwende, ist wie folgt:
6 Antworten
Database INSERT OR UPDATE -Methoden sind unglaublich teuer, da die Datenbank nicht alle Commits gleichzeitig abarbeiten kann und sie Zeile für Zeile ausführen muss (ACID-Transaktionen erzwingen dies, weil sie versuchen, eine Insert-Operation auszuführen und dann alle fehlgeschlagen sind) der anderen Datensätze in diesem Commit würde ebenfalls fehlschlagen).
Stattdessen empfiehlt es sich bei großen Massenoperationen immer vorzubestimmen, ob ein Datensatz eingefügt oder aktualisiert wird, bevor das Commit an die Datenbank übergeben und dann zwei Transaktionen an die Datenbank gesendet werden.
Ein typischer Job, der diese Funktionalität benötigt, würde die Daten zusammenstellen, die INSERT OR UPDATEd sein müssen, und dann die Datenbanktabelle nach den vorhandenen Primärschlüsseln abfragen. Wenn der Primärschlüssel bereits vorhanden ist, können Sie ihn als UPDATE senden, andernfalls als INSERT . Die Logik dafür kann leicht in einer tMap -Komponente durchgeführt werden.
In diesem Job haben wir einige Daten, die wir INSERT OR UPDATE in eine Datenbanktabelle einfügen möchten, die einige bereits existierende Daten enthält:
Und wir möchten die folgenden Daten hinzufügen:
Der Job funktioniert, indem die neuen Daten in eine tHashOutput -Komponente geworfen werden, so dass sie mehrmals im selben Job verwendet werden kann (sie wird einfach in den Speicher geladen oder in großen Fällen auf die Festplatte zwischengespeichert).
Anschließend wird eine Datenmenge aus einer tHashInput -Komponente und direkt in eine tMap ausgelesen. Eine weitere Komponente tHashInput wird verwendet, um eine parametrisierte Abfrage für die Tabelle auszuführen:
Sie finden diese Anleitung zu Talend und parametrisierte Abfragen nützlich. Von hier aus werden die zurückgegebenen Datensätze (also nur diejenigen, die bereits in der Datenbank sind) als Nachschlagevorgänge für tMap verwendet.
Dies wird dann als INNER JOIN konfiguriert, um die Datensätze zu finden, die UPDATED mit den zurückgewiesenen Elementen aus dem einzufügenden INNER JOIN enthalten müssen:
Diese Ausgaben fließen dann so, dass tMySQLOutput -Komponenten je nach Bedarf in UPDATE oder INSERT getrennt werden. Und schließlich, wenn der Haupt-Subjob abgeschlossen ist, ändern wir commit .
Ich denke, dass @ydaetskcoRs Antwort aus teorischer Sicht perfekt ist (teile Zeilen, die Insert von denen zu Update brauchen) und gibt dir eine funktionierende ETL-Lösung, nützlich für kleine Datenmengen (einige tausend Zeilen).
>Die Suche nach der Möglichkeit, zu entscheiden, ob eine Zeile aktualisiert werden muss oder nicht, ist in ETL teuer, da alle Daten zwischen dem Talend-Rechner und dem DB-Server hin und her gehen.
Wenn Sie zu einigen Hunderttausenden oder sogar Millionen von Datensätzen gelangen, müssen Sie von ETL zu ELT übergehen: Sie laden Ihre Daten einfach in eine Temp (Staging) -Tabelle, wie von @Balazs Gunics vorgeschlagen, und dann manipulieren Sie mit SQL es.
In diesem Fall werden Sie nach dem Laden Ihrer Daten (nur INSERT = schnell, noch schneller mit BULK LOAD-Komponenten) einen LEFT OUTER JOIN zwischen der temporären Tabelle und dem Ziel ausgeben, um die bereits vorhandenen Zeilen zu teilen (Update erforderlich) und die anderen.
Diese Abfrage gibt Ihnen die Zeilen, die Sie einfügen müssen:
%Vor%Diese andere Zeile müssen Sie aktualisieren:
%Vor%Dies ist um Größenordnungen schneller als bei ETL, aber Sie müssen SQL verwenden, um Ihre Daten zu bearbeiten, während Sie in ETL Java verwenden können, da ALLE Daten zum Talend-Server übertragen werden Schritt auf dem lokalen Rechner, um die Daten in Java vorzuverarbeiten (um sie zu bereinigen und zu validieren) und dann in der DB zu starten, wo Sie sie verwenden, um sie auf die richtige Weise zu laden.
Hier sind die ELT JOB Screenshots.
Basierend auf Ihrer Notiz, dass Einfügungen eine Größenordnung schneller sind als Updates (4000 vs 17 / sec) - Es sieht so aus, als müssten Sie Ihre DB-Indizes betrachten. Das Hinzufügen eines Index, der mit Ihren Update-Parametern übereinstimmt, könnte Ihre Updates erheblich beschleunigen. Natürlich kann dieser Index Ihre Einsätze ein wenig verlangsamen.
Sie können auch den Abfrageausführungsplan für Ihre Aktualisierungsabfrage anzeigen, um festzustellen, ob sie Indizes verwendet. Wie erhalte ich einen Abfrageausführungsplan?
>Sie sollten eine Staging-Tabelle erstellen, in die Sie die Zeilen einfügen.
Basierend auf dieser Staging-Tabelle führen Sie eine DELETE-Abfrage mit t * SQLrow durch.
%Vor%Damit sind die Zeilen, die Sie aktualisieren wollten, nicht mehr vorhanden.
%Vor%Dadurch werden alle neuen / geänderten Zeilen verschoben.
Ich habe herausgefunden, wo dieses Leistungsproblem herkommt.
Ich mache ein INSERT OR UPDATE , wenn ich es durch ein einfaches INSERT ersetze, geht die Geschwindigkeit auf 4000 Zeilen / s.
Scheint es wie ein akzeptables Tempo?
Wie auch immer, ich brauche meine INSERT OR UPDATE , also denke ich, dass ich feststecke.
Ich hatte dasselbe Problem beim Laden von Daten in einen DB2-Server. Ich hatte auch das Commit auf 10000 gesetzt, aber sobald ich die Option zum Batch (auf der gleichen Komponente Optionen Bildschirm) ausgewählt Leistung drastisch verbessert. Als ich den Commit und den Batch auf 20000 verschoben habe, ging der Job von 5 Stunden auf unter 2 Minuten.
Tags und Links sql-server talend database-performance upsert