How To increase performance by reducing DB-reads?

1. The easiest (to program) but slowest (for performance) way

This is the way most programmers work.
When looping a table and doing a GET (or also FINDFIRST/FINDLAST) on another table for which you need some information, most people execute those GET-statements in each loop, even if the previous loop has already the correct record.

recItemLedgerEntry.RESET;
recItemLedgerEntry.SETCURRENTKEY("…");
recItemLedgerEntry.SETRANGE("…");
recItemLedgerEntry.SETRANGE("…");
recItemLedgerEntry.SETFILTER("…");
IF recItemLedgerEntry.FINDSET THEN
  REPEAT
    recInventorySetup.GET(); // people are really putting this GET here!!!!!
    recItem.GET("Item No.");
    CLEAR(recLocation);
    IF recLocation.GET("Location Code") THEN ;
  UNTIL recItemLedgerEntry.NEXT = 0;

2. The more difficult (to program) but faster (for performance) way

The trick is to avoid DB-reads when you already have read the information. To do that, you need to store it in temptables.

Global Variables:

Name		DataType		SubType
tmpItem		Record		Item		(property Temporary=Yes)

Some small function in the object:

GetItem(VAR OrecItem : Record Item;IblnErrorIfNotFound : Boolean;IcodItemNo : Code[20])
  OblnRecordFound : Boolean
// GetItem
// Gets the Item
// PARAMETERS:
//   OrecItem : record in which to put the General Posting Setup
//   IblnErrorIfNotFound : 
//     TRUE : give errormessage if record not found
//     FALSE : don't give errormessage and return blank record
//   IcodItemNo : Item No.
//   RETURN-VALUE : TRUE : record has been found ; FALSE : record has NOT been found

OblnRecordFound := 
  (tmpItem."No." = IcodItemNo);

IF NOT OblnRecordFound THEN
  OblnRecordFound := tmpItem.GET(IcodItemNo);

IF NOT OblnRecordFound THEN BEGIN
  CLEAR(tmpItem);
  IF IblnErrorIfNotFound THEN BEGIN
    LrecItem.GET(IcodItemNo);
    OblnRecordFound := TRUE;
  END
  ELSE
    OblnRecordFound := LrecItem.GET(IcodItemNo);
    
  IF OblnRecordFound THEN BEGIN
    tmpItem := LrecItem;
    tmpItem.INSERT(FALSE);
  END;
END;

OrecItem := tmpItem;

And the original code becomes this: (the functions “GetSETUPInventorySetup”, ”GetLocation” can be found in codeunit 90031):

Global Variables:

Name		DataType		SubType
cduGetRecord	Codeunit		Get Record
cduGetRecordSI	Codeunit		Get Record SI

recItemLedgerEntry.RESET;
recItemLedgerEntry.SETCURRENTKEY("…");
recItemLedgerEntry.SETRANGE("…");
recItemLedgerEntry.SETRANGE("…");
recItemLedgerEntry.SETFILTER("…");
IF recItemLedgerEntry.FINDSET THEN
  REPEAT
    cduGetRecordSI.GetSETUPInventorySetup(recInventorySetup);
          // even better would be putting it in the "OnInitReport"-trigger
    cduGetRecord.GetItem(recItem,TRUE,"Item No.");
    cduGetRecordSI.GetLocation(recLocation,FALSE,"Location Code");
  UNTIL recItemLedgerEntry.NEXT = 0;

3. Why 2 codeunits to put the functions in?

One is a singleinstance codeunit in which can be put data that (almost) never changes. Tables like the setup-tables, Location codes, Reason codes, G/L accounts, … The singleinstance codeunit (and it’s data) remains in memory until the company is closed (Opening the company list and selected the same company is also considered closing the company).

The tables for Items, Customers, Vendors, Documents, … are best put in the other codeunit (non-singleinstance) because they change more often. The normal codeunit remains only active as long as the object that uses the codeunit remains active. In general until the end of a process that reads a lot of records and takes some time to finish but in which the other data (items, customers, vendors, documents, …) may be considered static data.

4. The singleinstance codeunit and NAS.

Be careful when you use this codeunit in a NAS. It is advisably that the NAS is restarted once a day (and ALWAYS when some setuptable has been changed). Another option is to NOT use the singleinstance-codeunit and put all its functions/global variables in the other codeunit.

5. Important: best put the codeunits in the global variables and not in local variables

It is not necessary to define the singleinstance codeunit multiple times in the same object.

The normal codeunit would only be active in the function in which it is used, but not in the rest of the object.

6. When not to use the “Get Record”-system?

When you get a record and you want to change it. In this case you need the REAL record, not the one in memory. This to avoid you have an outdated record.

7. Confronting the number of database-reads:

Lets say we have a report that has to read all item ledger entries and get some information from the location-card and from the item-card. Let’s say we have 100.000 item ledger entries, 1.000 items and 10 locations. What does that mean in terms of database-reads:
 

We only have about one third of the database-reads left!
The locations/items are only read once (and only those needed!) and then stored in a temptable. All the next reads of those locations/items are then read from the temptable and only if the temptable-variable has the wrong location/item available.
Reading from a temptable (=LOCAL MEMORY!) is a lot faster than reading from the server (the locations/items will also be in the servers memory but there is still the network and other overhead between them).
We need fat clients to run the Navision-client, but we don’t fully use their potential. With this system the servers load is lightened a little by moving the load to the client.

8. In the fob there are 2 reports and 2 codeunits (they are compatible with Navision Client 3.01 and up):
 

1. R90030: This is a report with an example WITHOUT the “Get Record”-system.
2. R90031: This is a report with an example WITH the “Get Record”-system.
3. C90030: In this codeunit are the functions to get records that must only be cached while running a certain object (e.g. report R90031). When the report has finished, the codeunit is cleared and thus also its cached records.
4. C90031: In this codeunit are the functions to get records that may be cached from the moment a company is opened until the company (or the DB or the client) is closed.

9. A small benchmark:

On my computer I have SQL2000-DB (after a rebuild index). And a Navision DB-server with a cache of 20MB. The Navision clients are also on that computer. So I don’t have a network that slows down the reads (this means that with network-traffic, the difference will be more significative). I have warmed up the servers by running the loop several times before starting to measure it. I have about 1.000.000 Value Entry-records with about 50000 items. I read the value entry-table in order of “Entry No.”. I have run both systems several times alternating them (first with GET then with GetRecord and then again with GET,…)
 

Of course on other systems, these values can change a lot.
The reason that the test on the Navision DB improves a lot with the GetRecord-function is probably because the DB-server has little DBcache to keep records in memory.