Check-Constraints und Performance

with one comment

Ein CHECK-Constraint wird in einer Tabelle definiert, wenn in einer Spalte nur bestimmte Werte möglich sind. Er ermöglicht, einen beliebigen Ausdruck anzugeben, den jeder Wert einer Spalte erfüllen muss. Constraints sind in erster Linie eine Garantie für Datenintegrität. Man vergisst aber oft, dass Informationen aus den Constraints auch die Entscheidungen des SQL Server Optimizers beeinflussen können.

Code-Beispiel

Als Beispiel nehmen wir die Tabelle Sales.SalesOrderHeader aus der AdventureWorks Datenbank. In dieser Tabelle soll ein CHECK-Constraint definiert sein. Wenn dies in Ihrer Datenbank noch nicht der Fall ist, können Sie ihn mit dem folgenden Code anlegen:

ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT CK_SalesOrderHeader_Status CHECK ((Status>=(0) AND Status<=(8)))

Der Constraint beschränkt die Werte für die Spalte Status auf eine Zahl zwischen 0 und 8. Und was hat diese Information mit dem Optimizer zu tun?

Aufgabe

In der Tabelle Sales.SalesOrderHeader gibt es keine Zeile mit dem Wert 8 für die Spalte Status. Wenn wir eine Abfrage ausführen, um alle Orders mit dem Status 8 anzuzeigen, bekommen wir eine leere Liste:

SELECT * FROM Sales.SalesOrderHeader WHERE Status = 8

Um diese leere Liste liefern zu können, musste SQL Server die ganze Tabelle lesen. Jede Zeile könnte den Wert 8 haben und SQL Server hat alle Zeilen überprüfen müssen. Hier sind die Ausführungsinformationen:

Ausführungsplan

Wiederholen wir jetzt diese Aufgabe mit einem Wert, der außerhalb des definierten Bereichs liegt:

SELECT * FROM Sales.SalesOrderHeader WHERE Status = 9

Selbstverständlich ist die Liste wieder leer, die Ausführungsinformationen sehen aber komisch aus:

Keine einzige Datenseite wurde gelesen! Aufgrund von der CHECK-Constraint Definition hat der SQL Server beschlossen, dass keine Zeile in der Tabelle SalesOrderHeader mit dem Status 9 existieren kann und hat das Ergebnis zurückgeliefert, ohne die Tabelle berühren zu müssen!

Fazit

Der SQL Server Optimizer liest die Informationen aus Constraints-Definitionen. Diese Informationen können die Ausführungsplan-Generierung beeinflussen.

Written by Milos R.

4. Mai 2011 um 13:49

Veröffentlicht in Performance

Tagged with , ,

Eine Antwort

Subscribe to comments with RSS.

  1. Eine Kleinigkeit sollte man noch beachten: der Optimizer verwendet Check Constraints nur dann, wenn sie trusted sind.
    Ein Constraint ist nicht trusted, wenn er zB mit der nocheck Klausel erstellt wurde (ALTER TABLE Sales.SalesOrderHeader WITH nocheck ADD CONSTRAINT CK_SalesOrderHeader_Status CHECK ((Status>=(0) AND Status<=(8))) ) oder wenn er eine zeitlang disabled war.

    Ich verwende folgendes Statement, um die Contraints in einer DB zu prüfen:
    select ‚ALTER TABLE ‚ + schema_name(schema_id) + ‚.‘ + object_name(parent_object_id) + ‚ WITH CHECK CHECK CONSTRAINT ‚ + object_name(object_id) + ‚;‘
    from sys.objects
    where type_desc like ‚%CONSTRAINT‘
    and objectproperty(object_id, ‚CnstIsNotTrusted‘) = 1;

    Der Output, der auch gleich ausgeführt werden kann, sieht so aus:
    ALTER TABLE Sales.SalesOrderHeader WITH CHECK CHECK CONSTRAINT CK_SalesOrderHeader_Status;

    Danach verwendet der Optimizer wieder den Check Constraint.

    Gruss, Franz

    Franz Renesnicek

    5. Mai 2011 at 18:46


Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: