Consultor Eletrônico

Status: Unverified

GOAL:

How to achieve a fast query with a 'ends' WHERE clause?

GOAL:

Is there any way to improve the performance of a query using a MATCHES '*string' WHERE clause?

FIX:

It is a diffused practice to achieve the ends WHERE condition by using:

FOR EACH customer WHERE myField MATCHES ("*progress.com")

However, as MATCHES does not use index information, this will scans the entire data table resulting in slow performances.
If performance on this query is a crucial point of your application it may be worth to trade off something on database size and on the application complexity in order to achieve this.

The idea is to to create a duplicate field for the field you wants to perform the 'ends' query. This duplicate field (we'll call it 'myInvertedField') will contain the same string of myField, but inverted.

So if myField = "Vito.Imburgia@progress.com"
myInvertedField will be = "moc.ssergorp@aigrubmI.otiV". This field, off course, should be indexed.

For maintain easily the business logic you can write a simple 4GL function that returns the inverted string, we'll call it INVERTED()

So your query

FOR EACH customer WHERE myField MATCHES ("*progress.com")

will be:

myInvertedEnds = INVERTED("progress.com").
FOR EACH customer WHERE myInvertedField BEGINS (myInvertedEnds).

Differently to the former, the latter query will use the index on myInvertedField and therefore will perform much better than this.

Off course this approach needs you to maintain a second field every time a record is either updated or created.