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):
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:
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;
NAVfreak 