FWD » Core Development » Database

This is deferred work to understand the following pattern:

for first buf
             where lookup(buf.f1, 'a,b') > 0
               and buf.f2 = 'b'
               and buf.f3 = 'b'
               and buf.f4 = 'b'
               and buf.f5 = 'b'
               and buf.f6 = 'b'
               no-lock:
         end.

This works very fast in 4GL considering there is an index on (f1, f2, f3, f4, f5, f6), but it is slow in FWD because the leading component in the index is not checked for equality, but used with lookup. An index-information shows that this index isn't even used in OE. But somehow, on a table with >5M records, this works very fast in OE (<10ms) but very slow in FWD (~4s).

For more details, check #9692-38. The solution there was to replace the look-up with a sequence of OR checks (e.g. buf.f1 = 'a' or buf.f1 = 'b') in the legacy code. It is not always correct to do this replacement in FWD implicitly (consider the lookup field is not indexed or is very nested in the query).

I've seen this task an I thought of something. This:

for first buf
             where lookup(buf.f1, 'a,b') > 0

select * from buf where UPPER(buf.f1) = ANY(string_to_array(UPPER('a,b'), ','));

I agree with #9907-3. But I am more interested to find out how 4GL is able to make this work fast. INDEXED-REPOSITION doesn't show that an index is actually used for such construct (aka f1), so how is 4GL managing to work so fast?