SQL-Datenbank-Leistungsoptimierung für Entwickler

Die Optimierung der SQL-Leistung kann eine unglaublich schwierige Aufgabe sein, insbesondere bei der Arbeit mit großen Datenmengen, bei denen selbst die kleinste Änderung dramatische (positive oder negative) Auswirkungen auf die Leistung haben kann.

In mittelständischen und großen Unternehmen wird der Großteil der SQL-Leistungsoptimierung von einem Datenbankadministrator (DBA) durchgeführt. Aber glauben Sie mir, es gibt viele Entwickler da draußen, die DBA-ähnliche Aufgaben ausführen müssen. Außerdem haben viele der Unternehmen, die DBAs haben, die ich gesehen habe, oft Schwierigkeiten, gut mit Entwicklern zusammenzuarbeiten – die Positionen erfordern einfach unterschiedliche Arten der Problemlösung, was zu Meinungsverschiedenheiten unter den Kollegen führen kann.

Darüber hinaus kann auch die Unternehmensstruktur eine Rolle spielen. Angenommen, das DBA-Team befindet sich mit all seinen Datenbanken im 10. Stock, während sich die Entwickler im 15. Stock oder sogar in einem anderen Gebäude mit einer völlig separaten Berichtsstruktur befinden – es ist sicherlich schwierig, unter diesen Bedingungen reibungslos zusammenzuarbeiten.

In diesem Artikel möchte ich zwei Dinge erreichen:

1. Bieten Sie Entwicklern einige entwicklerseitige SQL-Leistungsoptimierungstechniken.
2. Erklären Sie, wie Entwickler und DBAs effektiv zusammenarbeiten können.

SQL-Leistungsoptimierung (in der Codebasis): Indizes

Wenn Sie ein absoluter Datenbank-Neuling sind und sich sogar fragen: „Was ist SQL-Leistungsoptimierung?“, sollten Sie wissen, dass die Indizierung eine effektive Möglichkeit ist, Ihre SQL-Datenbank zu optimieren, die während der Entwicklung oft vernachlässigt wird. Grundsätzlich ist ein Index eine Datenstruktur, die die Geschwindigkeit von Datenabrufvorgängen in einer Datenbanktabelle verbessert, indem sie schnelle zufällige Suchen und einen effizienten Zugriff auf geordnete Datensätze bereitstellt. Das bedeutet, dass Sie nach dem Erstellen eines Indexes Ihre Zeilen schneller als zuvor auswählen oder sortieren können.

Indizes werden auch verwendet, um einen Primärschlüssel oder eindeutigen Index zu definieren, der garantiert, dass keine anderen Spalten dieselben Werte haben. Natürlich ist die Indexierung von Datenbanken ein riesiges und interessantes Thema, dem ich mit dieser kurzen Beschreibung nicht gerecht werden kann (aber hier ist eine ausführlichere Beschreibung).

Wenn Sie mit Indizes noch nicht vertraut sind, empfehle ich die Verwendung dieses Diagramms beim Strukturieren Ihrer Abfragen:

Grundsätzlich besteht das Ziel darin, die wichtigsten Such- und Bestellspalten zu indizieren.

Beachten Sie, dass Sie bei der Indizierung vorsichtig sein sollten, wenn Ihre Tabellen ständig von INSERT , UPDATE und DELETE gehämmert werden – Sie könnten am Ende die Leistung beeinträchtigen, da alle Indizes nach diesen Operationen geändert werden müssen.

Darüber hinaus löschen DBAs häufig ihre SQL-Indizes, bevor sie Stapeleinfügungen von über einer Million Zeilen durchführen, um den Einfügungsprozess zu beschleunigen. Nachdem der Stapel eingefügt wurde, erstellen sie die Indizes neu. Denken Sie jedoch daran, dass sich das Löschen von Indizes auf jede Abfrage auswirkt, die in dieser Tabelle ausgeführt wird. Daher wird dieser Ansatz nur empfohlen, wenn mit einer einzelnen großen Insertion gearbeitet wird.

SQL-Optimierung: Ausführungspläne in SQL Server

Übrigens: Das Ausführungsplan-Tool in SQL Server kann nützlich sein, um Indizes zu erstellen.

Seine Hauptfunktion besteht darin, die vom SQL Server-Abfrageoptimierer ausgewählten Datenabrufmethoden grafisch anzuzeigen. Wenn Sie sie noch nie zuvor gesehen haben, gibt es eine detaillierte Komplettlösung.

Um den Ausführungsplan (in SQL Server Management Studio) abzurufen, klicken Sie einfach auf „Aktuellen Ausführungsplan einschließen“ (STRG + M), bevor Sie Ihre Abfrage ausführen.

Danach erscheint ein dritter Reiter mit dem Namen „Ausführungsplan“. Möglicherweise wird ein fehlender Index erkannt. Um es zu erstellen, klicken Sie einfach mit der rechten Maustaste in den Ausführungsplan und wählen Sie „Fehlende Indexdetails…“. So einfach ist das!

( Klicken zum Vergrößern )

SQL-Tuning: Vermeiden Sie Codierungsschleifen

Stellen Sie sich ein Szenario vor, in dem 1000 Abfragen nacheinander auf Ihre Datenbank einhämmern. Etwas wie:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Sie sollten solche Schleifen in Ihrem Code vermeiden. Beispielsweise könnten wir das obige Snippet transformieren, indem wir eine eindeutige INSERT oder UPDATE Anweisung mit mehreren Zeilen und Werten verwenden:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Stellen Sie sicher, dass Ihre WHERE Klausel das Aktualisieren des gespeicherten Werts vermeidet, wenn er mit dem vorhandenen Wert übereinstimmt. Eine solche triviale Optimierung kann die Leistung von SQL-Abfragen drastisch steigern, indem nur Hunderte von Zeilen statt Tausenden aktualisiert werden. Zum Beispiel:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

SQL-Tuning: Vermeiden Sie korrelierte SQL-Unterabfragen

Eine korrelierte Unterabfrage ist eine Abfrage, die Werte aus der übergeordneten Abfrage verwendet. Diese Art von SQL-Abfrage wird in der Regel Zeile für Zeile ausgeführt, einmal für jede Zeile, die von der äußeren Abfrage zurückgegeben wird, und verringert daher die Leistung der SQL-Abfrage. Neue SQL-Entwickler werden oft dabei erwischt, wie sie ihre Abfragen auf diese Weise strukturieren – weil es normalerweise der einfache Weg ist.

Hier ist ein Beispiel für eine korrelierte Unterabfrage:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

Das Problem besteht insbesondere darin, dass die innere Abfrage ( SELECT CompanyName… ) für jede Zeile ausgeführt wird, die von der äußeren Abfrage ( SELECT c.Name… ) zurückgegeben wird. Aber warum für jede Zeile, die von der äußeren Abfrage verarbeitet wird, immer wieder das Company durchgehen?

Eine effizientere Technik zur Optimierung der SQL-Leistung wäre, die korrelierte Unterabfrage als Join umzugestalten:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

In diesem Fall gehen wir am Anfang nur einmal die Company -Tabelle durch und VERKNÜPFEN sie mit der Customer -Tabelle. Von da an können wir die benötigten Werte ( co.CompanyName ) effizienter auswählen.

SQL-Tuning: Sparsam auswählen

Einer meiner Lieblingstipps zur SQL-Optimierung ist, SELECT * zu vermeiden! Stattdessen sollten Sie die spezifischen Spalten, die Sie benötigen, einzeln hinzufügen. Auch das klingt einfach, aber ich sehe diesen Fehler überall. Stellen Sie sich eine Tabelle mit Hunderten von Spalten und Millionen von Zeilen vor – wenn Ihre Anwendung wirklich nur ein paar Spalten benötigt, macht es keinen Sinn, alle Daten abzufragen. Es ist eine massive Ressourcenverschwendung. ( Weitere Probleme finden Sie hier. )

Zum Beispiel:

 SELECT * FROM Employees

vs.

 SELECT FirstName, City, Country FROM Employees

Wenn Sie wirklich jede Spalte benötigen, listen Sie jede Spalte explizit auf. Dies ist nicht so sehr eine Regel, sondern eher ein Mittel, um zukünftige Systemfehler und zusätzliches SQL-Performance-Tuning zu verhindern. Wenn Sie beispielsweise ein INSERT... SELECT... verwenden und die Quelltabelle durch das Hinzufügen einer neuen Spalte geändert wurde, können Probleme auftreten, selbst wenn diese Spalte von der Zieltabelle nicht benötigt wird. z.B:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Um diese Art von Fehler von SQL Server zu vermeiden, sollten Sie jede Spalte einzeln deklarieren:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Beachten Sie jedoch, dass es einige Situationen gibt, in denen die Verwendung von SELECT * angemessen sein könnte. Zum Beispiel mit temporären Tabellen – was uns zu unserem nächsten Thema führt.

SQL-Tuning: Die kluge Verwendung temporärer Tabellen (#Temp)

Temporäre Tabellen erhöhen normalerweise die Komplexität einer Abfrage. Wenn Ihr Code einfach und unkompliziert geschrieben werden kann, würde ich vorschlagen, temporäre Tabellen zu vermeiden.

Wenn Sie jedoch eine gespeicherte Prozedur mit einigen Datenmanipulationen haben, die nicht mit einer einzigen Abfrage behandelt werden können, können Sie temporäre Tabellen als Vermittler verwenden, um Ihnen beim Generieren eines Endergebnisses zu helfen.

Wenn Sie einer großen Tabelle beitreten müssen und Bedingungen für diese Tabelle vorhanden sind, können Sie die Datenbankleistung erhöhen, indem Sie Ihre Daten in eine temporäre Tabelle übertragen und dann eine Verknüpfung mit dieser vornehmen. Ihre temporäre Tabelle hat weniger Zeilen als die ursprüngliche (große) Tabelle, sodass die Verknüpfung schneller abgeschlossen wird!

Die Entscheidung ist nicht immer einfach, aber dieses Beispiel gibt Ihnen einen Eindruck von Situationen, in denen Sie temporäre Tabellen verwenden möchten:

Stellen Sie sich eine Kundentabelle mit Millionen von Datensätzen vor. Sie müssen eine Verbindung zu einer bestimmten Region herstellen. Sie können dies erreichen, indem Sie eine SELECT INTO Anweisung verwenden und dann mit der temporären Tabelle verbinden:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Hinweis: Einige SQL-Entwickler vermeiden es auch, SELECT INTO zum Erstellen temporärer Tabellen zu verwenden, da sie sagen, dass dieser Befehl die tempdb-Datenbank sperrt und anderen Benutzern das Erstellen temporärer Tabellen verbietet. Glücklicherweise wurde dies in 7.0 und höher behoben .)

Als Alternative zu temporären Tabellen können Sie eine Unterabfrage als Tabelle verwenden:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Aber warte! Bei dieser zweiten Abfrage ist ein Problem aufgetreten. Wie oben beschrieben, sollten wir nur die Spalten in unsere Unterabfrage aufnehmen, die wir benötigen (dh SELECT * nicht verwenden). Unter Berücksichtigung dessen:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Alle diese SQL-Ausschnitte geben die gleichen Daten zurück. Aber mit temporären Tabellen könnten wir beispielsweise einen Index in der temporären Tabelle erstellen, um die Leistung zu verbessern. Hier gibt es einige gute Diskussionen über die Unterschiede zwischen temporären Tabellen und Unterabfragen.

Wenn Sie schließlich mit Ihrer temporären Tabelle fertig sind, löschen Sie sie, um die tempdb-Ressourcen zu löschen, anstatt einfach darauf zu warten, dass sie automatisch gelöscht wird (wie es der Fall sein wird, wenn Ihre Verbindung zur Datenbank beendet wird):

 DROP TABLE #temp

SQL-Tuning: „Existiert mein Datensatz?“

Diese SQL-Optimierungstechnik betrifft die Verwendung von EXISTS() . Wenn Sie überprüfen möchten, ob ein Datensatz vorhanden ist, verwenden EXISTS() anstelle von COUNT() . Während COUNT() die gesamte Tabelle durchsucht und alle Einträge zählt, die Ihrer Bedingung entsprechen, wird EXISTS() , sobald es das benötigte Ergebnis sieht. Dadurch erhalten Sie eine bessere Leistung und einen klareren Code.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

SQL-Leistungsoptimierung mit SQL Server 2016

Wie DBAs, die mit SQL Server 2016 arbeiten, wahrscheinlich bewusst sind, markierte die Version eine wichtige Änderung in der Standard- und Kompatibilitätsverwaltung. Als Hauptversion enthält sie natürlich neue Abfrageoptimierungen, aber die Kontrolle darüber, ob sie verwendet werden, wird jetzt über sys.databases.compatibility_level gestrafft.

SQL-Leistungsoptimierung (im Büro)

SQL-Datenbankadministratoren (DBAs) und Entwickler streiten sich oft über datenbezogene und nicht datenbezogene Probleme. Basierend auf meiner Erfahrung, hier sind einige Tipps (für beide Parteien), wie man gut miteinander auskommt und effektiv zusammenarbeitet.

Twittern

Datenbankoptimierung für Entwickler:

1. Wenn Ihre Anwendung plötzlich nicht mehr funktioniert, liegt möglicherweise kein Datenbankproblem vor. Vielleicht haben Sie zum Beispiel ein Netzwerkproblem. Untersuchen Sie ein wenig, bevor Sie einen DBA beschuldigen!

2. Selbst wenn Sie ein Ninja-SQL-Datenmodellierer sind, bitten Sie einen DBA, Ihnen bei Ihrem relationalen Diagramm zu helfen. Sie haben viel zu teilen und anzubieten.

3. DBAs mögen keine schnellen Änderungen. Das ist selbstverständlich: Sie müssen die Datenbank als Ganzes analysieren und die Auswirkungen von Änderungen aus allen Blickwinkeln untersuchen. Die Implementierung einer einfachen Änderung in einer Spalte kann eine Woche dauern – aber das liegt daran, dass ein Fehler enorme Verluste für das Unternehmen bedeuten kann. Sei geduldig!

4. Bitten Sie SQL DBAs nicht, Datenänderungen in einer Produktionsumgebung vorzunehmen. Wenn Sie Zugriff auf die Produktionsdatenbank wünschen, müssen Sie für alle Ihre eigenen Änderungen verantwortlich sein.

Datenbankoptimierung für SQL Server DBAs:

1. Wenn Sie nicht möchten, dass Leute Sie nach der Datenbank fragen, geben Sie ihnen ein Echtzeit-Statusfeld. Entwickler sind immer misstrauisch gegenüber dem Status einer Datenbank, und ein solches Panel könnte jedem Zeit und Energie sparen.

2. Helfen Sie Entwicklern in einer Test-/Qualitätssicherungsumgebung. Vereinfachen Sie die Simulation eines Produktionsservers mit einfachen Tests mit realen Daten. Dies wird eine erhebliche Zeitersparnis für andere sowie für Sie selbst sein.

3. Entwickler verbringen den ganzen Tag mit Systemen mit häufig geänderter Geschäftslogik. Versuchen Sie zu verstehen, dass diese Welt flexibler ist, und in einem kritischen Moment einige Regeln zu brechen.

4. SQL-Datenbanken entwickeln sich weiter. Der Tag wird kommen, an dem Sie Ihre Daten auf eine neue Version migrieren müssen. Entwickler rechnen mit jeder neuen Version mit bedeutenden neuen Funktionen. Anstatt sich zu weigern, ihre Änderungen zu akzeptieren, planen Sie voraus und seien Sie bereit für die Migration.