I've said in the past that the new 4.0 SP1 version of
Microsoft Navision is not only a Service Pack (like someone
could imagine) but a new version with lots of improvements on the engine
(expecially on development and SQL Server optimization).
Navision 4.0 SP1 works with SQL Server 2005 and by using it
your performances will be increased a lot (for new projects that will start on
4.0 SP1 platform, start thinking on using SQL Server 2005). If you actually have
a project that works on Navision 4.0 platform, convert it to work under the new
4.0 SP1 platform is an easy step that requires few minutes of your time (by
opening the project with the new engine, you will be prompted to convert it),
but if you want to gain on performances, you've to use some little new
improvements that the 4.0 SP1 version has built-in.
Here I want to talk about one of the most simple (and quick) modification to
a Navision 4.0 project to gain on performances with the new 4.0 SP1 environment:
the use of 3 new instructions introduced in the 4.0 SP1 version: FindFirst, FindLast and FindSet.
FindFirst is used to find the first
record in a table based on the current key and filter. This function should be
used instead of FIND('-') when you only need the
first record. For example, if you have code that applies filters to a table and
then check if you have records that match these filters, instead of writing:
IF SalesSetup.FIND('-') THEN BEGIN
ConfigurePost(SalesSetup);
END;
you can write:
IF SalesSetup.FINDFIRST THEN BEGIN
ConfigurePost(SalesSetup);
END;
FindLast is used to find the last record in a
table based on the current key and filter. It should be used instead of FIND('+') when you only need the last record. It's
totally similar to the previous instruction Instead of writing:
IF GLEntry.FIND('+') THEN;
now you can write:
IF GLEntry.FINDLAST THEN;
FindSet is used to find a set of records in a
table based on the current key and filter (the records can only be retrieved in
ascending order). It's designed to optimize finding and updating sets and
it's a more efficient way to loop through all records in a table.
Instead of writing something like:
SalesLine.SETFILTER("Purch. Order Line
No.",'<>0');
IF SalesLine.FIND('-') THEN BEGIN
REPEAT
CopyLine(SalesLine);
UNTIL SalesLine.NEXT
= 0;
END;
now you can write:
SalesLine.SETFILTER("Purch. Order Line
No.",'<>0');
IF SalesLine.FINDSET THEN BEGIN
REPEAT
CopyLine(SalesLine);
UNTIL SalesLine.NEXT
= 0;
END;
This new code is extremely more efficient than the previous way of looping
through record and if you have the patience to check your 4.0 code and replace
only the FIND('-') and FIND('+') pieces of code with the use of these new
instructions, your performances will be increased a lot.
FindSet has also two parameters to improve
records updates performances, so you could consider also to use them like the
follow examples:
// Looping through a set and updating a field that is not within the
current key.
SalesLine.SETRANGE("Document
Type",DocumentType);
SalesLine.SETRANGE("Document No.",DocumentNo);
IF
SalesLine.FINDSET(TRUE, FALSE) THEN BEGIN
REPEAT
SalesLine."Location Code" := GetNewLocation(SalesLine);
SalesLine.MODIFY;
UNTIL SalesLine.NEXT = 0;
END;
// Looping through a set and updating a field that is within the current
key.
SalesShptLine.SETRANGE("Order
No.",SalesLine."Document No.");
SalesShptLine.SETRANGE("Order Line
No.",SalesLine."Line No.");
SalesShptLine.SETCURRENTKEY("Order No.","Order
Line No.");
IF SalesShptLine.FINDSET(TRUE, TRUE) THEN BEGIN
REPEAT
SalesShptLine2 :=
SalesShptLine;
SalesShptLine2."Order Line No." :=
SalesShptLine."Order Line No." + 10000;
SalesShptLine2.MODIFY;
UNTIL SalesShptLine.NEXT =
0;
END;
I think that it's not so difficult (and a so long task) to check your
previous 4.0 code and optimize it by using these new set of instructions. This
is the simplest step on optimizing a Navision solution to work with the new 4.0
SP1 and I recommend to consider it absolutely.