Index is ignored with "OR" in query

MarciaWalker

New Member
Hi, wonder if anyone can help me with this - it really has me stumped!

I have a table with around 500,000 records in it, let's call it "receivals". It has an index idx_location on a field called id_location and a descending index idx_receivalDate on a field called da_receival_date.

ETIME(YES).

FOR EACH ucd_receival NO-LOCK
WHERE ucd_receival.id_location = <value>
AND ucd_receival.da_receival_date = 11/19/2019:

END.
MESSAGE ETIME.

This runs quickly, taking 125 milliseconds, and using XREF I can see both indexes are being used. However, when I add an "OR" clause to the query, it slows down dramatically and only the first index gets used:

ETIME(YES).

FOR EACH ucd_receival NO-LOCK
WHERE ucd_receival.id_location = <value>
AND (ucd_receival.da_receival_date = 11/19/2019
OR ucd_receival.da_receival_date = ?):

END.
MESSAGE ETIME.

This takes around 180,000 milliseconds, sometimes more. Does Progress really ignore an index when there is an "OR" clause, and if that is the case, what is a better way to write this query?
 

Osborne

Active Member
As per rule 6/7 in this article slowness due to the bracketing resulting in reading all records:


Try this:

Code:
FOR EACH ucd_receival NO-LOCK
    WHERE (ucd_receival.id_location = <value> AND ucd_receival.da_receival_date = 11/19/2019) OR
          (ucd_receival.id_location = <value> AND ucd_receival.da_receival_date = ?):
 END.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I would add that using etime isn't a great measure of query efficiency as it can be affected by a variety of factors, regardless of the location (i.e. shared system or isolated) of your client and your database. Importantly, as you have noted, it can vary from run to run.

It is better to look at measures that are invariant with momentary changes in system or network load, like CRUD stats. Look at the data in the relevant records of _usertablestat and _userindexstat, as well as using the QryInfo log entry type.
 

MarciaWalker

New Member
Code:
FOR EACH ucd_receival NO-LOCK
    WHERE (ucd_receival.id_location = <value> AND ucd_receival.da_receival_date = 11/19/2019) OR
          (ucd_receival.id_location = <value> AND ucd_receival.da_receival_date = ?):
END.
Thanks very much, this works very well (although it will become complex as there are also more "OR" statements in the query). Essentially I will need to write one query for each combination of possibilities - not great for readability, but the speed improvement will make it well worth it.

Very interesting reading on the bracketing too, I was looking for exactly something like this but searching on the word index and not indexing, so I didn't find it!
 
Maybe you can do this a do like this
Code:
DEFINE VARIABLE listDate AS DATE EXTENT 2  NO-UNDO.
DEFINE VARIBALE cmpt AS INTEGER NO-UNDO.

ASSIGN
    listDate[1] = DATE("11/19/2019")
    listDate[2] = ? .
DO cmpt = 1 TO 2 :   
    FOR EACH ucd_receival NO-LOCK
        WHERE ucd_receival.id_location = <value>
        AND ucd_receival.da_receival_date = listDate[cmpt] :
        
    END.
END.
 
Top