Schlagwort-Archive: Performance

MySQL: UPDATE auf mehreren Tabellen und Subqueries

Computer, ,

Wer sich mal ein bißchen näher mit SQL beschäftigt, wird über die Ausdrucksmächtigkeit und Performance erstaunt sein. SQL kann eben doch mehr als simple SELECT-Anweisungen zu verarbeiten.

Mal ein Beispiel, was man alles mit UPDATE-Anweisungen machen kann:

Wir haben folgende Tabellen

1. Team

  • Teamid
  • Teamname
  • AnzahlMitglieder

2. User

  • Userid
  • Username
  • Teamid

Nun wollen wir das noch leere Feld Team.AnzahlMitglieder aus der Tabelle User berechnen. Naiver Ansatz ist wahrscheinlich folgendes UPDATE-Statement, wenn man sich vorher klar macht, dass man Subqueries verwenden kann, um Werte zu setzen:
[code lang=’sql‘]UPDATE Team
SET
AnzahlMitglieder =
(SELECT COUNT(*)
FROM User
WHERE User.Teamid = Team.Teamid )[/code]

Problem an dieser Abfrage ist allerdings, dass für jede Zeile, die aktualisiert werden soll, das Subquery ausgeführt wird. Bei kleineren Datenmengen macht das nichts, wenn es allerdings Millionen an Datensätze sind, braucht das UPDATE ewig.

Wie könnte die Lösung aussehen? Richtig, man führt den Subquery nur 1x für alle Teams aus und selektiert den jeweiligen Eintrag dann für das entsprechende Team:
[code lang=’sql‘]UPDATE
Team,
(SELECT teamid,
COUNT(*) as anzahl,
FROM User) AS usersub
SET
Team.AnzahlMitglieder = usersub.anzahl
WHERE
Team.Teamid = usersub.teamid)[/code]

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

Java, , , , ,

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