use of FIND FIRST

[BruceD Lindberg]

Does any one know of adverse effects by always using the FIRST as part of the FIND statement. When you are matching the unique and primary index of a table in your FIND statement and you use the FIRST clause, does that hamper performance?

FIND FIRST customer where customer.cono = 1
and customer.custno = 100
no-lock no-error.



the fields of the index are cono and custno and the index is primary & unique. Is this making Progress do more work?

 

[bulklodd]

Is this making Progress do more work?

No, it isn't, quite opposite. In general FIND without FIRST does more work trying to detect whether the found record is unique.

 

[BruceD Lindberg]

proof?

Is there a way to proove it?

 

[bulklodd]

Is there a way to proove it?

Of course, there is I took it from "The Engine Crew Monographs" it's one of best sources in that area. I think you should look at it more attentively:

http://www.peg.com/techpapers/monographs/query/query.html#3.8

3.8 Unique FIND queries

A FIND query without a NEXT, PREV, FIRST or LAST performs an operation which is different from other FIND operations: it not only locates a record, but also ensures that it is the only record which satisfies the query. If more than one record is found, it returns an error. In order to achieve this, PROGRESS must find the first record, and then look for the next record and not find one. The 2 nd step can be very costly in some cases: for example, if the city field is not indexed, the query

find customer where (city = "Boston")

​

requires PROGRESS to scan the customer table until such a record is found, and then continue scanning the rest of the table to ensure that no other such record exists.
Whenever possible, the verification is done by the server, but if the server cannot execute the query, the client must verify the record's uniqueness in addition to performing the selection.
​

HTH

​

 

[Lee Curzon]

As Bulklodd says, FIND FIRST is faster (noticeable only in a big loop - that's how you prove it).

See also:

KB P36144
Title: "Is there a difference between a FIND and a FIND FIRST when the index is unique?"

http://tinyurl.com/e3ckm

I don't agree with the advice there though. I would use FIND in case of later changes, and only use FIND FIRST if performance became a problem.

Lee

 

[BruceD Lindberg]

responses

thanks for the respones.

I have a co-worker who ALWAYS uses the FIRST in his FIND statements. Most of the time its on a Primary and Unique index match. I didn't know how to respond. I feel like critizing because the FIRST is used for a different purpose. Of course, there is always a NO-ERROR with it and is followed by an IF AVAILABLE (buffer) test. Well, it will never be in error especially with the FIRST clause (unless it doesn't find one) but I didn't really know. So it's actually faster to use the FIRST clause!; but you better be sure of how your searching the index. I still think it's a bad practice.

 

[mpowell_esq]

Find???

Greetings,
Just a thought here. FIND is old method / behaviour. I can appreciate that in some instences it may be the appropriate method. Though I have been within the PSC community now for a while (approx 10 years) and concluded this. The query method is now the given-standard-behaviour. Records are retrieved using queries, this makes third party languages applicable to access the no 1 database where Oracle is soo much harder.
Define your query with appropriate where clause and open the query-name.