CODEWITHSUNDEEP
openedgeprogress-4gl
Diego
Diego

Reputation: 57

is it the same to use MATCHES (* + "" + *) and no parameters in a FOR EACH in Progress 4GL?

So I made the following FOR EACH

FOR EACH insp_cd
WHERE insp_cd.status_ = 1
AND insp_cd.item MATCHES('*' + pc-itemPost + '*')
AND insp_cd.update_at < NOW:

So, when the pc-itemPost is "", should I avoid using the MATCHES? Like:

IF pc-itemPost = "" THEN DO:
FOR EACH insp_cd
WHERE insp_cd.status_ = 1
AND insp_cd.update_at < NOW:
...
END.
ELSE DO:
FOR EACH insp_cd
WHERE insp_cd.status_ = 1
AND insp_cd.item MATCHES('*' + pc-itemPost + '*')
AND insp_cd.update_at < NOW:

I know it's very slow because of the table scan, but I'd like to know if there is any difference. Thanks.

Upvotes: 0

Views: 383

Answers (1)

Tom Bascom
Tom Bascom

Reputation: 14020

Any time that you can avoid MATCHES you should do so.

Using an IF statement to choose branches that execute different static FOR EACH statements is one way to do it. Building dynamic queries based on similar logic would be another approach.

Whether or not your two queries are "different"? Sure, they are different. They have different WHERE clauses so their specific behavior (and performance) will depend on the index structure (which we don't know).

insp_cd.item matches “*” + pc-itempost + “*”

Can be very different from:

insp_cd.item = “”.

And logically it is not the same as omitting a check of insp_cd.item altogether. Logically maybe you’re attempting to exclude empty values? I’m not sure what the requirement is here.

If insp_cd.item is the first component of an index, or the second component after insp_cd.Status then a variation of this query using ‘ = “” ‘ will be much more efficient than one using MATCHES.

Back to avoiding MATCHES, at a high level:

  1. If there is no need for wild cards use "=". Equality matches are always preferred.

  2. If the wild card is at the end of the string use BEGINS.

  3. If the wild card is being used to signify a known list use a series of OR clauses or a LOOKUP() or build a temp-table to join in the query.

There are probably more ways to avoid MATCHES but these are the ones that spring to mind.

Upvotes: 3

Related Questions