Tipps zur Beschleunigung von SQL-Abfragen, um zu verhindern, dass Datenbankcode die Datenbank blockiert

Da die Datenbankwelt noch relativ unausgereift ist, haben SQL-Entwickler auf allen Plattformen Probleme und machen immer wieder die gleichen Fehler. Natürlich machen Datenbankanbieter einige Fortschritte und kämpfen weiterhin mit größeren Problemen.

Ganz gleich, ob SQL-Entwickler Code auf SQL Server, Oracle, DB2, Sybase, MySQL oder einer anderen relationalen Datenbankplattform schreiben: Parallelität, Ressourcenverwaltung, Speicherplatzverwaltung und Laufzeitgeschwindigkeit bereiten ihnen immer noch Probleme.

Ein Aspekt des Problems ist, dass es kein Allheilmittel gibt; ich kann bei fast jeder Best Practice auf mindestens eine Ausnahme verweisen.

Wir sagen, dass das Optimieren einer Datenbank sowohl eine Kunst als auch eine Wissenschaft ist, was Sinn macht, da es nur wenige feste Regeln gibt, die überall gelten. Ein Problem, das Sie auf einem System lösen, ist kein Problem auf einem anderen und umgekehrt.

Wenn es um Tuning-Fragen geht, gibt es keine richtige Antwort, aber das bedeutet nicht, dass Sie aufgeben sollten. Sie können einige der folgenden Prinzipien befolgen und hoffentlich großartige Ergebnisse erzielen.

Verwenden Sie nicht UPDATE anstelle von CASE

Dieses Problem ist sehr häufig, aber schwer zu erkennen. Viele Entwickler ignorieren dieses Problem oft, weil die Verwendung von UPDATE natürlich und logisch erscheint.

Nehmen Sie dieses Szenario als Beispiel: Sie fügen Daten in eine temporäre Tabelle ein, und wenn ein anderer Wert vorhanden ist, benötigen Sie diesen, um einen bestimmten Wert anzuzeigen.

Vielleicht ziehen Sie Datensätze aus der Kundentabelle und möchten Kunden mit Bestellungen über 100.000 $ als „Bevorzugt“ markieren.

Sie fügen also Daten in die Tabelle ein, führen eine UPDATE-Anweisung aus und setzen die CustomerRank-Spalte für jeden Kunden mit einem Bestellwert von mehr als 100.000 US-Dollar auf „Bevorzugt“.

Das Problem besteht darin, dass die UPDATE-Anweisung protokolliert wird, was bedeutet, dass sie jedes Mal, wenn sie in die Tabelle geschrieben wird, zweimal geschrieben wird.

Lösung: Verwenden Sie eine Inline-CASE-Anweisung in der SQL-Abfrage, die die Bestellmengenbedingung jeder Zeile überprüft und das „Preferred“-Tag festlegt, bevor sie in die Tabelle geschrieben wird.

Verwenden Sie Code nicht blind wieder.

Dieses Problem tritt auch sehr häufig auf. Es ist für uns einfach, von anderen geschriebenen Code zu kopieren, weil Sie wissen, dass er die Daten erhält, die Sie benötigen.

Das Problem besteht darin, dass oft zu viele Daten benötigt werden, die Sie nicht benötigen, und Entwickler sie selten rationalisieren, sodass Sie am Ende viele Daten haben.

Dies äußert sich normalerweise in einem zusätzlichen äußeren Join oder einer zusätzlichen Bedingung in der WHERE-Klausel. Wenn Sie den wiederverwendeten Code genau an Ihre Anforderungen anpassen, können Sie die Leistung erheblich verbessern.

Extrahieren Sie so viele Spalten, wie Sie benötigen

Diese Frage ähnelt Frage 2, ist jedoch nur für die Spalten gültig. Es ist einfach, alle Abfragen mit SELECT* zu codieren, anstatt die Spalten einzeln aufzulisten.

Das gleiche Problem besteht darin, dass zu viele Daten extrahiert werden, die Sie nicht benötigen. Ich habe diesen Fehler unzählige Male gesehen. Ein Entwickler führt eine SELECT*-Abfrage für eine 120-Spalten-Tabelle mit Millionen von Zeilen durch, verwendet jedoch nur drei oder fünf der Spalten.

Sie haben es also mit viel mehr Daten zu tun, als Sie tatsächlich benötigen, und es grenzt an ein Wunder, dass die Abfrage Ergebnisse liefert. Sie verarbeiten nicht nur zu viele Daten, die Sie nicht benötigen, sondern entziehen auch anderen Prozessen Ressourcen.

Nicht zweimal abfragen (Double-Dip)

Das ist ein weiterer Fehler, den ich bei vielen Leuten sehe: das Schreiben einer gespeicherten Prozedur, um Daten aus einer Tabelle mit Hunderten Millionen Zeilen zu extrahieren .

Der Entwickler wollte Informationen über Kunden extrahieren, die in Kalifornien leben und mehr als 40.000 US-Dollar pro Jahr verdienen. Also befragt er Kunden, die in Kalifornien leben, und fügt die Abfrageergebnisse in eine temporäre Tabelle ein.

Befragen Sie dann Kunden mit einem Jahreseinkommen von mehr als 40.000 US-Dollar und tragen Sie die Ergebnisse in eine andere temporäre Tabelle ein. Schließlich fügt er die beiden Tische zusammen, um das Endergebnis zu erhalten.

Willst du Witze machen? Dies sollte mit einer einzigen Abfrage erfolgen, stattdessen fragen Sie eine sehr große Tabelle zweimal ab. Seien Sie nicht dumm: Versuchen Sie, eine große Tabelle nur einmal abzufragen, und Sie werden feststellen, dass die gespeicherte Prozedur viel schneller ausgeführt wird.

Ein etwas anderes Szenario liegt vor, wenn mehrere Schritte eines Prozesses eine Teilmenge einer großen Tabelle erfordern, was dazu führt, dass die große Tabelle jedes Mal abgefragt wird.

Um dieses Problem zu vermeiden, fragen Sie einfach diese Teilmenge ab, speichern Sie sie an anderer Stelle und richten Sie dann die nachfolgenden Schritte auf diesen kleineren Datensatz aus.

Wissen Sie, wann Sie temporäre Tabellen verwenden sollten

Dieses Problem ist etwas schwieriger zu lösen, aber der Effekt ist erheblich. Temporäre Tabellen können in vielen Situationen verwendet werden, beispielsweise um zu verhindern, dass eine große Tabelle zweimal abgefragt wird. Temporäre Tabellen können auch verwendet werden, um die für die Verknüpfung großer Tabellen erforderliche Rechenleistung deutlich zu reduzieren.

Wenn Sie eine Tabelle mit einer großen Tabelle verknüpfen müssen und es Bedingungen für die große Tabelle gibt, extrahieren Sie einfach den erforderlichen Teil der Daten aus der großen Tabelle in eine temporäre Tabelle und verknüpfen Sie sie dann mit der temporären Tabelle. Dies kann die Abfrageleistung verbessern.

Dies ist auch hilfreich, wenn die gespeicherte Prozedur mehrere Abfragen enthält, die ähnliche Verknüpfungen mit derselben Tabelle durchführen müssen.

Vorstufendaten

Dies ist eines meiner liebsten Gesprächsthemen, da es sich um eine alte Methode handelt, die oft übersehen wird.

Wenn Sie über einen Bericht oder eine gespeicherte Prozedur (oder eine Gruppe davon) verfügen, die ähnliche Verknüpfungsvorgänge für große Tabellen ausführt, stellen Sie die Daten vorab bereit, indem Sie die Tabellen im Voraus verknüpfen und sie in einer Tabelle beibehalten kann Ihnen eine große Hilfe sein.

Berichte können jetzt anhand dieser Pre-Staging-Tabelle ausgeführt werden, wodurch große Verknüpfungen vermieden werden. Sie werden diese Methode nicht immer verwenden können, aber wenn Sie sie einmal nutzen, werden Sie feststellen, dass sie eine großartige Möglichkeit ist, Serverressourcen zu sparen.

Bitte beachten Sie: Viele Entwickler umgehen dieses Join-Problem, indem sie sich auf die Abfrage selbst konzentrieren und eine schreibgeschützte Ansicht basierend auf dem Join erstellen, sodass sie die Join-Kriterien nicht immer wieder eingeben müssen.

Aber das Problem bei diesem Ansatz besteht darin, dass Sie die Abfrage immer noch für jeden Bericht ausführen müssen, der sie erfordert. Wenn Sie die Daten vorab bereitstellen, müssen Sie die Verbindung nur einmal ausführen (z. B. 10 Minuten vor dem Bericht) und andere können die große Verbindung vermeiden.

Sie wissen nicht, wie sehr mir dieser Trick gefällt. In den meisten Umgebungen sind einige häufig verwendete Tabellen immer verbunden, daher gibt es keinen Grund, warum sie nicht zuerst vorab bereitgestellt werden können.

Batch-Löschen und Aktualisieren

Dies ist ein weiterer oft übersehener Tipp: Das Löschen oder Aktualisieren großer Datenmengen aus einer großen Tabelle kann ein Albtraum sein, wenn Sie es nicht richtig machen.

Das Problem besteht darin, dass beide Anweisungen als eine einzige Transaktion ausgeführt werden. Wenn Sie sie beenden müssen oder das System während der Ausführung auf ein Problem stößt, muss das System die gesamte Transaktion zurücksetzen, was viel Zeit in Anspruch nimmt.

Diese Vorgänge blockieren während der Dauer auch andere Transaktionen, was tatsächlich zu einem Engpass im System führt. Die Lösung besteht darin, in kleinen Mengen zu löschen oder zu aktualisieren.

Dies löst das Problem auf verschiedene Weise:

Egal aus welchem ​​Grund die Transaktion beendet wird, es gibt nur eine kleine Anzahl von Zeilen, die zurückgesetzt werden müssen, sodass die Datenbank wieder online ist viel schneller.

Wenn Transaktionen in kleinen Mengen auf der Festplatte festgeschrieben werden, können andere Transaktionen hinzukommen, um einen Teil der Arbeit zu erledigen, wodurch die Parallelität erheblich verbessert wird.

In ähnlicher Weise sind viele Entwickler hartnäckig davon überzeugt, dass diese Lösch- und Aktualisierungsvorgänge am selben Tag abgeschlossen werden müssen. Dies ist nicht immer der Fall, insbesondere wenn Sie archivieren.

Wenn Sie den Vorgang verlängern müssen, können Sie dies tun. Kleine Chargen helfen dabei. Wenn Sie länger brauchen, um diese intensiven Vorgänge auszuführen, verlangsamen Sie das System nicht.

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

Cursoren sollten Sie möglichst vermeiden. Cursor haben nicht nur Geschwindigkeitsprobleme, die für viele Vorgänge selbst ein großes Problem darstellen, sondern sie können auch dazu führen, dass Ihre Vorgänge andere Vorgänge für lange Zeit blockieren, was die Parallelität des Systems erheblich verringert.

Die Verwendung von Cursorn lässt sich jedoch nicht immer vermeiden. Wenn die Verwendung von Cursorn nicht vermieden werden kann, können Sie stattdessen Cursoroperationen für temporäre Tabellen ausführen, um die durch Cursor verursachten Leistungsprobleme zu beseitigen.

Erwägen Sie beispielsweise, eine Tabelle zu konsultieren und die Cursor mehrerer Spalten basierend auf einigen Vergleichsergebnissen zu aktualisieren. Möglicherweise können Sie diese Daten in eine temporäre Tabelle einfügen und mit der temporären Tabelle statt mit der aktiven Tabelle vergleichen.

Sie können dann eine einzelne UPDATE-Anweisung für eine viel kleinere, kürzer gesperrte aktive Tabelle ausführen.

Durch solche Datenänderungen kann die Parallelität erheblich verbessert werden. Abschließend möchte ich sagen, dass Sie überhaupt keine Cursor verwenden müssen, es gibt immer eine sammlungsbasierte Lösung.

Tabellenwertige Funktionen verwenden

Dies ist eine meiner absoluten Lieblingstechniken, weil es sich um ein Geheimnis handelt, das nur Experten kennen.

Bei Verwendung einer Skalarfunktion in der SELECT-Liste einer Abfrage wird die Funktion für jede Zeile im Ergebnissatz aufgerufen, was die Leistung großer Abfragen erheblich reduzieren kann.

Sie können jedoch die Skalarfunktion in eine Tabellenwertfunktion umwandeln und dann CROSS APPLY in der Abfrage verwenden, was die Leistung erheblich verbessern kann.

Führen Sie keine großen Operationen an vielen Tabellen im selben Stapel durch.

Das mag offensichtlich erscheinen, ist es aber nicht. Ich werde ein weiteres anschauliches Beispiel verwenden, weil es den Punkt besser verdeutlicht.

Ich habe ein System mit vielen Blockierungen und viele Vorgänge bleiben hängen. Es wurde festgestellt, dass eine mehrmals täglich ausgeführte Löschroutine in expliziten Transaktionen Daten aus 14 Tabellen löschte. Das Verarbeiten aller 14 Tabellen in einer Transaktion bedeutet, dass jede Tabelle gesperrt wird, bis alle Löschvorgänge abgeschlossen sind.

Die Lösung besteht darin, das Löschen jeder Tabelle in separate Transaktionen aufzuteilen, sodass jede Löschtransaktion nur eine Tabelle sperrt.

Dadurch werden andere Tabellen freigegeben, was das Blockieren erleichtert und die weitere Ausführung anderer Vorgänge ermöglicht. Sie sollten große Transaktionen wie diese immer in separate kleinere Transaktionen aufteilen, um eine Blockierung zu verhindern.

Verwenden Sie keine Auslöser

Dieser ist größtenteils derselbe wie der vorherige, aber er ist erwähnenswert. Das Problem mit Triggern: Was auch immer der Trigger tun soll, wird in derselben Transaktion wie der ursprüngliche Vorgang ausgeführt.

Wenn Sie einen Trigger schreiben, der Daten in eine andere Tabelle einfügt, während eine Zeile in der Tabelle „Bestellungen“ aktualisiert wird, werden beide Tabellen gesperrt, bis der Trigger abgeschlossen ist.

Wenn Sie nach der Aktualisierung Daten in eine andere Tabelle einfügen müssen, fügen Sie die Aktualisierung und Einfügung in eine gespeicherte Prozedur ein und führen Sie sie in separaten Transaktionen aus.

Wenn Sie ein Rollback durchführen müssen, ist das ganz einfach, ohne dass beide Tabellen gleichzeitig gesperrt werden müssen. Halten Sie die Transaktionen wie immer kurz und sperren Sie nicht mehrere Ressourcen gleichzeitig.

Gruppieren Sie sich nicht auf GUIDs

Nach all den Jahren kann ich nicht glauben, dass wir immer noch mit diesem Problem zu kämpfen haben. Aber ich stoße immer noch mindestens zweimal im Jahr auf Cluster-GUIDs.

Eine GUID (Globally Unique Identifier) ​​ist eine zufällig generierte 16-Byte-Zahl. Das Sortieren der Daten in Ihrer Tabelle nach dieser Spalte führt viel schneller zu einer Tabellenfragmentierung als die Verwendung eines stetig ansteigenden Werts wie DATE oder IDENTITY.

Vor ein paar Jahren habe ich einen Benchmark durchgeführt, bei dem ich eine Reihe von Daten in eine Tabelle mit einer gruppierten GUID und dieselben Daten in eine andere Tabelle mit einer IDENTITY-Spalte eingefügt habe.

Die GUID-Tabelle war so fragmentiert, dass die Leistung bereits nach 15 Minuten um Tausende Prozentpunkte einbrach.

Nach 5 Stunden sank die Leistung der IDENTITY-Tabelle nur um ein paar Prozentpunkte, und das gilt nicht nur für GUIDs, sondern für alle flüchtigen Spalten.

Wenn Sie nur überprüfen möchten, ob die Daten vorhanden sind, zählen Sie nicht die Zeilen

Diese Situation kommt sehr häufig vor. Sie müssen anhand der überprüfen, ob die Daten vorhanden sind Als Ergebnis dieser Prüfung müssen Sie bestimmte Vorgänge ausführen.

Ich sehe oft Leute, die SELECT COUNT(*) FROMdbo.T1 ausführen, um zu überprüfen, ob die Daten vorhanden sind:

1. SET @CT=(SELECT COUNT(*) FROM

2. dbo.T1);

3. If@CT>0

4. BEGIN

6. ENDE

Das ist völlig unnötig, wenn du überprüfen möchtest, ob die Daten vorliegen existiert, machen Sie einfach Folgendes:

1. If EXISTS (SELECT 1 FROM dbo.T1)

2. BEGIN

4. ENDE

Zähle nicht alles in der Tabelle, sondern gehe einfach zur ersten Zeile zurück, die du findest . SQL Server ist intelligent genug, um EXISTS korrekt zu verwenden, und der zweite Codeteil liefert superschnell Ergebnisse.

Je größer die Tabelle, desto offensichtlicher ist die Lücke in diesem Aspekt. Tun Sie das Richtige, bevor Ihre Daten zu groß werden. Es ist nie zu früh, Ihre Datenbank zu optimieren.

Ich habe dieses Beispiel tatsächlich gerade in einer meiner Produktionsdatenbanken ausgeführt, anhand einer Tabelle mit 270 Millionen Zeilen.

Die erste Abfrage dauerte 15 Sekunden und enthielt 456197 logische Lesevorgänge. Die zweite Abfrage lieferte Ergebnisse in weniger als 1 Sekunde und enthielt nur 5 logische Lesevorgänge.

Wenn Sie jedoch wirklich die Zeilen einer Tabelle zählen müssen und die Tabelle groß ist, besteht eine andere Möglichkeit darin, aus den Systemtabellen zu extrahieren.

SELECT rows fromsysindexes liefert Ihnen alle indizierten Zeilen Nummer.

Und da der Clustered-Index die Daten selbst darstellt, fügen Sie einfach WHERE indid = 1 hinzu, um die Tabellenzeilen zu erhalten, und fügen Sie dann einfach den Tabellennamen ein.

Die letzte Abfrage lautet also:

1.SELECT Zeilen aus Sysindexes, wobei object_name(id)='T1'und indexid =1

In meinem 2.7 In einer Tabelle mit 100 Millionen Zeilen werden die Ergebnisse in weniger als 1 Sekunde zurückgegeben und es gibt nur 6 logische Lesevorgänge. Jetzt ist die Leistung anders.

Keine Rückwärtssuche durchführen

Nehmen Sie die einfache Abfrage SELECT * FROMCustomers WHERE RegionID Sie können bei dieser Abfrage keinen Index verwenden, da es sich um eine umgekehrte Suche handelt, die einen zeilenweisen Vergleich mithilfe eines Tabellenscans erfordert. Wenn Sie eine solche Aufgabe ausführen müssen, stellen Sie möglicherweise fest, dass die Leistung viel besser ist, wenn Sie die Abfrage so umschreiben, dass sie einen Index verwendet.

Die Abfrage lässt sich leicht wie folgt umschreiben:

1.SELECT * FROM Customers WHERE RegionID

Diese Abfrage verwendet einen Index. Wenn Ihr Datensatz also groß ist, ist die Leistung viel besser als bei der Tabellenscan-Version.

Natürlich ist nichts so einfach und vielleicht ist die Leistung schlechter, also probieren Sie es aus, bevor Sie es verwenden. Es funktioniert zu 100 %, obwohl so viele Faktoren eine Rolle spielen.

Endlich wurde mir klar, dass diese Abfrage gegen Regel Nummer 4 verstößt: Nicht zweimal abfragen, aber es zeigt auch, dass es keine festen Regeln gibt. Obwohl wir hier zweimal abfragen, tun wir dies, um teure Tabellenscans zu vermeiden.

Sie werden nicht immer alle diese Tipps nutzen können, aber wenn Sie sie im Hinterkopf behalten, werden Sie eines Tages damit einige große Probleme lösen können.

Das Wichtigste, woran Sie sich erinnern sollten, ist: Betrachten Sie das, was ich sage, nicht als Dogma. Probieren Sie es in Ihrer tatsächlichen Umgebung aus. Die gleichen Lösungen funktionieren nicht in jedem Fall, aber ich verwende sie ständig, wenn ich Probleme mit der Leistung behebe, und sie funktionieren immer.