mibuso.com

Microsoft Business Solutions online community
It is currently Thu Apr 24, 2014 10:55 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 57 posts ]  Go to page Previous  1, 2, 3, 4  Next
Author Message
 Post subject: Re: How to work with record-variables?
PostPosted: Thu Jul 30, 2009 4:35 pm 
Offline

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

I have a question about GET vs. FINDFIRST.

Quote:
GET: This command is the best to use if you want to search the record using its primary key. You can also filter on the primary key and use FINDFIRST. The SELECT-statement sent to SQL is the same. But the GET requires less coding and is easier to read. No RESET-SETCURRENTKEYSETRANGE are needed. The GET does NOT EVEN consider them.

I have read somewhere that the the GET method only locks 1 record and the FINDFIRST method also locks the record before and the record after the fetched record.

Can you confirm this?

Best Regards,

_________________
Reijer Molenaar
Freelance NAV Developer
Object Manager
TVblik


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

Joined: Wed Nov 24, 1999 8:01 am
Posts: 575
Location: Aalborg,Denmark
Country: Denmark (dk)
DenSter wrote:
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.

That's also how I read it. My question is regarding the usage of FIND('-') instead of FINDSET.
I don't understand why anyone would use FIND('-') in these situations after having read Krikis description.

_________________
Regards
Peter


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

Joined: Wed Nov 24, 1999 8:01 am
Posts: 575
Location: Aalborg,Denmark
Country: Denmark (dk)
reijermolenaar wrote:
I have read somewhere that the the GET method only locks 1 record and the FINDFIRST method also locks the record before and the record after the fetched record.

I'm quite sure about the GET. But I would expect the FINDFIRST locks the found rec and the previus rec. Simple to ensure it still is the first rec in the range when comitting. I don't see why it should lock next rec as well. But you should test it and let Kriki know, so this point could be added to the recommendations.
Mark Brummel has made a few posts about locking in NAV right here.

_________________
Regards
Peter


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

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7966
Location: Howell, MI
Country: United States (us)
pdj wrote:
That's also how I read it. My question is regarding the usage of FIND('-') instead of FINDSET.

You weren't asking a question about FINDSET vs FIND('-'), you were correcting Andri. Andri was replying to a message by Navuser1, who was comparing FINDFIRST and FIND('-'), he wasn't saying not to use FINDSET. He was saying "if you are going to use FIND('-'), only use it when you need to loop". Whether to use FIND('-') or FINDSET was not being discussed.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


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

Joined: Wed Nov 24, 1999 8:01 am
Posts: 575
Location: Aalborg,Denmark
Country: Denmark (dk)
DenSter wrote:
You weren't asking a question about FINDSET vs FIND('-')

Correct. I should have been clearer.

DenSter wrote:
Andri was replying to a message by Navuser1, who was comparing FINDFIRST and FIND('-'),

No, Navuser1 was asking if you should use FIND('-') or FINDFIRST when you need to find just one record.
Andri answered him correctly (FINDFIRST), but also mentioned that FIND('-') should be used when looping.
Navuser then quoted all of Andris answer and concluded he should use FIND('-') when needing to find just one record??

This frustraded me, and I tried to direct both of them to the sections in Krikis article describing where they each went wrong.

DenSter wrote:
Whether to use FIND('-') or FINDSET was not being discussed.

Correct. But I think it would be wrong to accept Navuser1's remark about using FIND('-') for loops.

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:

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Fri Jul 31, 2009 12:59 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7966
Location: Howell, MI
Country: United States (us)
And meanwhile, everyone means exactly the same :mrgreen: I would LOVE to sit down with this group for beers

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Fri Jul 31, 2009 9:49 am 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 575
Location: Aalborg,Denmark
Country: Denmark (dk)
Oh yeah! I'm sure we could handle this in the popular beer summit style 8)

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: How to work with record-variables (version 2)?
PostPosted: Sat Aug 01, 2009 4:20 pm 
Offline
Site Admin

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

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 Aug 02, 2009 2:25 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7649
Location: Milan
Country: Italy (it)
pdj wrote:
>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?

The FAST is indeed old. Microsoft implemented it in the beginning, but it did not work out as expected. I don't know the details about it. But I know that if you have to loop FINDSET is better then FIND('-') except in some cases.


pdj wrote:
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.

You're right. In my document it is correct (I checked my document). Now I noticed that while converting it in HTML, something went wrong.

_________________
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: Sun Aug 02, 2009 2:47 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7649
Location: Milan
Country: Italy (it)
navuser1 wrote:
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!


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.

_________________
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: Sun Aug 02, 2009 2:51 pm 
Online
Site Admin
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Sun Nov 07, 1999 8:01 am
Posts: 3455
Location: Wilrijk
Country: Belgium (be)
kriki wrote:
You're right. In my document it is correct (I checked my document). Now I noticed that while converting it in HTML, something went wrong.

I have corrected the indentation of the example code in this How To article.

_________________
No support using PM or e-mail - Please use this forum.
Search is your friend || Mark your Topic as SOLVED (= green checkmark) when your question is answered || Read the Forum Rules before making a posting

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


Top
 Profile  
 
 Post subject: Re: How to work with record-variables?
PostPosted: Sun Aug 02, 2009 3:11 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

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

_________________
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: Sun Aug 02, 2009 3:25 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7649
Location: Milan
Country: Italy (it)
reijermolenaar wrote:
Hi Kriki,

I have a question about GET vs. FINDFIRST.

Quote:
GET: This command is the best to use if you want to search the record using its primary key. You can also filter on the primary key and use FINDFIRST. The SELECT-statement sent to SQL is the same. But the GET requires less coding and is easier to read. No RESET-SETCURRENTKEYSETRANGE are needed. The GET does NOT EVEN consider them.

I have read somewhere that the the GET method only locks 1 record and the FINDFIRST method also locks the record before and the record after the fetched record.

Can you confirm this?

Best Regards,

I did some checking (after also rereading Mark's blog : http://dynamicsuser.net/blogs/mark_brummel/archive/2009/06/18/tip-7-sql-range-locks.aspx and http://dynamicsuser.net/blogs/mark_brummel/archive/2009/06/13/tip-6-find-locked-records.aspx ).

I did some tests with GET,FINDFIRST,FIND('=') and locking and I noticed ALL 3 behave the same way! There is no difference in locking between them. But remember that I used them to get exactly 1 record using the primary key.
Like Mark explains in his blogs, it is possible to lock a range of records, but it depends on the filters.

_________________
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 (version 2)?
PostPosted: Sun Aug 02, 2009 6:55 pm 
Offline

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

They probably changed the locking meganism in the newer versions of NAV.
Good to hear.
Thanks!

_________________
Reijer Molenaar
Freelance NAV Developer
Object Manager
TVblik


Top
 Profile E-mail  
 
 Post subject: Re: How to work with record-variables (version 2)?
PostPosted: Mon Aug 03, 2009 5:42 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Fri Jun 11, 2004 9:05 pm
Posts: 4990
Location: Los Angeles, CA
Country: United States (us)
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.

_________________
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work

Getting Started with Dynamics NAV 2013 Application Development = my book


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 57 posts ]  Go to page Previous  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: