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;
  GLEntry.SETRANGE("G/L Account No.",'2645');
FindSetDuration := TIME - InitTime;

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


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:


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:

If findfirst then begin
     //Do stuff
  Until next = 0;

Should now instead be:

If Not IsEmpty then begin
     //Do stuff
  Until next = 0;

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


  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.

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

    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.



Leave a Reply

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

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

Facebook photo

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

Connecting to %s

The BC Docs Librarian

Tips and news for AL programmers

Stefan Maroń

Dynamics 365 Business Central

James Pearson

Thoughts about Dynamics 365 Business Central, Git, Azure DevOps, PowerShell, Automated Testing, Visual Studio Code...and maybe some other stuff

Roberto Stefanetti BLOG

MVP & MCT Microsoft Dynamics 365 Business Central, Powershell, Azure, SQL Server


DevOps starts with culture!

Comments for DevOps ABCs Blog

Reflections from the ERP field

DevOps ABCs Blog

Reflections from the ERP field

Dynamics 365 Business Central for Partners

Reflections from the ERP field

Freddys blog

Learn something new... - then Teach someone!

Stefano Demiliani Technical Blog

Reflections from the ERP field

David Worthington's NAV Performance Blog

Micosoft Dynamics NAV Performance Testing & Optimization

Freddys Blog

Reflections from the ERP field

Marije Brummel Blog | Business Central, Azure and more...

Design Patterns, Performance, Clean Code

Ideas in the cloud

Goprowe AB

Reflections from the ERP field


Reflections from the ERP field

The NAV Viking´s Blog

- Sharing my experience in the Dynamics NAV world

Comments for

Reflections from the ERP field

Nav Can Be Fun

Its Really Fun to Work with Microsoft Dynamics Nav / D365 Business Central

%d bloggers like this: