In einem zu implementierenden Projekt ging es darum, den vom Programmierer gewählten “zeilenorientierten” Lösungsansatz zu untersuchen und gegebenenfalls zu optimieren. Hintergrund war die Aufgabe, für jeweils einen Monat eine Analyse über Einkaufszahlen in den Reporting Services zu erstellen. Basis des Reports ist eine Stored Procedure, die ich auf Performanceprobleme zu untersuchen hatten. Das Ergebnis der Untersuchung war mehr als überraschend. Lernen Sie im nachfolgenden Artikel die unterschiedlichen Lösungsansätze unter Verwendung von CTE in einer Tabellenfunktion, Tabellenvariablen und temporären Tabellen kennen.

Problemstellung

Für einen Analysebericht in den Reporting Services kann ein Anwender durch Angabe eines Startdatums sowie eines Enddatums einen Datumsbereich eingrenzen, für den Verkaufszahlen analysiert werden sollen. Der Programmierer hat als Dataset für den Report eine Stored Procedure verwendet, die als Parameter sowohl Start- als auch Endedatum verwendet. Diese Prozedur galt es auf Optimierungspotenzial zu untersuchen und gegebenfalls anzupassen. Zu Demonstrationszwecken verwende ich die Datenbank AdventureWorks2012, die bei Microsoft heruntergeladen werden kann.

Die Prozedur für die Evaluierung der Verkaufszahlen hat den folgenden – vereinfachten - Aufbau:

-- Definition von Start- und Endedatum
DECLARE    @StartDate  datetime = '20080701'
DECLARE    @EndDate    datetime = '20080731'

-- Erstellen einer Tabellenvariable für die Speicherung des Datumsbereichs
DECLARE    @DateRange TABLE (OrderDate date PRIMARY KEY CLUSTERED);

-- Befüllen der Tabellenvariablen
WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO @DateRange (OrderDate) VALUES (@StartDate);
    SET @StartDate = DATEADD(dd, 1, @StartDate)
END;

SELECT  dr.OrderDate,
        oh.SalesOrderID,
        oh.PurchaseOrderNumber,
        COUNT(od.SalesOrderDetailID)    AS    Positions,
        SUM(od.LineTotal)               AS    TotalAmount
FROM    @DateRange dr LEFT JOIN sales.SalesOrderHeader oh
        ON (dr.OrderDate = oh.OrderDate) LEFT JOIN sales.SalesOrderDetail od
        ON (oh.SalesOrderId = od.SalesOrderID)
GROUP BY
        dr.OrderDate,
        oh.SalesOrderId,
        oh.PurchaseOrderNumber
ORDER BY
        dr.OrderDate,
        oh.SalesOrderId

Für die Sammlung eines Datumsbereichs wird eine Tabellenvariable verwendet, in die mittels WHILE-Schleife ein Datumsbereich im Rahmen der beiden Variablen @StartDate und @EndDate eingefügt werden. Anschließend wird diese Tabellenvariable mittels OUTER JOIN für die anschließende Gruppierungsabfrage verwendet. In dem Beispielscript finden Sie diese Prozedur unter dem Namen [proc_SalesData_TableVar.sql].

Von mir wurde vorgeschlagen, neben der obigen Variante noch zwei weitere Varianten zu verwenden und anschließend die Ergebnisse gegenüber zu stellen.

Verwendung temporärer Tabellen statt Tabellenvariablen

Zunächst wurde darüber diskutiert, statt einer Tabellenvariablen eine temporäre Tabelle einzusetzen. Der Vorteil von temporären Tabellen im konkreten Fall ist die Verwendung von Statistiken. Durch die Verwendung von Statistiken kann ein möglicher Ausführungsplan besser berechnet werden und so eventuell ein besseres Ergebnis erzielt werden. Dieses Möglichkeiten bestehen für Tabellenvariablen grundsätzlich nicht. Aus diesem Grund wurde die obige Prozedur nur geringfügig geändert. Statt der Deklaration einer Tabellenvariablen wurde eine temporäre Tabelle in der Prozedur definiert:

-- Erstellen einer temporären Tabelle für die Speicherung des Datumsbereichs
IF OBJECT_ID('tempdb..#DateRange') IS NOT NULL
    DROP TABLE #DateRange

CREATE TABLE #DateRange(OrderDate date PRIMARY KEY CLUSTERED);

-- Befüllen der Tabellenvariablen
WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO #DateRange (OrderDate) VALUES (@StartDate);
    SET @StartDate = DATEADD(dd, 1, @StartDate)
END;

Sie finden diese Prozedur in dem Beispielscript unter dem Namen [proc_SalesData_temptable.sql].

Kapselung der WHILE-Schleife in Transaktion

Das Beispiel für die Verwendung einer temporären Tabelle statt einer Tabellenvariable führte zu der Überlegung, das Eintragen von Daten in die temporäre Relation in einer Transaktion zu kapseln. Dadurch wollte ich erreichen, dass nur einmal ein phsyikalischer Schreibvorgang initialisiert wird.

Verwendung einer Tabellenfunktion (TVF)

Als zweite Option wurde die Verwendung einer Tabellenfunktion in Betracht gezogen. Hierbei wird also nicht mittels einer Schleifenkonstruktion zunächst eine Tabellenvariable oder eine temporäre Tabelle gefüllt sondern die Funktion selbst liefert die Daten, die anschließend mit einem INNER JOIN in der Abfrage verwendet werden.

Die hierzu im Vorfeld erstellte Tabellenfunktion hat den folgenden Aufbau (Dank für das Script geht an Eden Ridgway, aus dessen Blog ich das Script als Anlehnung verwendet habe):

CREATE FUNCTION dbo.ft_DateRange(@StartDate date, @EndDate date)
RETURNS @DateRange TABLE (OrderDate date PRIMARY KEY CLUSTERED)
AS
BEGIN
    WITH DateRange(Date) AS
    (
        SELECT  @StartDate  AS Date
        UNION ALL
        SELECT  DATEADD(day, 1, Date) Date
        FROM    DateRange
        WHERE   Date < @EndDate
    )
    INSERT INTO @DateRange (OrderDate)
    SELECT Date
    FROM   DateRange
    OPTION (MaxRecursion 10000);

    RETURN END
GO

Die Funktion dbo.ft_DateRange kann unmittelbar im SELECT-Statement der Prozedur verwendet werden. Das Durchlaufen einer Schleife ist somit nicht mehr notwendig. Die entsprechende Prozedur findet sich im Beispielprojekt unter dem Namen [proc_SalesData_TableFunction].

CREATE PROC dbo.proc_SalesData_TableFunction
    @StartDate  datetime,
    @EndDate    datetime
AS
    SET NOCOUNT ON;

    SELECT  dr.OrderDate,
            oh.SalesOrderID,
            oh.PurchaseOrderNumber,
            COUNT(od.SalesOrderDetailID)  AS Positions,
            SUM(od.LineTotal)             AS TotalAmount
    FROM    dbo.ft_DateRange(@StartDate, @EndDate) dr LEFT JOIN sales.SalesOrderHeader oh             ON (dr.OrderDate = oh.OrderDate) LEFT JOIN sales.SalesOrderDetail od
            ON (oh.SalesOrderId = od.SalesOrderID)
    GROUP BY
            dr.OrderDate,
            oh.SalesOrderId,
            oh.PurchaseOrderNumber;

    SET NOCOUNT OFF

Analyse der einzelnen Verfahren

Basierend auf den obigen Kombinationsmöglichkeiten gab es insgesamt 6 Möglichkeiten zu testen. Jede Kombination wurde in eine separate Stored Procedure für die Tests ausgegliedert.

Zu analysierende Abfrageverfahren

Die jeweils auszuführenden Prozeduren wurden für ihre Messungen wie folgt erstellt:

Protokollierung der Messung

Die ursprüngliche Idee ist es gewesen, für jede Prozedur den jeweiligen Abfrageplan zu analysieren und dessen Laufzeit im Verhältnis zur Gesamtlaufzeit zu bewerten. Ein solches Verfahren ist jedoch nicht möglich, da jedes DML-Statement und DDL-Statement einen Abfrageplan generiert. Von daher wurde auf den unmittelbaren Vergleich der Abfragepläne verzichtet und die Laufzeit der Prozeduren mittels SQL Profiler aufgezeichnet. Für den SQL-Profiler wurde folgende Ereignisauswahl konfiguriert:

DECLARE @StartDate  date = '20080101';
DECLARE @EndDate    date = '20081231';

EXEC    dbo.proc_SalesData_TableVar @StartDate, @EndDate;
EXEC    dbo.proc_SalesData_TableVar_Recompile @StartDate, @EndDate;
EXEC    dbo.proc_SalesData_TempTable @StartDate, @EndDate;
EXEC    dbo.proc_SalesData_TempTable_Transaction @StartDate, @EndDate;
EXEC    dbo.proc_SalesData_TableFunction @StartDate, @EndDate;
EXEC    dbo.proc_SalesData_TableFunction_Recompile @StartDate, @EndDate;

Für die Messungen habe ich bewußt einen Zeitraum von einem ganzen Jahr ausgewählt um besser die Unterschiede herausstellen zu können. Das Ergebnis überrascht auf den ersten Blick.

Analyse der Meßergebnisse

Schaut man das Ergebnis an, fällt auf, dass auf dem letzten Platz die Verwendung einer Tabellenvariablen mit der Option “OPTION (RECOMPILE)” liegt. Besonders überraschend ist jedoch das Ergebnis der Verwendung von temporären Tabellen. Auch diese Variante liegt weit abgeschlagen auf einer der letzten Plätzen. Dennoch ist erkennbar, dass die Kapselung der Einfügevorgänge in eine dedizierte Transaktion einen minimalen Performancevorteil von ca. 10% liefert.

Das – für mich – interessanteste Ergebnis ist die Tatsache, dass die Verwendung einer Tabellenvariablen in Verbindung mit einer Schleifenkonstruktion unter den Top 3 in der Ausführungszeit liegt. Klarer Sieger dieses Vergleichs ist die Verwendung einer Tabellenfunktion. Neben dieser – ersten – Überraschung wollte ich natürlich den Ergebnissen und ihrer Kausaltäti im Verhalten auf den Grund gehen. Die extremen Unterschiede können nur zwei Ursachen haben, die das Abfrageverhalten (und damit auch die Abfragezeit) maßgeblich beeinflussen:

- Abfrageplan
- IO

Um beide Informationen möglichst parallel zu analysieren, wurde in den Prozeduren vor und nach der Ausführung des SELECT-Statements die Option SET STATISTICS PROFILE ON / OFF hinzugefügt. Die Ergebnisse sind recht interessant:

proc_SalesData_TableVar

Es ist auffällig, das die tatsächliche Zeilenzahl (Rows) deutlich von der geschätzten Zeilenzahl (EstimateRows) abweicht. Basierend auf diesen Informationen scheint ein “Clustered Index Scan” die optimale Abfragestrategie zu sein. Geschuldet ist dieses Phänomen der Tatsache, dass für Tabellenvariablen keine Statistiken geführt werden (siehe http://msdn.microsoft.com/de-de/library/ms188752.aspx). Somit ergibt sich für Tabellenvariablen IMMER eine geschätzte Zeilenzahl von 1. Der Ausführungsplan sieht bei einer zu erwartenden Datenmenge von 1 Datensatz als logische Konsequenz einen Table Scan als beste Alternative. Wie sieht es aber aus, wenn SQL Server bereits weiß, wie viele Datensätze zu verarbeiten sind?

proc_SalesData_TableVar_Recompile

Um SQL Server zu veranlassen, dennoch die korrekte Anzahl von Zeilen zu ermitteln, muss die Abfrage mit der Option OPTION (RECOMPILE) ausgeführt werden. SQL Server kann nun die korrekte Anzahl von Datensätzen ermitteln, da in diesem Moment bereits alle Datensätze in die Tabellenvariable eingefügt wurden. Entsprechend sieht dann auch das Abfrageprofil aus:

Man erkennt deutlich, dass nun – basierend auf den geschätzten Zeilen (EstimateRows) – SQL Server eine vollkommen andere Abfragestrategie verwendet. War im ersten Beispiel basierend auf dem geschätzten Wert von 1 noch ein KeyLookup eine sinnvolle Operation, hat SQL Server nun eine andere Abfragestrategie ausgewählt. Die Frage, die sich hier stellt – warum wurde eine andere Abfragestrategie gewählt, die fast die doppelte Zeit benötigt? Die Antwort liegt sowohl in der Anzahl der geschätzten Zeilen als auch in der Anzahl der Ausführungen [Executions]. SQL Server hat –basierend auf en Statistiken – ermittelt, dass im ersten Beispiel in der Tabellenvariable nur ein Datensatz vorhanden ist (es liegen ja keine Statistiken vor!) und somit muss die gewählte Aktion auch nur ein Mal ausgeführt werden. Tatsächlich wurde dann aber der nachfolgende “Index Seek” 366 mal ausgeführt, da sich erst im Nachhinein die wahre Anzahl der Datensätze ergab.

Im zweiten Beispiel wurde eine vollständig andere Abfragestrategie gewählt, da SQL Server bereits bei Ausführung der Abfrage Informationen über die tatsächliche Anzahl von Datensätzen hatte, die sich in der Tabellenvariable befinden. Aus diesem Grund wurde eine Abfragestrategie favorisiert, die weniger Ausführungszyklen benötigt (in diesem Fall nur 1 Zyklus). Zwar ist das IO deutlich höher aber die Ausführungen wurden deutlich reduziert.

SQL Server kann Ausführungskosten nur schätzen. Dabei werden aber nicht ALLE möglichen Abfragestrategien berücksichtigt sondern SQL Server muss einen Mittelweg zwischen Abfrageanalyse und Berechnung eines geeigneten Abfrageplans finden (ansonsten würde u. U. die Berechnung einer geeigneten Strategie länger dauern als die eigentliche Abfrage. Dieses heuristischen Berechnungen können – wie im obigen Beispiel gezeigt – auch zu schlechterer Performance führen.

Obwohl scheinbar die gleiche Abfrage – führt sie doch bei Kenntnis über die tatsächliche Datenmenge zu einem vollständig anderen Ergebnis.

proc_SalesData_TempTable

Basierend auf den obigen Aussagen sollte nun bei der Verwendung von temporären Tabellen ein identischer Abfrageplan resultieren, da – im Gegensatz zu Tabellenvariablen – für temporäre Tabellen immer Statistiken mitgeführt werden. Das bedeutet in der Konsequenz, dass SQL Server bereits bei Ausführung des SQL Statements weiß, wie viele Datensätze sich in der Relation befinden. Die Ausführung und daraus resultierenden Statistiken zeigen, dass es sich genau so verhält.

Das Ergebnis ist – basierend auf den bisherigen Erkenntnissen – zu erwarten gewesen. Der Abfrageplan ist identisch mit dem Abfrageplan der Prozedur dbo.proc_SalesDate_TableVar_Recompile, da in beiden Fällen die Information über die tatsächliche Datenanzahl vorhanden war. Da für temporäre Tabellen Statistiken geführt werden, war jedoch ein “OPTION (RECOMPILE)” nicht notwendig.

proc_SalesData_TempTable_Transaction

Interessant ist jedoch die Kapselung der “Befüllung” der temporären Tabelle in eine abgeschlossene Transaktion. Durch die Kapselung der Befüllung der Relation muss eine Sperre nur einmal (zu Beginn der Transaktion) erstellt werden. Diese Sperre wird entweder bei einem COMMIT oder ROLLBACK wieder freigegeben. Das Ergebnis hat jedoch gezeigt, dass sich dadurch keine wirklichen Performancevorteile erreichen lassen. Der Ausführungsplan ist identisch mit dem Ausführungsplan der Prozedur proc_SalesData_TempTable.

proc_SalesData_TableFunction

Wie entwickelt sich aber das Laufzeitverhalten, wenn statt einer codierten Schleife eine “Table Valued Function” zum Einsatz kommt. Bei einer Tabellenwert-Funktion (TVF) handelt es sich um eine benutzerdefinierte Funktion, deren Rückgabewert kein einzelner Wert ist sondern eine komplette Tabelle.

Der Vorteil des Einsatzes einer TVF im konkreten Beispiel ist die Vermeidung von Programmschleifen, die wiederhotl Daten in eine Relation übertragen. Wie die erste Auswertung gezeigt hat, ist der Einsatz von TVF die schnellste Variante gewesen, um ein Ergebnis zu generieren. Schaut man sich das Abfrageprofil an, dass bei der Verwendung einer TVF verwendet wird, ist sofort auffällig, dass es zu fast 100% identisch ist mit dem Abfrageprofil aus der Prozedur proc_SalesData_TableVar.

Die Abfragestrategie wurde – wie schon bei der Tabellenvariablen – auf Basis der geschätzten Zeilen (EstimateRows) gewählt. Insgesamt ist ein deutlich höherer IO erkennbar aber dennoch waren die Einzeloperationen deutlich schneller als die vorherigen Ausführungspläne.

Ursächlich für dieses Verhalten (wie eine Tabellenvariable) ist die Tatsache, dass bei TVF ebenfalls keine Statistiken erstellt werden, wenn es sich – wie im obigen Beispiel – um eine “Multi-Statement-Table-Valued-Function” handelt. Der Unterschied einer Multi-Statement-Tablefunction zu einer “Inline-Table-Valued-Function besteht darin, dass in der Funktion mehrere Befehle ausgeführt werden (ähnlich einer Prozedur) während eine Inline-Table-Valued-Function unmittelbar nach der Funktionsdeklaration ein SELECT absetzt. Eine Inline-Table-Valued-Function ist – im engeren Sinne – also nichts anderes als eine parameterisierte View.

Das nachfolgende Beispiel – mit Daten aus AdventureWorks2012 - verdeutlicht den Unterschied zwischen den Varianten:

CREATE FUNCTION dbo.if_personal (@NationalIdNumber nvarchar(15))
RETURNS TABLE
AS
RETURN
    SELECT  NationalIDNumber, LoginId, JobTitle
    FROM    HumanResources.Employee
    WHERE   NationalIDNumber LIKE @NationalIdNumber + '%'
GO

CREATE FUNCTION dbo.tf_personal (@NationalIdNumber nvarchar(15))
RETURNS @Result TABLE
(
    NationalIdNumber nvarchar(15),
    LoginId          nvarchar(256),
    JobTitle         nvarchar(50)
)
AS
BEGIN
    INSERT INTO @Result
    (NationalIdNumber, LoginId, JobTitle)
    SELECT  NationalIdNumber, LoginId, JobTitle
    FROM    HumanResources.Employee
    WHERE   NationalIDNumber LIKE @NationalIdNumber + '%'

    RETURN
END
GO

Während die erste Funtion dbo.if_personal unmittelbar das Ergebnis einer Abfrage zurückliefert (Inline-Valued-Tablefunction) arbeitet die zweite Funktion dbo.tf_personal nach einem anderen Muster. Die zweite Funktion liefert das Ergebnis der Abfrage zunächst in einer Tabellenvariablen (aha!) ab. Der Inhalt dieser Tabellenvariable wird dann an den aufrufenden Batch zurück geliefert. Der Ausführungsplan verdeutlicht diesen Unterschied.

Eine Abfrage auf beide Funktionen liefert die folgenden – interessanten – Ergebnisse:

-- Aktivieren des Ausführungsplans mit [STRG]+[M] SELECT * FROM dbo.if_personal('204035155'); SELECT * FROM dbo.tf_personal('204035155');

Während bei der Verwendung einer Inline-Table-Valued-Function ein Index verwendet werden kann (was auf Statistiken schliessen läßt), kann die “Multi-Table-Valued-Function” nicht auf Statistiken zurückgreifen und ein Table Scan muss durchgeführt werden. Da keine Statistiken geführt werden, ist auch erklärbar, warum die Prozedur dbo.proc_Sales_TableFunction_Recompile keine Verbesserung bringt und einen identischen Ausführungsplan verwendet.

Fazit

Zunächst war ich der festen Überzeugung, dass der vom Programmierer gewählte zeilenorientierte Ansatz nicht ideal ist. Bei den für dieses Projekt zu erwartenden Datenmengen war die Performance jedoch sehr gut. Dennoch bietet es sich an, statt zeilenorientierten Schleifen über den Einsatz von Tabellenfunktionen nachzudenken. Tabellenfunktionen  haben den besonderen Vorteil, dass sie wie eine Relation oder eine View in das SELECT-Statement eingebunden werden.

Bevor jedoch über einen solchen Einsatz in Erwägung gezogen wird, sollte auf jeden Fall die Datenmenge geprüft werden und alle Ansätze auf ihre Performance untersucht werden. Bei der Verwendung von Tabellenfunktionen, die in SELECT-Statements verwendet werden, sollte auf jeden Fall versucht werden, Inline-Table-Valued-Functions zu verwenden. Wie gezeigt, können Multi-Table-Valued-Functions keine Statistiken verwenden und so kann die vermeintliche Optimierung unter Umständen die Abfrage sogar noch verschlechtern.

Bei sehr großen Datenmengen empfiehlt sich auf jeden Fall der Einsatz von temporären Tabellen an Stelle von Tabellenvariablen da – wie in diesem Beispiel demonstriert – auch Tabellenvariablen nicht in der Lage sind, Statistiken zu verwenden. Ein Ausführungsplan wird somit bei großen Datenmengen unter Umständen eher zum Nachteil der Anwendung ausgeführt. Des Weiteren können temporäre Tabellen wie “normale” Tabellen verwendet werden. Es stehen also alle Werkzeuge (Indexe / Constraints / Statistiken  …) zur Verfügung, wie sie auch für “normale” Tabellen zur Verfügung stehen.

Alle Prozeduren und Analysen befinden sich im Beispielscript, das als SSMS-Solution heruntergeladen werden kann.

Herzlichen Dank für’s Lesen.