16 Jun 09 MS SQL Server 2008 - Webcast Series on MSDN

Hi there,

I’ve found a great series of webcasts on MSDN about MS SQL Server 2008. I’m sorry, that it is in German language only, so it might help only some of you. There are many topics handled, beginning with installing a named instance of the server, doing administration, creating databases, tables, etc. and ends with some Reporting Services stuff.

Here it is:
http://www.microsoft.com/germany/msdn/webcasts/serien/MSDNWCS-0902-02.mspx

Tags: Non-Navision, SQL, Workshop

05 May 09 Find-Statements on SQL-Server

It might not be the first posting about this topic, but I did some tests on SQL Server using Navision 5. My goal was to understand all those different FIND-statements, Navision offers since Navision 4.

So here is my “quick FIND-statement guide” on SQL-Server:

A) General

Every GET or FIND will be transformed by the SQL-Server into a statement like this:
SELECT * [,DATALENGHT Blob-Field,...] FROM “Table” WHERE “Condition”
ORDER BY “Navision-Key” DESC|ASC

• If there is any BLOB (Binary Large Object) field, there will be automatically asked for its size.
• The “Where” results on all SETRANGE or SETFILTER statements done by you.
• The “Order By” results on the used Navison-Key and its sorting. This does not mean that SQL-Server will use this key! It is only a sorting!

SQL-Server will now process a FETCH and return a resultset, managed by a so called Coursor, to the Navision Client.

So what does SQL-Server, when using those FIND-Statements?

B) FINDFIRST
SELECT * TOP 1 FROM...

So this means SQL-Server will return only ONE record, without managing a resultset. Hence you should use this always, if you only need the first record!

C) FINDLAST

SELECT * TOP 1 FROM ... ORDER BY ... DESC
Same as FINDFIRST but with descending order.

D) FINDSET

This command transfers a predefined set of records to the client (standard value is 500 and can be changed within the DB-options) without using a Resultset. If there are more records it falls back to a server-side Cursor (using Fetch) as the FIND('-') statement does.
You can use FINDSET with parameters. FINDSET(TRUE,FALSE) accepts two boolean parameters. The first one will tell the system that you’ll modify the read records and the second one informs Navision that this change will affect TRUE or not FALSE fields, which are part of the Key of this table. So if you are using FINDSET(TRUE,x), you’ll lock the table!

E) ISEMPTY

The command Table.ISEMPTY will be translated on SQL-Server to

SELECT NULL TOP 1 FROM ...

and hence is the best command to check if there is still a record existing or not, because it will not read the record. It only checks if there is a record or not! So this also means, that if you will process this record, you have to add a FIND-Statment like this:

IF NOT Table.ISEMPTY THEN
Table.FINDFIRST;

F) FIND(’-') and FIND(’+')

Those are the old commands for FINDFIRST and FINDLAST and they are always using a server-side Cursor for the resultset. It seams that it locks the table.

Conclusion:

• Table.ISEMPTY should be used to check if there is a record or not.
  Example: If the temporary table is not empty, then deleteall records within it.
• Table.FINDFIRST or Table.FINDLAST should be used to read the first record or last record.
  Example: When having ledger entries and you want to know the “Entry No.” of the last ledger entry use Table.FINDLAST.
• Table.FINDSET should be used to process a smaller number of records when they will be read only and not modified.
  Example: Read all Sales Lines within a single Sales Order to find some special lines within it.
• Table.FIND('-') or Table.FIND('+') should be used to read and / or modify larger resultsets.

Tags: Development, Navision, SQL

28 Apr 09 MS Dynamics NAV 2009 Performance Guide

Here it is: The NAV 2009 Performance Guide

Download it, read it, have speed!

Tags: NAV 2009, Navision, Navision 2009

11 Feb 09 MSDN now offers the Microsoft Dynamics NAV Developer Center

Hi there,

I’ve just found a link to the Microsoft Dynamics NAV Developer Center on MSDN at the Navigate Into Success website. It seems to be a great entry into the world of Navision, linking to public Navision Blogs, PartnerSource stuff and other partly public documentation. So hopefully it will be a good portal to keep us - as Navision developer / consultant / user - in touch with product.

Have fun!

Tags: Internet, Navision

08 Feb 09 Performance in SQL-Server - Use ISEMPTY or don’t use it?

Hi there,

I’ve heard that the following Navision statement

A)
SalesHeader.setrange(”Document Type”,SalesHeader.”Document Type”::Order);
IF SalesHeader.FINDFIRST THEN BEGIN
<some code>
END;

should be substitued on SQL Server by

B)
SalesHeader.setrange(”Document Type”,SalesHeader.”Document Type”::Order);
IF NOT SalesHeader.ISEMPTY THEN BEGIN
SalesHeader.FINDFIRST;
<some code>
END;

due to better performance. Maybe this means finally (because you might re-use your code and hence you do not know if the customer uses the native Navision Server or the SQL Server) that you shoud code it like this

C)
SalesHeader.setrange(”Document Type”,SalesHeader.”Document Type”::Order);
IF RECORDLEVELLOCKING THEN
IF NOT SalesHeader.ISEMPTY THEN BEGIN
SalesHeader.FINDFIRST;
<some code>
END
ELSE
IF SalesHeader.FINDFIRST THEN BEGIN
<some code>
END;

What’s your opinion about this theory and do you have any idea how to measure the performance in order to verify this idea?

keep on coding,
stefan

Tags: Development, Navision