Dienstag, 1. Januar 2013

Verwendung von “Indexed views” zur Verbesserung der Performance

Dieser Artikel beschäftigt sich mit einem Feature, das ich in der letzten Woche bei einem Kunden in einem BI-Projekt implementiert habe, um die Ausführung von Berichten zu optimieren – indizierte Views (indexed views). Insbesondere, wenn die Enterprise Edition von Microsoft SQL Server verwendet wird, ergeben sich durch die Verwendung von Indexed Views besondere Vorteile der – unbewußten – Verwendung.

Problemstellung

In einem Projekt eines Kunden werden die Reporting Services für Managementberichte in Verbindung mit Microsoft SQL Server 2008 R2 (Enterprise Edition) eingesetzt. Hierbei kommt es neben den reinen Zahlen auch darauf an, dass die Berichte möglichst schnell ausgeführt werden. Die Datengrundlage eines Managementberichts umfasst die Analyse von Rechnungen und Nettosummen, die pro Kunde ausgewertet werden sollen. Das System umfasst derzeit einen Bestand von ca. 60.000 Kunden sowie ca. 1.000.000 Rechnungen aus unterschiedlichen Jahren. Für die Rechnungsanalyse werden primär zwei Relationen verwendet, welche die Stammdaten ([dbo].[tbl_Stammdaten]) als auch die Rechnungen ([dbo].[tbl_Invoices]) beinhalten. Zwischen beiden Relationen ist eine 1:n Fremdschlüsselbeziehung vorhanden.

Hinweis: Für den Artikel wurde die Datenstruktur sehr stark vereinfacht!

Die Basisabfrage für den Managementbericht sieht wie folgt aus:

SELECT  s.Sid            AS Customer_Id,
        s.SortName       AS Customer_Name,
       
COUNT(*)         AS No_Of_Invoices,
        SUM(i.NetAmount) AS Sum_Of_Invoices
FROM    dbo.tbl_Stammdaten s INNER JOIN dbo.tbl_Invoices i
        ON (s.Sid = i.Sid)
GROUP BY
        s.Sid,
        s.SortName
;

Die Analyse des Abfrageplans sowie des IO ergab das folgende Ergebnis:

image

tbl_Invoices-Tabelle. Scananzahl 1, logische Lesevorgänge 364, . . .
tbl_Stammdaten-Tabelle. Scananzahl 1, logische Lesevorgänge 1263, . . .

Um die Abfrage zu optimieren, wurde vorgeschlagen, als Datengrundlage für den Bericht nicht mehr das native SQL-Statement zu verwenden sondern mittels einer indizierten View die Abfrage zu beschleunigen. Jedoch wollte der Projektleiter aber die Überarbeitung aller Datasets für die bestehenden Reports vermeiden.

Indexed Views

Eine “Indexed View” ist im Gegensatz zu einer “normalen” View ein auf Basis einer Abfrage materialisiertes Objekt. Man kann eine Indexed View mit einer gewöhnlichen Relation vergleichen – und somit alle Vorteile einer Tabelle anwenden. Indexed View sind insbesondere dann von sehr großem Vorteil, wenn die Abfrage Aggregatfunktionen verwendet. Für Indizierte Sichten gibt es einige Dinge zu beachten. Die vollständige Beschreibung von Indizierten Sichten findet sich in der MSDN von Microsoft (siehe Links am Ende des Artikels)

Um eine Indexed View zu erstellen, sind die folgenden Punkte zu beachten:

  • die SET-Optionen für alle vorhandenen Tabellen müssen korrekt eingestellt sein
  • die Sichtdefinition muss deterministisch sein.
  • die Sicht muss mithilfe der Option WITH SCHEMABINDING erstellt werden.
  • zuerst muss ein UNIQUE CLUSTERED Index auf die View erstellt werden bevor weitere Indexe erstellt werden.

Basierend auf die Anforderungen wird die View wie folgt erstellt:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;

IF OBJECT_ID('dbo.view_indexed_StammdatenNetAmount', 'V') IS NOT NULL
    DROP VIEW dbo.view_indexed_StammdatenNetAmount
    GO

CREATE VIEW dbo.view_indexed_StammdatenNetAmount
WITH SCHEMABINDING
AS
SELECT
  s.Sid               AS Customer_Id,
        s.SortName          AS Customer_Name,
        COUNT_BIG(*)        AS No_Of_Invoices,
        SUM(i.NetAmount)    AS Sum_Of_Invoices
FROM    dbo.tbl_Stammdaten s INNER JOIN dbo.tbl_Invoices i
        ON (s.Sid = i.Sid)
GROUP BY
        s.Sid,
        s.SortName
GO

CREATE UNIQUE CLUSTERED INDEX ix_view_indexed_StammdatenNetAmount_SId ON dbo.view_indexed_StammdatenNetAmount (Customer_Id);

Im Besonderen ist darauf hinzuweisen – ebenfalls eine Einschränkung für Indexed Views – dass die Aggregatfunktion COUNT() nicht verwendet werden darf. Stattdessen ist die Verwendung von COUNT_BIG() als Aggregation zwingend vorgeschrieben. Dieses Verfahren hilft dem Abfrageoptimierer die Anzahl der pro Aggregation vorhandenen Datensätze zu terminieren.

Der Abfrageplan und die IO Statistiken bei Verwendung der Indexed View zeigen nun eine DEUTLICHE Verbesserung.

image

view_indexed_StammdatenNetAmount-Tabelle. Scananzahl 1, logische Lesevorgänge 138, . . .

So weit so gut – interessant wird es jedoch, wenn man die Enterprise Edition oder Developer Edition von SQL Server verwendet. In diesem Fall erkennt die SQL Engine automatisch, ob eine Abfrage (z. B. Ad Hoc) durch eine Indexed View ersetzt werden kann. Die nachfolgende Abbildung verdeutlicht den Sachverhalt.

image

Die obige Abbildung zeigt den Abfrageplan bei Ausführung der Abfrage (Zeile 4 –13) sowie die Ausführung der Indexed View (ihr liegt die Abfrage zu Grunde) in Zeile 15. Betrachtet man die Ausführungspläne beider Abfragen, erkennt man sofort, dass beide Abfragen die Indexed View (Clustered Index Scan von dbo.view_indexed_StammdatenNetAmount) verwenden. Man spricht bei einem solchen Verhalten von “automatic query-to-indexed-view matching”. Leider ist diese Option jedoch nur der Enterprise Edition oder der Developer Edition von SQL Server vorbehalten.

Anwendung in aktuellem Projekt

Der Einsatz von Indexed Views in Verbindung mit SQL Server Enterprise Edition führte dazu, dass die Datasets der Berichte nicht überarbeitet werden mußte. Aufgrund von “automatic query to indexed-view matching” konnte dennoch eine Optimierung der Berichte durchgeführt werden. Insgesamt wurde die Abfragezeit für den Bericht um fast 70% optimiert.

Herzlichen Dank für’s Lesen.
Allen Lesern einen guten Rutsch und viel Erfolg für das Jahr 2013.

 

Indexed Views: http://msdn.microsoft.com/de-de/library/ms191432.aspx#Restrictions
http://technet.microsoft.com/de-de/library/cc917715.aspx
COUNT_BIG()

http://msdn.microsoft.com/de-de/library/ms190317.aspx

Aufbau von Indexed Views: http://msdn.microsoft.com/de-de/library/ms187864(v=sql.105).aspx
Download der Beispieldaten http://www.db-berater.de/files/blog/partitionedviews.zip
   

Kommentare :

  1. Besten Dank für den Beitrag, hat mir in jedem Fall geholfen. Microsoft empfiehlt ja den Aufbau einer Indexed View nachdem die zugrunde liegenden Tables mit Daten gefüllt wurden. Ebenfalls empfiehlt Microsoft die View neu aufzubauen wenn sich viele(?) Daten in den Tables ändern bzw. hinzukommen. Ist der Unterschied hinsichtlich Performance so gravierend? Was heißt in diesem Fall "viel"? In Ihrem Beispiel müsste dann ja jede Woche die View neu aufgezogen werden. Oder verstehe ich da etwas komplett falsch? Vielen Dank.

    AntwortenLöschen
  2. Hi Uwe,

    hast Du auch ein Beispiel parat, wie sich eine indexed view negativ auswirken kann auf die Performance? Es gibt ja die Empfehlung eine indexed view auf Tabellen zu legen, die sich nicht großartig ändern. (siehe auch Vorredner)

    AntwortenLöschen