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.
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.