Performance beim Ändern der Sortierung

Hallo zusammen,

vielleicht hat jemand von euch eine Idee, wie folgendes Verhalten zu stande kommt:

Es geht um eine Kontaktliste, die der Benutzer durch Auswahl diverser Schlüssel umsortieren kann.
Nun verhält sich Navision mal so, dass die Umsortierung 1-2 Sekunden dauert, andermal dauert es 3-5 Minuten bis die Liste dargestellt wird.
Provozieren kann man die "lange Ausführungszeit" nicht wirklich, sie tritt gelegentlich bei unterschiedlichen Benutzern auf.

Ein paar Rahmendetails:

- es spielt keine Rolle wieviele User in der DB angemeldet sind, das Verhalten tritt auch bei nur einem User auf
- es spielt "scheinbar" auch keine Rolle, auf welchem Datensatz man steht (Anfang der Liste, Ende der Liste...)
- die Kontakttabelle umfasst ca. 2 Millionen Datensätze (ca. 2/3 Unternehmenskontakte, 1/3 Personenkontakte)
- Navision Version: 4.0 SP3, Datenbank auf SQL-Server 2005
- die Liste wird vordefiniert gestartet, allerdings nicht über Properties eingestellt, sondern über folgenden Code:

Code:

Form - OnOpenForm()
SETCURRENTKEY(Type,"Post Code");
FILTERGROUP(10);
SETRANGE(Type,Type::Company);
FILTERGROUP(0);


Der Benutzer wählt den Schlüssel: "Type, Name, Name 2, Name 3, Name 4". Filter is keiner gesetzt, ausser dem auf Type::Company.
Um der Sache auf den Grund zu gehen, habe ich den SQL-Profiler mal mitlaufen lassen, und folgendes Statement wies die lange Ausführungsdauer auf:

Code:

declare @p1 int
set @p1=180151301
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=1
exec sp_cursoropen @p1 output,N'SELECT  *,DATALENGTH("Picture") FROM "NAVDB"."dbo"."Company$Contact" WITH (READUNCOMMITTED, INDEX("$8"))  WHERE (("Type"=@P1) AND ("Type"=@P2)) AND  "Type"=@P3 AND "Name">@P4 ORDER BY "Type","Name","Name 2","Name 3","Name 4","No_" ',@p3 output,@p4 output,@p5 output,N'@P1 int,@P2 int,@P3 int,@P4 varchar(50)',0,0,0,'Willi Wichtig'
select @p1, @p3, @p4, @p5


Für mich sieht das SQL-Statement nicht ungewöhnlich aus (Index $8 ist der angegebene Schlüssel der ORDER BY Anweisung).
Woran könnt es also liegen, dass es manchmal so lange dauert, andermal aber sofort umsortiert?

mfg
Phae

Hallo Phae,

sind die Schlüssel auch auf dem SQL-Server aktiviert (Stichwort MaintainSQLIndex)?

Hallo Phae,

NAV wird immer dann langsam beim Umsortieren, wenn du nach einem Schlüssel sortieren willst, der nicht die Felder enthält, auf denen gefiltert wurde, in deinem Fall 'Post Code'.

Gruß, Fiddi

Hallo,

@Patrick:
MaintainSQLIndex = Yes. Bei allen Schlüsseln.

@Fiddy:
Nach dem "Post Code" wird gar nicht gefiltert. Das ist nur die vorgegebene Sortierung, wenn das Fenster geöffnet wird. Wie ich schon schrieb, ist beim Setzen des neuen Schlüssels kein Feld gefiltert (außer dem vom Code vorgegebenen Type - was aber in allen Schlüsseln das erste Feld ist).

mfg
Phae

wie "wählt" denn der User den Schlüssel. Ist das programmiert oder wird die Standardfunktion (Umschalten F8) genutzt?

Ganz normal über die Standardfunktion.

Code:

...'SELECT  *,DATALENGTH("Picture") FROM "NAVDB"."dbo"."Company$Contact" WITH (READUNCOMMITTED, INDEX("$8"))  WHERE (("Type"=@P1) AND ("Type"=@P2)) AND  "Type"=@P3 AND "Name">@P4 ORDER BY "Type","Name","Name 2","Name 3","Name 4","No_" ',@p3 output,@p4 output,@p5 output,N'@P1 int,@P2 int,@P3 int,@P4 varchar(50)',0,0,0,'Willi Wichtig'
select @p1, @p3, @p4, @p5

Ich habe da mal eine Zwischenfrage:
Was bedeutet das '@' (at) bzw. ("Type"=@P1) nach der Where Klausel und das '@p1, @p3, @p4, @p5' nach dem select?

Hallo Phae,

zunächst hast du natürlich recht, da ist kein Filter, aber auch ein aufrufendes Programm kann schon Filter setzen. Mit welchen Filtern wird denn das Form aufgerufen, (kannst du über den Tabellenfilter in deinem Form herausfinden)?

@mikka,

die mit @ bezeichneten Namen sind Parameter/Variablen in der SQL-Abfrage.


Gruß, Fiddi

@Mikka:
Das sind Variablen im Transact SQL, eine selbstdefinierte Variable beginnt mit einem @.

@Fiddy:
Es sind definitiv keine Filter aktiv. Nur Type = Type::Company.
Ansonsten würden die Abgrenzungen auch im Profilter-Log ausgeweisen sein!

mfg
Phae

wieso ist da dann eine Abfrage auf 'Name > @P4' drin

Gruß, Fiddi

Der Wert der Variablen @P4 enthält die Information aus dem aktuellen Datensatz, also wo der Cursor zum Zeitpunkt der Schlüsseländerung steht.
Warum Navision das SQL-Statement entsprechend so baut, entzieht sich meiner Kenntnis.
Allerdings kann es nicht so unperformant sein, weil das Feld Name eh das zweite Feld im Schlüssel ist (meine Meinung).

mfg
Phae

Hast du schon einen Clustered Index in deiner NAV-Version, und wenn ja, worauf ist der gesetzt?
Ist die Sortierungsänderung auch beim zweiten Versuch genauso langsam wie beim ersten Mal (zwei mal Schlüssel wechseln)?

Gruß, Fiddi

Das Property Clustered gibt es schon, ist allerdings nicht gesetzt. Es könnte aber sowieso nur ein Schlüssel pro Tabelle clustered=yes sein.

Das Verhalten beim zweiten oder dritten Ändern der Sortierreihenfolge kann ebenso noch so sein, es kann aber auch schon wieder schneller erfolgen. Die User berichten "das sich Navision dann irgendwann wieder fängt" und alles wieder normal läuft.

mfg
Phae

Clustered sollte man normalerweise auf dem Primärschlüssel setzen. Andererseits kann es sinnvoll sein, den Clustered Index auf einen anderen Index zu legen, der häufiger verwendet wird.
Wieviel Speicher hat dein SQL-Server denn zur Verfügung?
Da dein Schlüssel sehr groß ist (wegen der Namen) ist die Suche im Schlüsselbaum der Tabelle sehr ineffizient und Speicher fressend (wenige Schlüssel pro Page).
U.u. macht es Sinn einen zweiten Schlüssel zu definieren, der z.B. nur Name evtl. noch "Name 2" als Schlüssel enthält, diesen vom SQL-Server pflegen zu lassen, und deinen gewünschten als NAV-Schlüssel ohne SQL-Pflege laufen zu lassen. Das sollte für 90% der Sortierung und Filterung ausreichen, und den Rest erledigt der SQL-Server im Speicher.

Du solltest auf dem SQL-Server auch noch prüfen, ob und welche Schlüssel der SQL-Server für diesen Zugriff verwendet.

Sind die Statistiken der Datenbank aktuell? evtl. mal sp_updatestats ausführen MS-Technet

Gruß, Fiddi

Hallo,

ich hab mir gerade mal die Schlüssel auf dem SQL Server direkt angeschaut, interessanterweise steht dort der Primärschlüssel auf "gruppiert" (was doch das "clustered" ist, oder?). Im Navision wird im Property allerdings "<No>" angezeigt.

Umsetzen würd ich das trotzdem nicht, weil der Primärschlüssel schon der meistgenutzte ist.

Die Anzahl Felder des Schlüssel zu reduzieren könnt ich mal ausprobieren, da im Normalfall nur Name und Name 2 gefüllt sind, sollte das vielleicht ausreichen diese beiden Felder im Index zu haben.

Im Server sind 8 GB RAM drin, wovon im Durchschnitt aber auch nur 2-3 GB genutzt werden (belegt sind)... sollte somit nicht der Engpass sein.

...
Du solltest auf dem SQL-Server auch noch prüfen, ob und welche Schlüssel der SQL-Server für diesen Zugriff verwendet.
...

Ich dachte das genau sagt das Profiler-Log aus?

...SELECT *,DATALENGTH("Picture") FROM "NAVDB"."dbo"."Company$Contact" WITH (READUNCOMMITTED, INDEX("$8")) WHERE ...

Oder täusch ich mich da? Wenn ja wie find ich sonst raus, welchen Schlüssel der SQL Server nimmt?

...
Sind die Statistiken der Datenbank aktuell?
...

Da alles auf "Statistik automatisch neu berechnen" steht, war ich von ausgegangen. Hab mir aber mal speziell für den Schlüssel $8 über DBCC SHOW_STATISTICS anzeigen lassen, da steht allerdings in der Spalte Updated der 08.02.09 drin... schon ne Weile her...
Wobei mit dem Thema Statistiken kenn ich mich auch nicht so gut aus, deshalb die Frage mal... welchen Einfluss haben die auf die Umsortierung?

mfg
Phae