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 
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
LikeLike
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.
LikeLike