Differences in using FINDSET, FINDFIRST and FIND('-')

jensthomsen · 2008-01-16

Hi
Could anyone explain the differences (mainly performance) in using
FINDSET, FINDFIRST and FIND('-') ? I know that there are some (especially on SQL) but what are the "guidelines"? Code examples would be great:-)

DenSter · 2008-01-16

FIND('-'): don't use this anymore, it really has not purpose anymore.

FINDSET: retrieves a set of records

FINDFIRST: retrieves only one record, the first one within the filter, sorted ascendingly

FINDLAST: retrieves only one record, the last one within the filter, sorted ascendingly

If you want to know if records exist within a certain filter, use ISEMPTY.

Check out the C/SIDE reference guide for more information, and code samples. If you need real life code samples, check out the posting codeunits in standard NAV.

stryk · 2008-01-16

Here some more explanations with examples:

http://www.stryk.info/PFG_79_81.pdf

(Taken from "The NAV/SQL Performance Field Guide", ISBN: 978-3-8370-1442-6, http://www.stryk.info/fieldguide.html (c) STRYK System Improvement)

Best regards,

Jörg

ajhvdb · 2008-01-16

Nice, is this book available as pdf?

bbrown · 2008-01-17

One difference between FIND('-') and FINDSET(TRUE) is when the transaction begins. With FIND('-') the transaction begins when the call to update the database (INSERT, MODIFY, DELETE) is made. With FINDSET(TRUE) the transaction begins with the FINDSET command.

Alex_Chow · 2008-01-17

I might also add that findset, findfirst, findlast are more typically used if you're using SQL database. This is implemented to enhance performance.

If you're using C/SIDE, find('-') will work fine.

kriki · 2008-01-17

Alex Chow wrote:

I might also add that findset, findfirst, findlast are more typically used if you're using SQL database. This is implemented to enhance performance.

If you're using C/SIDE, find('-') will work fine.

Some advice : ALWAYS use FINDSET,FINDFIRST,FINDLAST. Even if you work on a Navision-DB. At least if the customer decides to convert to SQL, this is already ok.

Alex_Chow · 2008-01-17

Yes, elmininate the use of FIND('-') as a habit is a good idea.

pdj · 2008-01-17

stryk wrote:

Here some more explanations with examples:
http://www.stryk.info/PFG_79_81.pdf

Nice, but in the very bottom you have an Wrong/Correct code. In the Correct section you have this statement:
Rec2.GET(Rec1.PrimaryKey);
Wouldn't it be just as good and even faster to simply:
Rec2 := Rec1;

Is there any reason to retrieve it from the db again?

stryk · 2008-01-17

ajhvdb wrote:

Nice, is this book available as pdf?

No.

It's only available as book, e.g. from Amazon, etc.

stryk · 2008-01-17

[quote="pdjNice, but in the very bottom you have an Wrong/Correct code. In the Correct section you have this statement:
Rec2.GET(Rec1.PrimaryKey);
Wouldn't it be just as good and even faster to simply:
Rec2 := Rec1;

Is there any reason to retrieve it from the db again?[/quote]
The direct assignment Rec2 := Rec1; is also fine; maybe even better as you save one query. I think I will change this example in the edition of the PFG

Stef-B · 2008-01-28

Question to the experts:
Is there a performance difference (assuming a correct key is chosen) between GET and FINDFIRST?

bbrown · 2008-01-28

Stef-B wrote:

Question to the experts:
Is there a performance difference (assuming a correct key is chosen) between GET and FINDFIRST?

GET only works with the primary key. FINDFIRST can be used with any key.

Stef-B · 2008-01-28

the question was, if there's a performance difference. Assuming a primary key is used.

Waldo · 2008-01-28

You can only compare the two statements when you search a value based on the primary key. The difference in statements will be:

GET : SELECT * FROM table WHERE PrimaryKey = Value
FINDFIRST : SELECT TOP 1 * FROM table WHERE PrimaryKey = Value

In this case, the TOP 1 is no necessary, so I would go for the GET.

Waldo · 2008-02-03

Thismight be interesting ...

.

Differences in using FINDSET, FINDFIRST and FIND('-')

Comments

Categories