Index selection differs between FIND and FOR EACH - Forum - OpenEdge Development - Progress Community

Index selection differs between FIND and FOR EACH

 Forum

Index selection differs between FIND and FOR EACH

This question is answered

We have a table called wft_activity_log with a non-unique index on process_log_obj and status_key.

Now when I have the following code code and do a XREF compile (only checked under OE11.3):

FIND FIRST wft_activity_log NO-LOCK
    WHERE (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Active")
       OR (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Busy").

FOR EACH wft_activity_log NO-LOCK
  WHERE (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Active")
     OR (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Busy"):

   DISPLAY wft_activity_log.
END.

The FIND FIRST surprisingly does a WHOLE-INDEX scan
SEARCH mipdb.wft_activity_log xPK_wft_activity_log WHOLE-INDEX

But the FOR EACH is using the correct index (2 times):
SEARCH mipdb.wft_activity_log xIE1_wft_activity_log

So it seems the FIND and FOR EACH doesn't follow the same rules?

Anyone had the same behaviour before? And maybe an explanation of why the compiler does this?

Verified Answer
  • FIND statements can only use a single index. With the OR breaking it up into two brackets on the index you want, both are essentially non-selectable so you default to the primary index and a full search.

    That's the reason many advocate using "FOR FIRST" instead of "FIND FIRST".

    Mike Lonski

    Allegro

    Michael Lonski
    President
    Allegro has the Progress 4GL Programmer Experts You Need
    http://AllegroConsultants.com/progress-4gl-applications
    Coding Smart - ADM2 "How To" now on iTunes and the B&N Nook

All Replies
  • FIND statements can only use a single index. With the OR breaking it up into two brackets on the index you want, both are essentially non-selectable so you default to the primary index and a full search.

    That's the reason many advocate using "FOR FIRST" instead of "FIND FIRST".

    Mike Lonski

    Allegro

    Michael Lonski
    President
    Allegro has the Progress 4GL Programmer Experts You Need
    http://AllegroConsultants.com/progress-4gl-applications
    Coding Smart - ADM2 "How To" now on iTunes and the B&N Nook

  • http://knowledgebase.progress.com/articles/Article/21098

    OpenEdge documentation bug PSC00312958 was logged to stress that even when the WHERE clause is the same, different query statements (FIND, FOR, Open QUERY, etc) may select different indexes.

     

  • It doesn't surprise me the FIND is doing a whole-Index scan. It does surprise me that FOR EACH does not. :)

    I never use OR in the where clause (except maybe with a temp-table and then only when I am 100% sure that Hell would freeze over before there would be more than a couple of records in the temp-table). I rather do two separate statements. FIND FIRST WHERE (case a). IF NOT AVAILABLE FIND FIRST (case b). Similar with FOR EACH - extract the body of the loop and do two loops...

    I'd be curious to see whether that index would be used, if you extract the "wft_activity_log.process_log_obj = 1" and just have the "status_key part" within the OR. I forgot the exact rule, but it could be that it would just ignore the OR part of the WHERE but use the first part...

    WHERE wft_activity_log.process_log_obj = 1

    AND ( wft_activity_log.status_key = "Active"

        OR wft_activity_log.status_key = "Busy"

           )

    Thomas Hutegger

    tmh@smat-consulting.com

    SMAT-Tools - Web-Apps with RollBase simpleness, ABL flexibility and power

  • It would need to be:

    WHERE (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Active")

       OR (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Busy")