Default RecordSet 50?

Alex_Chow · 2008-12-22

Is there a reason why the default value in the RecordSet is set to 50 in NAV2009 instead of 500 in version 5.0SP1 and earlier?

Does NAV prefer the use of FIND('-') in NAV2009 for anything larger than 50 records?

ara3n · 2008-12-22

I'm thinking it's a mistake (bug)

Marije_Brummel · 2008-12-22

This is amazing and hard to believe. ](*,)

Personaly I like the value of 500 and wonder why this is 50. It must be a mistake.

More work for us... #-o

Slawek_Guzek · 2008-12-22

Not necessairly... Who knows..

Perhaps they (MS) did some statistical researches and found out that having smaller recordset makes things faster ?

50 makes sense when for example the cose is using old FIND('-') or FINDSET in places when FINDFIRST or ISEMPTY are necessary in fact. Perhaps Alex is right and NAV2009 prefers the use of FIND('-')

. Sure MS should rather update the code than just decrease record set sizem but it is easier to change just in one place.

Anyway - it's just a guess - nobody knows for sure.

And honestly - you think 500 is good because 500 is there for ages. Did anybody make a serious tests on standard code finding out which value is the best ? Is it possible at all ? Probably not or hardly possible for the partner as it depends on data and system usage..

Regards,
Slawek

PS I preffer 512 personally which probaby doesn't make any sense over 500, but I just like 'round' numbers

Alex_Chow · 2008-12-23

I'm pretty sure Mark Brummel has some statistics on that.

Nonetheless, we've changed it to 500 for a new NAV2009 installation, I really hope MSFT doesn't come out and say 50 is the new 500.

kriki · 2008-12-23

The only reason I can think of to choose 50 over 500 is when you know that your result set is big, but you know you will use only the first few records and not read the other ones. But this is a scenario that does happen in a lot of places in the code.

Sometimes it would be even better to have a bigger number.

It would be good if MS made an extra option in the FINDSET where we can overrule the default recordset.

@Mark : at least you can't complain that MS isn't creating work for you.

jfalkebo · 2009-01-05

The different Find commands are and will continue to be optimized towards a specific intended use.

I don't know how FindSet was explained in the past but it is intended to be used to work with a confined set of records only, e.g. the lines for a single sales order. If you are working with a larger amount of rows then find('-')/Find('+') should be used because we might end up firing extra and more expensive statements anyway if the resultset is bigger than the specified record set size.

As you probably know FindSet does not use a cursor, so to gain optimal performance all rows are read into memory as soon as the FindSet command is executed. This means that a large record set size will cause a large amount of temporary memory to be allocated/reallocated.
Allocatiing and deallocating memory happens all the time but in this case we are talking about really large amounts of memory which causes the heap to become badly fragmented. Heap fragmentation causes the system to run out of memory sooner but it also causes a lot of extra work for the heap manager. With NAV2009 this problem becomes even bigger as each client has it's own connection to SQL server through the NST.

So our testing showed that reducing the default size had a very positive effect on performance. This was in a test scenario where most sales/purchase orders had about 5 lines.

The application has somewhat been optimized to use FindSet where appropriate, one example is when reading lines for a sales order. So with the standard application you should set the Record Set size to the average number of sales order lines per sales order.

/Jesper Falkebo, NAV Server Team

Slawek_Guzek · 2009-01-05

TaDaaaa!!!

I was right !!! \:D/ :whistle:

BTW Thanks Jesper for very nice explanation

Slawek.

PS. It woud be nice if you (MSFT) could take into account Kriki's (and I suspect others as well) suggestion and add an extra parameter RecordSize to the FIND/FINDSET commands...

Alex_Chow · 2009-01-05

Congrats! You're next cookie is on me.

So the new rule is setting the RecordSet based on the number of sales order lines. Simple enough. But I really would like to see some actual performance numbers on setting different values on RecordSet.

garak · 2009-01-05

good to know

DenSter · 2009-01-05

Alex Chow wrote:

So the new rule is setting the RecordSet based on the number of sales order lines.

Not really. They said they tested with an average of 5 lines per order, and the recordset size of 50 would indicate that the 'rule' is to set it at 10 times the number of lines per order. I'm not so much interested in what the 'good' results were, but how they determined that the value 500 caused performance issues. We might be looking at more complex code reviews as a result.

What I take out of this (which I have been taking out of the parameter all along) is that we need to be aware of what the parameter means and set it to a value that makes sense for the customer.

Could I add my support in favor of an optional parameter to FINDSET?

Alex_Chow · 2009-01-05

The optional recordset parameter would be great as most of the time when we write FIND or FINDSET, we can expect how many records we're reading.

A yes for me! I hope PGs are listening.

davmac1 · 2009-03-13

Of course, what is typical varies by company. Some companies regularly have sales orders and purchase orders of hundreds of lines.
A better solution would be for NAV to have a way to limit the fields returns instead of returning everything.

Marco_Ferrari · 2009-09-17

jfalkebo wrote:

As you probably know FindSet does not use a cursor, so to gain optimal performance all rows are read into memory as soon as the FindSet command is executed. This means that a large record set size will cause a large amount of temporary memory to be allocated/reallocated.

I cannot understand: this means that the FINDSET instruction ever allocates the memory for the record set parameter value and not for the really number of records read? So for example if I loop on 5 records only and I use FINDSET to read them, then NAV allocates memory for 500 records and not for 5? :thumbsdown:

Marco

Belias · 2009-09-21

I don't know sql so much, but as far as i can see from a bit of queries i run, if you filter 5 records, and you do a findset, (recordset = 500), the retrieved records are only 5:

Here's the generated query
SELECT TOP 500 * ...

Recordset affects the number after the "top", and if the retrieved recordset is lower, then you retrieves only the necessary lines. If the result set is higher than 500, sql has to fetch more records, but this is another story...

Alex_Chow · 2009-09-21

I think there's another thread that the users are asking for something like this:

Record.FINDSET(43);

So instead of a predefine 50 or 500, we can define the number of records we want to get.

David_Singleton · 2009-09-21

Alex Chow wrote:

I think there's another thread that the users are asking for something like this:

Record.FINDSET(43);

So instead of a predefine 50 or 500, we can define the number of records we want to get.

In theory this sounds wonderful.

But in practice, hard coding a parameter like this is possibly worse than having a global parameter. Maybe in a vertical application it might work, and possibly in areas of code where you know that there will be a small subset of records, but how would it be if you have to go in and hard code every customers database according to how many sales lines, items, customers, GL transactions etc.

The only thing I could see is possibly an over ride in the dbconfig table where we set a value for each table we are working on based on the core sorting. But then it gets equally hard to maintain. But at least we could tune it for those specific issues.

Alex_Chow · 2009-09-21

Well, if the the code is written like this:

Record.FINDSET

It will use the default parameter set.

But if you define:

Record.FINDSET(43)

It will use what you've written as the recordset.

David_Singleton · 2009-09-21

Yeah but you would have to go through and modify the paramater for each and evry client individually.

One client will have orders that are 1000 lines long, and sales orders one line each, another will have both sales and purchase orders with 20 lines each.

I can see it would be very usefull for a specific client with a specific issue. BUt I really don't approve of hard coding in this way.

David_Singleton · 2009-09-21

By the way, originally I thought that the idea of a parameter to force the record set size was a good idea (same as your suggestion). But I sat and thought about it and realized I was wrong. At first glance it looks like a great idea, but I was wrong, and now believe that it must be done through some configuration, as I said most likely through the dbconfig table in sql.

Belias · 2009-09-22

moreover, this parameter won't be shipped "out of the box" in standard functionality (it won't be good if MS set a FINDSET(number) on standand codeunits, obviously MS doesn't know which is the data volume of the customer).
Maintenance of code would become really hard when changing the release.
as David said, i was looking for this in the beginning, but a dbconfig parameter (or a table property) would be easier to maintain.
The table property should be specified differently for each company, and this can become harder to implement.

Slawek_Guzek · 2009-09-22

Belias wrote:

moreover, this parameter won't be shipped "out of the box" in standard functionality (it won't be good if MS set a FINDSET(number) on standand codeunits, obviously MS doesn't know which is the data volume

Why not ? Now it is shipped 'out of the box' and is equal FINDSET("Record Set"). So what's the difference ?

It should be there and it should be:

FINDSET or FINDSET(0) - = use the "Record Set" value
FINDSET(Any onter integer) - use that integer.

@David: I believe that FINDSET(43) was just an example. But if this hardcoding bothers you perhaps

IntegerVar := 43; //(or watever you calculate or estimate in your code)
FINDSET(IntegerVar);

would be a better example

Slawek

Belias · 2009-09-22

Slawek Guzek wrote:

Belias wrote:

moreover, this parameter won't be shipped "out of the box" in standard functionality (it won't be good if MS set a FINDSET(number) on standand codeunits, obviously MS doesn't know which is the data volume

Why not ? Now it is shipped 'out of the box' and is equal FINDSET("Record Set"). So what's the difference ?

It should be there and it should be:

FINDSET or FINDSET(0) - = use the "Record Set" value
FINDSET(Any onter integer) - use that integer.

Slawek

I'll explain better: microsoft won't review every FINDSET in standard business logic -it will leave it as it is: "take value of recordset"-.
In a customer environment, our company decides to review the "new parameter" of findset in order to solve performance issues and so on. After some years, our customer decides to upgrade to a new version: we'll have to review all the FINDSET instructions and rewrite the number in it. This is quite annoying

A more dynamic way to do this, would be to declare some global variables and then use them to set the recordset parameter; Anyway, i think we're "building castles in the air"

David_Singleton · 2009-09-22

Slawek Guzek wrote:
@David: I believe that FINDSET(43) was just an example. But if this hardcoding bothers you perhaps
IntegerVar := 43; //(or watever you calculate or estimate in your code)
FINDSET(IntegerVar);
would be a better example

Slawek

No this is still hard coded, because the value is specific to the code (objects) not the data. So if you used this code for another company it would use the same parameters. You need to work out what hard coded means.

David_Singleton · 2009-09-22

By the way, the first computer programming language I learn't was FORTRAN IV. On the topic of variable and hard coding, here is a classic extract....

FORTRAN IV user manual wrote:

The primary purpose of the DATA statement is to give names to constants;
instead of referring to PI as 3.141592653589797, at every appearance,
the variable PI can be given that value with a DATA statement, and
used instead of the longer form of the constant. This also simplifies
modifying the program, should the value of PI change.

Alex_Chow · 2009-09-22

David Singleton wrote:

No this is still hard coded, because the value is specific to the code (objects) not the data. So if you used this code for another company it would use the same parameters. You need to work out what hard coded means.

A predefined record set of 50 (Or 500 in prior version) on the setup table is hardcoded as well.

DenSter · 2009-09-22

Alex Chow wrote:

A predefined record set of 50 (Or 500 in prior version) on the setup table is hardcoded as well.

Maybe the default value is hard coded, but you can change that value.

Alex_Chow · 2009-09-22

Belias wrote:

I'll explain better: microsoft won't review every FINDSET in standard business logic -it will leave it as it is: "take value of recordset"-.

They dont' have to evaluate every FINDSET, they can leave the code as is. The FINDSET(Integer) is an option for the partner's development.

Belias wrote:

In a customer environment, our company decides to review the "new parameter" of findset in order to solve performance issues and so on. After some years, our customer decides to upgrade to a new version: we'll have to review all the FINDSET instructions and rewrite the number in it. This is quite annoying

Again, with FINDSET(Integer) it should be an option for the partner to use. During the upgrade, the partner should have properly documented the modifications done to the database so going through the custom FINDSETs shouldn't be that big of an issue.

Alex_Chow · 2009-09-22

DenSter wrote:

Maybe the default value is hard coded, but you can change that value.

Yeah, but it's very unlikely the end user will do that. And changing the default value to a larger value because of a single instance is worst for the overall performance...

Slawek_Guzek · 2009-09-22

David Singleton wrote:

No this is still hard coded, because the value is specific to the code (objects) not the data. So if you used this code for another company it would use the same parameters. You need to work out what hard coded means

This must be one of your worse days

(no offence !)

//(or whatever you calculate or estimate in your code)

New FINDSET option - FINDSET(integer) - WOULD BE superior to existing FINDSET. There is NOTHING wrong about that as this should be an option. If you want - you can use it, and change default behaviour in some areas. No doubt about that.

Example - ReadOnly looping through large record set. Say looping through ledger entries where some OR condition needs to be met. There is no way to use OR filter on two columns, so looping and evaluating values in C/AL is the only one alternative. In such a scenario you could use FINDSET(100000) and get rid of potential cursor use. It is better EVEN if you hardcode 100000 value.

But if FINDSET() accepted third integer parameter - what could stop you to calculate its value (or read from some setup) prior to calling it ?

I just can't get how you came to conclusion that FINDSET(integer) would be bad option.

Now you have one option - global, per database, even not per company.

Slawek

Belias · 2009-09-23

Alex Chow wrote:

Belias wrote:

I'll explain better: microsoft won't review every FINDSET in standard business logic -it will leave it as it is: "take value of recordset"-.

They dont' have to evaluate every FINDSET, they can leave the code as is. The FINDSET(Integer) is an option for the partner's development.

Belias wrote:

In a customer environment, our company decides to review the "new parameter" of findset in order to solve performance issues and so on. After some years, our customer decides to upgrade to a new version: we'll have to review all the FINDSET instructions and rewrite the number in it. This is quite annoying

Again, with FINDSET(Integer) it should be an option for the partner to use. During the upgrade, the partner should have properly documented the modifications done to the database so going through the custom FINDSETs shouldn't be that big of an issue.

Yes, this is what i meant...MAYBE we'll have to modify a lot of findsets in the standard code, and this will be painful during an upgrade...as you said, it would be an optional parameter, but if you begin to use it, you have to merge it with all future versions; moreover, for an add-on solution, you'll probably have to refine the parameter everytime depending on the specific customer.
it's a difficult choice :-k
Some hints from MicroSoft guys?

Howdy, Stranger!

Quick Links

Categories

Default RecordSet 50?

Answers