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?
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?