Difference between FINDFIRST and Find(-)

vivek4121

Hello Everyone,

Can anybody help me in that, What's the exact difference between FINDFIRST and Find(-)?

In what scenarios its feasible using FINDFIRST and in what scenarios we need to use FIND(-)?

Thanks in advance.

bbrown

FINDFIRST returns only a single record. That being the first record of the current sort,that meets the filters set. FIND('-') returns a the matching records. FIND('-') would be used to begin a REPEAT..UNTIL loop. FINDFIRST would not be used with REPEAT..UNTIL.

vivek4121

I have used the below code snippets, both give the same result of '4' records in my case.

Using Find('-') :-

Customer.RESET;
Customer.SETRANGE(City,'London');
Customer.FIND('-');
MESSAGE(FORMAT(Customer.COUNT));


Using FindFIRST :-

Customer.RESET;
Customer.SETRANGE(City,'London');
Customer.FINDFIRST;
MESSAGE(FORMAT(Customer.COUNT));

bbrown

This would achieve the same results:

Customer.RESET;
Customer.SETRANGE(City,'London');
MESSAGE(FORMAT(Customer.COUNT));

The FIND or FIND('-') is not needed here. The COUNT is not reading the records returned. But rather goes back to the DB itself to "count" the records that match the filters set. That is why you get the same number of records.

jglathe

Hi Vivek,

in both cases you wouldn't need the find anyway... bbrown is right. findfirst returns one record and doesnt prepare sql for subsequent fetches (no cursor), find('-') opens a cursor, and is faster when you fetch in a repeat..until loop, for example.

with best regards

Jens

vivek4121

Can you provide any other code snippet, where I can find out the exact difference between them?

bbrown

Here's a couple simple examples:

In this first example, you need to retrieve and process all customers that belong to the RETAIL posting group.

Customer.RESET;
Customer.SETCURRENTKEY("Customer Posting Group");
Customer.SETRANGE("Customer Posting Group", 'RETAIL');
IF Customer.FIND('-') THEN
  REPEAT
    // do something with each record
  UNTIL Customer.NEXT = 0;

In the second example, you need to retrieve a single customer without having the primary key. The field you have is not enforced unique by the database, but logically is expected to be unique. Only 1 customer should have that tax id.

Customer.RESET;
Customer.SETCURRENTKEY("Tax Registration No.");
Customer.SETRANGE("Tax Registration No.",'123456');
IF Customer.FINDFIRST THEN
  // do something with this customer

Hope these help.

vivek4121

Yup, it will help a lot.

Thanks bbrown

vivek4121

But I have concern - In the First Example

Customer.RESET;
Customer.SETCURRENTKEY("Customer Posting Group");
Customer.SETRANGE("Customer Posting Group", 'RETAIL');
IF Customer.FIND('-') THEN
REPEAT
// do something with each record
UNTIL Customer.NEXT = 0;

Instead of using the "IF Customer.FIND('-') THEN" I can also use "IF Customer.FINDSET THEN".

So whats exactly the difference between FIND('-') and FINDSET?

bbrown

I'd thought about commenting on that, but decided to stay on the original topic. But since you bring it up:

The answer depends on a couple things. (1) What you intend to do with the Customer records. (2) How many records are in the retreived set.

If the retrieved set has a large number of records, the you are better to use FIND('-')

If the retrieved set has a small number of records, and you only need to read them, then you could use FINDSET (which is the default of FINDSET(FALSE,FALSE))

if the retrieved set has a small number of records, and you need to modify them, thne you could use FINDSET(TRUE, FALSE).

NOTE: There are of course other examples and situations, but just covering basics here.

The difference is in how/what NAV prepares for SQL statements and cursor behavior.

DenSter

The annoying part about this is that even when you use FINDFIRST, you can still start a loop, and it still works. The result is the same, so technically it doesn't matter which one you use. I've always advocated for the compiler to reject FINDFIRST before a loop. I see it all the time in code, even after all these years people just don't understand the difference, and it's almost impossible to see where the difference is.

Just to be clear: the COUNT command goes back to the database independently from the FIND command. FINDFIRST only gets one record, and then COUNT goes back and counts all records within your filter set, but it does it in the database, not in the recordset. FIND('-') gets all the records, and then COUNT goes back and counts them in the database. You cannot use COUNT to evaluate the results of FINDFIRST or FINDSET.

BUT, if you want to see the difference, you should run a SQL Profiler trace to see the SQL commands come in. FINDFIRST will generate a TOP 1 query, and once you start looping, it will generate another query to retrieve the data for a second time but now without the TOP 1 clause. When you use FIND('-'), or even better FINDSET, it only fires the one single query.

The difference is that when it generates only one query there is only one trip to the database. In the other case there are multiple trips to the database for the same purpose. This can have performance problems when you are talking about higher numbers of users, or when those users are running long and/or complicated processes.

bbrown

Running SQL Profiler against some examples is a good way to learn what is happening behind the scene. Often it's not about whether something will work or not, but which is the more efficient approach.

Another example of "works but not the best", is modifying records within a FINDSET(FALSE) loop.

Another point is where a transaction starts with the different 'find' commands. With the older FIND('-') approach, a transaction is stared on the INSERT,DELETE, or MODIFY. This is what many of us learn back in our original NAV developer training days. But with the newer (well not so new) FINDSET(TRUE), the transaction is started with that command.

vivek4121

Hi bbrown,

As you said above if the retrieved set has a small number of records, then you could use the FINDSET.

As according to my knowledge, the default recordset which FINDSET function will retrieve is 500
(which is the default caching value) which we can increase / chnage at the time of creating a new database.

Now if at the time of creating a new database, I didn't changed that recordset. Later on if I want to increase the caching of recordset, which the FINDSET function will retrieve for me, How can I do this?

Thanks
Vivek

jglathe

Hi Vivek,

the caching cannot be changed by changing the database settings? Anyway, as it is with these caching values, they have been constantly decreased from 500 in the first versions (3.70?) down to 50 in NAV2009R2. Afterwards, this parameter has disappeared. I would use findset only when I can be sure that my record set is well below 50, and only when it is used very often (like, for info panels with many screen updates). Since I can't be sure most of the time, I wouldn't use findset very often. Usual use cases are when you go over dimension entries for a record, or price selection, finding the UOMs, SKUs and suchlike.

with best regards

Jens

vivek4121

Hi Jens,

I checked in NAV2009SP1 as well, that the record set has been decreased to 50 from 500.

I totally agree with you. But after NAV2009R2 as you said this parameter has disappeared.

So the maximum number of recordset which FINDSET function will retrieve in later versions as well (afterwards NAV2009R2) is same 50? or How can we check the same in later versions as the parameter has been disappeared?


Thanks
Vivek

Torben_R.

In NAV 2013 the FINDSET always finds all the records within the filter.

So you use
FINDFIRST if you only want the first record within the filter.
FINDLAST if you only want the last record within the filter.
FINDSET if you want all the records within the filter.

The FIND(-) should not be used anymore.....

vivek4121

So there is no limit on caching the record set for FINDSET function in NAV 2013?
that's why the record set parameter has also been disappeared from New database creation dialog box I suppose?

bbrown

To clarify a few points:

The older commands (FIND('-'), FIND('+')) remain as valid choices in the proper situations.

The newer commands (FINDSET, FINDFIRST, FINDLAST) were not introduced until version 5.0 SP1. Their recommneded usage has not changed

The default record caching size for FINDSET was originally set to 500. It was reduced to 50 with the introducing of the Role-Tailored Client. This was becuase the caching happens at the service-tier and the setting must take into consideration all the active sessions. If you are only running Classic clients (NAV 2009/NAV 2009 R2) then it is recommended to push the setting to 500.

The parameter went away with NAV 2013 since the classic client is no longer supported.

vivek4121

Thanks bbrown.

bbrown

BTW - What version of NAV are you using?

vivek4121

I am using NAV 2009 SP1

Kowa

bbrown wrote:

The newer commands (FINDSET, FINDFIRST, FINDLAST) were not introduced until version 5.0 SP1.

Actually, they started in Version 4.0 SP1. Version 5.0 SP1 introduced indexed views to replace the SIFT-tables.