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

jensthomsen

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

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

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

Nice, is this book available as pdf?

bbrown

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

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

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

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

pdj

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

ajhvdb wrote:

Nice, is this book available as pdf?

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

stryk

[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

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

bbrown

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

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

Waldo

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

Thismight be interesting ... .