Vor jeder Optimierungsmaßnahme ruft man sich nochmal das Erste Grundgesetzt der Optimierung ins Gedächtnis, Donald E. Knuth hat es formuliert:
Premature optimization is the root of all evil.
Man sollte einfachen, klaren und selbstbeschreibenden Code immer der (vermeintlich) optimierten Variante vorziehen. Etwas detaillierter lässt sich diese Regel auch anders formulieren:
Die Granularität der Datenbankkommunikation
Datenbanken repräsentieren transaktionale Netzwerkressourcen. Jede Datenbankkommunikation beinhaltet deshalb mindestens einen Netzwerkzugriff und muss von einem Transaktionsmanager angenommen, verwaltet und abgearbeitet werden. Hier ist die richtige Granularität der Datenbankkommunikation entscheidend und zwei Extreme sind zu vermeiden:
Datenbankseitig wird ein Aufruf in zwei Phasen abgearbeitet. Zunächst wird das Statement geparsed und auf Wohlgeformtheit bezüglich des vorhandenen Tabellenschemas geprüft. Wenn möglich versucht die Datenbank schon in dieser Phase Optimierungsstrategien vorzubereiten ("query planning"). In der zweiten Phase werden die übergebenen Parameter eingesetzt und das Statement wird in einer Transaktion abgewickelt, die Antwort wird zusammengestellt und an den Client zurückgesendet.
Clients unterstützen dieses Vorgehen, indem sie der Datenbank Anfragen aufgeteilt in Struktur und Parameter senden. Die Datenbank hat nun die Möglichkeit, die Struktur einmalig zu prüfen und zu cachen. Bei weiteren Aufrufen mit derselben Struktur kann dann die Überprüfung der Struktur entfallen und es werden immer nur die neuen Parameter eingesetzt. Derart aufbereitete Statements heißen Prepared Statements und es ist die wichtigste Optimierungsmaßnahme überhaupt. Auf Prepared Statements sollte deshalb nur aus gutem Grund verzichtet werden.
Ein Beispiel für einen solchen Verzicht wäre eine komplexe clientseitige Suchmaske, aus deren Eingaben das fertige Statement erst bestimmt wird. Hier bestimmt also der Client erst durch seine Vorgaben, welche Struktur das Statement am Ende haben wird. Dennoch kann auch hier mit Prepared Statements optimiert werden, indem die am häufigsten vorkommenden Statements identifiziert und als Prepared Statements abgebildet werden.
Prepared Statements können übrigens clientseitig verworfen werden, denn das Caching erfolgt ja durch die Datenbank. Typischer JDBC Code sieht dann so aus:
PreparedStatement ps = con.prepareStatement("UPDATE Person SET name = ? WHERE personId = ?");
ps.setInt(2, 99);
ps.setString(1, "John Doe");
ps.executeUpdate();
ps = null; // das Prepared Statement kann verworfen werden, gecached wird sowieso datenbankseitig
An diesem kurzen Beispiel kann man auch sehen, dass Prepared Statements clientseitig begrenzt Typsicherheit liefern und auch einfacher zu lesen sind, da auf Hochkommas komplett verzichtet wird. Außerdem werden Angriffe durch SQL-Injection erheblich erschwert.
Prepared Statements dürfen clientseitig durchaus auch "dynamisch" (zur Laufzeit) erzeugt werden. Allerdings muss man darauf achten, dass dabei nicht zu viele unterschiedliche "Permutationen" möglich werden, um die Wirksamkeit des Datenbank-Caches nicht zu gefährden. Die Datenbank muss entscheiden, welche Prepared Statements im Cache verbleiben. Oftmals wird dabei eine LRU-Strategie benutzt. Wird die Datenbank nun mit unzähligen unterschiedlichen Prepared Statements geflutet, verliert der Datenbankcache seine Wirkung.
Batching verringert die Zahl der Datenbank/Netzwerk-Zugriffe indem mehrere gleichartige Statements in eine Batch geschrieben und dann in einem Aufruf zur Datenbank geschickt und dort verarbeitet werden. Clientseitig wird dazu die addBatch() Methode des Prapared Statements genutzt. Das Beispiel von eben sieht dann im Batchbetrieb so aus:
PreparedStatement ps = con.prepareStatement("UPDATE Person SET name = ? WHERE personId = ?");
ps.setInt(2, 99);
ps.setString(1, "John Doe");
ps.addBatch();
ps.setInt(2, 101);
ps.setString(1, "Max Muster");
ps.addBatch();
ps.executeUpdate();
Das entspricht einer seriellen Abarbeitung mit den gegebenen Parametern. Beide Strategien werden als Batching (Batch Select, Batch Update, ...) bezeichnet.
Eine dritte Möglichkeit für Batch-Verarbeitung ist das Verketten von Statements:
PreparedStatement ps = con.prepareStatement("SELECT name FROM Person WHERE personId = ? ; SELECT name FROM Person2 WHERE personId = ?");
ps.setInt(1, 99);
ps.setInt(2, 101);
ps.executeQuery();
Das Laden von Objektgrafen und das N+1 Problem
Betrachtet sei folgender Pseudo-Code:
SELECT Organisation FOR ALL Organisation SELECT Person WHERE organisationID = ? LOOP COMMITFindet das erste SELECT 10000 Organisationen werden in der Schleife weitere 10000 SELECTs abgesetzt. Das ist das N+1 Problem und es passiert, weil ein (unproblematisches) Iterieren über Objektinstanzen ein (problematisches) Iterieren über darin enthaltenen Abhängigkeiten in Form von Datenbankzugriffen und Netzwerk-Roundtrips auslöst. Datenbankseitig müssen alle 10000 SELECTs transaktional verwaltet und abgearbeitet werden.
Für solche Szenarien nutzt man die IN-Klausel im SELECT Statement (oder gleichwertig Batching), und lädt mit einem Netzwerk-Roundtrip und einer Transaktion viele Personen:
SELECT Organisation
FOR ALL Organisation
SELECT Person WHERE organisationID IN (?,?,?,?,?,?,?,?,?,?)
FOR 10 Organisation
SET organisationID
LOOP
COMMIT
LOOP+10
Im Beispiel werden 10 Personen in einem Rutsch geladen - das N+1 Problem ist zum N/10+1 Problem geschrumpft. Ein solches SELECT heißt Batch Select und die Zahl 10 im Beispiel ist die Batchsize.
Festlegen der @BatchSize mit Hibernate
Hibernate bietet dieses Feature als Automatismus über die Angabe der @BatchSize auf Klassen- oder Feldebene. Bei Selects sucht Hibernate in seinem Persistence Context nun nach nicht initialisierten Entitäten und versucht, diese gebündelt zu initialisieren.
@Entity
public class Person {b
//
@Id
@GeneratedValue
private long id;
//
private String name;
//
@OneToMany(mappedBy="person", cascade=CascadeType.ALL)
@BatchSize(size=3)
private Collection<Organisation> organisations = new HashSet<Organisation>();
//
// Getter und Setter etc...
}
Nehmen wir an, im gegebenen Beispiel führt die Suche nach Personen zu 10 Treffern. Diese liegen zunächst unvollständig initialisiert im Persistence Context. Der erste Zugriff auf eine Entität Person veranlasst Hibernate, nach bis zu 3 nicht initialisierten Personen zu suchen, und diese in einem gemeinsamen Select (das mit IN (?, ?, ?) enden wird) zu initialisieren. Die optimale Batchsize hängt natürlich vom fachlichen Problem ab und kann am besten durch Ausprobieren optimiert werden. Immerhin kostet das Suchen nach nicht vollständig initialisierten Entitäten Zeit und es werden möglicherweise Entitäten initialisiert, die nie gebraucht werden. Eine zu große Batchsize kann deshalb kontraproduktiv wirken.
Leider sind Angaben zur Batchsize nicht Bestandteil der JPA.
Join Fetch bietet eine weitere Möglichkeit, dem N+1 Problem zu begegnen. Dabei wird mit einem LEFT JOIN Konstrukt eine Tabelle selektiert, die die Inhalte einer Tabelle und davon "abhängige" Tupel einer anderen Tabelle enthält. Im genannten Beispiel würde eine solche Tabelle alle Kombinationen von Organisationen und Personen enthalten, zwischen denen eine Fremdschlüsselbeziehung exisitiert. Im Abschnitt JPA Join Fetching wird das ausführlich erläutert.
Während alle bisher behandelten Optimierungen auf die Vergröberung der Datenbankzugriffe abzielen, soll nun das Problem sein, dass zuviel Daten selektiert und übertragen werden. Es geht im Kern darum, dass ein Nutzer in der Lage ist, sich in großen Ergebnismengen zurechtzufinden, ohne alle Daten sehen zu müssen. Die übliche Strategie ist Paging. Dabei werden der Anfang und Größe des Resultsets als SELECT Parameter mitgegeben, der Nutzer iteriert nach Bedarf über dieses eingeschränkte Ergebnisfenster.
Auf JDBC Ebene kann die maximale Größe eines Resultats mit java.sql.Statement.setMaxRows() festgelegt werden. Gleichzeitig kann dann die Fetch Size auf diese Größe gesetzt werden, es ist ja nun nicht mehr mit größeren Resultsets zu rechnen: java.sql.Statement.setFetchSize(). Im SELECT Statement wird dann noch eine ORDER BY Klausel für den PK und eine Bedingung mitgegeben, die die untere Grenze für den PK angibt. Zum Rückwärts-Navigieren kann man die ORDER BY Klausel negieren und eine obere Grenze angeben. Zwei Prepared Statements lassen dann durch den gesamten Suchraum navigieren und diese Lösung ist portabel!
Wenn Portabilität keine Rolle spielt, kann man auch die proprietären Klauseln seiner Datenbank benutzen. Beispielsweise kennt Oracle das ROWNUM-Keyword (mit einer ziemlich seltsamen Semantik), MySQL glänzt mit dem selbsterklärenden Zusatz LIMIT start,anzahl.
In JPA ist Paging berücksichtigt worden und wird im Abschnitt JPA Paging beleuchtet.