[Gelöst] Report 111: Performanceprobleme bei SQL

[Natalie]

Man nehme eine Datenbank mit 36.560 Debitoren und 202 Debitorenposten und rufe den Report 111 "Debitor - Top 10 Liste".

In der Native-Version benötigt der Report eine halbe Minute (nur zum Vergleich),
auf SQL deutlich länger (bei uns lief er noch nie durch, laut unserem Kunden "mehrere Stunden".

Wenn man die Fortschrittsanzeige beobachtet, sind auch schon hier Geschwindigkeitsunterschiede zu erkennen.

Nun findet in diesem Report nicht allzu viel statt. Das, was vermutlich hier den Performanceunterschied macht, sind die verwendeten FlowFields "Sales (LCY)" und "Balance (LCY)".

Was kann ich am SQL-Server schrauben, damit ich hier akzeptable Zeiten erreiche?

Zuletzt geändert von Natalie am 14. Februar 2008 11:43, insgesamt 1-mal geändert.

[...]
Was kann ich am SQL-Server schrauben, damit ich hier akzeptable Zeiten erreiche?

Den Report ein paar Mal laufen lassen, sodaß sich der SQL-Server selbst optimiert.

Markus

In Navision 5 wurde Maintain Sift Index für den Key "Document Type,Customer No.,Posting Date,Currency Code" in der Tabelle 21 deaktiviert, das führt unter anderem dazu, dass der Report Verkaufsübersicht elendig lange braucht. nach reaktivierung dieses Häkchens läuft der Report mehr als 10 mal so schnell.
Da die Top10 Liste auf den gleichen Flowfields basiert, sollte der Trick auch da greifen. Hier aber nur die wirklich benötigten Sift-Level aktivieren, sonst kostet das zuviel performance beim buchen.

Den Report ein paar Mal laufen lassen, sodaß sich der SQL-Server selbst optimiert.

Leider ist dieses "Zutrauen" in die "selbstheilenden Kräfte" von SQL Server nicht ganz realistisch ...

Um so einen Report schneller zu machen, stehen folgende Optionen zur Verfügung:

1. SIFT Optimierung
Reduzieren SIFT Buckets
"Covering Index" auf verbleibende SIFT Tabellen

2. Clustered Index Optimierung
I.d.R. ist der CI der "Customer Ledger Entry" Tabelle sub-optimal ("Entry No."). Besser ist u.U. die Tabelle nach "Customer No.", "Posting Date" zu clustern.

3. Korrekte Indizierung
Für die Abfragen im Bericht müssen die erforderlichen Indexe zur Verfügung stehen.

Sollte helfen ... !?

Beide Tipps haben (angeblich) nicht geholfen (habs nicht selbst überprüft).
Ob überhaupt eine Verbesserung eingetreten ist, kann ich selbst leider nicht beurteilen.
Ich sehe nur, dass die SQL-Version noch meilenweit von der Performance der Native-Version entfernt ist

Hi stryk,
irgendwie lese ich deinen Beitrag erst jetzt ...?!

Ich habe keinerlei nennenswerte Erfahrung zum Thema SQL; könntest du mir sagen, was ich wie im SQL Server Management Studio bzw. in Navision machen muss ...?
Das wäre super ...

Hi!

OK, hier im einzelnen - fast alles kann via NAV bewerkstelligt werden:

1) SIFT Optimierung
a) Reduzierung SIFT Buckets:

Bei allen SIFT Indexen der Postentabellen sollen NUR die notwendigen "Buckets" aktiviert sein. Wenn man nich genau weiß, auf welche Aggregationsstufen (Buckets) zugegriffen wird, dann gilt die Daumenregel "alle deaktivieren, außer den Vorletzten".

Z.B. gibt es die Buckets 0 - 9, dann sind 0 und 9 ohnehin deaktiviert. Nun werden die Level 1 bis 7 ebenfalls deaktiviert; bleibt nur noch Level 8 (= erste Stufe der Aggregation). Alle höheren Aggregate können aus 8 generiert werden. Dauert dieses "Summieren der Summen" zu lange (im SQL Profiler Abfragen mit Reads > 1000 auf SIFT Tabellen), dann kann der nächste Level dazugenommen werden (im Beispiel die 7).

b) Covering Index

Das Lesen der SIFT Datensätze - und vor allem das "Summieren der Summen" - wird durch einen solchen Index erheblich beschleunigt. Hier handelt es sich um einen Index, der alle Felder der (SIFT-)Tabelle beinhaltet.
Im Management Studio z.B. auf T21 "Customer Ledger Entry"; Key 2 (= SIFT Index 0)

Code:

use [MeineNAVdb]
go
create index "MeinIndex_01" on "Mandant$21$0"
("bucket", "f3", "f4", "f11", "s18", "s19", "s20")


2. Clustered Index Optimierung
Nun, der optimimale CI hängt von der Abfragestrategie in NAV ab. Im Falle von T21 "Customer Ledger Entry" haben meine Erfahrungen gezeigt, daß ein CI nach "Customer No." und "Posting Date" wesentlich performanter ist, als der Standard "Entry No.".
Dazu im Object Designer beim Key 2

Customer No.,Posting Date,Currency Code

die Eigenschaft "SQL Index" auf

Customer No.,Posting Date

setzen und "Clustered" auf TRUE. Speichern & Fertig.
Achtung: Alle Datensätze werden dabei physikalisch neu angeordnet, was je nach Anzahl ein ganzes Weilchen dauern kann! Die Tabelle ist in dieser Zeit nicht nutzbar.

3) Korrekte Indizierung
Hierzu muss der Bericht via SQL Profiler überprüft werden. Tauchen Abfragen auf die mit hohen "Read" Zahlen (>1000) auffallen, dann ist dies meistens auf "Index Scans" zurückzuführen; was wiederum auf fehlende/schlechte Indexe schließen lässt.
Was jedoch ein schlechter bzw. guter Index ist, hängt vom Einzelfall ab ... kann hier schlecht 'ne Empfehlung eben ...
Allgemein gilt, daß Felder, die häufig in Abfragen/Filtern benutz werden, auch indiziert sein sollten ... abhängig von deren Selektivität ...

Ich hoffe das hilft Dir ein wenig weiter!

Gruß,
Jörg

Hi Jörg,

erst einmal ein fettes Dankeschön; bevor ich es ausprobiere hier noch mal im Einzelnen:

1) SIFT Optimierung
a) Reduzierung SIFT Buckets:
b) Covering Index

Wo und wie deaktiviere ich nun Buckets? Ich weiß nicht einmal, was das ist und wie die arbeiten ...

Die anderen Punkte schaffe ich (hoffentlich) auch so - wie gesagt, keine Ahnung davon!

Hallo Natalie,

die SIFT-Buckets bzw. die SIFT-Level kannst Du in der SQL-Option in den Keys verändern.

Viele Grüße
Björn

Hallo Natalie,

die SIFT-Buckets bzw. die SIFT-Level kannst Du in der SQL-Option in den Keys verändern.

Viele Grüße
Björn

Hm -
befinde mich gerade auf den Schlüsseln der Tabelle 21 und habe das Feld SIFTLevels eingeblendet.
Für den o.g. Schlüssel ist das Feld leer. Gefüllt ist es nur für den zweiten Schlüssel. Wie krieg ich dieses Feld nun gefüllt? Der Assist-Edit-Button ist ohne Funktion. Kopiere ich mir den Inhalt des gefüllten Feldes, gibts beim Einfügen eine Fehlermeldung.

Im Management Studio z.B. auf T21 "Customer Ledger Entry"; Key 2 (= SIFT Index 0)

Code:

use [MeineNAVdb]
go
create index "MeinIndex_01" on "Mandant$21$0"
("bucket", "f3", "f4", "f11", "s18", "s19", "s20")


Krieg ich nicht hin
Was muss ich wo klicken? Sorry!!

2. Clustered Index Optimierung
Dazu im Object Designer beim Key 2

Customer No.,Posting Date,Currency Code

die Eigenschaft "SQL Index" auf

Customer No.,Posting Date

setzen und "Clustered" auf TRUE. Speichern & Fertig.

Der bisherige SQLIndex lautet:
Customer No.,Posting Date,Entry No.
Clustered ist bereits TRUE.
Lösche ich nun den Bezug zu Entry No. und versuche zu speichern, kommt:

Ein gruppierter Schlüssel kann nur einmal in einer Tabelle auftreten.

Tabelle: Cust. Ledger Entry

Das ist schon i.O. so!

Die "Standard" T21 hat nur einen SIFT Index, nämlich den zweiten Key (Customer No, Posting Date, Currency Code). Hier den "AssistButton" im Feld "SIFTLevels" anklicken und dann die unnötigen "Buckets" "disablen". Mit OK schließen, speichern & fertig.

Ich vergas zu erwähnen, daß auch diese Aktion zeitraubend sein kann, da hier nicht nur die deaktivierten "Buckets" gelöscht werden, sondern der verbleibende neu aufgebaut wird ...

Ups, da war ich nicht schnell genug

Nun, wenn der CI schon geändert wurde, dann ist das schon OK so, wenn auch die "Entry No." am Ende nicht wirklich benötogt wird ...

Was den "Covering Index" angeht:

Im "Management Studio" einfach "Neue Abfrage" (links oben) wählen, dann dort das TSQL eingeben:

Code:

use [MeineNAVdb]
go
create index "MeinIndex_01" on "Mandant$21$0"
("bucket", "f3", "f4", "f11", "s18", "s19", "s20")


Wichtig ist dabei, dass "Mandant" durch den richtigen Mandantennamen ersetzt wird. Bei meheren M. muss das ganze je M. wiederholt werden.

Die SIFT Tabellen findest Du im MgmtSt immer "oben", also alle ...$123$0 etc. Tabellen. Hier kannst Du den Index auch manuell anlegen, unter "Indexe", dann Rechtsklick und "Neu" ...

Die "Standard" T21 hat nur einen SIFT Index, nämlich den zweiten Key (Customer No, Posting Date, Currency Code). Hier den "AssistButton" im Feld "SIFTLevels" anklicken und dann die unnötigen "Buckets" "disablen". Mit OK schließen, speichern & fertig.

Bei und sind momentan nur Buckets 1-3 aktiviert (ich weiß nicht, durch wen)- kann man das so lassen?

Was den "Covering Index" angeht:

Im "Management Studio" einfach "Neue Abfrage" (links oben) wählen, dann dort das TSQL eingeben:

Code:

use [MeineNAVdb]
go
create index "MeinIndex_01" on "Mandant$21$0"
("bucket", "f3", "f4", "f11", "s18", "s19", "s20")


Bekomme folgende Fehlermeldung beim Ausführen:
Der Spaltenname 'f11' ist in der Zieltabelle oder Sicht nicht vorhanden.

Hast du eine Idee, was ich machen kann/soll?

Bei und sind momentan nur Buckets 1-3 aktiviert (ich weiß nicht, durch wen)- kann man das so lassen?

Mit dem Key Information Tool aus dem Database Resource Kit kannst Du das validieren.

Der Spaltenname 'f11' ist in der Zieltabelle oder Sicht nicht vorhanden.

Ist den f11 (Feldnr. 11) in der Tabelle Mandant$21$0 enthalten bzw. ist in der Tabelle 21 ein Key definiert, der Feld 11 enthält?

Ist den f11 (Feldnr. 11) in der Tabelle Mandant$21$0 enthalten bzw. ist in der Tabelle 21 ein Key definiert, der Feld 11 enthält?

Ah, da finde ich es also
Nun, bei uns gibt es nur bucket, f3, f4, s18, s19, s20.
Spricht was dagegen, wenn ich das SQL-Statement entsprechend kürze?

Wenn Feld 11 nicht vorkommnt bzw. genutzt wird, nichts.

Bei und sind momentan nur Buckets 1-3 aktiviert (ich weiß nicht, durch wen)- kann man das so lassen?

Nun, grundsätzlich hängt das "Optimum" der aktivierten Buckets von der Abfragestartegie der NAV Lösung ab, d.h. also welche Aggregate hauptsächlich abgefragt werden.
1-3 wären im Standard:

1 Customer No.
2 Customer No., Posting Date::Year
3 Customer No., Posting Date::Month

Das sind schon recht hohe Aggregate ...
Dies setzt weiterhin voraus, daß niemals auf "Day" Ebene abgefragt wird (ziemlich unwahrscheinlich); ebenso, daß nie auf "Currency Code" gefiltert wird (auch eher unwahrscheinlich, außer die entsprechenden C/AL Code-Strecken wurden geändert). Ansonsten muss hier stets die Postentabelle selbst gelesen werden.

Meine Empfehlung ist hier, alle Buckets zu deaktivieren, außer Nr. 5

5 Customer No., Posting Date::Day, Currency Code

Alle höheren Buckets können daraus berechnet werden. Wird z.B. eine Verdichtung auf Monatsebene (Bucket 3) benötigt, dann wird NAV eben alle relevanten Datensätze aus Bucket 5 summieren; also z.B. 30 Tagessätze lesen.

Nun, bei uns gibt es nur bucket, f3, f4, s18, s19, s20.
Spricht was dagegen, wenn ich das SQL-Statement entsprechend kürze?

Nö, das ist schon in Ordnung. Ein "Covering Index" ist per Definition ein Index, der alle Felder, die in einer Abfrage enthalten sind, beinhaltet.

Die "Abfragen" um die es geht lesen/summieren die s-Felder und Filtern dabei auf "bucket" und die f-Felder.

D.h. in Deinem Fall sollte der Index also die Felder bucket, f3, f4, s18, s19, s20 enthalten.

Ich fasse mal zusammen, was ich bis jetzt geändert habe und was es gebracht hat:

NAV
-----
T21, zweiter Key: nur noch SIFTLevel Bucket 5 aktiv.

SQL
-----
Neuen SQL-Befehl zu CoverinIndex wie von Jörg vorgeschlagen (ohne f11, weil ich den nicht habe). Wurde auch erfolgreich ausgeführt, nur: Wo finde ich ihn nun? Kann man sich den noch irgendwo ansehen?
Unter Indizes finde ich ihn nicht.

Der Report läuft jetzt 3-4 Minuten, das ist 8x länger als in native.
Nun wird bei der Berechnung der FlowFields noch die Tabelle "Detailed Cust. Ledg. Entry" (T379) durchlaufen.
Kann man da noch was optimieren?

Nun wird bei der Berechnung der FlowFields noch die Tabelle "Detailed Cust. Ledg. Entry" (T379) durchlaufen.
Kann man da noch was optimieren?

Ja, kann man. Kannst Du mit dem SQL Profiler die Abfrage auf T379 "herausfischen"? Sollte irgendein "SELECT SUM(...) FROM "...$Detailed Cust_Ledg_Entry" ... sein.

Dass T379 gelesen wird, deutet darauf hin, dass Felder summiert werden, die so nicht in einem SIFT Index aggregiert werden.
Nun kann man entweder den fehlenden SIFT Index anlagen - wenn man weiß welches "Summenfeld" hier die Probleme macht - oder man legt einen Index an (CREATE INDEX etc.) in T379 der die Felder der WHERE Clause, sowie die "zu summierenden Felder" enthält (= "Covering Index" auf Postentabelle für diese spezielle Abfrage).

Die zweite Option ist eigentlich die bessere; da zusätzliche SIFT Indexe deutlich die "Schreib"-Performance drücken. Das Aktualieren eines neuen Indexes ist da wesentlich schneller!

Habe in der Zwischenzeit nicht mehr weiter die native Variante testen können, ABER:

Für NAV 4.0 gibt es als MS-Download das so genannte Report Pack. Dieser beinhaltet ca. 20 Standardberichte aus Navision, darunter auch die Debitoren Top 10, programmiert für SQL Reporting Services (RS).

Diesen RS-Bericht habe ich also auf meine SQL-Kundendatenbank aufgesetzt - der Report spuckt das Ergebnis SOFORT aus! Also zum NAV-Report überhaupt kein Vergleich - dringend zu empfehlen zumal der RS-Bericht auch noch mit einem Diagramm daher kommt.