So schreiben und optimieren Sie gespeicherte SQL Server-Prozeduren

[Einführung] Während des Entwicklungsprozesses einer Datenbank treten häufig komplexe Geschäftslogiken und Datenbankoperationen auf. Zu diesem Zeitpunkt wird SP zum Kapseln von Datenbankoperationen verwendet. Wenn es im Projekt viele SPs gibt und es keine bestimmten Standards zum Schreiben gibt, wird es schwierig, das System in Zukunft zu warten und es schwierig zu machen, die Logik der großen SPs zu verstehen. Darüber hinaus ist es beispielsweise

Während des Entwicklungsprozesses der Datenbank treten häufig komplexe Probleme auf. Für Geschäftslogik und Datenbankoperationen wird SP zu diesem Zeitpunkt verwendet, um Datenbankoperationen zu kapseln. Wenn das Projekt viele SPs hat und das Schreiben nicht standardisiert ist, wird es schwierig, das System in Zukunft zu warten und die Logik der großen SPs zu verstehen. Darüber hinaus ist die Datenmenge in der Datenbank groß Wenn das Projekt groß ist oder hohe Leistungsanforderungen an die SPs stellt, werden Sie auf ein Optimierungsproblem stoßen. Andernfalls kann die Geschwindigkeit sehr langsam sein. Nach persönlicher Erfahrung ist ein optimierter SP sogar hunderte Male effizienter als ein SP mit schlechter Leistung .
Details:

1. Wenn Entwickler Tabellen oder Ansichten aus anderen Bibliotheken verwenden, müssen sie Ansichten in der aktuellen Bibliothek erstellen, um bibliotheksübergreifende Vorgänge zu implementieren sie direkt. „databse.dbo.table_name“, da sp_depends die vom SP verwendete datenbankübergreifende Tabelle oder Ansicht nicht anzeigen kann, was für die Überprüfung unpraktisch ist.

2. Vor dem Einreichen von SP müssen Entwickler den Abfrageplan mithilfe von „set showplan on“ analysiert und eine eigene Abfrageoptimierungsprüfung durchgeführt haben.

3. Um die Effizienz des Programmbetriebs zu verbessern und Anwendungen zu optimieren, sollten Sie während des SP-Schreibprozesses auf die folgenden Punkte achten:

(a) SQL Verwendungsspezifikationen:

i Versuchen Sie, große Transaktionsvorgänge zu vermeiden und verwenden Sie die Holdlock-Klausel mit Vorsicht, um die Systemparallelität zu verbessern.

ii. Versuchen Sie, den wiederholten Zugriff auf dieselbe(n) Tabelle(n) zu vermeiden, insbesondere auf Tabellen mit großen Datenmengen. Sie können erwägen, Daten zunächst auf der Grundlage von Bedingungen in eine temporäre Tabelle zu extrahieren und dann eine Verbindung herzustellen.

iii Versuchen Sie, die Verwendung von Cursorn zu vermeiden, da Cursor weniger effizient sind. Wenn die vom Cursor verarbeiteten Daten mehr als 10.000 Zeilen umfassen, sollten Sie versuchen, Cursorschleifen zu vermeiden. Führen Sie dann den Tabellenverknüpfungsvorgang aus.

Achten Sie auf die Schreibweise der Wörter. Die Reihenfolge der Bedingungssätze sollte entsprechend der Indexreihenfolge und der Bereichsgröße festgelegt werden Reihenfolge im Einklang mit der Indexreihenfolge und dem Bereich von groß nach klein.

v. Führen Sie keine Funktionen, arithmetischen Operationen oder andere Ausdrucksoperationen auf der linken Seite von „=" in der where-Klausel aus, da das System sonst den Index möglicherweise nicht korrekt verwenden kann.

vi Versuchen Sie, „exists“ anstelle von „select count(1)“ zu verwenden, um festzustellen, ob ein Datensatz vorhanden ist. Die Zählfunktion wird nur verwendet, wenn alle Zeilen in der Tabelle gezählt werden und count(1) größer ist praktischer als count(*).

vii. Versuchen Sie, „>=“ anstelle von „>“ zu verwenden.

viii. Achten Sie auf die Ersetzung zwischen einigen oder-Klauseln und Union-Klauseln

ix. Achten Sie auf die Datentypen von Verbindungen zwischen Tabellen und vermeiden Sie Unterschiede zwischen verschiedenen Typen der Daten.

x Achten Sie auf die Beziehung zwischen Parametern und Datentypen in der gespeicherten Prozedur.

xi. Achten Sie auf das Datenvolumen von Einfüge- und Aktualisierungsvorgängen, um Konflikte mit anderen Anwendungen zu vermeiden. Wenn die Datenmenge 200 Datenseiten (400 KB) überschreitet, aktualisiert das System die Sperre und die Sperre auf Seitenebene wird auf eine Sperre auf Tabellenebene aktualisiert.

(b) Indexnutzungsspezifikationen:

i Die Erstellung von Indizes sollte im Zusammenhang mit der Anwendung in Betracht gezogen werden Große OLTP-Tabellen sollten 6 Indizes nicht überschreiten.

ii. Versuchen Sie, Indexfelder als Abfragebedingungen zu verwenden, insbesondere Clustered-Indizes. Bei Bedarf können Sie die Angabe des Index erzwingen

iii. Vermeiden Sie Paarungen. Führen Sie einen Tabellenscan durch, wenn Sie große Tabellen abfragen, und erwägen Sie bei Bedarf die Erstellung neuer Indizes.

iv. Wenn ein Indexfeld als Bedingung verwendet wird und der Index ein gemeinsamer Index ist, muss das erste Feld im Index als Bedingung verwendet werden, um sicherzustellen, dass das System den Index verwendet. andernfalls wird der Index nicht verwendet.

v. Achten Sie auf die Wartung des Index, erstellen Sie den Index regelmäßig neu und kompilieren Sie die gespeicherte Prozedur neu.

(c) Tempdb-Nutzungsspezifikationen:

i Versuchen Sie, die Verwendung von „distinct“, „order by“, „group by“, „have“, „join“, „cumute“ zu vermeiden , da diese Anweisungen die Belastung von tempdb erhöhen.

ii. Vermeiden Sie das häufige Erstellen und Löschen temporärer Tabellen und reduzieren Sie den Verbrauch von Systemtabellenressourcen.

iii. Wenn die auf einmal eingefügte Datenmenge groß ist, können Sie „Auswählen in“ anstelle von „Tabelle erstellen“ verwenden, um Protokolle zu vermeiden und die Geschwindigkeit zu verbessern ist nicht groß, um das System zu entlasten. Für Tabellenressourcen wird empfohlen, zuerst eine Tabelle zu erstellen und dann einzufügen.

iv. Wenn die temporäre Tabelle eine große Datenmenge enthält und indiziert werden muss, sollte der Prozess der Erstellung der temporären Tabelle und der Indizierung in einer separaten unterspeicherten Prozedur platziert werden, um sicherzustellen, dass die Das System kann problemlos den Index der temporären Tabelle verwenden.

v. Wenn temporäre Tabellen verwendet werden, müssen alle temporären Tabellen am Ende der gespeicherten Prozedur explizit gelöscht werden. Dadurch kann eine langfristige Sperrung vermieden werden der Systemtabellen.

vi. Seien Sie vorsichtig, wenn Sie Abfragen und Änderungen zwischen großen temporären Tabellen und anderen großen Tabellen verbinden, um die Belastung der Systemtabellen zu verringern, da dieser Vorgang die tempdb-Systemtabelle mehrmals in einer Anweisung verwendet.

(d) Angemessener Algorithmuseinsatz:

Basierend auf der oben genannten SQL-Optimierungstechnologie und den SQL-Optimierungsinhalten im ASE Tuning-Handbuch, kombiniert mit praktischen Anwendungen, werden mehrere Algorithmen zum Vergleich verwendet, um die Methode zu erhalten, die die wenigsten Ressourcen verbraucht und am effizientesten ist . Es stehen spezifische ASE-Tuning-Befehle zur Verfügung: „Statistik-IO aktivieren“, „Statistik-Zeit aktivieren“, „Showplan aktivieren“ usw.

Das obige ist der detaillierte Inhalt vonSo schreiben und optimieren Sie gespeicherte SQL Server-Prozeduren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!