Hello together. I have a quite interesting question, I hope. The new FINDSET function is quite cool to improve performance on SQL. Yesterday I have exchanged all FIND(’-’) and most of the FIND(’+’) (not all of course) in our AddOn Smart RetailSuite by FINDFIRST,FINDSET and FINDLAST. Now my question: In the Online help and also in the webcast (http://www.mibuso.com/files7h4f0/webcastnav40sp1sql.zip it is stated that a FINDSET(TRUE) is implicitely performing a LOCKTABLE. Is that information valid for SQL database access only or also for C/SIDE (native) database ? And another one: When trying to change the values of the key field in a REPEAT, we have been using a temp table until now: T1.LOCKTABLE; T1.SETRANGE(FIELD5,1,8); T1.FIND(‘-‘); REPEAT TempT1 := T1; TempT1.INSERT(); UNTIL T1.NEXT = 0; T1.RESET; TempT1.FIND(‘-‘); REPEAT T1 := TempT1; T1.Field5 := T1.Field5 * 2; T1.MODIFY; UNTIL TempT1.NEXT = 0; Will the new FINDSET(TRUE,TRUE) enable us to remove such complicate code and just go for the following code without “skipping” records which just fall out of the filter (or change their position inside the filter) ? T1.SETRANGE(FIELD5,1,8); T1.FINDSET(TRUE,TRUE); REPEAT T1.Field5 := T1.Field5 * 2; T1.MODIFY(); UNTIL T1.NEXT = 0; Any comments will be highly appreciated.
1st question. The behaviour is for both database platforms so that FINDSET has exactly the same semantics for both. 2nd question. There are several issues here. 1. When you modify a record in a loop and do a NEXT on the new values, this is perhaps not the bahaviour you want and can lead to errors. You are doing a NEXT relative the values you have changed the record to, not to the record you actually read in the set. If you change a value for a field that is present in the current key, your NEXT will actually find a record that is not orderd after the one you just read. You will get unexpected behaviour. If the value is for a field that is not present in the current key, it does not matter. The general pattern when changing a record in a loop is to use a separate record variable for the MODIFY, and use the original variable for the NEXT. This is true also for FINDSET as well as for the old FIND(-). If the field is not in the current key, this can be relaxed to the code you have, but if you change your keys at some point you may hit the problem so the general case is the safest. 2. The filter does not matter because a NEXT will never retrieve a record that is not within the current filter. In your code you are changing a field value that is in the filter and doing a NEXT on that ‘new’ record, which wont matter if Field5 is not in the current key, but will matter if it is. The TRUE,TRUE of FINDSET is for the case of changing a field value and the field is in the current key. In this case FINDSET will be optimized against SQL Server to fetch records within a cursor (set) rather than having to create new queries for each NEXT. This is provided you do the NEXT on the values you just read, and not on the values you just modified to. If the latter, then performance will be bad again. 3. When using a temporary record variable, the optimizations in FINDSET are not relevant because your are not using SQL Server but a local memory index. However, using the same pattern in all cases (i.e. regarding all usages as going against the server) is the simplest approach and will work well if you ever change from being a temporary record later. 4. Golden rule for using any FIND/FINDSET loop against SQL Server: Regard the CURRENTKEY, the FILTER/RANGE and the record set produced after the FIND as being constant. I.e. after the FIND: - Do not change the CURRENTKEY. - Do not change the FILTER. - Do not do a NEXT on record values that you did not read. If you break any of these, you destroy c/side’s ability to use a set-based approach against SQL Server and it will use multiple queries giving bad (sometimes terrible) performance. Most querying languages and APIs do not allow these to be changed once a data set has been read; it is the AL language that allows any of these 3 factors to be changed, because Navision Server is not set based and has no context, therefore on that platform it doesnt matter.
If I understand right: 1. When doing a FINDSET(TRUE) I can omitt the LOCKTABLE before, as the table is LOCKED already and the following write and read transactions will be consistent, no matter what database being used. 2. I will stick to my temporary table loop. [:D] But THX for your very comprehensive explaination.
I didnt mention that your temporary table loop example is exactly what I was describing with regard to using a second variable for the MODIFY.