SQL Befehle und temporäre Recordvariable

Hallo,

ich hätte da mal eine Frage bzgl. NAV auf SQL Datenbank und temporäre Recordvariablen.

Auf einer SQL Datenbank sollte man ja bevorzugt die entsprechenden optimierten Befehle, FINDFIRST, FINDLAST, FINDSET und ISEMPTY benutzen.
Wobei der ISEMPTY die schnellste Abfrage ist, zumindest hab ich das mal so gelernt.

Gilt das auch im Bezug auf temporäre Recordvariablen?
Oder sollte man da z.B. ISEMPTY eher nicht nutzen, sondern dann auf FINDFIRST ausweichen?

Gruß Rolf

Temporäre Records werden nicht vom SQL-Server verwaltet, sondern ausschließlich temporär in deinem Arbeitsspeicher abgelegt und dort abgefragt.
So gesehen glaube ich nicht, dass die unterschiedlichen Methoden (FINDFIRST vs. FIND('-') vs. FINDSET) Performanceunterschiede ausmachen können - Jörg Stryk möge mich gern korrigieren
Vorstellen kann ich mir aber, dass auch hier ein ISEMPTY weniger Daten transferieren muss als ein FIND(FIRST).

Es spricht sicher nichts dagegen, auch bei temp. Recordvariablen "den guten C/AL-Ton" zu wahren ...

Hallo Natalie,

danke für die schnelle Antwort.
Wie temp. Records intern funktionieren ist klar. Daher kommen ja auch immer die vielen schönen Files im User-Temp.

Mir gehts da hauptsächlich im die Performance und der Standard C/AL Code ist da nicht wirklich eindeutig in der Handhabung der Befehle und, wie ich finde, auch sehr inkonsequent bis ignorant.
Der Standardcode hält sich an vielen Stellen ja auch nicht an den Styleguide, aber das ist ein anderes Thema.

Ich war einfach nur neugierig, ob das Handling der Temp. Records sich da in irgend einer Weise unterscheidet und ich u.U. noch Optimierungspotenzial an der ein oder anderen Stelle habe.

Gruß Rolf

Ich war einfach nur neugierig, ob das Handling der Temp. Records sich da in irgend einer Weise unterscheidet und ich u.U. noch Optimierungspotenzial an der ein oder anderen Stelle habe

Wie gesagt, ich habe dazu keine offizielle Aussage. Ich denke aber, wer sich an die sonst auch gültigen Regeln hält, macht nichts falsch ...

Interessant sein könnte auch, dass bei Temporären Tabellen die C/Side Sortierung für Codes genutzt wird, diese also unter Umständen anders sein kann als auf der SQL-Server Tabelle.

Gruß

Mathes

Interessant sein könnte auch, dass bei Temporären Tabellen die C/Side Sortierung für Codes genutzt wird, diese also unter Umständen anders sein kann als auf der SQL-Server Tabelle.

Gruß

Mathes

Daher sollte man auf verschieden lange und gleichzeitig rein numerische Nummernserien absehen bzw. in Code Feldern

Wenn sich das nicht mittlerweile geändert hat, sind die temporären Tabellen ja auf 2GB begrenzt. Das sollte also kaum einen Unterschied machen, wie man dort sucht/liest/schreibt. Zumal wie Natalie schon gesagt hat, der gesamte Inhalt im Arbeitsspeicher vorgehalten wird und sämtliche Aktionen darauf somit eh etwas schneller sind als bei normalen Tabellen.

Da glaube ich, sind solche Fallstricke wie z.B. die bereits erwähnte Sortierung oder das hier deutlich wichtiger für die Verwendung von temporären Tabellen sein können.

Temporäre Records werden nicht vom SQL-Server verwaltet, sondern ausschließlich temporär in deinem Arbeitsspeicher abgelegt und dort abgefragt.
So gesehen glaube ich nicht, dass die unterschiedlichen Methoden (FINDFIRST vs. FIND('-') vs. FINDSET) Performanceunterschiede ausmachen können - Jörg Stryk möge mich gern korrigieren
Vorstellen kann ich mir aber, dass auch hier ein ISEMPTY weniger Daten transferieren muss als ein FIND(FIRST).

Es spricht sicher nichts dagegen, auch bei temp. Recordvariablen "den guten C/AL-Ton" zu wahren ...

Ich hab das (fast) nix zu korrigieren
Das stimmt schon, dass Zugriffe auf temp-Recs nix mit dem SQL Server zu tun haben, sondern rein Client-seitig verwaltet werden. Allerdings m.E. nicht (nur) im RAM, sondern (auch) im Dateisystem - in dem "temppath" den man in den NAV Optionen findet, werden Dateien für diese temp-Objekte angelegt!
D.h. hier spiel tatsächlich die Festplattengeschwindigkeit des Client-PC eine Rolle im Bezug auf Performance!
Diese Dateien können je nach Nutzung knackig groß werden; z.B. 3 Mio GLE erzeugen eine Datei von über 2GB ... dann raucht NAV mit einem Fehler ab (Limit erreicht) ...

Bezieht sich das auf CC oder RTC?
Und wo sind die Temp-Tables bei Verwendung mit dem RTC? Auf dem Client oder dem NST? Falls NST, könnte es ja sinnvoll sein den Temppfad für den NST auf eine RAM-Disk zu legen.

Volker

Oh, ich dachte, das wäre klar, weil Rolf das ja schon erwähnt hatte...

Wie temp. Records intern funktionieren ist klar. Daher kommen ja auch immer die vielen schönen Files im User-Temp.

Im CC werden die Temporären Tabellen im RAM des Rechners, auf dem der CC ausgeführt wird, vorgehalten. Gleichzeitig werden die Daten auch in einer Datei im Benutzerverzeichnis hinterlegt. Außerdem sind diese (nur für diese Instanz des CC) irgendwo im Bereich der Systemtabellen (1.000.000.000 oder sowas) angelegt.

Ohne es für den RTC getestet zu haben, würde ich vermuten, daß läuft auf dem Client ab, denn ansonsten müßte die Middle Tier das ja für alle User (bzw. alle Verbindungen) getrennt verwalten. Das erscheint mir doch etwas viel, wenn man bedenkt, daß pro Instanz ja dann theoretisch 2GB RAM gebraucht würden.

Man sieht also aus Performancesicht spielt hier eine Menge eine Rolle, z.B. Auslagerungsdatei, Ort des Benutzerverzeichnisses, Plattenplatz und -kapazität, usw. Aber um mal wieder zur ursprünglichen Frage zurückzukehren, bzgl. Empfehlungen bzw. Best Practice von FIND-Befehlen und ISEMPTY gibt es meines Wissens nach keine Vorgaben.

Außerdem sind diese (nur für diese Instanz des CC) irgendwo im Bereich der Systemtabellen (1.000.000.000 oder sowas) angelegt.

Ich glaube Du verwechselst da was. Diese temporären Tabellen (ID > 1000000000) werden beim FBK Import angelegt - und zwar physisch zur Zwischenspeicherung der Daten; eine als temporär deklarierte Record Variable tut das nicht!

Ohne es für den RTC getestet zu haben, würde ich vermuten, daß läuft auf dem Client ab, denn ansonsten müßte die Middle Tier das ja für alle User (bzw. alle Verbindungen) getrennt verwalten. Das erscheint mir doch etwas viel, wenn man bedenkt, daß pro Instanz ja dann theoretisch 2GB RAM gebraucht würden.

Das ist eine spannende Frage ... Ich würde das eher so sehen: die temp-Daten müssen da liegen, wo sie gebraucht werden, also da wo die Logik ausgeführt wird - ergo dem NST ... müsste man mal ausprobieren ...

Was die Verwendung diverse FIND-Befehle angeht, so kann man vielleicht diese kurzgefassten Richtlinien bedenken (relativiert durch die tatsächlich eingesetzte NAV Version):

FIND('-'):
Immer Dynamic Cursor; Sortierung(Key) muss zum Filter und Index passen, sonst kann es zu teuren Index Scans kommen. Es werden alle Daten im Filter in den DC geladen und dann Paketweise an den Client übertragen.
MUSS verwendet werden wenn mit ASCENDING(FALSE) gearbeitet wird!

FIND('+'):
Wie FIND('-') nur absteigend sortiert.

FINDSET:
Server-Call mit TOP Klausel gem. DB Eigenschaft "bufferedrows"; z.B. SELECT TOP 500 *
Erster Call kein Cursor, Sortierung weniger relevant; SQL kann besser verfügbare Indexe ziehen; weniger Scans.
Übersteigt das tatsächlich gefilterte Result die TOP Klausel, dann wir der Rest der Daten via DC nachgeladen (Verhalten dann wie FIND('-'))

FINDSET(TRUE):
Dynamic Cursor mit UPDLOCK; exakt wie LOCKTABLE und FIND('-')

FINDFIRST:
SELECT TOP 1 *; kein DC; nur ein Datensatz wird übertragen. Startet man danach eine Schleife (REPEAT .... UNTIL) werden die Daten via DC nachgeladen (dann wie FIND('-'))

FINDLAST:
Wie FINDFIRST nur absteigend sortiert

ISEMPTY:
SELECT TOP 1 NULL; kein DC; kein Datensatz wird übertragen, lediglich 0 oder 1. Wer mit diesem nicht vorhandenen DS weiterarbeitet ist selber Schuld ...

ALLE FIND Befehle erfordern die entsprechenden Index-Strukturen um die Daten auffinden zu können! Dynamic Cursor ändern dabei die "Spielregeln" im SQL Server:
Standard: WHERE vor ORDER BY
Dynamic Cursor: ORDER BY vor WHERE

Ergo (IMHO): wenn's um Ergebnismengen geht, dann verwende ich immer den FINDSET (wenn nix dagen spricht) - wenn die TOP Klausel überschritten wird, gibt's zwar einen zweiten Server-Call, aber das wäre mir erstmal Wurscht. Wenn ich den ersten oder letzten DS brauche ist's FINDFIRST oder FINDLAST, will ich wissen ob was im Filter ist, ist's der ISEMPTY. Egal ob temp oder physisch.

Außerdem sind diese (nur für diese Instanz des CC) irgendwo im Bereich der Systemtabellen (1.000.000.000 oder sowas) angelegt.

Ich glaube Du verwechselst da was. Diese temporären Tabellen (ID > 1000000000) werden beim FBK Import angelegt - und zwar physisch zur Zwischenspeicherung der Daten; eine als temporär deklarierte Record Variable tut das nicht!

Vermutlich war das hier diskutierte ID-Verhalten (Wert über 2.000.100.000) gemeint.

Ergo (IMHO): wenn's um Ergebnismengen geht, dann verwende ich immer den FINDSET (wenn nix dagen spricht) - wenn die TOP Klausel überschritten wird, gibt's zwar einen zweiten Server-Call, aber das wäre mir erstmal Wurscht. Wenn ich den ersten oder letzten DS brauche ist's FINDFIRST oder FINDLAST, will ich wissen ob was im Filter ist, ist's der ISEMPTY. Egal ob temp oder physisch.

Das entspricht auch meiner Arbeitsweise, und wenn's egal ist ob temp oder nicht, dann passt das ja soweit.
Besten Dank an alle.

Außerdem sind diese (nur für diese Instanz des CC) irgendwo im Bereich der Systemtabellen (1.000.000.000 oder sowas) angelegt.

Ich glaube Du verwechselst da was. Diese temporären Tabellen (ID > 1000000000) werden beim FBK Import angelegt - und zwar physisch zur Zwischenspeicherung der Daten; eine als temporär deklarierte Record Variable tut das nicht!

Stimmt, ist der falsche Nummernkreis, Kai hat den richtigen genannt.

Ohne es für den RTC getestet zu haben, würde ich vermuten, daß läuft auf dem Client ab, denn ansonsten müßte die Middle Tier das ja für alle User (bzw. alle Verbindungen) getrennt verwalten. Das erscheint mir doch etwas viel, wenn man bedenkt, daß pro Instanz ja dann theoretisch 2GB RAM gebraucht würden.

Das ist eine spannende Frage ... Ich würde das eher so sehen: die temp-Daten müssen da liegen, wo sie gebraucht werden, also da wo die Logik ausgeführt wird - ergo dem NST ... müsste man mal ausprobieren ...

Ich hab mal etwas herumgespielt. Du hast Recht! Beim RTC werden die Dateien der temporären Tabellen auf dem Middle Tier Server in einem Temp-Verzeichnis pro jeweiligem Users angelegt. Das heißt für Windows Server 2008 bspw. unter \ProgramData\Microsoft\Microsoft Dynamics NAV\60\Server\%Servername%\users\%DOMAIN%\%Username%\TEMP. Das finde ich schon hochinteressant. Man sollte also darauf achten, daß nicht zu viele Benutzer temporäre Tabellen im RTC verwenden.

Generell ist noch zu sagen, daß die Temp Files nicht sofort erstellt werden, sondern erst, wenn die temporären Tabellen eine gewisse Größe erreicht haben.

@Jörg: Die o.g. Richtlinien gelten doch für die Verwendung im Zusammenhang mit SQL. Ich glaube, das war soweit klar. Die Frage ist, ob es hier im Zusammenhang mit temporären Tabellen auch irgendwelche Richtlinien gibt. Ich denke, man macht mit der Anwendung der SQL-Richtlinien nichts falsch, aber wenn man es anders macht, sollte es auch nicht schlimmer (oder besser) werden. Oder hast du irgendwelche Anhaltspunkte, daß das auch für temporäre Tabellen gilt?

Beim RTC werden die Dateien der temporären Tabellen auf dem Middle Tier Server in einem Temp-Verzeichnis pro jeweiligem Users angelegt. Das heißt für Windows Server 2008 bspw. unter \ProgramData\Microsoft\Microsoft Dynamics NAV\60\Server\%Servername%\users\%DOMAIN%\%Username%\TEMP. Das finde ich schon hochinteressant. Man sollte also darauf achten, daß nicht zu viele Benutzer temporäre Tabellen im RTC verwenden.

Gilt dann immer noch die Grenze von 2 GB?
Wurde die Grenze mit NAV2013 aufgehoben?
Was passiert mit dem NST, wenn eine Tabelle die Grenze von 2 GB überschreitet? Crash?

Volker

@Jörg: Die o.g. Richtlinien gelten doch für die Verwendung im Zusammenhang mit SQL. Ich glaube, das war soweit klar. Die Frage ist, ob es hier im Zusammenhang mit temporären Tabellen auch irgendwelche Richtlinien gibt. Ich denke, man macht mit der Anwendung der SQL-Richtlinien nichts falsch, aber wenn man es anders macht, sollte es auch nicht schlimmer (oder besser) werden. Oder hast du irgendwelche Anhaltspunkte, daß das auch für temporäre Tabellen gilt?

Also ich denke, dass wenn man die Zugriffmethoden für den SQL Server "richtig" (?) wählt, dann sollte es für den nativen Server wie auch tempRec genauso gut funktionen - dort werden die Befehle m.E. eh gleich interpretiert (also FIND('-') = FINDSET etc.) weil ja das native NAV Verhalten weder Cursor noch TOP Klauseln etc. kann.
Oder kurz gesagt; wenn's für SQL passt, dann passt's auch für alle nativen Geschichten! DAs wären jedenfalls meine bishereigen Erfahrungen ...

Gilt dann immer noch die Grenze von 2 GB?

Ja, leider.

Wurde die Grenze mit NAV2013 aufgehoben?

Keine Ahnung, nicht getestet.

Was passiert mit dem NST, wenn eine Tabelle die Grenze von 2 GB überschreitet? Crash?

Der Middle Tier Server bleibt stabil und läuft weiter. Die Datei wird automatisch sofort wieder gelöscht und der RTC erhält folgende Fehlermeldung:

Das Betriebssystem hat die Fehlermeldung (131) ausgegeben: An attempt was made to move the file pointer before the beginning of the file.

Es kann gut sein, daß in einem Umfeld mit mehreren Benutzern die RTC-Sessions des betroffenen Middle Tier Servers Performanceverluste haben werden. Ich hab es nicht getestet, aber als die Dateigröße in den entsprechenden Bereich kam, wurde sie immer langsamer beschrieben. Es kann gut sein, daß das den Middle Tier Server dermaßen beansprucht, daß andere Sessions davon auch etwas mitbekommen.

Was passiert mit dem NST, wenn eine Tabelle die Grenze von 2 GB überschreitet? Crash?

Der Middle Tier Server bleibt stabil und läuft weiter. Die Datei wird automatisch sofort wieder gelöscht und der RTC erhält folgende Fehlermeldung:

Das Betriebssystem hat die Fehlermeldung (131) ausgegeben: An attempt was made to move the file pointer before the beginning of the file.

Kann das nicht gefährlich werden? Kann man denn per Code einen Rollback einbauen?

Ich dachte da an ein Szenario, in dem ich z. B. Daten ändere, dann eine weitere Aktion ausführe in der ich eine Temp-Table verwende, dann eine weiter Aktion. Wen nun die mittle Aktion mit der Temptable abbricht, könnte es ja sein , dass ich ein komplettes Rollback brauche. In SQL würde ich da halt einfach mit Transactions arbeiten, aber in NAV?

Volker

Wieso? Das Rollback und damit die Transaktionssicherheit sind durch die Fehlermeldung doch gegeben. Der Middle Tier Server wird nur nicht unterbrochen, d.h. die Session kann ganz normal weiter benutzt werden und auch andere Sessions sollten hier (bis auf die angeprochene Möglichkeit für Performanceverlust) keine Probleme haben.