Question IF CAN-FIND() THEN FIND...

[rzr]

Is it a better programming practice to do a CAN-FIND(Table...) and then FIND Table if record exists?

IF CAN-FIND(FIRST Customer WHERE Customer.CustNum =1 ) THEN
            FIRST Customer WHERE Customer.CustNum =1 NO-LOCK NO-ERROR.

Does this improve performance and/or decrease db read operations?

I have a simple code that does a FOR EACH (the query is correctly indexed) and create a temp-table. Within this FOR EACH there are ton's of FIND statements which when available will update some fields of a temp-table. I'm curious to know if I do a CND-FIND before the actual FIND then will it improve performance.

 

[Cringer]

I see that sort of code all over the place. I think the argument is that CAN-FIND doesn't actually put a record into the buffer (or something) and therefore is quicker. The trouble is, if there is a record you're finding it twice. I would never use this code because I think it's messy. Also, FIND FIRST is a big no-no anyway. If the record is really unique then the FIRST is redundant, if it's not unique, then using FIRST will not always get your the same record so is dangerous.

 

[rzr]

Yes, I understand the "FIRST" issue and will not be using it. All my FINDs are unique. I was only quoting an example here; sorry !
The thing that concerns me is that the FOR EACH processes few hundred thousand records and for many of these records the FIND statements "will fail"; Irrespective of whether the FIND fails or not I will still need to create the temp-table.

I'm not happy with the idea of having to go "FIND" records when they are not available.

BTW. I'm on 10.1C + Windows.

 

[Cringer]

Could you use OPEN QUERY, and have the finds as OUTER-JOINs?

 

[rzr]

Thanks Cringer.

I'll give this a try. But "few" of my FINDs are based out of data calculated from TEMP-TABLE records and not the main FOR EACH....
Maybe for these I will still keep them as a FIND and the other that can be linked to the FOR EACH I'll OUTER-JOIN them to the OPEN QUERY.

 

[tamhas]

Sounds like a use case for FIND ... NO-ERROR.

 

[rzr]

Tamhas, all FINDs are unique and have a NO-LOCK NO-ERROR.

 

[tamhas]

Then you can test IF AVAILABLE and cover the cases when the record doesn't exist.

 

[TomBascom]

CAN-FIND() dates from the stone-age when every single IO operation was a good reason to get a fresh cup of coffee and the database engine was less optimized.

And even then it didn't really make a lot of sense.

This block of code:

for each order no-lock:
  if can-find( customer no-lock where customer.custNum = order.custNum ) then
    do:
      find customer no-lock where customer.custNum = order.custNum.
      /* do stuff... */
    end.
end.

Should be slightly less efficient than this:

for each order no-lock:
  find customer no-lock where customer.custNum = order.custNum no-error.
  if available customer then
    do:
      /* do stuff... */
    end.
end.

because in the 1st block if the record is found you then have to re-find it to work with it.

Also, the first one will fail if another session deletes (or modifies the custNum of) the CAN-FIND() record after the CAN-FIND().

IMHO CAN-FIND() is only worth considering if the only action that you are going to take is in response to the FALSE result or if the action does not need the target record. If the target record is going to be used you should FIND ... NO-ERROR and then test AVAILABLE().

Personally I never use CAN-FIND(). I've not found a reason to.

 

[jongpau]

I believe that there is (or was, it may have been resolved) also an issue when using can-find on temp-tables where this sometimes will return a false while there actually is a record.

The only time I would personally consider using can-find is if that is really all I want to do: know there is a record there that meets the criteria but not needing to access its data. There should theoretically be a small performance benefit there because the record does not get moved into the buffer.

 

[Stefan]

I believe that there is (or was, it may have been resolved) also an issue when using can-find on temp-tables where this sometimes will return a false while there actually is a record.

This is generally a result of poor scoping

 

[jongpau]

Hmm I tried to find the KB related to it this morning but was unable to. I think the "solution" that Progress gave was "don't do it, we cannot guarantee that it works"

 

[rzr]

Thanks all for the comments and guidance.