Angelehnt an das Buch "The Art of War" (Szun Tzu, 500 v. Chr.) werden hier von Stéphane Faroult Datenbank-Strategien als Feldzug gegen unüberschaubare Heerscharen von Daten beschrieben.

Unter Intention findet man den ersten Absatz des zweiten Kapitels, weil der Author hier sehr gut beschreibt, warum man einmal in SQL-Projekten diesen Blickwinkel einnehmen sollte. Im letzten Absatz habe ich einige wichtige Punkte zusammengefasst, die sich verstreut im Buch finden und die man mit "lessons learned" überschreiben könnte. Das Buch ist nicht auf ein spezielles Datenbanksystem ausgerichtet, sondern hat den Fokus auf allgemeine SQL-Projekte.

Acknowledgements

Vor einiger Zeit hat Frank Kalis bereits eine Rezension über dieses hervorragende Buch geschrieben.

Many thanks to Teri Finn from O'Reilly Media, Inc. for granting the permission to publish this article as a partial translation of the book "The Art of SQL by Stéphane Faroult with Peter Robson. Copyright 2006 O'Reilly Media, 0-596-00894-5."

Intention

Der Hintergrund dieser Aktivitäten ist meist der gleiche: Anybody who has ever been involved in the switch from development to production of a critical system knows how much it can feel like the noise and tumult of battle. Very often, a few weeks before D-Day, performance tests will show that the new system is going to fall short of expectations. Experts are brought in, SQL statements are fine-tuned, and database and system administrators are called to contribute to a succession of crisis meetings. Finally, performance vaguely comparable to the previous system is obtained on hardware that is now twice as expensive as the original installation.

Maximen und Zusammenfassungen

1. Pläne schmieden (Design)

• Daten-Zusammenhänge gehören in die Datenbank nicht in die Applikation (wie sonst soll sich der Optimizer vernünftig entscheiden?)
• Wahre Flexibilität erreicht man durch eine gesunde Anwendung von Design-Regeln und nicht durch Denormalisierung
• Das Handling von Daten, die sich gleichzeitig ansammeln und ändern können (z. B. historisierte Preislisten) verlangt sorgfältiges Design und Taktiken, die von der Änderungshäufigkeit abhängen
• Der größte einzelne Einflussfaktor für schlechte Performance ist ein falsches Design
• Ein Datenmodell ist erst dann vollständig, wenn auch der Datenfluß (z. B. Batch-Programme) berücksichtigt wurde
• Je näher man an den Daten ist, desto schneller ist der Zugriff. Verteilung von Daten kostet Performance
• Datenbank-Systeme sind Gemeinschaftswerke von Anwendern, Administratoren und Entwicklern
• Erfolgreiche Datenmodellierung ist die disziplinierte Anwendung einfachster Design-Regeln
  

2. Ins Feld ziehen (Effiziente Zugriffe)

• Identifizierbare Statements erleichtern das Auffinden problematischer Programme
• Datenbank-Verbindungen und Round-Trips sind wie chinesische Mauern - je mehr man hat, um so länger dauert es um korrekte Nachrichten zu empfangen
• Strategie vor Taktik - Zuerst einen Überblick verschaffen und dann ins Detail gehen
• Fundamente vor Mode - lerne die zur Verfügung stehenden Künste kennen, bevor die neuesten Tools angewendet werden
• In einer Applikation dürfen keine Datenbank-Objekte angelegt, geändert oder gelöscht werden. Ausnahme sind temporäre Objekte, die der Optimizer als solche erkennt
• Tausende von Statements in einer Cursor-Schleife für endlose Batch-Verarbeitung, mehrere Statements die auf die gleichen Daten angewendet werden, während User verdammt sind zu warten; ein einzelnes kraftvoll zuschlagendes Statement schlägt sie alle
• Überlasse möglichst viel Arbeit dem Datenbank-Optimizer
• Unglücklicherweise ist es gute OO-Praxis Methoden zu verwenden, die immer alle Attribute betreffen (lesen/schreiben). Man darf nicht OO-Methoden mit relationaler Datenbank-Verarbeitung verwechseln. Es ist ein fataler Fehler relationale und OO-orientierte Konzepte zu vermischen und Tabellen als Klassen und Spalten als Attribute zu sehen. Maximiere jeden Zugriff auf die Datenbank dahingehend, dass möglichst viel Arbeit damit erledigt wird.
• Code liebt den SQL-Kernel, je näher am Kernel, desto schneller der Code. Also keine überflüssigen Funktionen programmieren!
• Es gibt keinen Grund explizit zu programmieren, was die Datenbank implizit schon tut! Z. B. ein COUNT(*) vor einem UPDATE ersetzen durch UPDATE mit Kontrolle der Rückgabe in SQL%ROWCOUNT.
• Kodiere wenn möglich Abfrage-Logik in die SQLs statt in einer Host-Sprache
• Fasse Updates zusammen, reduziere die wiederholten Zugriffe auf immer die selbe Tabelle
• Der Code von User-Definded Functions entzieht sich dem Optimizer
• SQL ist eine deklarative Sprache, versuche Abstand zur prozeduralen Business-Logik zu bewahren. Versuche z. B. mehrere Prüfungen in einem zu erledigen, anstelle von vorher
• Kodiere offensiv und wahrscheinlich. Setze das wahrscheinlichste vorraus und behandle Ausnahmen, nur wenn es wirklich notwendig ist
• Ausnahmebehandlung fordert oft prozedurale Logik. Versuche Ausnahmen zu vermeiden während Du deklaratives SQL verwendest
  

3. Taktische Vorkehrungen (Indizierung)

• Die Wartung eines Index, kann teurer sein als für die Tabelle
• Sei ziemlich sicher, was und warum Du indizierst
• Wenn man Zeilen nach Index sortiert ablegt, führt dies zu schnelleren Range-Scans
• Der Einsatz von function-based Indizes ist oft die implizite Erkenntnis, das die Analyse der Daten noch nicht einmal die grundlegende Zerlegung in unabhängige Teile gebracht hat (atomicity, 1NF)
• Es muss einen Grund für die Indizierung geben, dies gilt sowohl für Foreign-Key-Spalten, als auch für normale Spalten. Indizes über Foreign-Keys (FKs) machen dann Sinn, wenn für die Parent-Tabelle Löschungen oder Updates auf den Primary Key laufen können. FKs von reinen Lookup-Tabellen müssen nicht durch einen Index unterstützt werden, solange es bei einer Auslieferung zu keinen Löschungen kommt. Für die Auslieferung sind sie auch dann verzichtbar, wenn es ein genügend grosses Zeitfenster gibt.
• Indizierung von allen Foreign-Keys kann redundante Indizes erzeugen. Hier geht der Blick von den Indizes über mehrere Felder zu einspaltigen Indizes über das führende Feld.
• Vom System generierte Schlüssel (Sequences) können im richtigen Zusammenhang Nutzen bringen, aber man behalte den wahllosen Einsatz im Auge. Reverse Indizes können Hot-Spots vermeiden helfen, Abhängige Tabellen sind oft besser bedient mit zusammengesetzten Primärschlüsseln bestehend aus Foreign-Key plus laufende Nummer.
• Indizierung ist kein Allheilmittel: Effektiver Einsatz beruht auf dem vollständigen Verständnis der Daten mit denen man es zu tun hat und der angemessenen Einschätzung
  

4. Taktische Übungen (In SQL Denken)

• Halte die wirkliche relationale Funktionalität eines SQLs und die zusätzliche Präsentationsschicht auseinander
• Der Optimizer belohnt diejenigen, die die meiste Arbeit in der relationalen Schicht erledigen lassen
• Füttere den Optimizer mit kleinen Brocken und er wird kleine Teile optimieren. Füttere ihn mit einem großen Brocken und er optimiert eine ganze Aufgabenstellung.
• Ein fähiger Programmierer peilt eine Antwortzeit an, die in einem vernünftigen Verhältnis zu der Anzahl der zurück gegebenen Zeilen steht
• Die Einfachheit einer Abfrage kann den Blick auf die Komplexität der verwendeten Views verschleiern
• Anstelle komplexe Views in Abfragen zu verwenden, wenn diese View für die Abfrage unnötige Teile enthält, versuche die View zu zerlegen und in die Abfrage zu integrieren
• Das korrekte Ergebnis einer SQL-Abfrage ist nur der erste Schritt auf dem Weg zum besten SQL
• Filtere nicht benötigte Daten so früh wie möglich heraus
• Wenn wir viele Daten wollen, wollen wir nicht unbedingt einen Index
  

5. Das Terrain (Die Physik verstehen)

• Schreib- und Lesevorgänge leben nicht in Harmonie miteinander: Leser wollen Daten gebündelt; konkurierende Schreiber wollen sie verstreut
• Einige Abfragen können alle Daten aus einem Index ziehen ohne die Tabelle aufzusuchen
• Range Scans auf geclusterten Daten können beeindruckende Performance zeigen, aber andere Abfragen werden in der Konsequenz darunter leiden
• Partitionierung ist am wertvollsten, wenn sie die Zusammenhänge der Daten berücksichtigt
• Partitionierung kann verwendet werden um Daten zu bündeln oder zu streuen, je nach Anforderung (Concurrent Inserts v. Select)
• Die größten Gewinne erreichen Abfragen gegen partitionierte Tabellen, wenn die Daten gleichmässig über den Partition-Key verteilt sind
• Es gibt viele Wege eine Tabelle zu partitionieren und der offensichtlichste ist nicht immer der effektivste. Behalte immer das globale Bild im Auge.
• Pre-Joining von Tabellen ist eine sehr spezialisierte (Oracle-)Taktik um ausgewählte Abfragen zu beschleunigen, aber dies wird oft zum Schaden für alle anderen Abfragen sein
• Die Art der Speicherung, die heute für uns arbeitet, kann morgen gegen uns arbeiten
  

6. Die neun Situationen (Erkennung von klassischen SQL-Mustern)

1. Kleine Ergebnismenge, direkte spezielle Kriterien

• Exzellente Abfragen müssen nicht notwendigerweise von excellenten Programmen kommen. Auch wenn eine Abfrage einfach aussieht, würde evtl. die Kombination mit einer anderen Abfrage die vorher läuft die Effizienz erhöhen.
• Performance-Unterschiede zwischen vergleichbaren Indizes können von der physikalischen Datenverteilung kommen
• Alle speziellen Kriterien sind nicht gleich gute Kandidaten für Indizierung. Spalten, die häufig geändert werden, erhöhen den Wartungsaufwand für Indizes.
• Falls ein Full-Table-Scan notwendig ist, werden Indizes auf der Tabelle irrelevant
• Inline-Abfrage können eine Abfrage vereinfachen, aber können in exzessiver und mehrfacher Verarbeitung enden, wenn sie mit wenig Sorgfalt angewendet werden
  

2. Kleine Ergebnismenge, indirekte Kriterien

• Indirekte Kriterien sind welche, die auf Spalten einer Tabelle angewendet werden, die nur für diese Auswertung per Join verknüpft wird
• Falls Unterabfragen verwendet werden, denke sorgfältig darüber nach, bevor Du Dich für eine korrelierte (exists) oder unkorrelierte (in) entscheidest
  

3. Kleine Schnittmenge von weitläufigen Kriterien

• Das Hinzufügen eines Suchkriteriums an eine bestehende Abfrage kann das vorherige Konstrukt komplett verändern: eine geänderte Abfrage ist eine neue Abfrage
  

4. Kleine Schnittmenge, indirekte weitläufige Kriterien

• Denke immer daran alles absolut aussagekräftig zu dokumentieren, was gemacht wurde, um das DBMS zu irgend einem Handeln zu zwingen
• Eine konfuse Abfrage kann auch den Optimizer konfus machen. Klarheit und vorgeschlagene Joins (z. B. als Nested Query) reichen oft als Hilfe aus damit der Optimizer gute Performance anbieten kann.
  

5. Grosse Ergebnissmenge
   

6. Self-Joins

• Falls mehrfache Selektionen auf verschiedene Zeilen der gleichen Tabelle angewendet werden, können (OLAP-)Funktionen, die gleitende Auswahl (sliding Windows) auf den Daten unterstützen, hilfreich sein
  

7. Durch Aggregation erhaltene Ergebnismengen

• Aggegiere so wenige Daten wie Du kannst
  

8. Einfache- oder Bereichs-Suche mit Datums-Feldern

• Du mußt Deine Daten und ihre Verteilung verstehen, wenn Du verstehen willst, wie der Optimizer Dein System sieht
  

9. Ergebnismengen basierend auf dem Fehlen von Daten

• Data-Sets können mit verschiedenen Techniken verglichen werden aber Outer Joins und Mengen-Operatoren (in, exists) sind wahrscheinlich am effektivsten.
  

7. Variationen in der Taktik (Der Umgang mit hierarchischen Daten)

• Verknüpfungen zwischen Objekten der gleichen Art sollten als Bäume modelliert werden sobald die Anzahl der Ebenen zwischen den Objekten nicht länger konstant ist.
• Die SQL Implementierung von Bäumen ist DBMS-abhängig; nutze was Dein DBMS zu bieten hat.
• Das Navigieren in Bäumen, egal ob von unten nach oben oder umgekehrt, ist eine sequentielle und damit langsame Operation.
• Eine einfache Implementierung von Bäumen funktioniert mit Aggregaten manchmal recht schnell.
• Falls das DBMS eine komplexere Verarbeitung von Bäumen mitbringt, werden komplexere Abfragen einfacher mit SQL-Mitteln zu lösen sein.
  

8. Schwächen und Stärken (Erkennung und Behandlung schwieriger Fälle)

• Versuche unübliche Bedingungen wie z. B. Vergleiche gegen ein Präfix oder den Teil eines Schlüssel in gut bekannte Arten von Bereichsüberprüfungen zu transformieren; versuche wann immer möglich sicherzustellen, dass es eine untere und obere Grenze gibt.
• Coole Datenbank-Zugriffsschichten sind nicht unbedingt auch effiziente Zugriffsschichten.
• Der Optimizer arbeitet gut mit dem was er gut kennt: lokale Daten. Extensive Interaktion mit verteilten Daten reduziert die Performance.
• Eine variable Anzahl von Suchkriterien schreit nach dynamischem SQL.
• Wenn Du dynamisches SQL verwendest, verwende Parameter-Marker und Bind-Variablen aus Gründen der Performance und Sicherheit (SQL-Injection).
• Die Verknüpfung von Suchkriterien mit dynamisch aufgebauten Abfragen verbessert die Performance durch Minimierung von Joins und eliminiert die Fälle von fehlenden Werten (für bestimmte Joins).
• Mehr Intelligenz in der Software beim dynamischen Aufbau von SQL-Statements erzeugt effizientere SQL-Statements als komplexe Abfragen, die in Teilen keine Daten liefern und deren Ergebnis erst vom DBMS kombiniert werden muss.
  

9. Viele Fronten (Konkurierende Zugriffe in Angriff nehmen)

• Auch hinter Antwortzeiten weit unterhalb einer Sekunde können sich manchmal große Performance-Probleme verstecken. Vertraue keinen Einheits-Tests. Bei Abfragen die sehr häufig ausgeführt werden, summieren sich auch kleine Zeiten schnell auf.
• Eine gestiegene Last muss keine Performance-Probleme bringen, aber kann sie tatsächlich offenbaren. Diesem Verhalten kann durch Programm-Verbesserungen als Alternative zur Hardware-Aufrüstung begegnet werden.
• Die System-Performance bricht zusammen, wenn Statements schneller kommen, als sie abgearbeitet werden können; alle Abfragen sind betroffen, nicht nur die langsamen.
• Wenn es viele Aktivitäten mit Transaktionen gibt, ist es lebenswichtig keine Sperren für die Dauer von Operationen zu halten, die diese Sperren eigentlich nicht benötigen. Die Transaktion beginnt also (erst) mit einem DML-Statement, verwendet eine minimale Anzahl von Round-Trips zur Datenbank und reduziert diese z. B. durch den Einsatz von Stored Procedures.
• Um so größer die Anzahl konkurierender User ist, um so kürzer sollten die Commit-Intervalle sein.
• Concurrency hängt von den Schutzmechanismen der Datenbank ab, welche ausser Locking noch andere Kontrollfunktionen beinhaltet. Diese Mechanismen variieren je nach Datenbank-System.
• Architekten, Entwickler und Administratoren können das Design dahingehend auslegen, dass Streitpunkte in der Datenbank begrenzt bleiben.
  

10. Vereinigung der Kräfte (Mit großen Datenmengen fertig werden)

• Nicht alle Datenbank-Operationen reagieren gleich empfindlich auf steigende Datenmengen. Ahne voraus, wie sich Abfragen auf der angepeilten Menge verhalten werden.
• Um die Abhängigkeit von Abfragen vom Ansteigen der Datenmenge zu reduzieren, verwende in den unteren Schichten (z. B. Subqueries) nur die Daten, die dort wirklich notwendig sind. Hebe alle Hilfsjoins (z. B. zur Übersetzung von Schlüsseln in Texte) für den äußeren Teil der Abfrage auf.
• Minimiere die Abhängigkeiten von korrelierten Unterabfragen von Elementen der äußeren Abfrage.
• Halb eingeschränkte Bedingungen (>= oder <=) können sowohl Indizes, als auch Partitionen nur schlecht verwenden. Besser ist z. b. between welches nur eine kleine Anzahl von Partitionen betrifft.
• Von allen datenverändernden Operationen ist DELETE diejenige, die am meisten Ärger und Arbeit verursachen kann. Versuche z. B. Deletes durch Truncate zu ersetzen, wobei die zuviel gelöschten Sätze aus einer vorher erstellten temporären Tabelle zurück geholt werden.
• Eine der Hauptgründe für temporäre Tabellen ist es massive, tabellenorientierte Operationen zu ermöglichen, die zeilenweise Operationen in der Geschwindigkeit bei weitem übertreffen.
• Die Design-Beschränkungen bei Dimensional Modeling (Data Warehouse) sind absichtlich auf Leseoperationen ausgerichtet und ignorieren konsequenterweise die Regeln des relationalen Designs.
• Schwach entworfene Abfragen können gegen dimensionale Modelle eine akzeptable Performance zeigen, weil die Komplexität des Designs deutlich niedriger ist als bei einem transaktionsorientierten Modell.
• Mehrfache massive Updates auf einer Tabelle wirken sich oft verheerend auf die physische Schicht aus.
• Der Vorteil von einfacheren Abfragen gegen dimensionale Modelle wird mit dem Nachteil der komplexeren Vorbereitung und dem Laden der Daten erkauft.
• Der massive Upload in ein Decision-Support-System ist einer der wenigen Fälle, wo vorübergehende Änderungen an einem Schema toleriert werden.
• Dimensional Modeling wird auf dem Versprechen gebaut, dass der Einstiegspunkt die Dimensionen sind. Auf Fakten-Tabellen wird zuletzt zugegriffen.
• Die Art und Weise wie Daten in ein Star-Schema geladen werden, kann einige Dimensionen gegenüber anderen begünstigen.
  

11. Kriegslisten (Versuche die Antwortzeit zu retten)

• Mehrfache Self-Joins können oft dadurch verhindert werden, dass alle Sätze in einem Durchgang gelesen werden, wobei die einzelnen Werte über verschiedene Spalten verteilt werden, indem man Aggregatsfunktionen (z. B. max()) verwendet, um aus vielen Zeilen eine zu machen.
• Pivot Hilfstabellen und Operatoren können hilfreiche Techniken mit eigener Daseinsberechtigung sein, aber sie sollten niemals verwendet werden um die Unzulänglichkeiten von schlechtem Design zu beschönigen.
• Aggregieren von Bereichen (Gruppen) erfordert die Generierung eines künstlichen Sortierschlüssels um die Ergebnisse in der gewünschten Reihenfolge (und z. B. nicht alphabetisch sortiert) anzuzeigen.
• Der gute Ausführungsplan, der heute einer Abfrage aufgezwungen wird, kann morgen bereits verheerend sein.
  

12. Die Beschäftigung von Spionen (Performance beobachten)

• Um die Performance einer Applikation im echten Leben vorauszuahnen, muß man die Aktivitäten während Stress-Tests und der Erprobung durch Anwender genau beobachten.
• Wiederholte kurzlaufende zweitklassige Statements belasten einen Server oft mehr als die grossen schlechten SQL-Abfragen die eine lange Laufzeit benötigen.
• Die beiden wichtigsten Indikatoren für die Datenbank-Last sind die Menge an CPU-Zeit, die verwendet wird um Statements zu parsen und die Anzahl von Datenseiten, die bei der Ausführung von Abfragen aufgesucht werden.
• Belastungswerte müssen in Relation zu SQL Statements gesetzt werden. SQL Statements müssen in Relation zu den Geschäftsaktivitäten gesetzt werden. Geschäftsaktivitäten müssen in Relation zu Geschäftsanforderungen gesetzt werden.
• Kenne die Grenzen Deiner Umgebung. Messe wieviele Zeilen mit insert, fetch, update oder delete in einer bestimmten Zeit auf Deiner Maschine bearbeitet werden können.
• Performance Verbesserung wie sie vom Endanwender wahrgenommen wird, zählt am meisten, aber vergiß niemals den schmalen Grat zwischen akzeptabler und inakzeptabler Performance in einer Umgebung unter Last.
• Verbesserte Performance meint in erster Linie mehr Arbeit in derselben Zeit zu erledigen und erst in zweiter Linie sie in weniger Zeit zu erledigen.
• Brilliant optimierte Statements in einem schlechten Programm die gegen eine schlecht entworfene Datenbank laufen sind nicht effektiver als eine brilliante Taktik im Dienste einer schwachen Strategie; alles was sie tun können ist den Tag der Abrechnung hinauszuzögern.
• Das Tuning von SQL Statements bevor die Programme verbessert und die Datenbankzugriffe minimiert werden bedeutet, dass man einige der wichtigsten Mittel zur Laufzeitverbesserung ignoriert.
• Der einzige Maßstab für die Performance von Abfragen ist wie lange sie brauchen, und nicht ob der Ausführungsplan den vorgefaßten Meinungen entspricht.
• Der wesentliche Wert von Ausführungsplänen liegt darin einen Startpunkt für die Untersuchung der Performance zu liefern und die verborgenen Datenbankoperationen durch Views oder Trigger zu enthüllen.
• Der Bau von optimal performenden SQLs kann eine Quelle großer Befriedigung sein - viel Spaß!

Schwachstellen erkennen

• Tabellen in denen einige Spalten (überwiegend) NULL sind, können Kandidaten für Sub-Typen sein
• In einer OLTP-Datenbank weisen Tabellen mit "zu vielen" Indizes auf Schwachstellen im Design hin
• Kontrolliere, ob die Datenbank-Aktivitäten zur Laufzeit konsistent zu den Geschäftsprozessen sind. Oder haben einige Geschäftsprozesse überproportional viele Zugriffe?
• Wurden function-based Indizes verwendet? Dann ist hier die Überprüfung der Normalisierung angeraten!
• Werden Foreign-Keys auf reine Lookup-Tabellen unnötigerweise mit Indizes unterstützt?
• Gibt es redundante Indizierung?
• Gibt es abhängige Tabellen, die besser einen zusammengesetzten Primärschlüssel haben sollten?
• Untersuche UNION-Abfragen dahingehend, ob immer wieder gleiche Teile vorkommen. Fasse die anderen Teile als inline-View per Union zusammen und mache einen Join mit den immer wieder gleichen Teilen!
• Gibt es Abfragen, die sehr häufig ausgeführt werden und relativ gute Performance zeigen? Kleine Optimierungen in diesem Umfeld zahlen sich entsprechend oft aus.