• Artikel

Entwerfen von indizierten Sichten

Sichten werden auch als virtuelle Tabellen bezeichnet, da das Resultset, das von der Sicht zurückgegeben wird, dieselbe allgemeine Form wie eine Tabelle mit Spalten und Zeilen aufweist. Zudem können SQL-Anweisungen genauso auf Sichten verweisen, wie sie auf Tabellen verweisen. Das Resultset einer Standardsicht wird nicht dauerhaft in der Datenbank gespeichert. Jedes Mal, wenn eine Abfrage auf eine Standardsicht verweist, ersetzt SQL Server die Definition der Sicht in der Abfrage, bis eine geänderte Abfrage entsteht, die nur noch auf Basistabellen verweist. Danach wird die so entstandene Abfrage ganz normal ausgeführt. Weitere Informationen finden Sie unter Sichtauflösung.

Bei Standardsichten kann der erforderliche Aufwand, um für jede Abfrage, die auf eine Sicht verweist, das Resultset dynamisch zu erstellen, für diejenigen Sichten erhebliche Ausmaße annehmen, die das komplexe Verarbeiten einer großen Anzahl von Zeilen einschließen. Beispiele hierfür sind Sichten, die große Mengen an Daten aggregieren oder viele Zeilen verknüpfen. Wenn in Abfragen häufig auf diese Sichten verwiesen wird, können Sie die Leistung verbessern, indem Sie einen eindeutigen gruppierten Index für die Sicht erstellen. Wird ein eindeutiger, gruppierter Index für eine Sicht erstellt, wird das Resultset genauso wie eine Tabelle mit einem gruppierten Index in der Datenbank gespeichert.

Ein weiterer Vorteil des Erstellens eines Index für eine Sicht besteht darin, dass der Optimierer in Abfragen, die die Sicht nicht direkt in der FROM-Klausel benennen, mit dem Verwenden des Sichtindex beginnt. Vorhandene Abfragen können von der erhöhten Effizienz beim Abrufen von Daten aus der indizierten Sicht profitieren, ohne dass ihr Code geändert werden muss. Weitere Informationen finden Sie unter Auflösen von Indizes für Sichten.

Wenn Änderungen an den Daten in den Basistabellen vorgenommen werden, werden die Datenänderungen in den Daten widergespiegelt, die in der indizierten Sicht gespeichert sind. Aufgrund der Tatsache, dass der gruppierte Index der Sicht eindeutig sein muss, erhöht sich die Effizienz, mit der SQL Server die Zeilen im Index ermitteln kann, die von einer Datenänderung betroffen sind.

Die Fähigkeit des Abfrageoptimierers zur Nutzung indizierter Sichten beim Verarbeiten von Abfragen wurde gegenüber früheren Versionen verbessert, wenn sowohl die Abfrage- als auch die Sichtdefinition die folgenden übereinstimmenden Elemente enthalten:

  • Skalare Ausdrücke. Beispielsweise kann der Abfrageoptimierer eine Zuordnung der folgenden Abfrage mit einem skalaren Ausdruck in ihrem Prädikat:

    SELECT ColA, ColB FROM TableT WHERE ColC * (ColD + 10) > 50

    zu einem für diese Sicht erstellten Index herstellen:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC * (ColD + 10) AS ExpCol
FROM dbo.TableT

    In gleicher Weise können auch skalare Ausdrücke zugeordnet werden, die benutzerdefinierte Funktionen enthalten.

  • Skalare Aggregatfunktionen. Beispielsweise kann eine Zuordnung der folgenden Abfrage, die eine skalare Aggregatfunktion in ihrer SELECT-Liste enthält:

    SELECT COUNT_BIG (*) FROM dbo.TableT

    zu einem für diese Sicht erstellten Index erfolgen:

    CREATE VIEW V2 WITH SCHEMABINDING AS
SELECT COUNT_BIG (*) AS Cnt 
FROM dbo.TableT

Der Abfrageoptimierer berücksichtigt bei der Auswahl einer Abfrage außerdem Folgendes:

  • Ob ein in einem Abfrageprädikat definierter Werteintervall in einem Intervall liegt, das in einer indizierten Sicht definiert ist. Betrachten Sie z. B. einen Index, der für die folgende Sicht erstellt wurde:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC FROM dbo.TableT
WHERE ColA > 1 and ColA < 10

    Jetzt nehmen wir die folgende Abfrage an:

    SELECT ColB, ColC FROM dbo.TableT
WHERE ColA > 3 and ColA < 7

    Der Abfrageoptimierer würde diese Abfrage der Sicht V1 zuordnen, weil das Intervall zwischen 3 und 7, das in der Abfrage definiert ist, im Intervall zwischen 1 und 10 liegt, das in der indizierten Sicht erstellt wurde.

  • Inwieweit ein in einer Abfrage definierter Ausdruck mit dem Ausdruck übereinstimmt, der in einer indizierten Sicht definiert ist. SQL Server versucht, die Ausdrücke zuzuordnen, indem es ihre Spaltenverweise, Literale, die logischen Operatoren AND, OR, NOT, BETWEEN und IN sowie die Vergleichsoperatoren =, <>, >, <, >= und <= betrachtet. Arithmetische Operatoren wie z. B. + und % sowie Parameter werden dabei nicht berücksichtigt.

    Beispielsweise würde der Abfrageoptimierer die folgende Abfrage:

    SELECT ColA, ColB from dbo.TableT
WHERE ColA < ColB

    einem für diese Sicht erstellten Index zuordnen:

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB FROM dbo.TableT
WHERE ColB > ColA

Denken Sie daran, dass SQL Server wie bei allen Indizes nur dann eine indizierte Sicht in seinem Abfrageplan verwendet, wenn der Abfrageoptimierer feststellt, dass dies hilfreich ist.

Indizierte Sichten können in jeder Edition von SQL Server 2008 erstellt werden. In SQL Server 2008 Enterprise verwendet der Abfrageoptimierer automatisch die indizierte Sicht. Zum Verwenden einer indizierten Sicht in allen anderen Editionen muss der NOEXPAND-Tabellenhinweis verwendet werden.

Richtlinien für das Entwerfen einer indizierten Sicht.

Indizierte Sichten funktionieren am besten, wenn die zugrunde liegenden Daten selten aktualisiert werden. Die Wartungskosten einer indizierten Sicht können höher sein als die eines Tabellenindex. Falls die zugrunde liegenden Daten häufig aktualisiert werden, können die Kosten für die Wartung der Daten der indizierten Sicht die Leistungsvorteile durch das Verwenden der indizierten Sicht aufwiegen. Wenn die zugrunde liegenden Daten regelmäßig in Batches aktualisiert werden, zwischen den Aktualisierungen jedoch primär als schreibgeschützt behandelt werden, sollten Sie das Löschen aller indizierten Sichten vor dem Aktualisieren in Betracht ziehen und sie anschließend neu erstellen. Das kann die Leistung der Aktualisierungen steigern.

Indizierte Sichten bewirken bei den folgenden Abfragetypen eine Leistungsverbesserung:

  • Verknüpfungen und Aggregationen, die viele Zeilen verarbeiten.

  • Verknüpfungs- und Aggregationsvorgänge, die von vielen Abfragen häufig ausgeführt werden.

    Beispielsweise würde bei einer OLTP-Datenbank (Online Transaction Processing, Onlinetransaktionsverarbeitung), die Bestände aufzeichnet, erwartet, dass viele Abfragen die ProductMaster-, ProductVendor- und VendorMaster-Tabellen verknüpfen. Auch wenn jede Abfrage, die diese Verknüpfung ausführt, nicht viele Zeilen verarbeitet, kann der Gesamtaufwand der Verknüpfung von Hunderten oder Tausenden solcher Abfragen erheblich sein. Da diese Beziehungen wahrscheinlich nicht oft aktualisiert werden, könnte die allgemeine Leistung des gesamten Systems verbessert werden, wenn eine indizierte Sicht definiert wird, die die verknüpften Ergebnisse speichert.

  • Decision Support-Arbeitsauslastungen.

    Analysesysteme sind gekennzeichnet durch die Speicherung zusammengefasster, aggregierter Daten, die selten aktualisiert werden. Viele Decision Support-Abfragen sind durch die zusätzliche Aggregierung der Daten und die Verknüpfung vieler Zeilen gekennzeichnet. Außerdem enthalten Decision Support-Systeme manchmal breite Tabellen mit vielen Spalten oder große Spalten oder beides. Abfragen, die auf eine kleine Teilmenge dieser Spalten verweisen, können von einer indizierten Sicht profitieren, die nur die Spalten in der Abfrage oder eine kleine (schmale) Obermenge dieser Spalten enthält. Das Erstellen schmaler Indexsichten, die eine Teilmenge der Spalten für eine einzelne Tabelle enthalten, wird als vertikale Partitionierung bezeichnet, weil dabei Tabellen vertikal geteilt werden. Betrachten Sie z. B. die folgende Tabelle und die folgende indizierte Sicht:

    CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ..., z int)
CREATE VIEW v_abc WITH SCHEMABINDING AS
SELECT a, b, c
FROM dbo.wide_tbl
WHERE a BETWEEN 0 AND 1000
CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)

    Die folgende Abfrage kann beantwortet werden, indem einfach v_abc verwendet wird:

    SELECT b, count_big(*), SUM(c)
FROM wide_tbl 
WHERE a BETWEEN 0 AND 1000
GROUP BY b

    Die Sicht v_abc belegt viel weniger Seiten als die wide_tbl-Tabelle. Deshalb kann es für den Optimierer besser sein, diese Sicht als Zugriffspfad auszuwählen, um die vorige Abfrage zu lösen.

    Wenn Sie eine komplette Tabelle anstelle einer Teilmenge davon vertikal teilen möchten, empfehlen wir Ihnen, einen nicht gruppierten Index für die Tabelle zu verwenden, der eine INCLUDE-Klausel verwendet, um nur die gewünschten Spalten anstelle einer indizierten Sicht einzubeziehen. Weitere Informationen finden Sie unter CREATE INDEX (Transact-SQL).

Indizierte Sichten bewirken bei den folgenden Abfragetypen normalerweise keine Leistungsverbesserung:

  • OLTP-Systeme mit vielen Schreibvorgängen.

  • Datenbanken mit vielen Aktualisierungen.

  • Abfragen ohne Aggregationen oder Verknüpfungen.

  • Aggregationen von Daten mit einem hohen Maß an Kardinalität für den GROUP BY-Schlüssel. Ein hohes Maß an Kardinalität bedeutet, dass der Schlüssel viele unterschiedliche Werte enthält. Ein eindeutiger Schlüssel hat das höchstmögliche Maß an Kardinalität, weil jeder Schlüssel einen anderen Wert hat. Indizierte Sichten verbessern die Leistung, indem die Anzahl der Zeilen reduziert wird, auf die eine Abfrage zugreifen muss. Falls das Resultset der Sicht fast so viele Zeilen wie die Basistabelle enthält, bietet das Verwenden der Sicht kaum einen Leistungsvorteil. Betrachten Sie z. B. die folgende Abfrage für eine Tabelle mit 1.000 Zeilen:

    SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

    Wenn die Kardinalität des Tabellenschlüssels 100 beträgt, würde eine indizierte Sicht, die anhand des Ergebnisses dieser Abfrage erstellt wird, nur 100 Zeilen aufweisen. Abfragen, die diese Sicht verwenden, würden im Schnitt ein Zehntel der Lesevorgänge für die Basistabelle benötigen. Falls der Schlüssel ein eindeutiger Schlüssel ist, beträgt die Kardinalität des Schlüssels 1.000 und das Resultset der Sicht gibt 1.000 Zeilen zurück. Wenn die Sicht und die ExampleTable-Basistabelle Zeilen gleicher Größe aufweisen, erzielt eine Abfrage durch Verwendung dieser indizierten Sicht keine Leistungssteigerung gegenüber dem direkten Lesen der Basistabelle.

  • Erweiterungsverknüpfungen. Dabei handelt es sich um Sichten, deren Resultsets größer als die ursprünglichen Daten in der Basistabelle sind.

Kombinieren von indizierten Sichten und Abfragen

Wenn die Einschränkungen bezüglich der Sichttypen, die indizierbar sind, Sie möglicherweise am Entwerfen einer Sicht hindern, die ein Problem vollständig löst, können Sie mehrere kleinere indizierte Sichten entwerfen, die Teile des Vorgangs beschleunigen.

Betrachten Sie die folgenden Beispiele:

  • Eine häufig ausgeführte Abfrage aggregiert Daten in zwei verschiedenen Datenbanken und verknüpft dann die Ergebnisse. Da eine indizierte Sicht nicht auf Tabellen von mehreren Datenbanken verweisen kann, ist es nicht möglich, eine einzige Sicht für die Durchführung des gesamten Vorgangs zu entwerfen. Sie können allerdings eine indizierte Sicht in jeder Datenbank erstellen, die die Aggregation für die jeweilige Datenbank ausführt. Falls der Abfrageoptimierer die indizierten Sichten mit den vorhandenen Abfragen zur Übereinstimmung bringen kann, wird zumindest der Aggregationsvorgang beschleunigt, ohne dass vorhandene Abfragen umgeschrieben werden müssen. Zwar ist die Verknüpfungsverarbeitung nicht schneller, aber die Abfrage ist insgesamt schneller, weil sie die in den indizierten Sichten gespeicherten Aggregationen verwendet.

  • Eine häufig ausgeführte Abfrage aggregiert Daten aus mehreren Tabellen und verwendet dann eine UNION-Anweisung, um die Ergebnisse zu kombinieren. UNION ist in einer indizierten Sicht nicht zulässig. Sie können auch in diesem Fall Sichten entwerfen, um die einzelnen Aggregationsvorgänge auszuführen. Der Abfrageoptimierer kann dann die indizierten Sichten auswählen, um Abfragen zu beschleunigen, ohne die Abfragen umschreiben zu müssen. Zwar wird die UNION-Verarbeitung nicht optimiert, aber die einzelnen Aggregationsprozesse.

Entwerfen Sie indizierte Sichten, die für mehrere Operationen geeignet sind. Da der Abfrageoptimierer eine indizierte Sicht verwenden kann, selbst wenn sie nicht in der FROM-Klausel angegeben ist, kann eine durchdachte indizierte Sicht die Verarbeitung vieler Abfragen beschleunigen.

Erstellen Sie z. B. einen Index für die folgende Sicht:

CREATE VIEW ExampleView WITH SCHEMABINDING
AS
SELECT GroupKey, SUM(Colx) AS SumColx, COUNT_BIG(Colx) AS CountColx
FROM MyTable
GROUP BY GroupKey

Diese Sicht ist nicht nur für Abfragen geeignet, die direkt auf die Spalten der Sicht verweisen, sondern sie kann auch für Abfragen verwendet werden, die die Basistabelle abfragen und Ausdrücke wie SUM(Colx), COUNT_BIG(Colx), COUNT(Colx) und AVG(Colx) enthalten. All diese Abfragen werden schneller ausgeführt, weil sie nicht alle Zeilen in den Basistabellen lesen müssen, sondern nur wenige Zeilen in der Sicht abrufen müssen.

Desgleichen kann eine indizierte Sicht, die Daten und Gruppen nach Tagen aggregiert, für Abfragen verwendet werden, die eine Aggregierung über mehrere verschiedene Bereiche von mehreren Tagen (z. B. 7, 30 oder 90 Tage) durchführen.