Functions messing up with the optimizer calc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

julio@prodesmaq.com.br - 31 Aug 2004 16:21 GMT

Do functions like trim,upper,lower,etc.. on sqls instructions mess up with
the optimizer cost calculations or best path definition?

I'm having a serious performance problem in my production environment, and
I think the problem is:

in a sql like that:
select text1 from table1
where trim(text1='T111')
I got a sequential scan on the table.

but if I change the command to this:
select text1 from table1
where text1[1,4]='T111'
I got an index path scan.

The table I'm querying have more than a thousand rows and a bunch of
columns. This query is driving my server nuts!! Sometimes I get uptime
values over than 9.0 !! Often these values are about 0.9.

Is there any workaround (via parameters on env vars) that I can do?

PS: INFORMIX 7.31UD1 - Linux - Redhat 8.0
OPTCOMPIND=2 (onconfig)

Thanks in advance,
Julio Cesar
sending to informix-list

Reply to this Message

Art S. Kagel - 31 Aug 2004 17:05 GMT

On Tue, 31 Aug 2004 11:21:34 -0400, julio wrote:

> Do functions like trim,upper,lower,etc.. on sqls instructions mess up with
> the optimizer cost calculations or best path definition?

Yes they do. Also, the engine cannot use an index (except in 9.xx where you
can have a functional index defined using the function you want to search
with) to compare to the results of a function.

> I'm having a serious performance problem in my production environment, and I
> think the problem is:
>
> in a sql like that:
> select text1 from table1
> where trim(text1='T111')

I assume this should be: trim(text1) = 'T111'

> I got a sequential scan on the table.
>
> but if I change the command to this:
> select text1 from table1
> where text1[1,4]='T111'
> I got an index path scan.

Why not just do:

select text1
from table1
where text1 = 'T111';

?? If the text1 column ends in spaces, the engine will automagically generate
the correct trailing spaces to perform the match. The trim is unneccessary
just to remove trailing spaces for comparison. Is your question more general
and I'm missing it?

> The table I'm querying have more than a thousand rows and a bunch of
> columns. This query is driving my server nuts!! Sometimes I get uptime
> values over than 9.0 !! Often these values are about 0.9.
>
> Is there any workaround (via parameters on env vars) that I can do?

No, except to make the WHERE clause comparisons in such a way as to avoid
table scans when possible. Like the transform above or replacing:

AND substr( col1, 4 ) = 'AbCd'
with:
AND col1 matches 'AbCd*'
or:
AND col1[1,4] = 'AbCd' as you did above.

> PS: INFORMIX 7.31UD1 - Linux - Redhat 8.0 OPTCOMPIND=2 (onconfig)

Art S. Kagel

> Thanks in advance,
> Julio Cesar
> sending to informix-list

Reply to this Message