mibuso.com

Microsoft Business Solutions online community
It is currently Tue Sep 02, 2014 1:30 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 57 posts ]  Go to page 1, 2, 3, 4  Next
Author Message
 Post subject: How to work with record-variables (version 2)?
PostPosted: Sun Jul 26, 2009 10:43 am 
Offline
Site Admin

Joined: Sun Nov 07, 1999 8:01 am
Posts: 2054
Location: Wilrijk, Belgium
Country: Belgium (be)
How to work with record-variables?

http://www.mibuso.com/howtoinfo.asp?FileID=22

Discuss this How To here.


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Sun Jul 26, 2009 11:25 am 
Offline

Joined: Fri Mar 30, 2007 12:50 pm
Posts: 215
Location: Nieuwe Niedorp
Country: Netherlands (nl)
Thanks Kriki!

A real eye opener. =D> =D>

Never realised that the following code will do two select statements:
Code: Select all
TheTable.GET(...);
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);

And the following only one!
Code: Select all
TheTable.LOCKTABLE;
TheTable.GET(...);
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);

I will keep it in mind from now on!

_________________
Reijer Molenaar
Freelance NAV Developer
Object Manager
TVblik


Top
 Profile E-mail  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Mon Jul 27, 2009 11:49 am 
Offline

Joined: Tue Jul 17, 2007 7:20 am
Posts: 593
Country: Indonesia (id)
Thanks for a Good post, Kriki!

I want to ask you, what is meant by 'open a cursor in SQL'?

Thanks.

_________________
Regards,
Andwian


Top
 Profile E-mail WWW  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Mon Jul 27, 2009 1:09 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7689
Location: Milan
Country: Italy (it)
SQL Server works on sets of records and not record by record.
NAV on the other hand works record by record.
SQL must emulate the record by record approach of the native DB. How can SQL do that : by using cursors.
The problem with cursors is that they are very heavy for SQL because SQL has to maintain them while working on it. So if you can avoid it (by using FINDFIRST,FINDLAST,ISEMPTY) you should do it.
If you ask pure SQL specialists about cursors, they will say to you NEVER to use them because they are very bad for performance (and that is true), but NAV doesn't have any other possibility.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Mon Jul 27, 2009 1:28 pm 
Offline

Joined: Wed Jun 04, 2008 12:37 pm
Posts: 0
Location: Florence
Country: Italy (it)
Thank you, kriki!


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Mon Jul 27, 2009 5:02 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 580
Location: Aalborg,Denmark
Country: Denmark (dk)
Great post Kriki.
I’ll start by saying I also learned something reading your entry. I didn’t know that a CLEAR(Rec) also respected InitValue properties. And that an array of temporary records only resulted in ONE temporary table. However; I have a FEW comment ;-)
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 :-)
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.

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…”

“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.

“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.

“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.

“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.”

“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.

“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.

“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 :-)

But besides these issues I think it is a very good walkthrough which I’ll show my co-workes as well. Keep up the good work. =D>

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Mon Jul 27, 2009 5:58 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7689
Location: Milan
Country: Italy (it)
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");

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Mon Jul 27, 2009 9:25 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 580
Location: Aalborg,Denmark
Country: Denmark (dk)
Thanks for the updates, but I still have a few comments:

>No. of records returned by FIND(‘-‘)
You might be right, but I will still claim that it used to do a FAST 25 in some older versions. :-)

kriki wrote:
=> 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.

Have you proved this by testing? What is wrong with my test with 100 x NEXT resulting in only four FETCH API_CURSOR?

kriki wrote:
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:

No, the indention it IS wrong. Otherwise the ”END” before the “UNTIL recGLEntry.NEXT = 0;” would be only 2 chars different and not 6 as now.
The code you made is fine, but the indention confused me a bit. If I only look at the indention (and ignore ; and BEGIN END) it actually only does something if none of the keys exists.

PS: My tests were done with NAV5SP1-Upd1.

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Tue Jul 28, 2009 5:00 am 
Offline

Joined: Tue Jul 17, 2007 7:20 am
Posts: 593
Country: Indonesia (id)
Hi Kriki,

Thank you again for your clear explanation. It really help. :thumbsup:


Top
 Profile E-mail WWW  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Wed Jul 29, 2009 11:15 am 
Offline

Joined: Wed Mar 19, 2008 9:08 am
Posts: 961
Location: XYZ
Dear all,

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


Code: Select all
IF TheTable.FINDFIRST THEN
     BEGIN
       ....................;
       ....................;
     END;


OR

Code: Select all
IF TheTable.FIND('-') THEN
     BEGIN
       ....................;
       ....................;
     END;




Thanks with regards!

_________________
Now or Never


Top
 Profile E-mail  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Wed Jul 29, 2009 11:50 am 
Offline

Joined: Tue Jul 17, 2007 7:20 am
Posts: 593
Country: Indonesia (id)
Hi navuser1,

Based on Kriki's post, I think it depends on the conditions.

FINDFIRST: Use it when you only need ONE record.
FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

If you want only check whether there are any records, prefer to use the best practice:
Code: Select all
IF NOT TheTable.ISEMPTY THEN
     BEGIN
       ....................;
       ....................;
     END;


Dear all, please CMIIW :oops:


Top
 Profile E-mail WWW  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Thu Jul 30, 2009 7:01 am 
Offline

Joined: Wed Mar 19, 2008 9:08 am
Posts: 961
Location: XYZ
Andri Arie Wianto wrote:
Hi navuser1,

Based on Kriki's post, I think it depends on the conditions.

FINDFIRST: Use it when you only need ONE record.
FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

If you want only check whether there are any records, prefer to use the best practice:
Code: Select all
IF NOT TheTable.ISEMPTY THEN
     BEGIN
       ....................;
       ....................;
     END;


Dear all, please CMIIW :oops:


Dear Andri

FINDFIRST,FIND('-'),ISEMPTY are clear to me. But I have seen the code(given below) in the base database in many places. I think that the function FIND('-') is the best for that particular cases. Please CMIIW.

Code: Select all
IF  TheTable.FINDFIRST THEN
         BEGIN
             ....................;
             ....................;
         END;

_________________
Now or Never


Top
 Profile E-mail  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Thu Jul 30, 2009 3:42 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 580
Location: Aalborg,Denmark
Country: Denmark (dk)
navuser1 wrote:
FINDFIRST,FIND('-'),ISEMPTY are clear to me. But I have seen the code(given below) in the base database in many places. I think that the function FIND('-') is the best for that particular cases. Please CMIIW.

Code: Select all
IF  TheTable.FINDFIRST THEN
         BEGIN
             ....................;
             ....................;
         END;

Kriki clearly say you should use FINDFIRST to avoid generating a SQL cursor. What makes you say that FIND('-') is better?
Check the section called You want ONY the first/last record (if it exists), but NEVER more.

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Thu Jul 30, 2009 3:45 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 580
Location: Aalborg,Denmark
Country: Denmark (dk)
Andri Arie Wianto wrote:
FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

Which part of the You want ALL records in ASCENDING order section don't you agree with? And why??

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Thu Jul 30, 2009 4:19 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7990
Location: Howell, MI
Country: United States (us)
pdj wrote:
Andri Arie Wianto wrote:
FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

Which part of the You want ALL records in ASCENDING order section don't you agree with? And why??

Peter I think this is a translation issue, I really don't think they disagree with anything. Andri is from Indonesia and his (her?) English may not be as good as yours. The way I read it is "you must only use it when you need to loop through records", as opposed to needing only one record.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 57 posts ]  Go to page 1, 2, 3, 4  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: