Hi All,

I have a complex inner join query in informix, which is taking quite sometime to execute. I would like to get some help on how i can optimize this query.

The query is :

select t1.tName, t2.tName
from trail t1, trail t2, tpoint t1a, tpoint t1z, tpoint t2a, tpoint t2z
where t1.tName matches 'I0*'
and t1.tspec = 100
and t1.tType= 5
and t2.tspec = 3000
and t2.ttype= 28
and t1.tpaId = t1a.tpId
and t1.tpzid = t1z.tpId
and t2.tpaId = t2a.tpId
and t2.tpzid = t2z.tpId
and t1a.connectsTp = t2a.connetsTp
and t1z.connectsTp = t2z.connectsTp

I am trying to get t2.tname using t1.tname
This is taking around 25 sec when we have one t1 kind of rows in trail table and around 1lak rows for t2 kind of rows in trail table.

i have executed the same query in another environment , where it takes less time, due to the less amount of rows for t2 kind.
i also ran the query with SET EXPLAIN ON.

it shows nested loop join being used, and found that nested loop join hits the performance. But i am not sure how i can optimize this.

Please anyone help me with this.

Do you have your statistics updated?
Do you have any indexes?
You can try force use HASHJOIN with directives...but.. I don't believed this is the solution...

Hi,

Sorry for the late reply and thanks for the reply

I am not sure of the statistics. I will have to check them, not very sure of what i need to look for.

Unique Index is there on the tName and region on trail table, also index is there on the tp_a_id, tp_z_id. TPoint table also has index on tp_id and connects_to_tp.

I havent done a force hash as of now, will try and see if i can do that.

Is there a way i can change the way the query is written, so that i minimize the nested loops thats happening????
Thanks,

For minimize the nested loops , without know how your system works and what is the data in the tables will be very hardly and annoying ...

If your database is a development and small , run :
update statistics high for trail;
update statistics high for tpoint ;

This will update your statistcs.
There is the possibility , after update your statistics the informix stop to use nested loop and use hashjoins or your indexes.

If you want , you can force before, using directives: Optimizer Directives

Hi,

The query is slow on DIT box, its not development. So i am not sure if i can run update statistics there.

i tried force HASH join using directive on trail and Tpoint. The force hash on trail didnt work. The error was "AVOID_NL ( trail ) Hash-Join cannot be forced w/o equality predicate or with Complex Outerjoins"

Did a force hash join on tpoint, that worked but no improvement in performance.

thanks,
Deepthi