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
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.
Regards,Alain Krikilion No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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
Jesper Falkebo
Senior SDE
Microsoft Dynamics Nav | Server & Tools
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...
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.
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?
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.
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
Marco Ferrari Microsoft Certified Trainer Cronus.it
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...
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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.
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.
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.
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.
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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);
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"
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
@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.
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.
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.
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.
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...
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.
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?
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
Answers
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
Personaly I like the value of 500 and wonder why this is 50. It must be a mistake.
More work for us... #-o
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
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
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.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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
Senior SDE
Microsoft Dynamics Nav | Server & Tools
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...
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
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.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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?
RIS Plus, LLC
MVP - Business Apps
A yes for me! I hope PGs are listening.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
A better solution would be for NAV to have a way to limit the fields returns instead of returning everything.
http://mibuso.com/blogs/davidmachanick/
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
Microsoft Certified Trainer
Cronus.it
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...
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
Record.FINDSET(43);
So instead of a predefine 50 or 500, we can define the number of records we want to get.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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.
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.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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.
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.
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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 would be a better example
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
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"
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
RIS Plus, LLC
MVP - Business Apps
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.
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.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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...
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
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
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
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?
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog