RSS

Tag Archives: Performance

Dynamics NAV Performance: Record Data Operations

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: ,

 
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