[gelöst] SQL Server und Sekundärschlüssel

[christiand]

Hallo zusammen!

Wir haben vor kurzem einen Kunden von Nativer Datenbank auf SQL-Server 2008 migriert, eigentlich ohne größere Probleme, doch ein seltsames Phenomen kann ich mir nicht erklären:

Gehe ich beispielsweise in die Angebotsliste (Form 45 Verkaufsübersicht) und scrolle durch die Zeilen, ist alles schnell und es gibt keine Probleme.
Ändere ich die Sortierreihenfolge auf einen der Sekundärschlüssel wie z.B. "Belegart","Verk. an Deb.-Nr.","Nr." ist alles gähnend langsam, an durchscrollen ist garnicht mehr zu denken.
Setze ich dann aber einen eigentlich sinnlosen Filter auf ein Schlüsselfeld wie z.B. Verk. an Deb.-Nr. <> '' fluppt es plötzlich wieder
Durch diesen Filter fällt keine einzige Zeile weg, aber er veranlasst den SQL-Server scheinbar anders zu handeln.

Was läuft hier schief??

LG Christian

Zuletzt geändert von christiand am 10. Mai 2012 16:08, insgesamt 1-mal geändert.

Hast du einmal geschaut welche Schlüssel er auf SQL-Seite in beiden Fällen verwendet?

ich muss gestehen, ich weiss nicht so genau wie ich das heraus finde...

Ist der fragliche Sekundärschlüssel als Schlüssel oder nur als Sortierkriterium angelegt?
Mit dem SQL-Server gibt es ja diese Unterscheidungsmöglichkeit.

Aus dem Client raus müsste es über den Client-Monitor funktionieren.
Ich persönlich nehme den SQL-Profiler dafür, da ich hier eigene Tools zum auswerten verwenden kann.

Also ich denke der Clientmonitor zeigt mir nur an, welchen Schlüssel NAV verwenden würde, für was sich der SQL-Server dann aber entscheidet kann man da doch nicht sehen oder? Im SQL-Profiler kann ich zwar den Select finden, aber auch nicht den Executionplan... irgendwie seh ich hier auch nicht wie der SQL-Server letztendlich mit meinem Select umgegangen ist.
@RaiNav Mir war zwar bekannt, dass ein Navision Sekundärschlüssel als Index im SQL Server erzeugt wird und dass dieser Index vom SQL-Server nicht für die Sortierung genommen wird, doch dass man das irgendwo wählen kann wusste ich nicht, wo kann man das denn einstellen?

Da bin ich mir nicht ganz sicher, aber überprüfe im Objektdesigner der Tabelle in den Keys den Parameter "MaintainSqlIndex".

Vielleicht kann ja auch Danjo etwas dazu sagen?

Hi,

was für ein Zufall - genau um DAS ging es in meinem Vortrag auf der "MSDynamics.de Konferenz 2012" in Kassel.

Nun der Folienfilm & CO., den Du im dortigen Thread findes alleine wird Dir nicht wirklich helfen, deshalb hier noch mal eine Kurzfassung:

1. Mit dem SQL Profiler findest Du heraus welche Abfragen exact betroffen sind
2. Im Management Studio kann man diese Problemabfrage dann "troubleshooten" - so detailliert wie man es vertragen kann
3. Leider kann es viele Gründe geben, warum eine Abfrage "zickt"; im wesentlichen läuft es aber auf Indizierung und Programmierung hinaus
4. In den meisten Fällen hilft es den richtigen Index zu erstellen - das kann entweder im SSMS geschehen oder direkt in NAV

Wenn Du im Profiler die Problemabfrage gefunden hast, dann könntest Du sie hier posten, so dass wir/ich Dir hier mal einen Index-Vorschlag machen können.
Ansonsten ist das aber ein Theme mit dem man sich etwas ausführlicher auseinandersetzen müsste ...

Hi Jörg,

vielen Dank für deine Antwort, ich werd mal sehen wie weit ich da komme und ob ich mit dem Profiler noch etwas herausfinden kann.
Was mich aber echt wundert ist dass die Abfrage mit einem Filter in der NAV-Form, der die Datenmenge um keine einzige Zeile einschränkt, plötzlich rennt...

Irgendwie scheint es aber nicht an dem eigentlichen Select auf den Sales Header zu liegen, denn wenn ich die beiden Abfragen (einmal mit und einmal ohne Filter) aus dem Profiler ins Management Studio kopiere, laufen Sie exakt gleich schnell.

Aber jetzt erst mal ...profiling...

LG Christian

Also ich habe jetzt mal nochmal mit dem Profiler die Angebotsliste geöffnet, einmal mit gesetztem Filter, einmal ohne.
Die einzige Zeile die ohne den Filter mit 6348 ms Duration zu sehen ist, ist wohl ein declare eines Cursors, und da bin ich dann mit meinem Latein am Ende, das sagt mir nicht mehr viel.

Zur Info vorweg, ich habe statt des Primärschlüssels (Document Type, No.) in NAV meinen Schlüssel (Document Type, sortint) gewählt. Sortint ist ein Integer Feld, was ich immer beim neu Anlegen eines Salesheaders mit dem Nummerischen Teil aus der Angebots bzw. Auftragsnummer befülle. Es enthält also einfach einen Integer Wert nach dem ich innerhalb einer Belegart sortieren möchte.

Hier der Cursor:

Code:

declare @p1 int
set @p1=1073742101
declare @p2 int
set @p2=180150309
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=8
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 int,@P2 int,@P3 int',N'SELECT  "timestamp","Document Type","Sell-to Customer No_","Bill-to Customer No_","Bill-to Name","Bill-to Name 2","Bill-to Address","Bill-to Address 2","Bill-to City","Bill-to Contact","Your Reference","Ship-to Code","Ship-to Name","Ship-to Name 2","Ship-to Address","Ship-to Address 2","Ship-to City","Ship-to Contact","Order Date","Posting Date","Shipment Date","Posting Description","Payment Terms Code","Due Date","Payment Discount %","Pmt_ Discount Date","Shipment Method Code","Location Code","Shortcut Dimension 1 Code","Shortcut Dimension 2 Code","Customer Posting Group","Currency Code","Currency Factor","Customer Price Group","Prices Including VAT","Invoice Disc_ Code","Customer Disc_ Group","Language Code","Salesperson Code","Order Class","No_ Printed","On Hold","Applies-to Doc_ Type","Applies-to Doc_ No_","Bal_ Account No_","Ship","Invoice","Shipping No_","Posting No_","Last Shipping No_","Last Posting No_","Prepayment No_","Last Prepayment No_","Prepmt_ Cr_ Memo No_","Last Prepmt_ Cr_ Memo No_","VAT Registration No_","Combine Shipments","Reason Code","Gen_ Bus_ Posting Group","EU 3-Party Trade","Transaction Type","Transport Method","VAT Country_Region Code","Sell-to Customer Name","Sell-to Customer Name 2","Sell-to Address","Sell-to Address 2","Sell-to City","Sell-to Contact","Bill-to Post Code","Bill-to County","Bill-to Country_Region Code","Sell-to Post Code","Sell-to County","Sell-to Country_Region Code","Ship-to Post Code","Ship-to County","Ship-to Country_Region Code","Bal_ Account Type","Exit Point","Correction","Document Date","External Document No_","Area","Transaction Specification","Payment Method Code","Shipping Agent Code","Package Tracking No_","No_ Series","Posting No_ Series","Shipping No_ Series","Tax Area Code","Tax Liable","VAT Bus_ Posting Group","Reserve","Applies-to ID","VAT Base Discount %","Status","Invoice Discount Calculation","Invoice Discount Value","Send IC Document","IC Status","Sell-to IC Partner Code","Bill-to IC Partner Code","IC Direction","Prepayment %","Prepayment No_ Series","Compress Prepayment","Prepayment Due Date","Prepmt_ Cr_ Memo No_ Series","Prepmt_ Posting Description","Prepmt_ Pmt_ Discount Date","Prepmt_ Payment Terms Code","Prepmt_ Payment Discount %","Quote No_","Doc_ No_ Occurrence","Campaign No_","Sell-to Customer Template Code","Sell-to Contact No_","Bill-to Contact No_","Bill-to Customer Template Code","Opportunity No_","Responsibility Center","Shipping Advice","Posting from Whse_ Ref_","Requested Delivery Date","Promised Delivery Date","Shipping Time","Outbound Whse_ Handling Time","Shipping Agent Service Code","Receive","Return Receipt No_","Return Receipt No_ Series","Last Return Receipt No_","Allow Line Disc_","Get Shipment Used","Signature","Assigned User ID","SalesPersonRabatt","SalesPersonProvision","ZusRabVorgabe","FromQuoteNO","Wahrscheinlichkeit","Entstandener Auftrag","Aktuelle Version","AufEndAngNÜber","Auftragsart","Betreff","Gewünschtes Lieferdatum bis","Sachbearbeiter","External Document No_2","Erstdurchlauf","druck_datum","Auftragsvol_ Netto n_ Rabatt","kw","kw_jahr","erstellt_von","Zusatzprojektnummer","External Document No_ 3","Wiederherstellung","ServiceAuftrag","ServiceartikelZeile","sortint","Lieferantennr","Variant Price List Code","Sales Order Type","Prices required","Status Control","ModelRelation is not necessary","Value Formula","Waiting for Prepayment","Prepayment Date of Receipt","Trade Margin Terms Document","Delivery Address Code","Billing Address Code","Sell-to Salutation","Bill-to Salutation","Ship-to Salutation","Advice Address Code","Advised","No_ Of Advice","Confirmation Addresscode","Assistant","Pre Delivery Note","Entry Date","Create from","Print Date","Language Code Print","Fixed Date","Calculate Delivery Date","Delivery Day Confirmed by","Delivery Time Agreement Code","Latest Delivery Date Agreement","Association Code","Association Line No_","Membership No_","Not Production","Tour Code","Sequence Number Tour Plan","Tour Plan No_","Check Tour","Posting Release","Parcel Code","Parcel Sorting","Return Order","Call Order","Posting","Mail Order","Small Parts Order","Sending Weight","Tour Sequence","Address Label printed","Delivery Date","Complete Order","Placement","Placement No_","Process No_","Commission Identifier","Return Order Process No_","Bonus allowed","Allow Order Entry","PUs created","PU is not necessary","Graphic Order","Graphic Plan No_","Graphic generated","Graphic SamRead","GOI Interface Type","GOI Blocked by","Check Rough Capacity","Sales Agent Type","Sales Agent Code","Customer Commission Group","Allow Commission","Relevant to Licence","EDI Group Code","Settlement Center Cust_ No_","Date Received","Time Received","BizTalk Request for Sales Qte_","BizTalk Sales Order","Date Sent","Time Sent","BizTalk Sales Quote","BizTalk Sales Order Cnfmn_","Customer Quote No_","Customer Order No_","BizTalk Document Sent","Kundencode","No_",DATALENGTH("Signature") FROM "Pfeuffer2009"."dbo"."Pfeuffer_GmbH$Sales Header" WHERE (("Document Type"=@P1)) AND  "Document Type"=@P2 AND "sortint"<@P3 ORDER BY "Document Type" DESC,"sortint" DESC,"No_" DESC OPTION (OPTIMIZE FOR UNKNOWN)',@p5 output,@p6 output,@p7 output,0,0,102640
select @p1, @p2, @p5, @p6, @p7


Ist in den "Keys" Einträgen der Tabelle auch das Feld SQLIndex leer? Das kann auch zu erheblichen Problemen führen.

Ich hab da mal probehalber nach Jorg Stryk's Buch die Schlüsselfelder reinkopiert, was ja dieses Unique kennzeichen und den angehängten PK entfernen sollte, wobei das meinem Verständnis nach eher die Schreibtransaktionen beschleunigen sollte, da ja eigentlich immer nur beim insert die Eindeutigkeit überprüft werden muss. Hat auch leider nix gebracht.
Ich muss auch nochmal dazu sagen, das Problem tritt nicht nur bei meinem Schlüssel auf, das Verhalten ist bei allen anderen Sekundärschlüsseln das gleiche.
Wähle ich den Schlüssel "Document Type", "Sell-To-Customer No." ist es langsam, filtere ich auf "Sell-To-Customer No." <> '' (wodurch wiederum so gut wie keine Zeile wegfällt, denn i.d.R. haben alle Angebotsköpfe auch einen Debitor) ist es plötzlich schnell...

Hmm, Statistiken ausgestellt und diese werden nicht aktualisiert?

Wir haben einen Wartungsplan eingerichtet mit UpdateStatistics... meinst du das?

Hast du schon mal alle Flow-Fields ausgeblendet, und dann den Test gemacht?

Gruß, Fiddi

Wenn also "sortint" ein hauptsächlich verwendets Abfragekriterium ist, dann sollte der "Key" in T36 ggf. so aussehen:

Key: "Document Type" ,"sortint" ,"No_"
SQLIndex: <leer>
MaintainSQLIndex: Yes
Clustered: TRUE (den Haken beim bisherigen "Clustered Index" entfernen)

Die Daten werden dann neu sortiert; das dauert eine Weile je nach Anzahl der Datensätze und kann super Last erzeugen - ist eoe Änderung für die lauen Abendstunden.
Ggf. kannst Du das auf einem Testsystem mal ausprobieren?

Ich hab mir sogar schonmal eine neue Form gemacht in der nur die Primärschlüsselfelder enthalten sind... gleiches Verhalten:
Sortierung nach PK -> schnell, Sortierung nach SK -> langsam, Sortierung nach SK und Filterung auf letztes SK Feld <> '' -> schnell.

Wenn mehr als 1 SK betroffen ist, kann man das Ändern des Clustered-Properties als Lösung eher ausschließen.

Wenn mehr als 1 SK betroffen ist, kann man das Ändern des Clustered-Properties als Lösung eher ausschließen.

Hä? Das verstehe ich leider gar nicht??

Also wenn es nach PK Sortierung schnell ist, dann liegt das unter anderem daran, dass im NAV Standard die daten nach PK "clustered" sind.
Wenn nun aber dieser "sortint" WICHTIG ist, dann macht das schon Sinn den quasi in den KEy mit einzubauen und die Daten danach zu "clustern", da dann die physikalische Sortierung der gewünschten Sortierung entspricht ...

naja, die Auftragsform ist ja beispielsweise nach wie vor nach dem jetzigen pk sortiert.
das Problem tritt ja immer auf sobald ich eine vom PK abweichende Sortierung auswähle.

Ich würde mich ja auch damit abfinden, dass das System nunmal eine Sortierung nach dem PK schneller bereitstellen kann als eine Sortierung nach einem SK, doch 6 Sekunden Wartezeit bei der Sortierung nach einem SK und die Tatsache dass das setzen eines eigentlich sinnlosen Filters plötzlich das Problem löst, lässt mich daran glauben dass man hier noch etwas machen kann...

Ich gehe mal davon aus, dass du normalerweise immer auf die Belegart filterst. Falls ja, setzte die Belegart mal den das Ende des Schlüssels, und schau mal was dann passiert.

Gruß, Fiddi

Ich gehe mal davon aus, dass du normalerweise immer auf die Belegart filterst. Falls ja, setzte die Belegart mal den das Ende des Schlüssels, und schau mal was dann passiert.

Gruß, Fiddi

Dann kannst Du es ganz weglassen, Sonst wäre der Schlüssel sortint, Doc Type, Doc Type, No

ok fiddi, werd ich mal ausprobieren.
Im Zweifelsfall bau ich einfach den Filter sortint <> 0 fest in den Aufruf der Angebotsübersicht ein. Aber manchmal da lässt es mich einfach nicht los, da will ich es verstehen

LG Christian

@JanGD wenn ich es in den SQLIndex eintrage, häng er doch den PK nichtmehr an dachte ich oder?

Ich gebs auf, das hat leider auch nichts gebracht. Ich werde jetzt einfach beim Aufruf der Angebotsliste auf sortint <>0 filtern, beim Aufruf der Auftragsliste nicht, somit laufen beide Sortierungen schnell.
Trotzdem vielen Dank für eure Hilfe, falls ich hier doch noch etwas herausfinde lasse ich es euch wissen.

Viele Grüße

Christian