Supercharge Your SQL Queries for Production Databases

Artem Yevtushenko | Builder Education |7 min read |April 24, 2019

SQL ist eines der leistungsstärksten Datenverarbeitungswerkzeuge überhaupt. In SQL Superstar geben wir Ihnen umsetzbare Tipps, damit Sie das Beste aus dieser vielseitigen Sprache herausholen und schöne, effektive Abfragen erstellen können.

Wenn Sie ohne Data Warehouse oder separate analytische Datenbank für das Reporting arbeiten, ist die Produktionsdatenbank wahrscheinlich Ihre einzige Quelle für die neuesten, aktuellen Daten. Bei der Abfrage einer Produktionsdatenbank ist Optimierung der Schlüssel. Eine ineffiziente Abfrage beansprucht die Ressourcen der Produktionsdatenbank und verursacht eine langsame Leistung oder einen Serviceverlust für andere Benutzer, wenn die Abfrage Fehler enthält. Es ist wichtig, dass Sie Ihre Abfragen optimieren, um die Leistung der Datenbank so wenig wie möglich zu beeinträchtigen.

Definieren Sie zuerst die Geschäftsanforderungen

Wir haben an anderer Stelle Best Practices zur Definition von Geschäftsanforderungen für BI behandelt. Stellen Sie auf jeden Fall sicher, dass Sie diese Praktiken auch bei der Optimierung von SQL-Abfragen anwenden, einschließlich:

• Identifizieren Sie relevante Stakeholder. Stellen Sie sicher, dass alle notwendigen Parteien an der Diskussion zur Entwicklung Ihrer Abfrage beteiligt sind. Wenn Sie Produktionsdatenbanken abfragen, stellen Sie sicher, dass das DBA-Team einbezogen wird.
• Konzentrieren Sie sich auf die Geschäftsergebnisse. Geben Sie der Abfrage einen eindeutigen und einzigartigen Zweck. Die Produktionsdatenbank für explorative oder doppelte Berichte zu belasten, ist ein unnötiges Risiko.
• Gestalten Sie die Diskussion für optimale Anforderungen. Definieren Sie die Funktion und den Umfang des Berichts, indem Sie die beabsichtigte Zielgruppe bestimmen. Dadurch wird die Abfrage auf die Tabellen mit dem richtigen Detailgrad fokussiert.
• Stellen Sie gute Fragen. Befolgen Sie die 5 W’s: Wer? Was? Wo? Wann? Warum?
• Schreiben Sie sehr spezifische Anforderungen und bestätigen Sie diese mit den Beteiligten. Die Leistung der Produktionsdatenbank ist zu kritisch, um unklare oder zweideutige Anforderungen zu haben. 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.

Felder auswählen statt SELECT * verwenden

Wenn Sie explorative Abfragen ausführen, verwenden viele SQL-Entwickler SELECT * (gelesen als „select all“) als Abkürzung, um alle verfügbaren Daten aus einer Tabelle abzufragen. Wenn eine Tabelle jedoch viele Felder und viele Zeilen hat, belastet dies die Datenbankressourcen, da viele unnötige Daten abgefragt werden.

Die Verwendung der SELECT-Anweisung weist die Datenbank darauf hin, nur die Daten abzufragen, die Sie benötigen, um die Geschäftsanforderungen zu erfüllen. Hier ein Beispiel, bei dem die Geschäftsanforderungen nach Postadressen für Kunden fragen.

Ineffizient:

SELECT *
FROM Customers

Diese Abfrage zieht möglicherweise andere Daten heran, die ebenfalls in der Kundentabelle gespeichert sind, z. B. Telefonnummern, Aktivitätsdaten und Notizen von Vertrieb und Kundendienst.

Effizient:

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

Diese Abfrage ist viel sauberer und zieht nur die benötigten Informationen für Postadressen.

Um einen Index aller Tabellen und Feldnamen zu erhalten, führen Sie eine Abfrage aus einer Systemtabelle wie INFORMATION_SCHEMA oder ALL_TAB_COLUMNS aus (für MS SQL Server, lesen Sie dies).

Starten Sie noch heute mit SQL:

SELECT DISTINCT vermeiden

SELECT DISTINCT ist eine praktische Methode, um Duplikate aus einer Abfrage zu entfernen. SELECT DISTINCT funktioniert, indem alle Felder in der Abfrage gruppiert werden, um unterschiedliche Ergebnisse zu erzeugen. Um dieses Ziel zu erreichen, wird jedoch eine große Menge an Verarbeitungsleistung benötigt. Außerdem können die Daten so gruppiert werden, dass sie ungenau sind. Um die Verwendung von SELECT DISTINCT zu vermeiden, wählen Sie mehr Felder aus, um eindeutige Ergebnisse zu erzeugen.

Ineffizient und ungenau:

SELECT DISTINCT FirstName, LastName, State
FROM Customers

Diese Abfrage berücksichtigt nicht, dass mehrere Personen im selben Staat denselben Vor- und Nachnamen haben. 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 läuft.

Effizient und genau:

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

Durch das Hinzufügen von mehr Feldern wurden nicht duplizierte Datensätze zurückgegeben, ohne SELECT DISTINCT zu verwenden. Die Datenbank muss keine Felder gruppieren, und die Anzahl der Datensätze ist korrekt.

Sehen Sie Sisense in Aktion:

Erkunden Sie das Dashboard

Joins mit INNER JOIN (nicht WHERE)

Einige SQL-Entwickler ziehen es vor, Joins mit WHERE-Klauseln zu erstellen, wie z. B. die folgenden:

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

Diese Art von Join erzeugt einen kartesischen Join, auch kartesisches Produkt oder CROSS JOIN genannt.

Bei einem kartesischen Join werden alle möglichen Kombinationen der Variablen erstellt. In diesem Beispiel würde die Abfrage bei 1.000 Kunden mit 1.000 Gesamtumsätzen zunächst 1.000.000 Ergebnisse erzeugen und dann nach den 1.000 Datensätzen filtern, bei denen die Kunden-ID korrekt gejoint ist. Dies ist eine ineffiziente Nutzung der Datenbankressourcen, da die Datenbank 100x mehr Arbeit als erforderlich geleistet hat. Cartesian Joins sind besonders in großen Datenbanken problematisch, da ein Cartesian Join von zwei großen Tabellen Milliarden oder Billionen von Ergebnissen erzeugen kann.

Um zu verhindern, dass ein Cartesian Join erzeugt wird, verwenden Sie stattdessen INNER JOIN:

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 erzeugen, bei denen CustomerID gleich ist.

Einige DBMS-Systeme sind in der Lage, WHERE-Joins zu erkennen und sie stattdessen automatisch als INNER JOINs auszuführen. In diesen DBMS-Systemen gibt es keinen Unterschied in der Leistung zwischen einem WHERE-Join und einem INNER JOIN. Der INNER JOIN wird jedoch von allen DBMS-Systemen erkannt. Ihr DBA wird Sie beraten, was in Ihrer Umgebung am besten ist.

Verwenden Sie WHERE anstelle von HAVING, um Filter zu definieren

Das Ziel einer effizienten Abfrage ist es, nur die benötigten Datensätze aus der Datenbank zu ziehen. Gemäß der SQL Order of Operations werden HAVING-Anweisungen nach WHERE-Anweisungen berechnet. Wenn das Ziel ist, eine Abfrage basierend auf Bedingungen zu filtern, 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 Tabelle „Verkäufe“ ziehen, dann nach den 200 Datensätzen aus dem Jahr 2016 filtern und schließlich die Datensätze im Datensatz zählen.

Im Vergleich dazu begrenzen WHERE-Klauseln die Anzahl der gezogenen 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 ziehen und dann die Datensätze im Dataset zählen. Der erste Schritt in der HAVING-Klausel entfällt komplett.

HAVING sollte nur beim Filtern auf ein aggregiertes Feld verwendet werden. In der obigen Abfrage könnten wir mit 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

>>Kostenloses Starterkit: Starten Sie noch heute mit unserem kostenlosen SQL-Starterkit

Wildcards nur am Ende einer Phrase verwenden

Bei der Suche nach Klartextdaten, wie z.B. Städten oder Namen, erzeugen Wildcards die weitestmögliche Suche. Allerdings ist die breiteste Suche auch die ineffizienteste Suche.

Wenn ein führender Platzhalter verwendet wird, insbesondere in Kombination mit einem endenden Platzhalter, hat die Datenbank die Aufgabe, alle Datensätze nach einer Übereinstimmung irgendwo innerhalb des ausgewählten Feldes zu durchsuchen.

Betrachten Sie diese Abfrage, um Städte zu finden, die mit ‚Char‘ beginnen:

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

Diese Abfrage liefert die erwarteten Ergebnisse von Charleston, Charlotte und Charlton. Sie liefert jedoch auch unerwartete Ergebnisse, wie Cape Charles, Crab Orchard und Richardson.

Eine effizientere Abfrage wäre:

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

Diese Abfrage liefert nur die erwarteten Ergebnisse von Charleston, Charlotte und Charlton.

Verwenden Sie LIMIT, um Abfrageergebnisse zu testen

Bevor Sie eine Abfrage zum ersten Mal ausführen, stellen Sie sicher, dass die Ergebnisse erwünscht und sinnvoll sind, indem Sie eine LIMIT-Anweisung verwenden. (In einigen DBMS-Systemen wird das Wort TOP austauschbar mit LIMIT verwendet.) Die LIMIT-Anweisung gibt nur die angegebene Anzahl von Datensätzen zurück. Die Verwendung einer LIMIT-Anweisung verhindert, dass die Produktionsdatenbank mit einer großen Abfrage belastet wird, nur um dann festzustellen, dass die Abfrage bearbeitet oder verfeinert werden muss.

In der Abfrage „Verkäufe 2016“ von oben werden wir eine Begrenzung auf 10 Datensätze 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

Wir können anhand der Probe sehen, ob wir einen brauchbaren Datensatz haben oder nicht.

Lassen Sie Ihre Abfrage außerhalb der Stoßzeiten laufen

Um die Auswirkungen Ihrer analytischen Abfragen auf die Produktionsdatenbank zu minimieren, sprechen Sie mit einem DBA darüber, die Abfrage so zu planen, dass sie zu einer Zeit außerhalb der Stoßzeiten läuft. Die Abfrage sollte dann laufen, wenn die Anzahl der gleichzeitigen Benutzer am geringsten ist, was typischerweise mitten in der Nacht ist (3 – 5 Uhr morgens).

Je mehr der folgenden Kriterien Ihre Abfrage aufweist, desto wahrscheinlicher ist es, dass sie nachts ausgeführt werden sollte:

• Auswahl aus großen Tabellen (>1.000,000 Datensätze)
• Cartesian Joins oder CROSS JOINs
• Schleifen-Anweisungen
• SELECT DISTINCT-Anweisungen
• Verschachtelte Unterabfragen
• Wildcard-Suchen in langen Text- oder Memo Feldern
• Mehrere Schemaabfragen

Sicher abfragen

Mit diesen Tipps im Hinterkopf (und einigen anderen SQL-Tipps und -Tricks in der Tasche) sollten Sie in der Lage sein, effiziente, schöne Abfragen zu erstellen, die reibungslos laufen und die entscheidenden Erkenntnisse liefern, die Ihr Team braucht.

Holen Sie sich das kostenlose SQL-Starterkit hier: