Schlagwort-Archive: CSV

Performancevergleich: Enzelne SQL-Statements vs. MySQL LOAD DATA INFILE vs. Prepared Statements

Performance: Einzelne SQL-Statements

Was macht das Programm?

  1. Aus einer großen XML-Datei werden per SAXParser Datensätze eingelesen. Insgesamt gibt es 1.109.270 „Datensätze“ in der XML-Datei.
  2. Aus jedem Datensatz werden 2 SQL-Queries erzeugt: Ein REPLACE-Statement zum Einfügen/Aktualsieren von Stammdaten und ein INSERT-Statement zum Einfügen von Bewegungsdaten

Es werden also kontinuierlich SQL-Statements abgesetzt.

Laufzeit des Programms

41 Minuten und 36 Sekunden

Performance: Generierung der CSV-Datei & LOAD DATA INFILE

Was macht das Programm?

  1. (identisch mit vorherigem Programm:) Aus einer großen XML-Datei werden per SAXParser Datensätze eingelesen. Insgesamt gibt es 1.109.270 „Datensätze“ in der XML-Datei.
  2. Aus jedem Datensatz werden 2 CSV-Dateien um einen Datensatz erweitert. Die erste Datei enthält die Daten für die Stammdaten-Tabelle, die zweite Datei enthält die Bewegungsdaten.
  3. Zwei LOAD DATA INFILE Befehle zum Einlesen der CSV-Dateien.

Das Programm erzeugt also zuerst 2 Dateien und greift erst zum Schluss auf die Datenbank zu.

Laufzeit des Programms

30 Minuten und 9 Sekunden

Performance: Nur LOAD DATA INFILE

Wenn wir annehmen, dass die 2 CSV-Dateien schon vorhanden sind und nur noch die LOAD DATA INFILE-Befehle ausgeführt werden müssen, so sind die immerhin 2.218.540 Datensätze innerhalb von

2 Minuten und 23 Sekunden

importiert.

Performance: Prepared Statements

Prepared Statements sollten nicht nur aus sicherheitsrelevanten Überlegungen eingesetzt werden, sondern bringen – richtig implementiert – auch einen Performance-Vorteil. Dieser Vorteil lässt sich so erklären, dass 1x das SQL-Statement mit „?“ als Platzhalter an das DBMS übertragen wird und danach werden für jeden Datensatz nur noch die Parameter übertragen, die in die entsprechenden Platzhalter durch das DBMS eingefügt werden. Es fallen also weniger zu übertragene Daten an, aber vor allem können die Daten so übertragen und verarbeitet werden, dass sie nicht mehr durch das DB-System interpretiert bzw. konvertiert werden müssen. Das Ergebnis kann sich sehen lassen:

Das Programm ist identisch mit dem Programm, welches kontinuierlich, einzelne SQL-Statements absetzt. Allerdings ist es so programmiert, dass es die Performance-Vorteile der Prepared Statements nutzt.

Laufzeit des Programms

28 Minuten und 30 Sekunden

Fazit

Bei so großen Datenmengen, die sequentiell eingelesen werden, kommt es auf die richtige Wahl der Methode an:

Vorteile Prepared Statements:

  • sind einfach zu implementieren
  • sind  im Performance-Vergleich noch ein Stück weit schneller als CSV-Dateien zu erstellen und per LOAD DATA INFILE einzulesen

Nachteile Prepared Statements:

  • die Datenbank wird über die gesamte Zeit stark belastet

Vorteile Generierung von CSV & LOAD DATA INFILE:

  • die Datenbank wird nur für kurze Zeit beansprucht
  • die CSV-Datei kann nach dem Generieren gespeichert bleiben und zu Diagnosezwecke bzw. wiederholten Datenimport verwendet werden

Nachteile Generierung von CSV & LOAD DATA INFILE:

  • kompliziertere Implementierung
  • zusätzlicher Festplattenverbrauch durch CSV-Dateien
  • geringfügig langsamer als Prepared Statements

Java: Erstellen großer CSV-Dateien zum Datenbank-Import

Anstatt Millionen von INSERT oder UPDATE-Befehlen an die Datenbank zu schicken, um eine Tabelle zu aktualisieren, bietet es sich bei großen Datenmengen an, eine CSV-Datei zu erstellen, welche dann über LOAD DATA INFILE mit extrem hoher Geschwindigkeit eingelesen wird.

Erstellen von CSV-Dateien mit Java

In Java bietet sich zum Erstellen von CSV-Dateien die Klasse Super CSV an. Um eine Datei zu erzeugen, benötigt man als erstes einen CSVMapWriter:
[code lang=’java‘]CsvMapWriter writer = new CsvMapWriter(new OutputStreamWriter(new FileOutputStream(„C:\meineDatei.csv“, true),“UTF-8″), CsvPreference.EXCEL_PREFERENCE);[/code]
Anstatt mit einem OutputStreamWriter zu arbeiten, kann man auch einen FileWriter verwenden. Da man bei letzterem aber kein Encoding („UTF-8“) angeben kann und in der Datenbank die Zeichen UTF-8-kodiert sind, müssen wir den Stream verwenden.
Interessant ist der zweite Parameter des FileOutputStream, der angibt, dass die Daten, die eingefügt werden, angehängt werden und nicht vorher gelöscht werden.
Die Einstellung CsvPreference.EXCEL_PREFERENCE legt fest, welche Trennzeichen in der CSV-Datei verwendet werden – wenn man andere Trennzeichen verwenden möchte, muss der später behandelte LOAD DATA INFILE-Befehl angepasst werden.

Als nächstes werden die Textfelder für die CSV-Datei definiert:
[code lang=’java‘]final String[] header = new String[] { „id“, „name“, „adressfeld“ };[/code]
Unsere Datensätze bestehen also aus 3 Feldern.

Um die CSV-Datei mit Daten zu füllen sind folgende Zeilen notwendig:
[code lang=’java‘]final HashMap data = new HashMap();
data.put(header[0], this.id);
data.put(header[1], this.name);
this.adressfeld = this.adressfeld.replace(„\\“, „\\\\“);
data.put(header[2], this.adressfeld);[/code]
Die Daten werden in einer HashMap gespeichert und per data.put eingefügt. Was mich etwas überraschte war, dass man das Backslash maskieren muss, ansonsten geht es in der Verarbeitung verloren. Eigenartig, weil man diese Funktionalität sicherlich problemlos in Super CSV hätte integrieren können.

Abschließend werden die Daten dem CSVMapWriter übergeben und dieser wieder geschlossen:
[code lang=’java‘]writer.write(data, header);
writer.close();[/code]

Und mehrere Datensätze?

Obiges Beispiel ist für einen einzelnen Datensatz, der in einer CSV-Datei gespeichert wird. Wenn man mehrere Datensätze einfügen möchte, müssen sämtliche Schritte (Anlegen eines CSVMapWriter, Daten vorbereiten, Daten einfügen) für jeden Datensatz wiederholt werden.

Den CSVMapWriter 1x anzulegen und am Ende per .close zu schließen, funktioniert nicht – meine Versuche mit der Super CSV-Klasse haben gezeigt, dass dann Datensätze verloren gehen bzw. abgeschnitten werden.

Einlesen der CSV mit LOAD DATA INFILE

Um die so erstellte Datei mit LOAD DATA INFILE in die Datenbank zu importieren, muss folgender Befehl eingetragen werden:
[code lang=“sql“]LOAD DATA [LOCAL] INFILE ‚C:\\meineDatei.csv‘
[REPLACE]
INTO TABLE meine_tabelle
FIELDS TERMINATED BY ‚,‘
OPTIONALLY ENCLOSED BY ‚\“‚
LINES TERMINATED BY ‚\n‘
(id, name, adressfeld);[/code]

Vorab: Der Befehl wird so nicht funktionieren, da die Bestandteile innerhalb der eckigen Klammern optional sind:

  • LOCAL wird dann angegeben, wenn die CSV-Datei vom Clientprogramm auf dem Clienthost gelesen und an den MySQL-Server geschickt wird. Fehlt LOCAL, liegt die Datei auf dem MySQL-Server
  • REPLACE nutzt man, wenn die eingelesene Datei nicht nur neue Datensätze importiert, sondern auch bereits vorhandene überschreibt – also UPDATES durchführt

Alle/Weitere Optionen zu dem LOAD DATA INFILE-Befehl gibt es im MySQL-Handbuch.

Performancevergleich

Ein Performancevergleich ist hier zu finden.