RSS

Dynamics NAV Performance: Record Data Operations

07 Oct

NAV has come very far as product and it has changed a lot over time and the big question today is, do you change with it or do you code like it is 1990? 🙂

When we were using native databases we used find(‘-‘) to find a record set. When SQL arrived we instead used findfirst because it was much faster. But what should we use nowadays?

I have created a function to measure all the record operations in a 2009 R2 SQL environment and in a 2013 R2.

Here is an example of the measuring code:

InitTime := TIME;
FOR Loop:=1 TO RECLoop DO BEGIN
  GLEntry.RESET;
  GLEntry.SETRANGE("G/L Account No.",'2645');
  IF GLEntry.FINDFIRST THEN;
END;
FindSetDuration := TIME - InitTime;

Lets take a look at the result in Dynamics NAV 2009 R2 (SQL):

PerformanceRecordDataOperations2009

Here are no big surprises. Find(‘-‘) is not the fastest operation anymore since the switch from native to sql database. Findfirst is the fastest operation and is good to use if you just want to see if a record set is empty or not.

Lets take a look at the result in 2013 R2:

Performance2013RecordDataOperations

If you start by comparing the overall times with the 2009 you can see major difference. Both tests have been run on the same machine, same and yet the 2013 R2 version is almost twice as fast as the 2009!

The next big surprise is IsEmpty. In 2009 R2 this was the slowest operation, in 2013 R2 this is the fastest operation!

What can we learn from this? If we want to optimize your code from a SQL perspective we should use IsEmpty instead of findfirst whenever we need to check if a record set is empty or not. For instance:

Setrange(x)
If findfirst then begin
  Findset
  Repeat
     //Do stuff
  Until next = 0;
end;

Should now instead be:

Setrange(x)
If Not IsEmpty then begin
  Findset
  Repeat
     //Do stuff
  Until next = 0;
end;
Advertisements
 
2 Comments

Posted by on 2014-10-07 in Okategoriserade

 

Tags: ,

2 responses to “Dynamics NAV Performance: Record Data Operations

  1. Kris

    2015-05-21 at 02:01

    Hi but example which you showed have no sence. Why you need to do ISEMPTY and then FINDSET? Why you do not do IF FINDSET then Repeat

    Like

     
  2. Capone

    2015-05-22 at 02:01

    Hi Kris,
    I understand your question but you have to look at it from a performance view. What I’m trying to illustrate is that each command (findfirst, isempty, findset…) takes different amount of time (performance) to execute.

    The IsEmpty example is for version > 2013 R2 and should be used when you don’t know if there exists record or not in a set. It all comes down to hit ratio, meaning how many times do you think the execution will find some records during the complete iteration.
    Example:

    For 1 to 10 000 Do begin
    setfilter (random X)
    if findset then
    //finds the record
    end

    How many times will the findset actually find a record? 100%? 50% or 10%?

    Since the findset operation is about 1,5 times slower than IsEmpty according to my example you should use If not IsEmtpy example when you expect a low hitratio:

    If you calculate on that findset is 1,5 times slower (according to my test) you will see that if you have a lower hit ratio than 30% it will be faster in total if you use the IsEmpty example.

    But if you are expecting to always find records when you do your findset or have a high hit ratio your right. Than the example will actually be slower.

    Like

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Mark Brummel Blog | Microsoft Dynamics NAV

Master Classes Design Patterns and more...

Vjeko.com

ideas in the cloud

Goprowe AB

Reflections from the ERP field

ArcherPoint Developer Blog

Reflections from the ERP field

The NAV Viking´s Blog

- Sharing my experience in the Dynamics NAV world

Comments for waldo's blog

Reflections from the ERP field

Nav Can Be Fun

Its Really Fun to Work with Microsoft Dynamics Nav

Navision-Girl.Com

Reflections from the ERP field

LET'S TALK NAV

Tips, Tricks, How-To's & News about Microsoft Dynamics NAV & Dynamics 365 for Financials

Kine's Info

Reflections from the ERP field

blog.wibeck.org/

Just another WordPress.com site

NAV/SQL Performance - My Two Cents

Reflections from the ERP field

Hougaard.com – Applied Hacking

Reflections from the ERP field

Kauffmann @ Dynamics NAV

Reflections from the ERP field

Kriki’s Dynamics NAV blog

Reflections from the ERP field

Rashed Amini

Reflections from the ERP field

Olof Simren - Microsoft Dynamics NAV Blog

My blog about Microsoft Dynamic NAV tips and tricks. Remember to Subscribe!

Van Vugt's dynamiXs

Reflections from the ERP field

Navisionary – Dynamics NAV

Reflections from the ERP field

%d bloggers like this: