Optimieren der Abfrageleistung (SQL Server Compact)

Artikel
01/14/2011

Sie können die Anwendungsleistung von SQL Server Compact 3.5 (SQL Server Compact 3.5) verbessern, indem Sie die von Ihnen verwendeten Abfragen optimieren. In den folgenden Abschnitten werden Techniken behandelt, die Sie zum Optimieren der Abfrageleistung verwenden können.

Indizes verbessern

Das Erstellen von sinnvollen Indizes stellt eine der wichtigsten Möglichkeiten zum Erreichen einer besseren Abfrageleistung dar. Sinnvolle Indizes helfen dabei, Daten mit weniger Datenträger-E/A-Vorgängen und geringerer Systemressourcenauslastung zu finden.

Zum Erstellen sinnvoller Indizes ist das Verständnis der Datenverwendung, der Typen von Abfragen und der Häufigkeit, mit der sie ausgeführt werden, sowie das Wissen darüber hilfreich, wie der Abfrageprozessor Indizes verwenden kann, um Daten schnell zu finden.

Wenn Sie auswählen, welche Indizes zu erstellen sind, überprüfen Sie die kritischen Abfragen, deren Leistung die stärksten Auswirkungen für die Benutzer zeigt. Erstellen Sie Indizes, die sich speziell auf diese Abfragen beziehen. Führen Sie nach dem Hinzufügen eines Indexes die Abfrage erneut aus, um zu überprüfen, ob sich die Leistung verbessert hat. Wenn dies nicht der Fall ist, entfernen Sie den Index.

Wie bei den meisten Techniken zur Leistungsoptimierung gibt es Nachteile. Beispielsweise werden mit mehr Indizes SELECT-Abfragen potenziell schneller ausgeführt. Allerdings verlangsamen sich DML-Vorgänge (INSERT, UPDATE und DELETE) spürbar, da mit jedem Vorgang mehr Indizes verwaltet werden müssen. Daher können mehr Indizes hilfreich sein, wenn Ihre Abfragen in der Regel aus SELECT-Anweisungen bestehen. Wenn von Ihrer Anwendung viele DML-Vorgänge ausgeführt werden, sollten Sie eine konservative Herangehensweise in Bezug auf die Anzahl der von Ihnen erstellten Indizes wählen.

SQL Server Compact 3.5 unterstützt Showplans, die beim Beurteilen und Optimieren von Abfragen hilfreich sind. SQL Server Compact 3.5 verwendet das gleiche Showplanschema wie SQL Server 2008, wobei SQL Server Compact 3.5 jedoch nur eine Teilmenge der Operatoren verwendet.Weitere Informationen finden Sie im Showplanschema von Microsoft unter https://schemas.microsoft.com/sqlserver/2004/07/showplan/.

In den nächsten Abschnitten werden zusätzliche Informationen zum Erstellen sinnvoller Indizes bereitgestellt.

Hoch selektive Indizes erstellen

Die Indizierung von Spalten, die in den WHERE-Klauseln Ihrer kritischen Abfragen verwendet werden, verbessert häufig die Leistung. Dies hängt allerdings davon ab, wie selektiv der Index ist. Bei Selektivität handelt es sich um das Verhältnis der qualifizierenden Spalten zu den Spalten insgesamt. Bei einem niedrigen Verhältnis ist der Index hoch selektiv. Die meisten Zeilen werden ausgefiltert, und die Größe des Resultsets wird stark reduziert. Daher ist das Erstellen dieses Indexes sinnvoll. Dagegen ist ein nicht selektiver Index weniger sinnvoll.

Ein eindeutiger Index weist die größte Selektivität auf. Nur eine Zeile kann übereinstimmen, wodurch dieser Index sehr sinnvoll für Abfragen ist, die genau eine Zeile zurückgeben sollen. Mit einem Index für eine eindeutige ID-Spalte finden Sie beispielsweise schnell eine bestimmte Zeile.

Sie können die Selektivität eines Indexes auswerten, indem Sie gespeicherte Prozeduren vom Typ sp_show_statistics für SQL Server Compact 3.5-Tabellen ausführen. Wenn Sie beispielsweise die Selektivität von zwei Spalten (Customer ID-Spalte und Ship Via-Spalte) auswerten, können Sie die folgenden gespeicherten Prozeduren ausführen:

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI 0 7 0

ANATR 0 4 0

ANTON 0 13 0

AROUT 0 14 0

BERGS 0 23 0

BLAUS 0 8 0

BLONP 0 14 0

BOLID 0 7 0

BONAP 0 19 0

BOTTM 0 20 0

BSBEV 0 12 0

CACTU 0 6 0

CENTC 0 3 0

CHOPS 0 12 0

COMMI 0 5 0

CONSH 0 4 0

DRACD 0 9 0

DUMON 0 8 0

EASTC 0 13 0

ERNSH 0 33 0

(90 rows affected)

Und

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1 0 320 0

2 0 425 0

3 0 333 0

(3 rows affected)

Die Ergebnisse zeigen, dass die Customer ID-Spalte einen viel geringeren Grad der Verdoppelung aufweist. Dies bedeutet, dass ein Index für diese Spalte selektiver ist als ein Index für die Ship Via-Spalte.

Weitere Informationen zum Verwenden dieser gespeicherten Prozeduren finden Sie unter sp_show_statistics (SQL Server Compact 3.5), sp_show_statistics_steps (SQL Server Compact 3.5) und sp_show_statistics_columns (SQL Server Compact).

Mehrspaltenindizes erstellen

Mehrspaltenindizes sind natürliche Erweiterungen von Einzelspaltenindizes. Mehrspaltenindizes sind zum Auswerten von Filterausdrücken sinnvoll, die mit einem Präfixsatz von Schlüsselspalten übereinstimmen. Der zusammengesetzte Index CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) ist beispielsweise beim Auswerten der folgenden Abfragen hilfreich:

...WHERE "Last Name" = 'Doe'
...WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
...WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

Der Index ist allerdings für die folgende Abfrage nicht sinnvoll:

...WHERE "First Name" = 'John'

Wenn Sie einen Mehrspaltenindex erstellen, sollten Sie die selektivsten Spalten ganz links im Schlüssel platzieren. Dadurch wird der Index selektiver, wenn für mehrere Ausdrücke eine Übereinstimmung festgestellt wird.

Indizierung von kleinen Tabellen vermeiden

Der Inhalt einer kleinen Tabelle passt in eine oder wenige Datenseite(n). Vermeiden Sie das Indizieren von sehr kleinen Tabellen, da das Scannen einer Tabelle normalerweise effizienter ist. Damit ersparen Sie sich das Laden und Verarbeiten von Indexseiten. Wenn kein Index für sehr kleine Tabellen erstellt wird, kann vom Optimierer keine Tabelle ausgewählt werden.

Von SQL Server Compact 3.5 werden Daten in 4-KB-Seiten gespeichert. Die Seitenanzahl kann mit der folgenden Formel abgeschätzt werden. Die tatsächliche Anzahl kann allerdings aufgrund des Verwaltungsaufwands für das Speichermodul etwas größer ausfallen.

<Anzahl von Seiten> = -----------------------------------------------------------------

4096

Eine Tabelle weist beispielsweise das folgende Schema auf:

Spaltenname	Typ (Größe)
Bestellnummer	INTEGER (4 Bytes)
Product ID	INTEGER (4 Bytes)
Einzelpreis	MONEY (8 Bytes)
Menge	SMALLINT (2 Bytes)
Rabatt	REAL (4 Bytes)

Die Tabelle hat 2820 Zeilen. Entsprechend der Formel werden zum Speichern der Daten 16 Seiten benötigt:

<Anzahl von Seiten> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15,15 Seiten

Indexbezug auswählen

Es wird empfohlen, dass Sie Indizes immer für Primärschlüssel erstellen. Häufig ist es auch sinnvoll, Indizes für Fremdschlüssel zu erstellen, da Primär- und Fremdschlüssel häufig zum Verknüpfen von Tabellen verwendet werden. Mit Indizes für diese Schlüssel können vom Optimierer effizientere Indexverknüpfungsalgorithmen berücksichtigt werden. Wenn mit Ihrer Abfrage Tabellen mithilfe anderer Spalten verknüpft werden, ist es aus dem gleichen Grund häufig sinnvoll, Indizes für diese Spalten zu erstellen.

Beim Erstellen von Primär- und Fremdschlüsseleinschränkungen werden von SQL Server Compact 3.5 automatisch Indizes für sie erstellt und diese Indizes für das Optimieren von Abfragen verwendet. Denken Sie daran, Primär- und Fremdschlüssel klein zu halten. Verknüpfungen werden dadurch schneller ausgeführt.

Indizes mit Filterklauseln verwenden

Indizes können zum Beschleunigen der Auswertung bestimmter Typen von Filterklauseln verwendet werden. Obwohl alle Filterklauseln das endgültige Resultset einer Abfrage reduzieren, kann durch einige dieser Klauseln auch die zu scannende Datenmenge reduziert werden.

Ein Suchargument (SARG) begrenzt eine Suche, da damit eine exakte Übereinstimmung, ein Wertebereich oder eine Konjunktion von mindestens zwei durch AND verknüpften Elementen angegeben wird. Das Argument weist eine der folgenden Formen auf:

Spalte Operator <Konstante oder Variable>

<Konstante oder Variable> Operator Spalte

Zu den SARG-Operatoren gehören =, >, <, >=, <=, IN, BETWEEN und manchmal LIKE (bei Präfixübereinstimmung wie LIKE 'John%'). Ein SARG-Operator kann mehrere Bedingungen einschließen, die mit einem AND verknüpft werden. SARG-Operatoren können Abfragen sein, die mit einem bestimmten Wert übereinstimmen, wie beispielsweise die Folgenden:

"Customer ID" = 'ANTON'
'Doe' = "Last Name"

SARG-Operatoren können auch Abfragen sein, die mit einem Wertebereich übereinstimmen, wie beispielsweise die Folgenden:

"Order Date" > '1/1/2002'
"Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
"Customer ID" IN ('ANTON', 'AROUT')

Ein Ausdruck ohne SARG-Operatoren verbessert die Leistung nicht, da vom SQL Server Compact 3.5-Abfrageprozessor jede Zeile ausgewertet werden muss, um zu bestimmen, ob sie der Filterklausel entspricht. Daher ist ein Index für Ausdrücke ohne SARG-Operatoren nicht sinnvoll. Zu Nicht-SARG-Operatoren gehören NOT, <>, NOT EXISTS, NOT IN, NOT LIKE und systeminterne Funktionen.

Abfrageoptimierer verwenden

Beim Bestimmen der Zugriffsmethoden für Basistabellen wird vom SQL Server Compact 3.5-Optimierer bestimmt, ob ein Index für eine SARG-Klausel vorhanden ist. Wenn ein Index vorhanden ist, wird dieser vom Optimierer durch das Berechnen der zurückgegebenen Zeilenanzahl ausgewertet. Vom Optimierer wird dann der Suchaufwand für die qualifizierenden Zeilen mithilfe des Indexes abgeschätzt. Es wird der indizierte Zugriff ausgewählt, wenn der Aufwand dabei kleiner als beim Scannen der Tabelle ist. Ein Index ist potenziell sinnvoll, wenn seine erste Spalte oder der Präfixsatz von Spalten in der SARG-Klausel verwendet wird und wenn von dieser eine untere oder obere Grenze festgelegt wird (bzw. beide Grenzen festgelegt werden), um die Suche einzuschränken.

Antwortzeit im Vergleich zur Gesamtzeit

Unter Antwortzeit wird der Zeitraum verstanden, der von einer Abfrage für die Rückgabe des ersten Datensatzes benötigt wird. Unter Gesamtzeit wird der Zeitraum verstanden, der von einer Abfrage für die Rückgabe aller Datensätze benötigt wird. Für interaktive Anwendungen ist die Antwortzeit wichtig, da sie auf Seiten des Benutzers die empfundene Zeit darstellt, die benötigt wird, um eine visuelle Bestätigung dafür zu empfangen, dass eine Abfrage verarbeitet wird. Bei einer Batchanwendung gibt die Gesamtzeit den gesamten Durchsatz wieder. Bestimmen Sie die Leistungskriterien für Ihre Anwendung und Ihre Abfragen, und gestalten Sie den Entwurf dementsprechend.

Angenommen von einer Abfrage werden beispielsweise 100 Datensätze zurückgegeben und die Abfrage wird verwendet, um mit den ersten fünf Datensätzen eine Liste aufzufüllen. In diesem Fall ist es für Sie nicht ausschlaggebend, wie viel Zeit für die Rückgabe aller 100 Datensätze benötigt wird. Stattdessen sollen die ersten Datensätze schnell zurückgegeben werden, sodass die Liste aufgefüllt werden kann.

Viele Abfragevorgänge können ausgeführt werden, ohne dass Zwischenergebnisse gespeichert werden müssen. Bei diesen Vorgängen spricht man von Pipelinevorgängen. Beispiele für Pipelinevorgänge sind Projektionen, Auswahlvorgänge und Verknüpfungen. Von mit diesen Vorgängen implementierten Abfragen können Ergebnisse sofort zurückgegeben werden. Für andere Vorgänge, wie SORT und GROUP-BY, werden alle Eingaben benötigt, bevor Ergebnisse an die übergeordneten Vorgänge zurückgegeben werden können. Bei diesen Vorgängen gilt, dass sie eine Materialisierung erfordern. Mit diesen Vorgängen implementierte Abfragen weisen normalerweise eine Anfangsverzögerung aufgrund der Materialisierung auf. Nach dieser anfänglichen Verzögerung werden Datensätze dann sehr schnell zurückgegeben.

Bei Abfragen mit Anforderungen an die Antwortzeit sollte die Materialisierung vermieden werden. Ein Index zum Implementieren von ORDER-BY-Objekten erzielt beispielsweise eine bessere Antwortzeit als das Sortieren. Im folgenden Abschnitt wird dies detailliert beschrieben.

ORDER-BY-, GROUP-BY- und DISTINCT-Spalten für bessere Antwortzeit indizieren

Die Vorgänge ORDER-BY, GROUP-BY und DISTINCT dienen alle der Sortierung. Vom SQL Server Compact 3.5-Abfrageprozessor wird die Sortierung auf zwei Arten implementiert. Wenn Datensätze bereits nach einem Index sortiert sind, wird vom Prozessor nur der Index benötigt. Andernfalls muss vom Prozessor eine temporäre Tabelle verwendet werden, um zuerst die Datensätze zu sortieren. Diese Vorsortierung kann zu erheblichen anfänglichen Verzögerungen bei Geräten führen, die CPUs mit niedrigem Energieverbrauch und begrenzten Arbeitsspeicher aufweisen. Sie sollte deshalb vermieden werden, wenn die Antwortzeit wichtig ist.

Damit von ORDER-BY oder GROUP-BY ein bestimmter Index im Kontext von Mehrspaltenindizes berücksichtigt wird, müssen die Spalten ORDER-BY oder GROUP-BY mit dem Präfixsatz von Indexspalten in der exakten Reihenfolge übereinstimmen. Der Index CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) ist beispielsweise beim Optimieren der folgenden Abfragen hilfreich:

...ORDER BY / GROUP BY "Last Name" ...
...ORDER BY / GROUP BY "Last Name", "First Name" ...

Bei den folgenden Abfragen wird keine Optimierung erreicht:

...ORDER BY / GROUP BY "First Name" ...
...ORDER BY / GROUP BY "First Name", "Last Name" ...

Damit von einem DISTINCT-Vorgang ein Mehrspaltenindex berücksichtigt wird, muss die Projektionsliste mit allen Indexspalten übereinstimmen, allerdings nicht notwendigerweise in Bezug auf die exakte Reihenfolge. Der vorherige Index ist beim Optimieren der folgenden Abfragen hilfreich:

...DISTINCT "Last Name", "First Name" ...
...DISTINCT "First Name", "Last Name" ...

Bei den folgenden Abfragen wird keine Optimierung erreicht:

...DISTINCT "First Name" ...
...DISTINCT "Last Name" ...

Hinweis

Wenn von Ihrer Abfrage immer eindeutige Zeilen zurückgegeben werden, vermeiden Sie die Angabe des DISTINCT-Schlüsselworts, da dadurch nur der Verwaltungsaufwand erhöht wird.

Unterabfragen zum Verwenden von JOIN umschreiben

Manchmal können Unterabfragen zum Verwenden von JOIN umgeschrieben werden, um eine bessere Leistung zu erzielen. Der Vorteil des Erstellens einer JOIN-Verknüpfung liegt darin, dass Sie Tabellen in einer anderen Reihenfolge auswerten können, als sie durch die Abfrage definiert ist. Der Vorteil einer Unterabfrage besteht darin, dass es häufig nicht notwendig ist, alle Zeilen der Unterabfrage zu scannen, um den Unterabfrageausdruck auszuwerten. Eine EXISTS-Unterabfrage kann beispielsweise TRUE schon nach der ersten qualifizierenden Zeile zurückgeben.

Hinweis

Vom SQL Server Compact 3.5-Abfrageprozessor wird die IN-Unterabfrage immer so umgeschrieben, dass JOIN verwendet wird. Sie müssen diese Herangehensweise nicht bei Abfragen ausprobieren, die die IN-Unterabfrageklausel enthalten.

Um beispielsweise alle Bestellungen zu bestimmen, die mindestens ein Element mit einem 25-prozentigem Rabatt oder mehr aufweisen, können Sie die folgende EXISTS-Unterabfrage verwenden:

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

Sie können dies mithilfe von JOIN umschreiben:

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

OUTER JOIN-Operatoren begrenzt verwenden

Vom Optimierer wird die Verknüpfungsreihenfolge von OUTER JOIN-Tabellen im Gegensatz zu INNER JOIN-Tabellen nicht umgestellt. Auf die äußere Tabelle (die linke Tabelle in LEFT OUTER JOIN und die rechte Tabelle in RIGHT OUTER JOIN) wird zuerst zugegriffen, dann auf die innere Tabelle. Diese feste Verknüpfungsreihenfolge kann zu nicht optimalen Ausführungsplänen führen.

Weitere Informationen über eine Abfrage mit INNER JOIN finden Sie in der Microsoft Knowledge Base.

Parametrisierte Abfragen verwenden

Wenn von Ihrer Anwendung eine Reihe von Abfragen ausgeführt wird, die sich nur in einigen Konstanten voneinander unterscheiden, können Sie die Leistung mithilfe einer parametrisierten Abfrage verbessern. Um beispielsweise Bestellungen von verschiedenen Kunden zurückzugeben, können Sie die folgende Abfrage ausführen:

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

Parametrisierte Abfragen führen zu besserer Leistung, indem die Abfrage nur einmal kompiliert und der kompilierte Plan mehrmals ausgeführt wird. Programmgesteuert müssen Sie sich an dem Befehlsobjekt orientieren, das den zwischengespeicherten Abfrageplan enthält. Durch das Löschen des vorherigen Befehlsobjekts und das Erstellen eines neuen wird der zwischengespeicherte Plan gelöscht. Dadurch muss die Abfrage erneut kompiliert werden. Falls Sie mehrere parametrisierte Abfragen überlappend ausführen müssen, können Sie mehrere Befehlsobjekte erstellen, wobei in jedem Objekt der Ausführungsplan für eine parametrisierte Abfrage zwischengespeichert ist. Dadurch vermeiden Sie effektiv das erneute Kompilieren aller Abfragen.

Abfragen nur bei Bedarf ausführen

Der SQL Server Compact 3.5-Abfrageprozessor stellt ein leistungsfähiges Tool für das Abfragen von Daten dar, die in einer relationalen Datenbank gespeichert werden. Es ist allerdings mit jedem Abfrageprozessor ein systeminterner Aufwand verbunden. Vom Abfrageprozessor muss ein Ausführungsplan kompiliert, optimiert und generiert werden, bevor die tatsächliche Ausführung des Plans anfängt. Dies ist insbesondere der Fall bei einfachen Abfragen, die schnell beendet werden. Implementieren Sie die Abfrage selbst, so kann dies zu enormen Leistungsverbesserungen führen. Wenn in Ihrer kritischen Komponente jede Millisekunde zählt, ist das eigene Implementieren einer einfachen Abfrage möglicherweise von Vorteil. Große und komplexe Abfragen werden dagegen besser vom Abfrageprozessor verarbeitet.

Angenommen Sie möchten beispielsweise nach der Kundennummer für eine Reihe von Bestellungen suchen, die nach den Bestellnummern angeordnet sind. Dies kann auf zwei Arten geschehen. Zunächst können Sie den folgenden Schritten für jede Suche folgen:

Öffnen Sie die Bestellungsbasistabelle.
Suchen Sie die Zeile mithilfe der entsprechenden Bestellnummer ("Order ID").
Rufen Sie die Kundennummer ab ("Customer ID").

Alternativ dazu können Sie die folgende Abfrage für jede Suche ausführen:

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

Die abfragebasierte Lösung ist einfacher, aber langsamer als die manuelle Lösung, da vom SQL Server Compact 3.5-Abfrageprozessor die deklarative SQL-Anweisung in die gleichen drei Vorgänge übersetzt wird wie die, die Sie manuell implementieren können. Diese drei Schritte werden dann nacheinander ausgeführt. Die Wahl der von Ihnen verwendeten Methode hängt davon ab, ob in Ihrer Anwendung eher Einfachheit oder Leistung wichtig ist.