FINDSET and FINDFIRST

souravb

I am using NAV 4.0 SP3 with SQL Server 2000. Is it better to use FINDFIRST with Repeat...Until OR FINDSET with Repeat...Until?. Please Advice.

Belias

enter c/al editor, press F1 and search findset in the online help, there, you'll see the correct use of these statements
anyway, NEVER use findfirst with loops, but sometimes find('-') is better than findfirst

kine

I think that there are many posts about that on the forum, it is documented in manuals, it is described on blogs.

garak

also you can find the quick reference in the download section.

ara3n

Belias wrote:

enter c/al editor, press F1 and search findset in the online help, there, you'll see the correct use of these statements
anyway, NEVER use findfirst with loops, but sometimes find('-') is better than findfirst

could you explain the scenario where fin('-') is better than findfirst.

Excluding loops off course.

Belias

ara3n wrote:

Belias wrote:

enter c/al editor, press F1 and search findset in the online help, there, you'll see the correct use of these statements
anyway, NEVER use findfirst with loops, but sometimes find('-') is better than findfirst

could you explain the scenario where fin('-') is better than findfirst.

Excluding loops off course.

Sorry, i mean better than findset... :oops:

ara3n

oh np. I've heard that as well.

I can't remember the scenario though.

Belias

FINDSET, find "n" records in the table, where "n" is the value of recordset property.
if the developer knows that the no. of retrieved records after a set of filters are bigger than recordset value, it's better to use find('-') because it will find all the resulting records.
In this way Nav and/or sql does not have to refill the cache after "n" reads (maybe the last sentence I wrote is not 100% correct)

ara3n

Oh yes, the number of records. They've changed it from 500 in 5.0 to 50 in 2009.
The mentioned that performance was better with 50. I wonder on how large of database did they do the test.

Belias

ara3n wrote:

Oh yes, the number of records. They've changed it from 500 in 5.0 to 50 in 2009.
The mentioned that performance was better with 50. I wonder on how large of database did they do the test.

I read in a post here in mibuso... here it is
http://www.mibuso.com/forum/viewtopic.php?f=32&t=30687&hilit=recordset

idiot

Belias wrote:

...
if the developer knows that the no. of retrieved records after a set of filters are bigger than recordset value, it's better to use find('-') because it will find all the resulting records.
...

How does the developer know?

ara3n wrote:

Oh yes, the number of records. They've changed it from 500 in 5.0 to 50 in 2009.
The mentioned that performance was better with 50. I wonder on how large of database did they do the test.

So what if it's > 50? The statement will be repeated?

Is this the path Nav is moving towards? Small databases? Non-uniformed/inconsistency in functions where developer of module A will use FIND('-') while developer of module B will use FINDSET?

kine

No. If you are iterating through ledger entries, you can assume that the count will be big. If you are iterating document lines, you can assume that there will be only some maximal count of the lines. That's the difference. There is more and more entries during time, there is still average same count of lines per document during time etc.

devu_13

Hi
If you are using FINDFIRST Then No. of records are retrive slowly as compare to FIND('-').
So use FIND('-') for better performance.

MBerger

devu_13 wrote:

Hi
If you are using FINDFIRST Then No. of records are retrive slowly as compare to FIND('-').
So use FIND('-') for better performance.

?? FINDFIRST does exactly as it says on the box...it retrieves the FIRST, and ONLY the first record it finds ( or none, of course ). if you need only 1 record, this performs much better than FIND('-') !

Why you should never use FINDFIRST in conjunction with a REPEAT..UNTIL NEXT loop is that this will in at least 2 DB-calls; as soon as it detects the NEXT statement, it will still have to retrieve the recordset. Using either FINDSET or FIND('-') is better in this case, because it will already have gotten multiplt records ( if any ) into memory.

DenSter

devu_13 wrote:

If you are using FINDFIRST Then No. of records are retrive slowly as compare to FIND('-').
So use FIND('-') for better performance.

You are getting things confused here. Read back through the replies in this thread to learn more about the difference between FIND('-') and FINDSET. Not FIRST, but SET.

FINDFIRST is not meant to be used when you need to loop through a set of records. FINDFIRST only retrieves one record, so when you do NEXT, the system now has to guess as to what you want to do, and since you're using the wrong keyword in the first place, it has to get more records, so it's another query, and the system slows down from there.

So yes, FIND('-') is faster than FINDFIRST for loops, because FINDFIRST is the wrong keyword to use in a loop to begin with.

<edit> sorry MBerger, I hadn't seen your reply yet :whistle: </edit>

MBerger

DenSter wrote:

<edit> sorry MBerger, I hadn't seen your reply yet :whistle: </edit>

*high fives Denster* no problem....maybe people will learn now

Belias

MBerger wrote:

DenSter wrote:

<edit> sorry MBerger, I hadn't seen your reply yet :whistle: </edit>

*high fives Denster* no problem....maybe people will learn now

i'm sure someone will ask for this again and again... maybe we should open a FAQ section?I'm gonna post in feedback, who knows...