How to work with record-variables (version 3)?

Thanks for your remarks.
I'll put them in the new version.
Under here I put my answers to your remarks.

I think you need to mention that your description of most of the different functions assumes that SQL is used. A lot of the explanations are not true for Native, but I guess you are only working with SQL by now :-)
=> CORRECT. It is specially for SQL. I wrote that in the title in the beginning but later I changed the title to make it somewhat shorter and forgot to put it in the text. I now put it in the text.

You should also explain that when you say a function returns several records, it actually means that the SQL server returns them to the NAV client, which then caches them and hope you will ask for the next one without changing filters or key or locking first.
=> CORRECT. In some places I didn't specifically write that

I found a few small mistakes in your list: (To the best of my knowledge - at least)
“FIND('>') / FIND('<'): This bases itself on the values in the primary key to get the next or previous record, it ALSO considering the filters. Better use NEXT or NEXT(-1).”
Should say “…values in the current key…”
=> I made that more clear

“ISEMPTY: This is THE statement to use if you want to check if there is at least one record in the (filtered) table.”
Please note, that Native DB uses the current key, so please set a proper key first to be DB independent.
=> CORRECT but The How To is specially for SQL.

“WRONG: This will give you 1 record at a time. \IF TheTable.FIND('-') THEN\REPEAT\ ...\UNTIL TheTable.NEXT = 0;”
I’m not sure, but I don’t’ think so. At least it used to it returns less records than the FINDSET command, but the number of records used to be variable. I guess it depended on recordssize, but it was typically around 25 records. I’m quite sure it used to say this in the Client Monitor as well, but for some reason it doesn’t. Using the profiler it seems it is doing a TOP 1.
=> I think it does, otherwise what is the reason of the existence of the FINDSET command (and why did they change the recordset from 500 to 50 in NAV2009?

“CORRECT: This will give you the first N records in one go, and after that 1 by 1.\IF TheTable.FINDSET THEN\REPEAT\ ...\UNTIL TheTable.NEXT = 0;”
Again: No – I don’t think so. I tried doing a FIND(‘-‘) followed by 100 times NEXT. This resulted in a SELECT (without TOP or FAST) and four times a FETCH API_CURSOR. This is almost as I expected, but I don’t know how we get to see the actual SQL statements it executes.
=> The actual SQL statement is "FETCH API_CURSOR". NAV opened a cursor at the beginning and now it is fetching the records 1 by 1. This means that NAV each time sends a command to fetch the next record and then SQL sends 1 record. With FINDSET, SQL sends immediately 500 (or 50) records on FINDSET command and after those record, it goes 1 by 1.

“First and for all some general advice for writing to the DB\...”
You forgot the most important one: “Never ever do a COMMIT unless all data is consistent and it is acceptable that only the queued changes are committed, since an error (logical or physical) can appear half a millisecond later and thereby stopping the remaining of the code.”
=> I found that sooooooo obvious I didn't write it..... But you are right!

“CORRECT in case most of the time you need to change the record to avoid a second SELECT in SQL..”
I guess you forgot the “IF to be changed THEN BEGIN” in the code below.
=> OOOOPPPPPPPPPPSSSSSS

“Method 2:\// now loop the temptable. This code retrieves the record again (with EXCLUSIVE-LOCK!) and then changes it. If the record was changed between the first read and now, it will NOT generate an error on the MODIFY because you will have received the latest version.”
I really don’t like this one. You risk the conditions from the first loop isn’t fulfilled anymore and thereby making inconsistent data. You should at least repeat the “IF (Record has to be changed) THEN BEGIN” again.
=> TRUE, Depending on the need it might be necessary to add the test again or not at all.

“But in C/AL we can't use SQL-statements (or we must use ADO), so there is another way. I advice to always use this way to do some summing in C/AL.”
You have an confusing indention under your “IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group") THEN ;” All the lines below should be 4 chars to the left :-)
=> Actually, the indentation is correct. The idea is that I try to use a key if it exists. If it doesn't exist, I try the second key. If that second key doesn't exist, never mind about keys. Just use the primary key.

I could also rewrite that part as follows, but I think the original is better:

tmpGLEntry.reset;

// try to get a good key for the filters
CASE TRUE OF
tmpGLEntry.SETCURRENTKEY("Gen. Bus. Posting Group"): BEGIN END;
tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group"): BEGIN END;
END;

// I filter on the records for which I want to group the records
tmpGLEntry.setrange("Gen. Bus. Posting Group",recGLEntry."Gen. Bus. Posting Group");

navuser1 wrote:

Dear all,

Which will give the better performance ? Please explain if possible.

IF TheTable.FINDFIRST THEN
     BEGIN
       ....................;
       ....................; 
     END;

OR

IF TheTable.FIND('-') THEN
     BEGIN
       ....................;
       ....................; 
     END;

Thanks with regards!

To summarize all other posts (and my how-to) in 1 line for your question:
If you want 1 record (if it exists), use FINDFIRST. FINDfirst does NOT create a cursor in SQL. FIND('-') creates a cursor in SQL. Cursors ARE SLOW.

pdj wrote:

I also agree that a lot of the confusion might be related to translation issues, mine included. We don't all speak nor write English flawlessly. :oops:

And even if we all did. About which English do we talk. A word can mean different things in different versions of English. Take a look at this one: http://www.mibuso.com/dlinfo.asp?FileID=1106. Specially the slides with the different meanings of words between UK and USA.

Alex Chow wrote:

Instead of saying WRONG or CORRECT, why not use EFFICIENT and INEFFICIENT?

Wrong is such a strong word for cases where the code needs to be as it is.

That is true, but sometimes you need something strong that remains in memory.
I have had an example some years ago: someone used always SETFILTER also when he could use SETRANGE (Native DB). The person remembered that one was more efficient than the other. But that person remembered (wrongly) that SETFILTER was more efficient then SETRANGE. And in reality it is SETRANGE that can be more efficient but never the other way around.