How To increase performance by reducing DB-reads?

Administrator · **Posted:** Tue Sep 18, 2007 11:43 am

How To increase performance by reducing DB-reads?

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

Discuss this How To here.

ara3n · **Posted:** Tue Sep 18, 2007 2:22 pm

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.

Code: Select all

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

kriki · **Posted:** Tue Sep 18, 2007 3:50 pm

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.

Code: Select all

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 · **Posted:** Tue Sep 18, 2007 4:04 pm

Same Results.

Code: Select all

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 · **Posted:** Tue Sep 18, 2007 5:32 pm

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 · **Posted:** Tue Sep 18, 2007 6:16 pm

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 · **Posted:** Wed Sep 19, 2007 4:28 pm

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 · **Posted:** Sat Sep 22, 2007 8:52 pm

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

Code: Select all

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 · **Posted:** Mon Sep 24, 2007 5:11 pm

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 · **Posted:** Thu Apr 09, 2009 7:57 am

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.

mibuso.com

How To increase performance by reducing DB-reads?

Who is online