Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    3

    Question Unanswered: Optimization of complex join query

    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.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    May 2009
    Posts
    3

    Question Optimization of the join query

    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,

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    May 2009
    Posts
    3

    Question Optimization of complex join query

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •