Der folgende Beitrag befasst sich mit dem Testen der Performance von Cubes auf einem SQL Server. Es soll aufgezeigt werden, wie man die Geschwindigkeit von Abfragen auf Cubes aufzeichnen kann und welche Unterschiede zwischen einem relationalen OLAP Cube und einem Tabular-Cube sowie zwischen Cubes ohne und mit Clustered-ColumnStore-Indizes in Bezug auf die Abfragegeschwindigkeit bestehen.
Vorbereitungen
Die hierbei zugrundeliegenden Daten sollen die Passagierzahlen an Berliner Bahnhöfen widerspiegeln. Grundlegend sind für die späteren Untersuchungen drei Tabellen wichtig. Eine Date-Dimension, eine Dimension zu den Stationen, sowie eine Log-Faktentabelle, die die Fahrgastzahlen(wartende, einsteigende & austeigende Passagiere) an den Bahnhöfen erfasst.
Anhand dieser Struktur sollen verschiedene beispielhafte Abfragen nach ihrer Dauer untersucht und verglichen werden. In der Faktentabelle sind dabei 492.591 Zeilen enthalten. Exemplarisch werden hier drei Abfragen untersucht:
- Durchschnitt aller hinzukommenden, wartenden und verlassenden Passagiere aufgeteilt nach Stationen
- Durchschnitt aller hinzukommenden, wartenden und verlassenden Passagiere aufgeteilt nach Stationen am 16.12.2014
- Neueste Einträge der hinzukommenden, wartenden und verlassenden Passagiere aufgeteilt nach Stationen (Diese Werte müssen vorher als Calculated Measures formuliert werden)
Da die Abfragen einmal für einen multidimensionalen und einmal für einen Tabular-Cube ausgeführt werden müssen, müssen diese zum einen in MDX (multidimensional) und zum anderen in DAX (Tabular) geschrieben bzw. ausgeführt werden. Die Abfragen wurden nicht im SQL-Server geschrieben und ausgeführt, sondern mittels Excel über eine Pivot-Tabelle (Rolap) und ein Power View Sheet (Tabular) realisiert.
Die Abfragegeschwindigkeit kann dabei mit dem SQL Server 2014 Profiler aufgezeichnet werden. Dazu benötigt man eine neue Trace (File -> New Trace). Daraufhin wird eine Verbindung zum Server hergestellt, wo der Cube liegt und die Abfragen geschrieben werden. In den darauffolgenden Einstellungen (Tab „Events Selection“) ist wichtig, dass die Häkchen für „Queries Events“ gesetzt sind:
In der folgenden Übersicht kann man für jede Query auf dem Server ein Start- und ein End-Event sehen, welche die Eigenschaften der Query beinhalten. Interessant ist hierbei vor allem die Dauer, welche in Millisekunden angegeben wird. Zum besseren Verständnis sind jedoch die später folgenden Werte der Abfragedauer in Sekunden angegeben.
Zuerst sollt jedoch bedacht werden, dass beim Testen der Abfragen die Zwischenspeicher der Datenbank und der SSAS gefüllt werden, sodass ein Wiederholen der Abfrage die Ergebnisse beinträchtigen könnte. Um diesen zu entgehen, sollte vor dem Messen der einzelnen Abfragen zum einen der Cache der Datenbank per SQL-Befehl geleert werden:
USE [Datenbankname];
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
Der zweite Schritt ist die Leerung des Cache von SSAS. Beachten sollte man hier, dass dies einmal für den Rolap Cube und einmal für den Tabular Cube passieren muss. Dazu lässt sich eine XMLA Query nutzen:
<ClearCache
xmlns=„http://schemas.microsoft.com/analysisservices/2003/engine„>
<Object>
<DatabaseID>Datenbankname</DatabaseID>
</Object>
</ClearCache>
Um die Abfragen mittels Clustered-ColumnStore-Indizes auszuführen muss in der Ausgangsdatenbank im SQL-Server für jede Tabelle ein Clustered-ColumnStore-Index angelegt werden:
Ergebnisse ohne Clustered-ColumnStore-Indizes
|
Abfrage 1 |
Abfrage 2 |
Abfrage 3 |
|
|
Rolap |
5,222 |
1,751 |
9,859 |
|
Tabular |
0,940 |
0,951 |
116,596 |
Man erkennt deutliche Unterschiede zwischen den zwei Cubes. Die Abfrage 1 zeigt einen erheblichen Geschwindigkeitsvorteil des Tabular Cube mit Direct Query Mode gegenüber dem Rolap Cube. Abfrage 2 ist eine Weiterentwicklung von Abfrage 1 mit einem zusätzlichen Filter. Dieser beschleunigt die Abfragegeschwindigkeit beim Rolap Cube, jedoch nicht beim Tabular Cube. Dennoch ist der Tabular Cube schneller als der Rolap Cube. Die dritte Abfrage beinhaltet Calculated Measures und dort wird ersichtlich, dass der Tabular Cube einen enormen Nachteil hinsichtlich der Abfragedauer hat, da er ca. 11mal länger benötigt als sein Gegenpart.
Ergebnisse mit Clustered-ColumnStore-Indizes
|
Abfrage 1 |
Abfrage 2 |
Abfrage 3 |
|
|
Rolap |
5,162 |
1,245 |
8,154 |
|
Tabular |
0,792 |
0,615 |
108,599 |
Mit den Clustered-ColumnStore-Indizes ändert sich an den Unterschieden zwischen dem Rolap Cube und dem Tabular Cube wenig. Die Dauer von Abfrage 1 hat sich bei beiden Cubes durch die Indizes nur unwesentlich verkürzt und somit bleibt der Geschwindigkeitsvorteil hier beim Tabular Cube, sowie auch bei Abfrage 2. Jedoch macht sich hier auch durch die Indizes bereits ein Unterschied bei beiden Cubes bemerkbar, wodurch die Abfragedauer um ca. 1/3 verkürzt wurde. Die dritte Abfrage wiederum bietet auf die gesamte Abfragedauer gesehen nur eine kleine Verkürzung im Bereich von 5% (Tabular) – 20% (Rolap) durch die Indizes.
Fazit
Aus den Ergebnissen kann man schließen, dass der Tabular Cube im Direct Query Mode für einfachere Abfragen sich besser eignet als der Rolap Cube. Dieser hat jedoch enorme Vorteile wenn man z.B. berechnete Measures einsetzt. Die Clustered-ColumnStore-Indizes vermögen bei diesem Beispiel keinen großen Ausschlag auf die Abfragegeschwindigkeit geben. Es ist zu vermuten, dass sich dies jedoch bei größeren Datenmengen ändert und so größere Vorteile durch den Einsatz der Indizes zu beobachten wären. Man sollte auch beachten, dass sich die Unterschiede zwischen dem Tabular Cube und dem Rolap Cube bei größeren Datenmengen durchaus verschieben können.