In Unternehmen, die ohne Data Warehouse oder separate analytische Datenbank für das Reporting arbeiten, befindet sich die einzige Quelle für die neuesten und aktuellsten Daten möglicherweise in der Live-Datenbank. Für Datenbankabfragen ist die Optimierung von entscheidender Bedeutung. Eine ineffiziente Abfrage kann die Ressourcen der Datenbank belasten und für andere Benutzer eine schlechte Performance oder Ausfälle zur Folge haben. Daher ist es wichtig, Ihre Abfragen so zu optimieren, dass die Leistung der Datenbank möglichst wenig beeinträchtigt wird.

1. Definieren Sie zunächst die Geschäftsanforderungen

In einem früheren Artikel haben wir Best Practices zum Definieren von Geschäftsanforderungen für BI behandelt. Bei der Optimierung von SQL-Abfragen sollten folgende Dinge beachtet werden:

  • Identifizieren Sie relevante Stakeholder und beteiligen Sie diese an der Entwicklung der Abfrage (Query). Stellen Sie bei Datenbankabfragen sicher, dass die Administratoren hinzugezogen werden.
  • Konzentrieren Sie sich auf den geschäftlichen Nutzen. Stellen Sie sicher, dass die Abfrage einen bestimmten und eindeutigen Zweck hat. Die Datenbank mit explorativen oder doppelten Berichten zu belasten, ist ein unnötiges Risiko.
  • Beachten Sie Anwendergruppen. Definieren Sie die Funktion und den Umfang des Berichts mit der Zielgruppe im Hinterkopf. So identifiziert man den richtigen Detaillierungsgrad.
  • Definieren Sie gute Anforderungen, indem Sie gute Fragen stellen. Dies sind normalerweise die fünf “W” – Wer? Was? Wo? Wann? Warum?
  • Dokumentieren sie die Anforderungen bis ins kleinste Detail und lassen Sie diese von den Stakeholdern bestätigen. Die Leistung der Datenbank ist zu kritisch, um unklare oder nicht eindeutige Anforderungen zu erfüllen. Stellen Sie sicher, dass die Anforderungen so spezifisch wie möglich sind, und bestätigen Sie die Anforderungen mit allen Beteiligten, bevor Sie die Abfrage ausführen.

2. Definieren Sie SELECT Felder statt SELECT *

Bei der Ausführung explorativer Abfragen verwenden viele SQL-Entwickler SELECT * („Alles auswählen“) als Kürzel, um alle verfügbaren Daten aus einer Tabelle abzufragen. Wenn eine Tabelle jedoch viele Felder und viele Zeilen enthält, belastet dies die Datenbankressourcen beim Abfragen vieler unnötiger Daten.

Das Definieren von Feldern für SELECT lässt die Datenbank nur die erforderlichen Daten abfragen, um die Geschäftsanforderungen zu erfüllen. Schauen wir uns ein Beispiel an, in dem die Postanschriften für Kunden angefordert werden.

Ineffizient:

SELECT * FROM Customers

Bei dieser Abfrage werden möglicherweise andere Daten abgerufen, die auch in der Kundentabelle gespeichert sind, z. B. Telefonnummern, Aktivitätsdaten und Notizen aus Vertrieb und Kundendienst.

Effizient:

SELECT FirstName, LastName, Address, City, State, Zip FROM Customers

Diese Abfrage bezieht nur die erforderlichen Informationen für Postanschriften.

Um einen Index aller Tabellen und Feldnamen zu erstellen, führen Sie eine Abfrage aus einer Systemtabelle wie INFORMATION_SCHEMA oder ALL_TAB_COLUMNS aus (für MS SQL Server – weitere Informationen finden Sie hier).

3. Wählen Sie weitere Felder aus, um SELECT DISTINCT zu vermeiden

SELECT DISTINCT ist eine praktische Methode zum Entfernen von Duplikaten aus einer Abfrage. SELECT DISTINCT gruppiert alle Felder in der Abfrage, um eindeutige Ergebnisse zu erzielen. Um dieses Ziel zu erreichen, ist jedoch eine große Menge an Rechnerleistung erforderlich. Außerdem können Daten so gruppiert werden, dass sie ungenau sind. Um die Verwendung von SELECT DISTINCT zu vermeiden, sollten mehr Felder ausgewählt werden, um eindeutige Ergebnisse zu erzielen.

Ineffizient und ungenau:

SELECT DISTINCT FirstName, LastName, State FROM Customers

Diese Abfrage berücksichtigt nicht mehrere Personen mit demselben Status und demselben Vor- und Nachnamen. Beliebte Namen wie David Smith oder Diane Johnson werden zusammengefasst, was zu einer ungenauen Anzahl von Datensätzen führt. In größeren Datenbanken führt eine große Anzahl von David Smiths und Diane Johnsons dazu, dass diese Abfrage langsam ausgeführt wird.

Effizient und genau:

SELECT FirstName, LastName, Address, City, State, Zip FROM Customers

Durch Hinzufügen weiterer Felder wurden nicht duplizierte Datensätze ohne Verwendung von SELECT DISTINCT zurückgespielt. Die Datenbank muss keine Felder gruppieren, und die Anzahl der Datensätze ist korrekt.


Sisense in Aktion erleben:

Quality Assurance Project Status - Software Dashboard

4. Erstellen Sie Verknüpfungen mit INNER JOIN und nicht mit WHERE

Einige SQL-Entwickler bevorzugen Verknüpfungen mit WHERE-Klauseln, z. B.:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate FROM Customers, Sales WHERE Customers.CustomerID = Sales.CustomerID

Diese Art der Verknüpfung erstellt eine kartesische Verknüpfung, die auch als kartesisches Produkt oder CROSS JOIN bezeichnet wird. In einem Cartesian Join werden alle möglichen Kombinationen der Variablen erzeugt. In diesem Beispiel würde die Abfrage bei 1.000 Kunden mit 1.000 Gesamtverkäufen zuerst 1.000.000 Ergebnisse generieren und dann nach den 1.000 Datensätzen filtern, bei denen CustomerID korrekt verknüpft ist. Dies ist eine ineffiziente Verwendung der Datenbankressourcen, da die Datenbank 100-mal mehr Arbeit geleistet hat als erforderlich. Kartesische Verknüpfungen sind in großen Datenbanken besonders problematisch, da durch eine kartesische Verknüpfung von zwei großen Tabellen Milliarden oder Billionen von Ergebnissen erzielt werden können.

Um das Erstellen eines kartesischen Joins zu verhindern, sollte stattdessen INNER JOIN verwendet werden:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID

Die Datenbank würde nur die 1.000 gewünschten Datensätze generieren, bei denen CustomerID gleich ist.

Einige DBMS-Systeme erkennen WHERE-Joins und führen sie stattdessen automatisch als INNER JOIN aus. In diesen DBMS-Systemen gibt es keinen Leistungsunterschied zwischen einem WHERE-Join und INNER JOIN. INNER JOIN wird jedoch von allen DBMS-Systemen erkannt. Ihr Datenbankadministrator wird Sie beraten, welche in Ihrer Umgebung am besten ist.

5. Verwenden Sie WHERE anstelle von HAVING, um Filter zu definieren

Ähnlich wie beim oben genannten Konzept besteht das Ziel einer effizienten Abfrage darin, nur die erforderlichen Datensätze aus der Datenbank abzurufen. Gemäß der SQL-Operationsreihenfolge werden HAVING-Anweisungen nach WHERE-Anweisungen berechnet. Wenn eine Abfrage basierend auf Bedingungen gefiltert werden soll, ist eine WHERE-Anweisung effizienter.

Angenommen, im Jahr 2016 wurden 200 Verkäufe getätigt, und wir möchten die Anzahl der Verkäufe pro Kunde im Jahr 2016 abfragen.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID) FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.CustomerID, Customers.Name HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#

Diese Abfrage würde 1.000 Verkaufsdatensätze aus der Verkaufstabelle ziehen, dann nach den 200 im Jahr 2016 generierten Datensätzen filtern und schließlich die Datensätze im Datensatz zählen.

Im Vergleich dazu begrenzen WHERE-Klauseln die Anzahl der abgerufenen Datensätze:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID) FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016# GROUP BY Customers.CustomerID, Customers.Name

Diese Abfrage würde die 200 Datensätze aus dem Jahr 2016 abrufen und dann die Datensätze im Datensatz zählen. Der erste Schritt in der HAVING-Klausel wurde vollständig beseitigt.

HAVING sollte nur verwendet werden, wenn nach einem aggregierten Feld gefiltert wird. In der obigen Abfrage können wir mithilfe einer HAVING-Anweisung zusätzlich nach Kunden mit mehr als 5 Verkäufen filtern.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID) FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016# GROUP BY Customers.CustomerID, Customers.Name HAVING Count(Sales.SalesID) > 5

6. Verwenden Sie Platzhalter nur am Ende einer Phrase

Beim Durchsuchen von Klartextdaten wie Städten oder Namen erstellen Platzhalter die größtmögliche Suchbreite. Die umfassendste Suche ist jedoch auch die ineffizienteste Suche.

Wenn ein führender Platzhalter verwendet wird, insbesondere in Kombination mit einem abschließenden Platzhalter, hat die Datenbank die Aufgabe, alle Datensätze innerhalb des ausgewählten Felds nach Übereinstimmungen zu durchsuchen.

Betrachten Sie diese Abfrage, um Städte abzurufen, die mit “Char” beginnen:

SELECT City FROM Customers WHERE City LIKE ‘%Char%’

Bei dieser Abfrage werden die erwarteten Ergebnisse von Charleston, Charlotte und Charlton abgerufen. Es führt jedoch auch zu unerwarteten Ergebnissen wie Cape Charles, Crab Orchard und Richardson.

Eine effizientere Abfrage wäre:

SELECT City FROM Customers WHERE City LIKE ‘Char%’

Bei dieser Abfrage werden nur die erwarteten Ergebnisse von Charleston, Charlotte und Charlton abgerufen.

7. Verwenden Sie LIMIT, um Abfrageergebnisse abzubilden

Stellen Sie vor dem ersten Ausführen einer Abfrage mithilfe einer LIMIT-Anweisung sicher, dass die Ergebnisse wünschenswert und aussagekräftig sind. (In einigen DBMS-Systemen wird das Wort TOP austauschbar mit LIMIT verwendet.) Die LIMIT-Anweisung gibt nur die Anzahl der angegebenen Datensätze zurück. Die Verwendung einer LIMIT-Anweisung verhindert, dass die Datenbank mit einer großen Abfrage belastet wird, nur um herauszufinden, dass die Abfrage bearbeitet oder verfeinert werden muss.

In der Verkaufsabfrage 2016 von oben werden wir ein Limit von 10 Datensätzen untersuchen:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID) FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016# GROUP BY Customers.CustomerID, Customers.Name LIMIT 10

An der Stichprobe können wir erkennen, ob wir einen verwendbaren Datensatz haben oder nicht.

8. Führen Sie analytische Abfragen außerhalb der Stoßzeiten aus

Wenden Sie sich an einen Datenbankadministrator, um die Auswirkungen von Abfragen auf die Datenbank zu minimieren, und planen Sie die Ausführung der Abfrage außerhalb der Stoßzeiten. Die Abfrage sollte ausgeführt werden, wenn die Anzahl der gleichzeitig angemeldeten Benutzer am niedrigsten ist, normalerweise mitten in der Nacht (3 bis 5 Uhr morgens).

Je mehr der folgenden Kriterien Ihre Abfrage erfüllt, desto wahrscheinlicher sollte es sein, dass eine Abfrage nachts ausgeführt wird:

  • Auswahl aus großen Tabellen (> 1.000.000 Records)
  • Cartesian Joins oder CROSS JOINs
  • Schleifen
  • SELECT DISTINCT-Anweisungen
  • Verschachtelte Unterabfragen
  • Platzhaltersuchen in Langtext- oder Memofeldern
  • Mehrere Schemaabfragen
Tags: | | |