Cluster index & SSRS: Was bringt er in meinem Fall und wie setzte ich ihn auf?

Question

Hallo zusammen,

ich habe unter SSRS div. relationale Berichte laufen, die recht langsam geworden sind. Die Endabfrage gibt 70.000 Datensätze aus und läuft ca. 3 Sekunden und basiert auf Subabfragen. Eine neue Extratabelle möchte ich nicht erzeugen. Die Berichte hingegen sind mit mehreren Datasets recht langsam und brauchen teilweise bis 25Sek. Die Basistabellen haben einen Index. Due Basistabellen werden täglich per truncate gelöscht und neu gefüllt.

So wie ich es verstanden habe, wird beim clustered index das Ergebnis der Abfrage morgens einmal erstellt, so dass ich hier nicht bei jedem select erneut die Abfrage durchführe, also ähnlich wie bei der Cubeaufbereitung.

Frage:

1.) Ist in meinem Fall mit einer deutlichen Performanceverbesserung zu rechnen, da die basierende Abfrage nur 3 Sek. für den Durchlauf braucht? Führt SSRS die Datasets parallel aus oder warum kann ein Bericht so lange brauchen (Gruppierungen sind minimal)?

2.) Wie erstelle ich das ganze bzw. habe ich den Sachverhalt überhaupt verstanden. Gerne hätte ich ein einfaches Beispiel wie sowas erstellt wird, denn create unique clustered index Vw_Test on (Schlüsselfeld1, Schlüsselfeld2, Schlüsselfeld3, usw.) geht nicht. Leider fehlt mir hier der Zusammenhang oder ein einfaches Beispiel für mein Verständnis.

Vielen Dank.

Gruß Chris

Answer 1

So wie ich es verstanden habe, wird beim clustered index das Ergebnis der Abfrage morgens einmal erstellt, so dass ich hier nicht bei jedem select erneut die Abfrage durchführe, also ähnlich wie bei der Cubeaufbereitung.

Hallo Chris,

wie kommst Du den darauf? Ja, der Index nur beim befüllen der Tabelle aufgebaut, aber Abfragen durch die Bericht Datasets werden jedes mal neu ausgeführt.

Dem Rest des Posts nach vermute ich mal, Du willst auf eine indizierte Sicht hinaus? Das setzt voraus, das die View mit SCHEMABINDING erstellt wird, was diverse Limitierungen mit sich bringt, siehe Designing Indexed Views

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hallo Chris,

leider gehöre ich nicht zu denen, die die Zeit haben, hier Demo-Szenarien umfangreich darzustellen.

Aber 2 Hinweise kann ich geben:

Zum Einen müsste man mal die Abfrage und den Ausführungsplan sehen, um Dein Problem besser zu verstehen. So oder so gilt aber, das ein Clustered Index halt die Daten anhand einer bestimmten Spalte sortiert auf der Festplatt abspeichert (den Index-Key-Columns). Bei Berichten hilft es oft, wenn diese Spalte diejenige ist, nach der gruppiert oder auch sortiert wird. Aber man kann ja nur einen Clustered Index pro Tabelle ertellen, so das man da schnell an die Grenze kommt.  Es sei denn man nutzt die erwähnten Indexed Views.

zum Zweiten kann man Berichte mithilfe der Shared Datasets sehr gut tunen - indem man dort das Caching verwendet. Das ist eigentlich der größte Voteil der Shared Datasets. Um das richtig zu machen muss man natürlich die Filter und Parameter des Berichtes kennen, und deren typische Verwendung.

Mit diesen beiden Techniken solltest Du sehr weit kommen.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

Answer 3

So wie ich es verstanden habe, wird beim clustered index das Ergebnis der Abfrage morgens einmal erstellt, so dass ich hier nicht bei jedem select erneut die Abfrage durchführe, also ähnlich wie bei der Cubeaufbereitung.

Hallo Chris,

wie kommst Du den darauf? Ja, der Index nur beim befüllen der Tabelle aufgebaut, aber Abfragen durch die Bericht Datasets werden jedes mal neu ausgeführt.

Dem Rest des Posts nach vermute ich mal, Du willst auf eine indizierte Sicht hinaus? Das setzt voraus, das die View mit SCHEMABINDING erstellt wird, was diverse Limitierungen mit sich bringt, siehe Designing Indexed Views

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Wir hatten eine SSRS-Schulung und dort wurde das Performance Thema angesprochen. Leider kam es nicht zu einer abschließenden beispielhaften Umsetzung wegen einer ungeklärten Fehlermeldung. Das Schema war bereits angelegt und die Aussage war, dass die Abfrage im Grunde so schnell sein sollte, als ob man die Tabelle weggeschrieben bzw. dessen Ergebnis redundant erzeugt wäre. Scheinbar sind es aber wohl nur bestimmte Spalten.

Das von Andreas vorgeschlagene Caching klappt nur solange die Filterkombination bekannt ist, aber leider gibt es kein richtiges typisches Filterverhalten bzw. immens viele.

Vielen Dank für Eurer Antworten, aber das Thema Performance und die Analyse zum Ausführungsplan werden wir erst mal stärker focusieren!

Answer 4

...

... die Aussage war, dass die Abfrage im Grunde so schnell sein sollte, als ob man die Tabelle weggeschrieben bzw. dessen Ergebnis redundant erzeugt wäre. Scheinbar sind es aber wohl nur bestimmte Spalten.

Das von Andreas vorgeschlagene Caching klappt nur solange die Filterkombination bekannt ist, aber leider gibt es kein richtiges typisches Filterverhalten bzw. immens viele.

Vielen Dank für Eurer Antworten, aber das Thema Performance und die Analyse zum Ausführungsplan werden wir erst mal stärker focusieren!

Ja, diese Aussage passt zu den "Cached Datasets". Da ist ALLES enthalten (nicht nur bestimmte Spalten).

Es gibt ja den Unterschied zwischen Report-Filtern und Dataset/Query-Parametern. Wenn man Report-Filter verwendet, wirkt sich das auf das Cached Dataset nicht aus. Es wird halt nur groß.

Noch beser ist in der Tat, wenn die Abfrage dahinter schon mal optimal ist und nicht unnötig viel IO und CPU benötigt.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com