Which is faster:

[WayneFrank]

Which is faster:

IF CAN-FIND(crlink WHERE crlink.case# = caseb.case#
AND crlink.cr = YES
AND crlink.case# <> crlink.ldcs#)
THEN NEXT.

-- OR --

FIND first crlink WHERE crlink.case# = caseb.case#
AND crlink.cr = YES
AND crlink.case# <> crlink.ldcs#
no-lock:

if avail crlink THEN NEXT.


PROGRESS Version 9.1B

 

[nate100]

I would say that the can-find is faster as it does not have the overhead associated with the find statment.

 

[LarryD]

They will likely take the same amount of time.

From my understanding, the only time can-find is more efficient is if you use strictly fields in an index and only for equality matches. In that case the can-find will only read the index blocks, and no data blocks need be retrieved. As soon as you either a) use a field not in the index used or b) use something other than an equality match (in you case you used a 'not equals), then it must retrieve the data blocks, which is the same as a "find".

Some here do not like can-find (nor can-do and some of the other older functions), but YMMV.

Those with more knowledge on this can either correct me or clarify further.

 

[WayneFrank]

I changed my code to:

IF CAN-FIND(FIRST crlink WHERE crlink.case_yr = INTEGER(SUBSTR(STRING(caseb.yr),2,4))
AND crlink.case# = caseb.case#
AND crlink.cr = YES
AND NOT (crlink.case# = crlink.ldcs#)
NO-LOCK
USE-INDEX cscr)
THEN NEXT.

I used all equal conditions, and I used all the fields in the index. I have been told you have to use all the fields in the index in order to have it take advantage of the index.

The increase in speed was HUGE. It solved my problem. Thanks

 

[jdpjamesp]

the alternative would be something like

FOR first crlink WHERE crlink.case# = caseb.case#
                                   AND crlink.cr = YES
                                   AND crlink.case# <>  crlink.ldcs#
                          no-lock:
  NEXT OuterBlockName. 
END.

You have to name your outer block to make sure the correct one gets the next. See if that makes any difference. I would only use USE-INDEX in direst of emergencies. Progress is usually much better at selecting the right index if you let it.