Re: Performance issue with SQL regexp_like?

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 06/04/2019
09:30:13 AM:

The regexp_like is (Please ignore any typos.

left join source on Regexp_like(srcdta,
'[^A-Za-z0-9_@#$]'||trim(field)||'[^A-Za-z0-9_@#$]','i')

When I try to run it via interactive SQL., it runs for about 5 minutes.

I tried changing it to this to see if the performance was any better.

left join source on srcdta like '%'||trim(field)||'%'

This version returns in seconds. That is a huge difference!!!
Unfortunately, it finds lines that contain the value but may not be what

I

am after. For example, if I am searching for ACCT, this also finds

ACCT01.


I'm no expert in this stuff but it appears that the "problem"
with the first version is that SQL can't tell in advance what column to
search on and, thus, can't optimize the results using the appropriate
index(es) and such. The second version tells SQL to search on the SRCDTA
column and, thus, it can optimize that query better than the first. But,
otherwise, I have heard that the use of Regular Expressions in SQL does
cause more overhead.


Sincerely,

Dave Clark