Can I find the value of Record Set via code?

Mike_HWG · 2012-11-05

Like the title implies.

I'm thinking I'd like to try this:

IF Record.COUNTAPPROX < "Record Set" THEN BEGIN
  IF Record.FINDSET THEN
    REPEAT
      DoSomething;
    UNTIL Record.NEXT = 0;
END ELSE BEGIN
  IF Record.FIND('-') THEN
    REPEAT
      DoSomething;
    UNTIL Record.NEXT = 0;
END;

I'm guessing there's a good answer why this is a bad idea since I don't see it being done as a regular practice, or can I just not access that Record Set value?

kine · 2012-11-06

I do not understand what you are trying to do. What do you mean with the "Record set"?

Mike_HWG · 2012-11-06

Go to File > Database > Alter. Click on the Advanced tab, look at the value for Record Set.

From this article:

(With FINDSET,) NAV allocates memory for a preset number of records and then begins reading the records. You can change the value of this preset number by changing the Record Set value in the New Database or Alter Database window

If the number of records that is read falls within this range, then all records are read with optimized performance. If there are more records to read than the preset number, then Microsoft Dynamics NAV must establish new commands to SQL Server to continue reading records. Microsoft Dynamics NAV reads all records successfully, but the additional commands are expensive for SQL Server to execute. The FIND('-') operation is more efficient than the FINDSET operation when there are more records to read than the preset number.
You must decide when to use the FINDSET operation and when to use the FIND('-') operation based on the maximum number of records to read and the value of the Record Set preset number.

After reading this, it sounds like the big question between a FINDSET and FIND('-') is guessing how many records will be returned. From what I've absorbed so far, this is a 'gut feeling' and you essentially hard code which search you are going to use.
So, I ask the question: Can we use COUNTAPPROX to do an educated guess on the number of records, then determine which search method to use? Maybe things don't work that way, but I'd like to make sure I know the answer

kine · 2012-11-06

If you are hunting milliseconds... but if I remember correctly, the countapprox under SQL means read all records and count them, but Count will tell you count based on the statistics (it is few years yet I have heard that). It means that it is better to do Count than CountAprox when using SQL server.

For me the real question is, if I can expect theoretically unlimited count of records, I will use the FIND (ledger entries etc.) but if you are working with limited record set (e.g. lines of one document, they will be never unlimited), I preffer FINDSET.

Mike_HWG · 2012-11-06

Other way around there, chief, but you got it -

From Microsoft:

The count is approximate because it uses statistical information maintained by SQL Server, which is not kept precisely in synchronization with modifications to the table and is not under transaction control. However, it is much faster to use this information than to perform an accurate record count under transaction control, especially when there are a large number of records in the table.

I'm thinking more in situations where you don't know what the user is going to filter on, which may affect your record set.
Imagine filtering on sales lines - if you are filtering a day or week's worth of lines, FINDSET might be appropriate. However, what if the user gets wild and sets the filter for a month or a quarter? Now you wish you had used FIND!

kine · 2012-11-06

Use FIND at all cases (in your example) and you are safe. Using Count/CountApprox to choose FINDSET will kill the advantage of it... :-) Again, in few rows, you will not loose too much when using FIND instead FINDSET. It is about the second option, where it is much better and the gain will be better. You do not need to think about some "selection logic" to use the BEST one. Use the one, which will give you best result in worst case. Why to optimize 10 percent of performance problems, when you can make better the rest?

Mike_HWG · 2012-11-06

So the solution is to relax a little? I think I like it 8)

DenSter · 2012-11-07

I go the other way, I use FINDSET. That's the newest keyword and the focus of improvement will go to the newest ones. I believe at some point there's not going to be an advantage with FIND at any level, and at that time you will have to revisit the same code again.

Personally, I have not seen many cases in which there is a big performance difference between FIND and FINDSET.

vijay_g · 2012-11-07

DenSter wrote:

Personally, I have not seen many cases in which there is a big performance difference between FIND and FINDSET.

This is the conclusion

kine · 2012-11-07

As you can see, it is not so "black&white". Even in NAV 2013 with the change to using MARS, I think that the difference is now zero...

Can I find the value of Record Set via code?

Comments

Categories