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

6 Gedanken zu „Performancevergleich: Enzelne SQL-Statements vs. MySQL LOAD DATA INFILE vs. Prepared Statements

  1. Sorry ich will echt nicht nerven, könnte es ja auch selber testen, aber es interessiert mich wirklich und Du hast die Testumgebung.

    Du schreibst:
    „Aus jedem Datensatz werden 2 SQL-Queries erzeugt, die per PreparedStatements abgesetzt werden: Ein REPLACE-Statement zum Einfügen/Aktualsieren von Stammdaten und ein INSERT-Statement zum Einfügen von Bewegungsdaten“

    Der Gag bei Prepared Statements unter Performance Gesichtspunkten ist ja unter anderem, dass man das Statement nur einmal absendet und dann n-mal die Parameter überträgt und das bereits übertragene Statement n-mal executed/ausführt. Kurz 2 Statements, die nur 2 mal von Mysql geparst werden müssen, aber 2 Millionen executes.

    Vielleicht hast Du das aber auch berücksichtigt und es ist nur nicht deutlich geworden. Dann bedanke ich mich, dass ich hier mit meinem Halbwissen klugscheißen durfte.

    Das wäre echt geil, wenn Du Dich dazu noch äußern könntest. Aus Performance Gesichtspunkten habe ich vorher LOAD DATA noch gar nicht betrachtet.

    Gruß Felix

    1. Moin Felix!

      Ach quatsch, du nervst doch nicht! ;) Ich finde das Thema ja auch interessant.

      Ich habe die Prepared Statements alleine aus Sicherheitsgründen verwendet. Auch wenn in der Uni Prepared Statements behandelt wurden, so ist auf die Verbesserung der Performance überhaupt nicht eingegangen worden. Da habe ich wieder etwas dazu gelernt. Danke!

      Mein Programm sieht derzeit so aus, dass bei jedem neuen Datensatz ein neues Prepared Statement erzeugt wird, die Parameter werden übergeben, das Query wird ausgeführt und beim nächsten Datensatz wird wieder ein Prepared Statement erzeugt. Aus Performance-Sicht sicherlich verbesserungswürdig.

      Ich schaue mal, ob ich mein Programm so umschreiben kann, dass die Performance-Verbesserung der Prepared Statements zum Tragen kommen.

      Ich melde mich dann. :)
      crille

  2. @crille: Cool und danke. Jetzt wäre noch interessant, wielange SAX brauchen würde, wenn es nur die XML-Datei parsen muss und keine CSV-Write-Aktionen oder executes() auslösen muss, also leere Funktionen aufruft. Dann wüssten wir, wieviel Zeit SAX tatsächlich benötigt. 2 Minuten LOAD DATA INFILE sind ja echt beeindruckend.

    1. Moin Felix!

      Das alleinige Parsen der XML-Datei dauert 1 Minute und 30 Sekunden.

      Da wurden dann aber auch keine Daten zum Schreiben in die CSV-Datei aufbereitet – also UTF-8 Kodierung, Maskierung von Backslashes, Formatierung von Dates in Timestamps etc.
      Im CSV-Fall werden die Daten eines Datensatzes zudem in einer Hashmap abgespeichert (Laufzeit der CSV-Aufbereitung ohne Schreiben der CSV-Datei 10 Minuten und 12 Sekunden) – während die Parameter der PreparedStatements sicherlich auch noch konvertiert werden (Laufzeit der PreparedStatement-Aufbereitung ohne Execute 10 Minuten 59 Sekunden).

      Wenn man nun eine bereinigte „Nur Prepared Statement“-Laufzeit errechnen will (ähnlich der „Nur LOAD DATA INFILE“), ergäbe sich also:
      28 Minuten und 30 Sekunden – 10 Minuten 59 Sekunden = 17 Minuten und 31 Sekunden

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.