How To increase performance by reducing DB-reads?

Administrator

How To increase performance by reducing DB-reads?

http://www.mibuso.com/howtoinfo.asp?FileID=14

Discuss this How To here.

ara3n

SQL client doesn't do a second read if the record is already in cache.

Run the following code and turn on Profiler, you'll see only two SELECT statements even though It's looping 10 times.

For I := 0 to 10 do begin
   Item.get('1000');
   Item.get('1100');
   if not confirm('continue?') then begin
     error('');
   end;
end;

kriki

ara3n wrote:

SQL client doesn't do a second read if the record is already in cache.

Run the following code and turn on Profiler, you'll see only two SELECT statements even though It's looping 10 times.

For I := 0 to 10 do begin
   Item.get('1000');
   Item.get('1100');
   if not confirm('continue?') then begin
     error('');
   end;
end;

But what if you do a GET on 1000's of different items?

ara3n

Same Results.

For I := 1 to 2000 do begin
   GLEntry.get(I);
end; 

For I := 1 to 2000 do begin
   GLEntry.get(I);
   if not confirm('continue?') then begin
     error('');
   end;
end; 

The second loop does not create any sql queries. Same results.


Now if you are modifying the records, then it's another story.[/quote]

kriki

I did some tests with a small amount of records (up to 10000) and the times are comparable between the GET and the GetRecord on my portable.
Once I get over 100000 records, the differences get more substantial.

Waldo

It's a good howto, and a creative way of programming ... but I think it's only interesting as a "last resort"... . It's quite a complex way of programming (not really "lazy programming") don't you think?

kriki

Waldo wrote:

It's a good howto, and a creative way of programming ... but I think it's only interesting as a "last resort"... . It's quite a complex way of programming (not really "lazy programming") don't you think?

It is a little more complex than the standard way. But each table needs only to be programmed once, and most of it is copy+past (of a function) and rename to use a new table. So not so complex.
And if you have a library in which you keep all functions and add a function when a project needs a new table, and then take that library in that project, also the next project will take profit.

hedegaard

If you had to do it I would rather do it like this and keep the temporary records local.

recItemLedgerEntry.RESET;
recItemLedgerEntry.SETCURRENTKEY("..");
recItemLedgerEntry.SETRANGE("..");
recItemLedgerEntry.SETRANGE("..");
recItemLedgerEntry.SETFILTER("..");
IF recItemLedgerEntry.FINDSET THEN begin
  recInventorySetup.GET();
  REPEAT
    IF NOT tempItem.GET("Item No.") THEN BEGIN
      recItem.GET("Item No.");
      tempItem := recitem;
      tempItem.INSERT;
    END;
    IF NOT tempLocation.GET("Location Code") THEN BEGIN
      recLocation.GET("Location Code");
      tempLocation := recLocation;
      tempLocation.INSERT;
    end;
  UNTIL recItemLedgerEntry.NEXT = 0;
end;

kriki

hedegaard wrote:

If you had to do it I would rather do it like this and keep the temporary records local.

That is best with records that can change quite often, like table 27.
But Locations-table doesn't change that often. And with the singleinstance codeunit, you read each location once per session (or until you change company).

rdebath

First a performance tweak: for small tables (like the location table) it's better to read the whole table in one go into your single instance codeunit. It's a lot slower going back to the DB every time. This will also make the code a little more simple.

Secondly on the NAS if you want to be able to clear the temp tables occasionally you can store the non-single instance codeunit in a global variable in the single instance codeunit. You then call the single instance codeunit to get a reference to the other one.

In this way the other codeunit becomes persistent and will exist until you CLEAR() it's variable in the single instance codeunit.

BTW: This works because codeunits are actually handles (with reference counting) unlike other Navision objects which act like C structures.