SQL entfesselt: 17 Möglichkeiten, um Ihre SQL-Abfragen zu beschleunigen

SQL-Entwickler auf jeder Plattform haben Probleme und scheinen in einer DO WHILESchleife zu stecken , die sie dazu bringt, dieselben Fehler immer wieder zu wiederholen. Das liegt daran, dass das Datenbankfeld noch relativ unausgereift ist. Sicher, die Anbieter machen einige Fortschritte, aber sie setzen sich weiterhin mit den größeren Problemen auseinander. Parallelität, Ressourcenverwaltung, Speicherverwaltung und Geschwindigkeit plagen SQL-Entwickler immer noch, unabhängig davon, ob sie auf SQL Server, Oracle, DB2, Sybase, MySQL oder einer anderen relationalen Plattform codieren.

Ein Teil des Problems ist, dass es kein Wundermittel gibt, und für fast jede bewährte Methode kann ich Ihnen mindestens eine Ausnahme zeigen. In der Regel findet ein Entwickler seine eigenen Lieblingsmethoden - obwohl diese normalerweise keine Konstrukte für Leistung oder Parallelität enthalten - und macht sich nicht die Mühe, andere Optionen zu untersuchen. Vielleicht ist das ein Symptom für mangelnde Bildung, oder die Entwickler sind einfach zu nah am Prozess, um zu erkennen, wenn sie etwas falsch machen. Möglicherweise läuft die Abfrage auf einem lokalen Satz von Testdaten gut, schlägt jedoch auf dem Produktionssystem kläglich fehl.

Ich erwarte nicht, dass SQL-Entwickler Administratoren werden, aber sie müssen Produktionsprobleme beim Schreiben ihres Codes berücksichtigen. Wenn sie dies während der ersten Entwicklung nicht tun, werden sie von den Datenbankadministratoren nur dazu gebracht, es später erneut zu tun - und die Benutzer leiden in der Zwischenzeit.

Es gibt einen Grund, warum wir sagen, dass das Optimieren einer Datenbank sowohl eine Kunst als auch eine Wissenschaft ist. Dies liegt daran, dass nur sehr wenige feste Regeln existieren, die allgemein gelten. Die Probleme, die Sie auf einem System gelöst haben, sind keine Probleme auf einem anderen und umgekehrt. Es gibt keine richtige Antwort, wenn es darum geht, Abfragen zu optimieren, aber das bedeutet nicht, dass Sie aufgeben sollten.

Es gibt einige gute Prinzipien, denen Sie folgen können und die in der einen oder anderen Kombination zu Ergebnissen führen sollten. Ich habe sie in einer Liste von SQL-Vor- und Nachteilen zusammengefasst, die oft übersehen werden oder schwer zu erkennen sind. Diese Techniken sollten Ihnen ein wenig mehr Einblick in die Gedanken Ihrer Datenbankadministratoren sowie die Fähigkeit geben, produktionsorientiert über Prozesse nachzudenken.

1. Verwenden Sie nicht UPDATEanstelle vonCASE

Dieses Problem ist sehr häufig und obwohl es nicht schwer zu erkennen ist, übersehen viele Entwickler es oft, da die Verwendung UPDATEeinen natürlichen Ablauf hat, der logisch erscheint.

Nehmen Sie zum Beispiel dieses Szenario: Sie fügen Daten in eine temporäre Tabelle ein und benötigen sie, um einen bestimmten Wert anzuzeigen, wenn ein anderer Wert vorhanden ist. Vielleicht ziehen Sie sich vom Kundentisch zurück und möchten, dass jeder mit einem Auftragsvolumen von mehr als 100.000 US-Dollar als "Bevorzugt" gekennzeichnet wird. Auf diese Weise fügen Sie die Daten in die Tabelle ein und führen eine UPDATEAnweisung aus, um die CustomerRank-Spalte für alle Bestellungen mit einem Auftragswert von mehr als 100.000 US-Dollar auf "Bevorzugt" zu setzen. Das Problem ist, dass die UPDATEAnweisung protokolliert wird, was bedeutet, dass sie für jeden einzelnen Schreibvorgang in die Tabelle zweimal schreiben muss. Um dies zu umgehen, müssen Sie natürlich eine Inline- CASEAnweisung in der SQL-Abfrage selbst verwenden. Dadurch wird jede Zeile auf die Bestellmengenbedingung getestet und das Etikett "Bevorzugt" festgelegt, bevor es in die Tabelle geschrieben wird. Die Leistungssteigerung kann erstaunlich sein.

2. Verwenden Sie Code nicht blind wieder

Dieses Problem ist auch sehr häufig. Es ist sehr einfach, den Code eines anderen zu kopieren, da Sie wissen, dass er die benötigten Daten abruft. Das Problem ist, dass häufig viel mehr Daten abgerufen werden, als Sie benötigen, und Entwickler sich selten die Mühe machen, diese zu reduzieren, sodass sie am Ende eine riesige Datenmenge haben. Dies erfolgt normalerweise in Form einer zusätzlichen äußeren Verknüpfung oder einer zusätzlichen Bedingung in der WHEREKlausel. Sie können enorme Leistungssteigerungen erzielen, wenn Sie wiederverwendeten Code genau auf Ihre Bedürfnisse zuschneiden.

3. Ziehen Sie nur die Anzahl der Spalten, die Sie benötigen

Dieses Problem ähnelt dem Problem Nr. 2, ist jedoch spezifisch für Spalten. Es ist allzu einfach, alle Ihre Abfragen zu codieren, SELECT *anstatt die Spalten einzeln aufzulisten. Das Problem ist wiederum, dass mehr Daten abgerufen werden, als Sie benötigen. Ich habe diesen Fehler Dutzende Male gesehen. Ein Entwickler führt eine SELECT *Abfrage für eine Tabelle mit 120 Spalten und Millionen von Zeilen durch, verwendet jedoch nur drei bis fünf davon. Zu diesem Zeitpunkt verarbeiten Sie so viel mehr Daten als Sie benötigen. Es ist ein Wunder, dass die Abfrage überhaupt zurückgegeben wird. Sie verarbeiten nicht nur mehr Daten als Sie benötigen, sondern entziehen auch anderen Prozessen Ressourcen.

4. Nicht doppelt eintauchen

Hier ist eine andere, die ich öfter gesehen habe, als ich sollte: Eine gespeicherte Prozedur wird geschrieben, um Daten aus einer Tabelle mit Hunderten von Millionen Zeilen abzurufen. Der Entwickler benötigt Kunden, die in Kalifornien leben und ein Einkommen von mehr als 40.000 US-Dollar haben. Also fragt er nach Kunden, die in Kalifornien leben, und schreibt die Ergebnisse in eine temporäre Tabelle. Dann fragt er nach Kunden mit einem Einkommen von über 40.000 USD und legt diese Ergebnisse in einer anderen temporären Tabelle ab. Schließlich verbindet er beide Tische, um das Endprodukt zu erhalten.

Willst du mich verarschen? Dies sollte in einer einzigen Abfrage erfolgen. Stattdessen tauchen Sie einen übergroßen Tisch doppelt ein. Seien Sie kein Idiot: Fragen Sie große Tabellen nach Möglichkeit nur einmal ab - Sie werden feststellen, wie viel besser Ihre Prozeduren sind.

Ein etwas anderes Szenario ist, wenn eine Teilmenge einer großen Tabelle in mehreren Schritten in einem Prozess benötigt wird, wodurch die große Tabelle jedes Mal abgefragt wird. Vermeiden Sie dies, indem Sie die Teilmenge abfragen und an anderer Stelle beibehalten und die nachfolgenden Schritte auf Ihre kleinere Datenmenge verweisen.

6. Führen Sie Daten vor der Phase durch

Dies ist eines meiner Lieblingsthemen, weil es eine alte Technik ist, die oft übersehen wird. Wenn Sie über einen Bericht oder eine Prozedur (oder noch besser über eine Reihe von Prozeduren) verfügen, die ähnliche Verknüpfungen mit großen Tabellen ausführen, kann es für Sie von Vorteil sein, die Daten vorab bereitzustellen, indem Sie die Tabellen vorab verknüpfen und beibehalten in einen Tisch. Jetzt können die Berichte für diese vorab bereitgestellte Tabelle ausgeführt werden und den großen Join vermeiden.

Sie können diese Technik nicht immer verwenden, aber wenn Sie können, werden Sie feststellen, dass dies eine hervorragende Möglichkeit ist, Serverressourcen zu sparen.

Beachten Sie, dass viele Entwickler dieses Join-Problem umgehen, indem sie sich auf die Abfrage selbst konzentrieren und eine Nur-Ansicht für den Join erstellen, damit sie die Join-Bedingungen nicht immer wieder eingeben müssen. Das Problem bei diesem Ansatz ist jedoch, dass die Abfrage weiterhin für jeden Bericht ausgeführt wird, der sie benötigt. Wenn Sie die Daten vorab bereitstellen, führen Sie den Join nur einmal aus (z. B. 10 Minuten vor den Berichten), und alle anderen vermeiden den großen Join. Ich kann dir nicht sagen, wie sehr ich diese Technik liebe; In den meisten Umgebungen gibt es beliebte Tabellen, die ständig verbunden werden. Es gibt also keinen Grund, warum sie nicht vorab bereitgestellt werden können.

7. Löschen und aktualisieren Sie stapelweise

Hier ist eine weitere einfache Technik, die oft übersehen wird. Das Löschen oder Aktualisieren großer Datenmengen aus großen Tabellen kann ein Albtraum sein, wenn Sie es nicht richtig machen. Das Problem ist, dass diese beiden Anweisungen als einzelne Transaktion ausgeführt werden. Wenn Sie sie beenden müssen oder wenn dem System während der Arbeit etwas passiert, muss das System die gesamte Transaktion zurücksetzen. Dies kann sehr lange dauern. Diese Vorgänge können auch andere Transaktionen für ihre Dauer blockieren, was das System im Wesentlichen zum Engpass macht.

Die Lösung besteht darin, Löschungen oder Aktualisierungen in kleineren Stapeln durchzuführen. Dies löst Ihr Problem auf verschiedene Arten. Erstens, wenn die Transaktion aus irgendeinem Grund abgebrochen wird, muss nur eine kleine Anzahl von Zeilen zurückgesetzt werden, sodass die Datenbank viel schneller online zurückkehrt. Zweitens, während die kleineren Stapel auf die Festplatte übertragen werden, können sich andere einschleichen und einige Arbeiten ausführen, sodass die Parallelität erheblich verbessert wird.

In diesem Sinne haben es viele Entwickler im Kopf, dass diese Lösch- und Aktualisierungsvorgänge am selben Tag abgeschlossen werden müssen. Das stimmt nicht immer, besonders wenn Sie archivieren. Sie können diesen Vorgang so lange ausdehnen, wie Sie möchten, und die kleineren Chargen helfen dabei. Wenn Sie diese intensiven Vorgänge länger dauern können, verbringen Sie die zusätzliche Zeit damit, Ihr System nicht herunterzufahren.

8. Verwenden Sie temporäre Tabellen, um die Cursorleistung zu verbessern

Ich hoffe, wir alle wissen inzwischen, dass es am besten ist, sich von Cursorn fernzuhalten, wenn dies überhaupt möglich ist. Cursor leiden nicht nur unter Geschwindigkeitsproblemen, die an sich bei vielen Vorgängen ein Problem darstellen können, sondern sie können auch dazu führen, dass Ihr Vorgang andere Vorgänge viel länger als nötig blockiert. Dies verringert die Parallelität in Ihrem System erheblich.

Sie können die Verwendung von Cursorn jedoch nicht immer vermeiden. In diesen Fällen können Sie möglicherweise von Cursor-induzierten Leistungsproblemen Abstand nehmen, indem Sie stattdessen die Cursoroperationen für eine temporäre Tabelle ausführen. Nehmen Sie zum Beispiel einen Cursor, der durch eine Tabelle geht und einige Spalten basierend auf einigen Vergleichsergebnissen aktualisiert. Anstatt den Vergleich mit der Live-Tabelle durchzuführen, können Sie diese Daten möglicherweise in eine temporäre Tabelle einfügen und stattdessen den Vergleich mit dieser Tabelle durchführen. Dann haben Sie eine einzige UPDATEAussage gegen den Live-Tisch, die viel kleiner ist und nur für kurze Zeit Sperren enthält.

Wenn Sie Ihre Datenänderungen auf diese Weise ändern, kann dies die Parallelität erheblich erhöhen. Abschließend möchte ich sagen, dass Sie fast nie einen Cursor verwenden müssen. Es gibt fast immer eine satzbasierte Lösung. Sie müssen lernen, es zu sehen.

9. Verschachteln Sie keine Ansichten

Ansichten können praktisch sein, aber Sie müssen vorsichtig sein, wenn Sie sie verwenden. Während Ansichten dazu beitragen können, große Abfragen von Benutzern zu verschleiern und den Datenzugriff zu standardisieren, können Sie sich leicht in einer Situation befinden, in der Sie Ansichten haben, die Ansichten aufrufen, die Ansichten aufrufen, die Ansichten aufrufen. Dies wird als Verschachtelungsansichten bezeichnet und kann schwerwiegende Leistungsprobleme verursachen, insbesondere auf zwei Arten:

  • Erstens werden sehr wahrscheinlich viel mehr Daten zurückkommen, als Sie benötigen.
  • Zweitens gibt der Abfrageoptimierer einen fehlerhaften Abfrageplan auf und gibt ihn zurück.

Ich hatte einmal einen Kunden, der es liebte, Ansichten zu verschachteln. Der Client hatte eine Ansicht, die er für fast alles verwendete, da er zwei wichtige Verknüpfungen hatte. Das Problem war, dass die Ansicht eine Spalte mit 2 MB Dokumenten zurückgab. Einige der Dokumente waren noch größer. Der Client hat für fast jede einzelne Zeile in fast jeder einzelnen ausgeführten Abfrage mindestens 2 MB mehr über das Netzwerk übertragen. Natürlich war die Abfrageleistung miserabel.

Und keine der Abfragen hat diese Spalte tatsächlich verwendet! Natürlich war die Säule sieben Ansichten tief vergraben, so dass es selbst schwierig war, sie zu finden. Als ich die Dokumentenspalte aus der Ansicht entfernte, stieg die Zeit für die größte Abfrage von 2,5 Stunden auf 10 Minuten. Als ich schließlich die verschachtelten Ansichten mit mehreren unnötigen Verknüpfungen und Spalten entwirrte und eine einfache Abfrage schrieb, sank die Zeit für dieselbe Abfrage auf Subsekunden.