HUDU

SQL-Abfragen optimieren


€ 9,99
 
pdf eBook
Sofort lieferbar (Download)
Juli 2014

Beschreibung

Beschreibung

SQL-basierte Datenbanken bilden das Rückgrat für nahezu jede Businessanwendung. Kritischer Faktor ist dabei oft die Performance der Datenbankzugriffe. Dieses Buch erklärt in kompakter Form am Beispiel von Microsoft SQL Server, wie man performante SQL-Abfragen formuliert. Dabei wird auch auf die verschiedenen Möglichkeiten hingewiesen, die verschiedene Versionen des Produkts (bis hin zu SQL Server 2014) bieten. Viele Optimierungsansätze sind jedoch auch auf SQL-basierte Datenbank-Management-Systeme anderer Hersteller anwendbar. Somit erhält jeder, der selbst SQL-Abfragen schreibt - egal, ob Anwendungsentwickler, Datenbankentwickler oder DB-Administrator -, wertvolle Hinweise für die Praxis.

Portrait

Der Diplom-Informatiker (FH) Robert Panther ist als Senior Consultant für CGI in Deutschland tätig. Daneben schreibt er regelmäßig Fachartikel und Bücher zu seinen Spezialgebieten SQL Server und Anwendungsentwicklung für mobile Devices. Im Mai 2012 veröffentlichte er bei Microsoft Press in der Reihe "Richtig einsteigen" eines der ersten deutschsprachigen Bücher zu SQL Server 2012. Anfang 2010 erschien bei entwickler.press sein SQL Server Performance-Ratgeber. Gelegentlich ist er auch als Speaker auf Konferenzen und in der Professional Association for SQL Server (PASS) aktiv. Mit Microsoft SQL Server beschäftigt er sich bereits seit der Version 6.0 sehr intensiv und hat hier bereits als Entwickler, Administrator und Datenbankarchitekt zahlreiche Projekte erfolgreich mitgestaltet.

Leseprobe

2 Interne Verarbeitung von Abfragen

Um besser einzuschätzen zu können, an welchen Stellen eine Abfrageoptimierung erfolgversprechend ist, hilft es zu wissen, wie SQL-Abfragen im Detail intern verarbeitet werden.

2.1 Ablauf der Abfrageverarbeitung

Bevor eine Abfrage ausgeführt wird, erstellt SQL Server einen Ausführungsplan, in dem festgelegt wird, wie auf die beteiligten Tabellen zugegriffen wird. Genau genommen geschieht dies in drei Schritten, bei denen verschiedene Komponenten beteiligt sind.

  1. Der so genannte Parser prüft die Syntax der SQL-Anweisung und erstellt einen groben Ausführungsbaum.
  2. Der Algebrizer erledigt ähnliche Aufgaben wie der Parser, geht hierbei jedoch einen Schritt weiter, da nun auch die vorhandenen Datenstrukturen der von der Abfrage verwendeten Datenbankobjekte berücksichtigt werden. So werden einerseits die Namen von Tabellen und Spalten geprüft, aber auch die Korrektheit der dazugehörigen Datentypen. Als Ergebnis des Algebrizers wird der logische Ausführungsbaum weiter optimiert und als Syntaxbaum im Plan-Cache gespeichert (der Plan-Cache wird weiter hinten in diesem Kapitel ausführlicher behandelt).
  3. Der Optimizer (dt.: Optimierer) schließlich erstellt den eigentlichen Ausführungsplan, der die Art und Reihenfolge des Zugriffs auf die beteiligten Tabellen regelt. Auch dieser Ausführungsplan wird im Plan-Cache gespeichert.

Dieses Vorgehen greift bei der Verarbeitung von Ad-hoc-Abfragen, parametrisierten Abfragen und sogar gespeicherten Prozeduren. Im Allgemeinen ist die oben dargestellte Dreiteilung nicht so relevant. Der wesentliche Punkt ist die Tatsache, dass der Optimierer einen Ausführungsplan erstellt, in dem festgelegt wird, wie die Abfrage intern ausgeführt wird.<
/p>

Dabei werden die einzelnen Klauseln einer SQL-Abfrage in folgender Reihenfolge abgehandelt:

  1. FROM bestimmt, welche Tabellen gelesen und wie sie miteinander verknüpft werden
  2. WHERE filtert die Tabellen zeilenweise aufgrund von Bedingungen
  3. GROUP BY fasst Zeilen des Zwischenergebnisses zusammen
  4. HAVING filtert die Zeilen des Zwischenergebnisses anhand von Bedingungen
  5. SELECT wählt die als Ergebnis zurückzugebenden Spalten aus
  6. ORDER BY sortiert die Ergebniszeilen
  7. TOP/OFFSET reduziert die Ergebnismenge auf eine bestimmte Anzahl von Zeilen (bei Verwendung von OFFSET ab einer bestimmten Position)

Auffällig ist dabei, dass dies nicht ganz der Reihenfolge entspricht, in der die einzelnen Klauseln in einer SQL-Abfrage angegeben werden. So werden die einzelnen Spalten, deren Daten gelesen werden, bei der Abfrage zwar gleich am Anfang angegeben, bei der Ausführung aber erst im vorletzten Schritt berücksichtigt.

Hinweis: Etwas logischer ist dies in der Abfragesyntax der Abfragesprache LINQ gestaltet. Hier werden die Klauseln in der Reihenfolge FROM, WHERE, ORDER BY, SELECT aufgeführt, was schon eher der Auswertungsreihenfolge entspricht und insbesondere verbesserte Möglichkeiten für die Eingabeunterstützung mittels IntelliSense im Code-Editor bietet. Ähnlich verhält es sich bei den in XQuery verwendeten FLWOR-Ausdrücken. Diese werden – entsprechend der Abkürzung – in der Reihenfolge FOR, LET, WHERE, ORDER BY, RETURN ausgewertet. Wenn man sich hier die letzten drei Bestandteile anschaut, wird zuerst die Menge der Zeilen mit WHERE gefiltert, dann mit ORDER BY an> sortiert und schließlich werden mit RETURN die zurückzugebenden Werte definiert.

2.2 Ausführungspläne

Im SQL Server Management Studio kann man sich den Ausführungsplan zu einer Abfrage anzeigen lassen. Dabei wird zwischen dem geschätzten und realen Ausführungsplan unterschieden.

Der geschätzte Ausführungsplan wird angezeigt, ohne dass die Abfrage wirklich ausgeführt wird. Dazu wählen Sie im Management Studio den Menüpunkt Abfrage |Geschätzten Ausführungsplan anzeigen oder das dazu gehörende Symbol in der Symbolleiste. Bis SQL Server 2008 R2 gab es zusätzlich noch eine entsprechende Tastenkombination STRG+L.

Alternativ kann der tatsächliche Ausführungsplan angezeigt werden, sobald die Abfrage ausgeführt wird. Auch hierzu gibt es einen Menüpunkt (Abfrage | Tatsächlichen Ausführungsplan einschließen), ein Symbol in der Symbolleiste sowie bis SQL Server 2008 R2 auch eine Tastenkombination (STRG+M). Durch jede dieser drei Varianten wird die Option umgeschaltet, also aktiviert bzw. deaktiviert. Wenn Sie die Anzeige des tatsächlichen Ausführungsplans aktiviert haben und anschließend die Abfrage ausführen, erscheint im Ergebnisbereich neben den Registerkarten Ergebnisse und Meldungen noch eine weitere, die mit Ausführungsplan betitelt ist und den tatsächlichen Ausführungsplan beinhaltet.

So wird für die folgende Abfrage …

SELECT pers.FirstName, pers.LastName,
phone.PhoneNumber
FROM Person.Person AS pers
INNER JOIN Person.PersonPhone AS phone
ON pers.BusinessEntityID
= phone.BusinessEntityID
INNER JOIN Person.PhoneNumberType AS ptype
ON phone.PhoneNumberTypeID
= ptype.PhoneNumberTypeID
WHERE ptype.Name = 'Home'
AND pers.LastName =
'Gonzalez'

… beispielsweise der in der Abbildung 2.1 dargestellte Ausführungsplan erstellt.

Doch wodurch unterscheiden sich der geschätzte und der tatsächliche Ausführungsplan?

Der geschätzte Ausführungsplan ist der Plan, der vom Abfrageoptimierer erstellt wird. Während die Abfrage ausgeführt wird, kann es jedoch vorkommen, dass beim Lesen von Tabellen oder Indizes festgestellt wird, dass der geschätzte Ausführungsplan auf veralteten Statistiken beruhte. Das kann dann zur Folge haben, dass der tatsächliche Ausführungsplan noch während der Ausführung angepasst wird und sich der tatsächliche Ausführungsplan damit vom geschätzten Plan unterscheidet.

Abbildung 2.1: Die grafische Darstellung eines Ausführungsplans

Verwenden Sie also zur Bewertung einer Abfrage nach Möglichkeit immer den tatsächlichen Ausführungsplan. Bei Abfragen mit extrem langer Laufzeit können Sie notfalls auf den geschätzten Ausführungsplan ausweichen.

Unabhängig davon, ob Sie den geschätzten oder tatsächlichen Ausführungsplan anzeigen lassen, ist aus diesem zu erkennen, in welcher Reihenfolge auf welche Tabellen zugegriffen wird, ob und welche Indizes verwendet werden, wie die verschiedenen Zwischenergebnisse miteinander verknüpft werden und vieles mehr.

Die im grafischen Ausführungsplan dargestellten Symbole stellen einzelne Operationen dar, von denen es eine relativ große Menge gibt. Eine vollständige Auflistung aller möglichen Operationen mit den dazu gehörenden Symbolen finden Sie im Internet unter http://msdn.microsoft.com/de-de/library/ms175913.aspx

Etwas gewöhnungsbedürftig ist sicherlich die Reihenfolge, in der die grafischen Ausführungspläne zu lesen sind. Da sie in einer Art Baumstruktur dargestellt
werden, stellt die Wurzel des Baums (links oben) das Endergebnis dar, während die ersten Schritte, die ausgeführt werden, ganz rechts in den Blättern der Baumstruktur stehen. Hier sind normalerweise die eigentlichen Tabellen- oder Indexzugriffe zu finden, die auf verschiedene Arten stattfinden können1:

  • Table Scan: sequenzielles Lesen der gesamten Tabelle
  • Clustered Index Scan: sequenzielles Lesen des gruppierten Index
  • Clustered Index Seek: gezielter Zugriff auf einzelne Zeilen des gruppierten Index
  • Index Scan: sequenzielles Lesen des nicht gruppierten Index
  • Index Seek: gezielter Zugriff auf einzelne Zeilen des nicht gruppierten Index

Verfolgt man die Verbindungslinien nun schrittweise in Richtung Wurzel, so lässt sich nachvollziehen, wie die im ersten Schritt gelesenen Daten weiter verarbeitet werden. So folgt beispielsweise nach einem Zugriff auf einen nicht gruppierten Index (per Index Scan oder Index Seek) meist ein Row Lookup, mit dem die eigentlichen Zeilendaten aus der Tabelle gelesen werden.

Hinweis: Damit der Abfrageoptimierer entscheiden kann, ob ein Indexzugriff sinnvoll, oder es doch effektiver ist, direkt über einen Table Scan die gesamte Tabelle zu lesen, sind zwei Informationen entscheidend. Die erste ist die Anzahl der Zeilen in der Tabelle, die zweite die Selektivität des Index. Sowohl bei geringer Zeilenanzahl als auch bei geringer Selektivität des Index (also wenn ohnehin fast alle Zeilen der Tabelle gelesen werden müssen), ist es effektiver, einen Table Scan auszuführen als den Umweg über den Index zu gehen. Damit dies möglich ist, wird die Anzahl der Zeilen in den verschiedenen Tabellen in Statistiken mit protokolliert. Diese müssen allerdings von Zeit zu Zeit aktualisiert werden, da sie sonst irrefü
hrende Werte beinhalten, die zu nicht optimalen Ausführungsplänen führen können.

In dem in Abbildung 2.1 dargestellten Beispiel wird der komplette Index...


EAN: 9783868023107
Untertitel: Was Entwickler über Performance wissen müssen. Dateigröße in MByte: 9.
Verlag: entwickler.press
Erscheinungsdatum: Juli 2014
Seitenanzahl: 176 Seiten
Format: pdf eBook
Kopierschutz: Wasserzeichen
Es gibt zu diesem Artikel noch keine Bewertungen.Kundenbewertung schreiben