Im Forum eines von mir sehr geschätzten MVP-Kollegen wurde eine Frage bezüglich der Verwendung von Variablen anstelle von Literalen gestellt (hier). Das Problem war, dass die Abfrage sich deutlich verlangsamte, wenn Variablen statt Literale verwendet wurden. Warum dieses Verhalten für Microsoft SQL Server jedoch korrekt ist, soll der folgende Artikel zeigen.

In einer Anfrage in den Microsoft Foren (link) ging es darum, warum Microsoft SQL Server trotz einer SEEK-Operation alle Datenseiten einer Tabelle durchsucht hat. Tatsächlich kann eine SEEK-Operation die vollständige Tabelle betreffen, wenn bestimmte Voraussetzungen nicht erfüllt sind. Wie wichtig zum Beispiel die korrekte Verwendung von Datentypen bei Einschränkungen sind, zeigt der nachfolgende Artikel.

Wenn alle Datensätze aus einem Heap gelöscht werden, mag man meinen, dass Microsoft SQL Server nach dem Löschvorgang auch die allozierten Datenseiten wieder frei gibt. Das macht der Microsoft SQL Server jedoch nur, wenn bestimmte Voraussetzungen vorhanden sind wie der nachfolgende Artikel zeigt.

Für viele Kollegen und Freunde, mit denen ich über Fremdschlüssel und deren Bedeutung für Datenbanken spreche, steht im Vordergrund, dass ein Fremdschlüssel sicherstellt, nur Werte aus einer referenzierten Tabelle zuzulassen. Diese Aussage ist nur bedingt richtig – vielmehr spielt eine Fremdschlüsselbeziehung auch bei Abfragen eine wichtige Rolle, wenn der Abfrage Optimierer seine Arbeit verrichtet.

Was ist eine Fremdschlüssel-Einschränkung?

Unter einer Fremdschlüssel-Einschränkung (Foreign Key Constraint) versteht man eine Kombination von Attributen in einer Tabelle, mit deren Hilfe eine Verknüpfung zwischen den Daten zweier Tabellen erzwungen wird. In einem sogenannten Fremdschlüsselverweis besteht zwischen zwei Tabellen eine Verknüpfung, wenn eine oder mehrere Attribute einer Tabelle (Detailtabelle) auf ein oder mehrere Attribute einer anderen Tabelle (Mastertabelle) verweisen.

Klassische Fremdschlüsselbeziehungen sind z. B. in einem Auftragssystem zu finden, in dem zu Auftragsdetails (Detailtabelle) immer ein Auftragskopf (Mastertabelle) enthalten sein muss. In einer solchen Konstruktion wird das Primärattribut der Mastertabelle (z. B. Auftrag_Id) zum Fremdschlüssel in der Detailtabelle. Neben der erwähnten Durchsetzung von Datenintegritätsregeln kann aber Microsoft SQL Server Fremdschlüssel auch ideal für die Optimierung von Abfragen verwenden, wie die nachfolgende Demonstration zeigt.

Testumgebung für Demonstration

Welche Einflüsse eine Fremdschlüssel-Einschränkung auf die Ausführung von Abfragen haben kann, soll mit dem folgenden Datenmodell demonstriert werden (SQL Server 2012).

Zu verschiedenen Projekten ([dbo].[Projects]) gibt es eine Vielzahl von Projektmitgliedern ([dbo].[ProjectMembers]). Beide Tabellen stehen in einer 1:n-Abhängigkeit zueinander. Damit in die Tabelle [dbo].[ProjectMembers] keine Projektnummern eingetragen werden können, die nicht in [dbo].[Projects] existieren, wird eine Fremdschlüssel-Beziehung zwischen beiden Tabellen implementiert. Gleiches gilt für die Tabelle [dbo].[Employees]. Das anschließende T-SQL-Script erzeugt die oben beschriebene Struktur.

-- Mitarbeiter

CREATE TABLE dbo.Employees

(

    Id         int         NOT NULL    IDENTITY (1, 1),

    FirstName  varchar(64) NOT NULL,

    LastName   varchar(64) NOT NULL,

    HiredAt    date        NOT NULL,

    FiredAt    date        NULL,

    CONSTRAINT pk_Employees_Id PRIMARY KEY CLUSTERED (Id)

);

-- Projekte

CREATE TABLE dbo.Projects

(

    Id             int         NOT NULL    IDENTITY(1, 1),

    ProjectName    varchar(64) NOT NULL,

    ProjectStart   date        NOT NULl    DEFAULT (getdate()),

    ProjectFinish  date        NULL,

    ProjectLead    int         NOT NULL,

    CONSTRAINT pk_Projects_Id PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT fk_Projects_ProjectLead FOREIGN KEY (ProjectLead)

    REFERENCES dbo.Employees (Id)

);

-- Projektmitarbeiter

CREATE TABLE dbo.ProjectMembers

(

    Project_Id  int    NOT NULL,

    Employee_Id int    NOT NULL,

    StartDate   date   NOT NULL,

    FinishDate  date   NULL,

    CONSTRAINT pk_ProjectMembers PRIMARY KEY CLUSTERED

    (

        Project_Id,

        Employee_Id

    ),

    CONSTRAINT fk_ProjectMembers_Project_Id FOREIGN KEY (Project_Id)

    REFERENCES dbo.Projects (Id),

    CONSTRAINT fk_ProjectMembers_Employee_Id FOREIGN KEY (Employee_Id)

    REFERENCES dbo.Employees (Id)

);

Demonstration

Der Ausführungsplan der folgenden Abfrage zeigt, wie “optimal” Microsoft SQL Server Ermittlung von geeigneten Ausführungsplänen vorgeht:

SELECT  p.Id,

        p.ProjectName,

        p.ProjectStart      AS    Project_StartDate,

        p.ProjectFinish     AS    Project_Finish,

        pm.StartDate        AS    Employee_StartDate,

        pm.FinishDate       AS    Employee_FinishDate

FROM    dbo.Projects p INNER JOIN dbo.ProjectMembers pm

        ON (p.Id = pm.Project_Id) INNER JOIN dbo.Employees e

        ON (pm.Employee_Id = e.Id);

Die Abfrage ermittelt neben den Projektdaten (Name, Start und Ende) ebenfalls die Start- und Endzeiten für die einzelnen Mitarbeiter. Die Abfrage selbst verwendet alle drei Tabellen, die in einer unmittelbaren Abhängigkeit zueinander stehen. Der Ausführungsplan für diese Abfrage gestaltet sich aber überraschender Weise wie folgt:

Obwohl in der auszuführenden Abfrage drei Tabellen verwendet werden, nimmt Microsoft SQL Server ausschließlich die Tabellen [dbo].[Projects] und [dbo].[ProjectMembers] für die Ausführung der Abfrage. Grund für dieses Ergebnis ist ein Prozessschritt innerhalb der Optimierungsphasen von Microsoft SQL Server bei der Suche nach einem optimalen Ausführungsplan – Simplification!

Bei “Simplification” versucht Microsoft SQL Server, den “Query Tree” auf einfache Formen zu reduzieren. um den Optimierungsprozess als solchen zu vereinfachen. Beispielsweise werden Subqueries in JOINS gewandelt oder redundante JOINS werden entfernt; widersprüchliche Anweisungen werden entfernt, …! Wer sich tiefer mit dieser Materie beschäftigen möchte, dem sei das Buch “Inside the SQL Server Query Optimizer” von Benjamin Nevarez sehr ans Herz gelegt.

Im Ergebnis der Abfrage werden Attribute der Tabellen [dbo].[Projects] und [dbo].[ProjectMembers] ausgegeben. Warum also sollte Microsoft SQL Server eine Prüfung der Daten aus [dbo].[Employees] vornehmen? Eine Prüfung ist obsolet, da beide Relationen durch eine Fremdschlüssel-Einschränkung miteinander in Beziehung stehen. Durch die Fremdschlüsselbeziehung wird gewährleistet, dass sich im Attribut [Employee_Id] von [dbo].[ProjectMembers] keine Daten befinden können, die nicht in [dbo].[Employees] existieren.

Gleiche Abfrage – keine Fremdschlüssel-Einschränkung

Um den – gravierenden - Unterschied zu zeigen, wird die Fremdschlüssel-Einschränkung vor der nächsten Ausführung der Abfrage deaktiviert.

ALTER TABLE dbo.ProjectMembers NOCHECK CONSTRAINT fk_ProjectMembers_Employee_Id;

Nach der Ausführung der Abfrage ergibt sich für die Ausführung ein vollständig neues Bild.

Microsoft SQL Server muss nun alle in die Abfrage involvierten Relationen prüfen. Zunächst wird eine Ergebnismenge der beiden Relationen [dbo].[Employees] und [dbo].[ProjectMembers] mittels eines – teuren – Hash Match gebildet; anschließend wird diese Ergebnismenge mit [dbo].[Projects] verbunden und das Ergebnis an den Client geschickt. Statt eines – effektiveren – Merge Join muss ein – teurer – Hash Join verwendet werden, da Schlüsselattribute innerhalb der Abfrage nicht sortiert vorliegen. Weitere Informationen zu den physikalischen Operatoren für JOINS kann man im Buch “SQL Server Execution Plans” von Grant Fritchey nachlesen. Die unterschiedlichen Ausführungspläne machen sich natürlich auch im I/O bemerkbar, das durch die Abfragen generiert werden.

I/O mit Fremdschlüssel

ProjectMembers-Tabelle. Scananzahl 1, logische Lesevorgänge 34, ...

Projects-Tabelle. Scananzahl 1, logische Lesevorgänge 2, ...

I/O ohne Fremdschlüssel

Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, ...

ProjectMembers-Tabelle. Scananzahl 1, logische Lesevorgänge 34, ...

Employees-Tabelle. Scananzahl 1, logische Lesevorgänge 4, ...

Projects-Tabelle. Scananzahl 1, logische Lesevorgänge 2, ...

Zusammenfassung

Microsoft SQL Server kann jede Art der “Unterstützung” gebrauchen, um optimale Ausführungspläne zu erstellen. Sicherlich ist die primäre Aufgabe einer Fremdschlüssel-Einschränkung nicht die Optimierung von Ausführungsplänen sondern die Durchsetzung von Datenintegrität; dennoch ist es bemerkenswert, dass Microsoft SQL Server basierend auf solchen Regeln Ausführungen von Abfragen vereinfachen und damit auch optimieren kann.

Herzlichen Dank fürs Lesen!

Dieser Artikel beschreibt Strategien, wie große Datenmengen aus Relationen gelöscht werden können und dabei möglichst effizient und schnell zu arbeiten. Ziel einer solchen Operation ist die Vermeidung großer Datenmengen im Transaktionsprotokoll. Diese Aufgabenstellung zu dokumentieren, rührt von den immer wieder auftretenden Fragen in den Microsoft Foren bezüglich effizienter Löschstrategien:

Need to build a job that purges old data and rebuilds indexes

Removing large number of records with truncate?