Bis zur Version 5.0 wurden Summenindexe in Dynamics NAV / Navision auf dem SQL Server durch einen Umweg über SQL Triggern und eigenen SQL Tabellen gebildet. Bei jedem Insert, Update oder Delete auf einer Tabelle mit Summenindexen wird ein Trigger durchlaufen, der dafür sorgt, dass in separaten Tabellen die definierten Summen gebildet werden. Der Vorteil dieser Technik ist, dass die, mit der Änderung der Datensätze gebildeten Summen, mit einzelnen lesenden Zugriffen sehr schnell abgefragt werden können. Der Nachteil ist der mitunter enorme Aufwand auf dem SQL Server die Summen über Trigger zu pflegen.
Wurde bisher in NAV für eine Tabelle ein Schlüssel definiert, der SumIndexFields enthält, so wurde auf dem SQL Server ein Trigger (SIFT Trigger) angelegt und eine neue SQL Tabelle (SIFT Tabelle). Über den SIFT Trigger wird auf dem SQL Server für jeden Detaillierungsgrad der Indexfelder die Summe in der SIFT Tabelle gespeichert.
Für einen Schlüssel mit den Feldern
Item No.,Variant Code,Drop Shipment,Location Code,Posting Date
heißt das in der Praxis, dass bis zu 7 Summen für jedes Insert, Update oder Delete aktualisiert werden müssen. Die 7 Summen ergeben sich aus den 7 Detaillierungsebenen (Buckets/SIFT Level) der Indexfelder.
Item No.
Item No.,Variant Code
Item No.,Variant Code,Drop Shipment
Item No.,Variant Code,Drop Shipment,Location Code
Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Year
Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Month
Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Day
Stellen wir uns jetzt eine Tabelle wie z.B. die Artikelposten vor. Die Tabelle hat, je nach Branchen-/Individuallösung weit über 10 Schlüssel. Viele dieser Schlüssel haben SumIndexFields. Für jeden Schlüssel mit SumIndexFields gibt es eine SIFT Tabelle. In der SIFT Tabelle gibt es Summen für jeden Bucket. Je mehr Felder der Schlüssel hat desto mehr Buckets gibt es in der SIFT Tabelle.
Alles in allem kann es soweit gehen, dass wir mit jedem Datensatz in der Tabelle mehr als 100 (in Worten: einhundert
) zusätzliche Aktionen auf dem SQL Server auslösen, wenn wir einen Datensatz in den Artikelposten speichern. Wir sprechen dann von einem hohen CostPerRecord. (Wobei für den Cost per Record auch die Aktualisierung der Indexe hinzugerechnet wird.)
Das heißt also das „einfache“ Speichern eines Datensatzes in den Artikelposen wird zu mehr als 100mal Insert oder Update auf dem SQL Server.
Und das soll dann performant sein?!?
Hier kommt jetzt das SP1 ins Spiel. Mit dem Service Pack 1 für Dynamics NAV 5.0 wurde diese “alte” Technik der Summenindexe abgelöst durch Indexed Views(VSIFT). Unter VSIFT versteht man Indexed Views zur Speicherung und Berechnung der Summen. Diese Indexed Views sind eine in den SQL Server integrierte Technik, die es ermöglicht Sichten (Views) auf Daten zu materialisieren. Will sagen, die berechneten Daten der Sicht sind physikalisch in der SQL Server Datenbank vorhanden. Gegenüber „normalen“ Views oder Abfragen auf dem SQL Server, die die Ergebnisse zur Laufzeit erst abfragen müssen, haben Indexed Views den Vorteil, dass die Ergebnisse tatsächlich, wie in einer normalen Tabelle, gespeichert sind.
Zur „alten“ SIFT Technik eigentlich gar kein so großer Unterschied, wie vielfach beschrieben wird, oder?
Genau wie die alte SIFT Technologie mit eigenen Tabellen zum Speichern der Ergebnisse gibt es jetzt Indexed Views, die berechnete Ergebnisse speichern. Genau wie bei der alten Technik müssen auch bei den Indexed Views die Ergebnisse mit jedem Insert, Update und Delete berechnet werden, damit die Ergebnisse für die nächste Abfrage bereitstehen.
Wo ist dann der große Vorteil der Index Views?
Der Vorteil liegt in der Art, wie die Summen gebildet werden. Nicht durch Trigger, die durch den Anwender (für uns übernimmt das der Navision Client) generiert werden sondern durch eine interne, genau für diesen Fall entwickelte Funktion des SQL Servers. Wir brauchen keine Trigger mehr, die, je nachdem ob man löscht, einfügt oder ändert entscheiden, welche meiner tausend Zeilen Quellcode muss ich den jetzt durchlaufen. Es kann auch nicht mehr passieren, dass sich Fehler in den automatisch generierten Trigger einschleichen (das soll es gegeben haben ;-), auch die Dynamics NAV Entwickler sind nur Menschen).
Diejenigen, die sich mit dem Thema VSIFT bereits beschäftigt haben werden jetzt sagen, da hat er aber etwas Wesentliches vergessen!
Natürlich ist ein weiterer Unterschied zwischen den SIFT Tabellen und VSIFTs, dass hier nicht für jede Detaillierungsebene, für jeden Bucket, Summen abgelegt werden sondern es gibt nur eine einzige Summierungsebene!
Daraus ergibt sich ein weiterer großer Vorteil von VSIFTs. Die Aktualisierung der VSIFTs ist wesentlich schneller, da weniger Aktionen auf dem SQL Server zum Aktualisieren notwendig sind. In unserem Beispiel oben, der Index mit 7 Buckets wären bei VSIFT nicht mehr 7 Aktionen zum Aktualisieren notwendig sondern nur eine. Das Aktualisieren der VSIFT ist also zumindest 7 mal schneller als die SIFT Trigger.
Hurra!! Hoch lebe VSIFT!
Oder wie Lars Lohndorf-Larsen einen Artikel betitelte
„Super Speedy SQL Server SIFTs“ kurz genannt SSSSSs
(Gruß an Lars)
Also nur noch bei allen Kunden NAV 5.0 SP1 installieren und alles wird gut???
Naja, wenn es doch nur so einfach wäre.
Warum ist dann nicht 5.1 die allgemeingültige Lösung für alle unsere Performance Probleme Herausforderungen.
Einige Branchen- und Individuallösungen bei denen wir Vergleiche zwischen NAV 4.x oder 5.0 und NAV 5.1 gemacht haben, haben gezeigt, dass von dieser enormen Geschwindigkeitssteigerung in der Praxis gar nicht so viel zu spüren ist. Bei anderen Tests waren die Ergebnisse mit 5.1 um ein vielfaches schneller als 4.x.
Die unterschiedlichen Erfahrungen macht man je nachdem wie optimiert die Datenbank bereits in Richtung SIFT ist. Eine bereits in 4.03 optimierte Datenbank wird gegenüber der 5.1 bei der Pflege der Summen nicht wesentlich langsamer sein als eine 5.1.
Das liegt daran, dass in einer optimierten Datenbank für einzelne Summenindexe nicht mehr alle Buckets auf dem SQL Server gepflegt werden sondern nur noch die wirklich notwendigen. Genau darin bestand die bisherige Optimierung von SIFT Triggern und Tabellen. Alle unnötigen SIFT Indexe und SIFT Buckets deaktivieren um die Last auf dem SQL Server so gering wie möglich zu halten. Hierzu gibt es in den Indexeigenschaften die Propertys „MaintainSIFTIndex“ und „SIFTLevels“.
Diejenigen, die meine Workshops und Vorträge besucht haben, kennen meine „Predigt“ dazu
und auch das
KeyInformationTool
In der „optimalen“ NAV 4.03 Datenbank gibt es also nur noch die Summen, die auch wirklich benötig werden. Wir pflegen für unseren Summenindex im obigen Beispiel also keine 7 Buckets sondern nur den einen, den wir benötigen(evtl. auch die zwei die wir benötigen).
z.B. den Bucket „Item No.“ um schnell an unsere Lagerbestände pro Artikel für unsere Übersichten zu kommen und
den Bucket „Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Day“ um alle detaillierteren Summen schnell berechnen zu können.
(Das ist wirklich nur ein Beispiel nicht unbedingt die richtige Lösung für diesen Summenindex).
So wird der Vorteil der VSIFTs schon ein wenig geringer
oder sagen wir lieber, so hatte sich unsere 4.x Datenbank in der Performance schon angenähert.
Eine bisher noch gar nicht optimierte oder wenig optimierte NAV Datenbank vor der Version 5.01 wird durch den Einsatz des neuen Servicepacks sicherlich merklich schneller beim Schreiben in Tabellen mit Summenindexen werden.
Wo Vorteile, da aber meist auch ein Preis, den wir dafür bezahlen.
Die Nachteile der VSIFTs stecken im Detail. Und zwar im Detaillierungsgrad der Summen, die im Indexed View gebildet werden.
Bei VSIFTs gibt es nur einen Detaillierungsgrad (siehe oben unter Vorteile
). Dieser Detaillierungsgrad entspricht immer dem detailliertestem Bucket der alten SIFT Tabelle.
In unserem Beispiel gibt es in der Indexed View nur die Summen auf der Ebene
„Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Day“.
Dieser Level bedeutet aber, wenn ich jetzt den aktuellen Lagerbestand für den Artikel „70000“ (da ist sie, unsere Seitenwand) berechnen will, dann muss ich aus der IndexedView jeden Tag in jedem Lagerort für jede Variante summieren. Das kann heißen, dass ich hier aus tausenden Einträgen in meiner Indexed View die Summe bilde.
Wenn ich an jedem 2. Arbeitstag in den letzten 5 Jahren aus allen 6 Lagerorten Seitenwände in 4 Varianten verkauft habe sind das immerhin ca.
100 Tage * 5 Jahre * 6 Lagerorte * 4 Varianten = 12.000 Detailsummen
die der SQL Server summieren muss, zu einer Gesamtsumme für den NAV Client.
In meiner „optimalen“ NAV 4.03 Datenbank brauche ich nur den einen gebildeten Summendatensatz auf der Ebene „Item No.“ zu lesen. Hier verursachen die VSIFT wesentlich mehr lesende Zugriffe als SIFT Tabellen und sind somit auch langsamer.
Ich will damit nicht sagen VSIFT wären keine tolle Erfindung. Ich will nur dazu anstoßen sich Gedanken zu machen diese neue sehr gute Technik optimal einzusetzen.
Warum nicht die VSIFTs mit ein paar Anpassungen in unseren NAV Objekten noch besser bei ihrer Arbeit unterstützen?
In meinem Beispiel werde ich für die optimalen VSIFT Zugriffe beim Summieren einen neuen Schlüssel in NAV anlegen, nur mit dem Feld „Item No.“ und mit den benötigten SumIndexFields. Bei diesem Schlüssel werde ich das Property MaintainSQLIndex auf Nein stellen und das Property MaintainSIFTindex auf Ja.
Als Ergebnis bekomme ich auf dem SQL Server die gespeicherten Summen pro „Item No.“ in eine neue IndexedView, die abgefragt werden kann. Ich bekomme aber keinen neuen Index, den ich auch nicht benötige. So kann ich auch bei den VSIFTs mit nur einem Lesezugriff meinen Lagerbestand pro Artikel ermitteln.
Mein Fazit zu den VSIFTs:
Microsoft hat uns die Möglichkeit gegeben, lasst sie uns nutzen.
Viel Spaß mit VSIFTs