Best Practice for FIND commands for addon development

wakestar

There are many resources around which explain how FINDSET, FINDFIRST, FIND('-') etc. work but my understanding is that it really depends on the NAV version.
Not all NAV versions behave the same on the SQL Server.

Let's say I want to maintain *one* codebase (addon) which works for all NAV versions between NAV5.0 classic and NAV2016.
And let's say the goal is to have the best *possible* performance while keeping the code differences between the different NAV version to a minimum.

Would you still use the old commands like FIND('-'), FIND('+'), etc? Or would you use FINDSET, FINDFIRST, FINDLAST... ?
Would you split the NAV Versions in two codebases? One codebase for everything older than NAV2013 and one codebase for NAV2013 and higher?
What's the best approach in your opinion?

JasminkaT

https://msdn.microsoft.com/en-us/library/dd355237(v=nav.71).aspx

In all versions:
FIND should be used when requesting a set of data which may not be completely enumerated/read.
Example, you loop through x number of records, but depending on conditions within the loop, you might abort the loop after reading only a few records

FINDSET should be used when requesting a confined set of data that will be read near entirety.
X is a not-too-large number of records (in 2009 and earlier defined by RecordSet).
Example: you loop through x number of records, and you expect to read all (or nearly all) of them.

Performance:
In 2009 and earlier, FIND issued dynamic cursor, pre-fetched 50 rows at the time. FINDSET used default result sets to retrieve up to X (= RecordSet) rows in a single roundtrip.

In 2013++ MARS is used. Performance wise, the two now perform the same. They should still be used following the same rules (expect to read all vs. expect to read X number of rows), as using findset, but not reading the selection in entirety, might in 2013++ lead to increase in async_network_io (we ask for, but don't intend to read, the entire set)

JasminkaT

Correct. FINDSET was more efficient thenFIND on earlier versions and with confined sets.
So FINDSET uses default result set (vs find = dynamics cursor) and will be more efficient (on 2009 and earlier) when retrieving up to X = RecordSet rows. However, if your resultset is > X, then FINDSET reverts to FIND after X rows, so the benefit is lost above RecordSet number of records, the result being actually a bit slower then a FIND alone would have been. On versions 2013 and higher, both are equally efficient

In greater details, on 2009 and earlier:

FINDSET:
▪ Used Default Result Sets to retrieve up to X rows in a single round trip to SQL Server.
‒ Where X = RecordSet value
▪ If value for X is too large, this will cause blocking, since default result sets can only have one request per connection.
▪ Uses server-side cursors to retrieve the remaining rows of the result set past the first X.
▪ Note, if the ForUpdate argument is set to true, FINDSET(TRUE, …), FINDSET behaves like FIND(‘-’) and uses only server-side cursors.

FIND:
▪ Used server-side cursors.
▪ Pre-fetched 50 rows at a time.


If Microsoft Dynamics NAV detects a pattern in which FINDSET would be a better choice than FIND, then it converts the FIND operation to a FINDSET operation.

The above refers to size of record set retrieved .

On 2013 and higher, FIND will:
▪ Issue a TOP X, where X is self-tuning, to roughly predict the number of rows that will be read and to avoid canceling of result set costs.
‒ X starts off at 50.
‒ If statistics around number of rows read begins to converge to a number smaller than 50, X is changed to that number.
▪ If more than X rows are read, another request is issued positioned at row X+1 for the remaining rows.
‒ No further TOP is placed at this point.



But to summarize, the rule should be:

FINDSET for confined data set that will be read in entirety.
FIND otherwise.

JasminkaT

@wakestar:
So you are basically saying: Try to keep one codebase and decide for each individual case what command to use and you would not split the NAV versions in two codebases... correct?


Yes

Let's go one step further:
The addon is already developed and the code is the same across all versions between 4.0 sp1 and NAV2016... and performance is not really an issue (yet). I guess you would still change from the old commands to the new commands by the rules you wrote in your post?

The guidelines I've given are our recommended best practice. So in principal - yes.
But whether that effort/risk would balance with impact of that change in your case, given no performance issues (yet) and the fact performance difference between the two in 2013 ++ is small - you will know best.

matttrax

If you only need from 5.0 and up I think you can use the FINDSET like commands exclusively as that is the version they were introduced.

wakestar

I think the new commands came with 4.0 SP1 ... IIRC

What I wonder: Are there any NAV versions in the wild where the new commands are performing worse than the old ones?

JasminkaT

https://msdn.microsoft.com/en-us/library/dd355237(v=nav.71).aspx

In all versions:
FIND should be used when requesting a set of data which may not be completely enumerated/read.
Example, you loop through x number of records, but depending on conditions within the loop, you might abort the loop after reading only a few records

FINDSET should be used when requesting a confined set of data that will be read near entirety.
X is a not-too-large number of records (in 2009 and earlier defined by RecordSet).
Example: you loop through x number of records, and you expect to read all (or nearly all) of them.

Performance:
In 2009 and earlier, FIND issued dynamic cursor, pre-fetched 50 rows at the time. FINDSET used default result sets to retrieve up to X (= RecordSet) rows in a single roundtrip.

In 2013++ MARS is used. Performance wise, the two now perform the same. They should still be used following the same rules (expect to read all vs. expect to read X number of rows), as using findset, but not reading the selection in entirety, might in 2013++ lead to increase in async_network_io (we ask for, but don't intend to read, the entire set)

Phenno

Nice post Jasminka!

I would emphasize one quote from MSDN:
The FIND('-') operation is more efficient than the FINDSET operation when there are more records to read than the preset number.

And it says tha default value for nav2009 is 50, and for 5.0 is 500. Interesting to see this.

But, I also found this note: If Microsoft Dynamics NAV detects a pattern in which FINDSET would be a better choice than FIND, then it converts the FIND operation to a FINDSET operation.

Wonder when this will occur?

Phenno

Though, FINDSET is available in Navision 4.0 too, at least in SP3, so MSDN doesn't says it entirely correctly.

JasminkaT

Correct. FINDSET was more efficient thenFIND on earlier versions and with confined sets.
So FINDSET uses default result set (vs find = dynamics cursor) and will be more efficient (on 2009 and earlier) when retrieving up to X = RecordSet rows. However, if your resultset is > X, then FINDSET reverts to FIND after X rows, so the benefit is lost above RecordSet number of records, the result being actually a bit slower then a FIND alone would have been. On versions 2013 and higher, both are equally efficient

In greater details, on 2009 and earlier:

FINDSET:
▪ Used Default Result Sets to retrieve up to X rows in a single round trip to SQL Server.
‒ Where X = RecordSet value
▪ If value for X is too large, this will cause blocking, since default result sets can only have one request per connection.
▪ Uses server-side cursors to retrieve the remaining rows of the result set past the first X.
▪ Note, if the ForUpdate argument is set to true, FINDSET(TRUE, …), FINDSET behaves like FIND(‘-’) and uses only server-side cursors.

FIND:
▪ Used server-side cursors.
▪ Pre-fetched 50 rows at a time.


If Microsoft Dynamics NAV detects a pattern in which FINDSET would be a better choice than FIND, then it converts the FIND operation to a FINDSET operation.

The above refers to size of record set retrieved .

On 2013 and higher, FIND will:
▪ Issue a TOP X, where X is self-tuning, to roughly predict the number of rows that will be read and to avoid canceling of result set costs.
‒ X starts off at 50.
‒ If statistics around number of rows read begins to converge to a number smaller than 50, X is changed to that number.
▪ If more than X rows are read, another request is issued positioned at row X+1 for the remaining rows.
‒ No further TOP is placed at this point.



But to summarize, the rule should be:

FINDSET for confined data set that will be read in entirety.
FIND otherwise.

wakestar

Thanks a lot Jasminka!

So you are basically saying: Try to keep one codebase and decide for each individual case what command to use and you would not split the NAV versions in two codebases... correct?

Let's go one step further:
The addon is already developed and the code is the same across all versions between 4.0 sp1 and NAV2016... and performance is not really an issue (yet). I guess you would still change from the old commands to the new commands by the rules you wrote in your post?

kriki

Give also this one a look : mibuso.com/howtos/how-to-work-with-record-variables-version-2. I wrote that for up to NAV2009R2. NAV2013+ has some other rules because it doesn't use cursors anymore. But some tips&tricks are still useful.

Phenno

Jasminka,

Do we have any performance penalties on nav2013+ for async_network_io wait type in case we asked for more records then we read on nav?

Async_network_io is usually dominant wait type in NAV2013+ but I wonder if this should be controlled or checked more thoroughly, or could be moderately ignored.

JasminkaT

@wakestar:
So you are basically saying: Try to keep one codebase and decide for each individual case what command to use and you would not split the NAV versions in two codebases... correct?


Yes

Let's go one step further:
The addon is already developed and the code is the same across all versions between 4.0 sp1 and NAV2016... and performance is not really an issue (yet). I guess you would still change from the old commands to the new commands by the rules you wrote in your post?

The guidelines I've given are our recommended best practice. So in principal - yes.
But whether that effort/risk would balance with impact of that change in your case, given no performance issues (yet) and the fact performance difference between the two in 2013 ++ is small - you will know best.

JasminkaT

So, the process is - we call a FIND/FINDSET, and some no,. of records (50 –ish) is retrieved (that number is self-tuned for FIND). Opening a list, we read all records (in case user scrolls).

For a list, or a FINDSET, this will run until all the records are retrieved (or the buffer is full), even if we only read just a first few recs.
With MARS, multiple threads are run in parallell, retrieving the set. These threads are waiting around (async waits) for a period of time (5 mins or so) in case user starts scrolling, or application proceeds reading. Then they are consumed. If not, they are discarded. You see more of those comparing to 2009 and earlier, because of MARS.

So the resulting rows are waiting around for 5 mins (configurable setting i believe, must check though), or until the session is discarded, or transaction closed, or buffers full. Whichever comes first.

In my experience, they can be moderately ignored. However very high level should be checked thoroughly, primarily for high findset usage (where find should be used), check buffer is sufficiently large, and in general - to reduce any excessive roundtrips.

Phenno

Jasminka,

thanky you very much for your insights on this topic!