How To increase performance by reducing DB-reads?

AdministratorAdministrator Posts: 2,429Member, Moderator, Administrator
edited 2009-04-09 in How Tos section
How To increase performance by reducing DB-reads?

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

Discuss this How To here.

Comments

  • ara3nara3n Posts: 9,226Member
    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;
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • krikikriki Posts: 8,660Member, Moderator
    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?
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • ara3nara3n Posts: 9,226Member
    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]
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • krikikriki Posts: 8,660Member, Moderator
    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.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • WaldoWaldo Posts: 3,412Member
    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?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • krikikriki Posts: 8,660Member, Moderator
    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.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • hedegaardhedegaard Posts: 25Member
    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;
    
  • krikikriki Posts: 8,660Member, Moderator
    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).
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • rdebathrdebath Posts: 383Member
    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.
    Robert de Bath
    TVision Technology Ltd
Sign In or Register to comment.