mibuso.com

Microsoft Business Solutions online community
It is currently Tue Oct 21, 2014 5:08 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: How To increase performance by reducing DB-reads?
PostPosted: Tue Sep 18, 2007 11:43 am 
Offline
Site Admin

Joined: Sun Nov 07, 1999 8:01 am
Posts: 2070
Location: Wilrijk, Belgium
Country: Belgium (be)
How To increase performance by reducing DB-reads?

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

Discuss this How To here.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 2:22 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 9058
Location: 3rd rock from sun
Country: United States (us)
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;

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 3:50 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7701
Location: Milan
Country: Italy (it)
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?

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 4:04 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 9058
Location: 3rd rock from sun
Country: United States (us)
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]

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 5:32 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7701
Location: Milan
Country: Italy (it)
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! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 6:16 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Fri Dec 06, 2002 11:50 am
Posts: 3397
Location: Kontich, Belgium
Country: Belgium (be)
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 19, 2007 4:28 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7701
Location: Milan
Country: Italy (it)
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! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Keep it simple
PostPosted: Sat Sep 22, 2007 8:52 pm 
Offline

Joined: Sun Jun 27, 2004 5:55 pm
Posts: 23
Location: Denmark
Country: Denmark (dk)
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;


Top
 Profile E-mail  
 
 Post subject: Re: Keep it simple
PostPosted: Mon Sep 24, 2007 5:11 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7701
Location: Milan
Country: Italy (it)
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! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: How To increase performance by reducing DB-reads?
PostPosted: Thu Apr 09, 2009 7:57 am 
Offline

Joined: Tue May 06, 2008 2:56 pm
Posts: 371
Location: Walton-On-Thames
Country: United Kingdom (uk)
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: