Best Practice for FIND commands for addon development

wakestarwakestar Member Posts: 207
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?

Best Answers

Answers

  • matttraxmatttrax Member Posts: 2,309
    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.
  • wakestarwakestar Member Posts: 207
    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?
  • PhennoPhenno Member Posts: 630
    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?
  • PhennoPhenno Member Posts: 630
    Though, FINDSET is available in Navision 4.0 too, at least in SP3, so MSDN doesn't says it entirely correctly.
  • wakestarwakestar Member Posts: 207
    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?
  • krikikriki Member, Moderator Posts: 9,115
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • PhennoPhenno Member Posts: 630
    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.
  • JasminkaTJasminkaT Member, Microsoft Employee Posts: 34
    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.
  • PhennoPhenno Member Posts: 630
    Jasminka,

    thanky you very much for your insights on this topic!
Sign In or Register to comment.