Is it good if we use lookup function in for each where clause? Will it cause performance issue? Please help to understand and provide the example how to avoid.
define variable cGroupID as character no-undo.
for each <table> no-lock where lookup(cGroupID,<table.fieldname>) <> 0:
**do something...**
end.
note - table field name can have multiple comma separated group
A lookup function cannot use an index, so yes you can introduce sub-par performance which can be avoided. See the following example using the sports database which will read all records when using lookup and will limit the set to what meets the criteria when breaking the parts out to individual query parts:
def var ii as int no-undo.
def var iread as int64 no-undo.
function reads returns int64:
find _file where _file._file-name = 'customer' no-lock.
find _tablestat where _tablestat._tablestat-id = _file._file-number no-lock.
return _tablestat._tablestat-read.
end function.
iread = reads().
for each customer
where lookup( customer.salesrep, 'dkp,sls' ) > 0
no-lock:
ii = ii + 1.
end.
message 'lookup - records:' ii 'read:' reads() - iread.
ii = 0.
iread = reads().
for each customer
where customer.salesrep = 'dkp'
or customer.salesrep = 'sls'
no-lock:
ii = ii + 1.
end.
message 'or - records:' ii 'read:' reads() - iread.
https://abldojo.services.progress.com/?shareId=6272e1223fb02369b2545bf4
Your example however seems to be performing a reverse lookup, ie the database field contains a comma separated list of values, which seems like not obeying the basic rules of database normalization.
If you want to keep the list in a single field, adding a word index on your comma separated field may help. You can then use contains.