Informix

Expand all | Collapse all

Informix 14 - ALTER JOIN -

  • 1.  Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 07:55 AM
    Edited by Miguel Carbone Fri July 23, 2021 08:34 AM
    INFORMIX OUTER JOIN - We upgrade server to Version 14.10 FC5 , we are facing a performance issues in queries who uses OUTER JOIN.   Comparing in the same machine / same configuration, between verions 12.10 FC14 and 14.10 FC5, queries who uses OUTER join are 4 times slower, others not, could be  faster or same.  Are there any new behavior in how version 14 deal with OUTER JOIN, any new Parameter to improve this performance.....

    ------------------------------
    Miguel Carbone
    miguel@mcsoftware.com.br
    SkypeID miguelcarbone
    ------------------------------


  • 2.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 08:10 AM

    Outer joins ? 

    Stupid question but did you check if update statistics was done under same conditions in both versions ?
    What does sqlexplain say on both versions ?
    What does sqltrace say ?
    Abraço



    ------------------------------
    [eric] [Vercelletto] []
    [Founder]
    [kandooerp.org]
    [Pont l'Abbé] [France]
    [+33 626 52 50 68]

    Disclaimer: My own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
    ------------------------------



  • 3.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 08:15 AM

    You will have checked but ... are the sqexplains identical ?

     

    Cheers

    Paul

     






  • 4.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 08:24 AM
    Edited by Miguel Carbone Fri July 23, 2021 08:34 AM
    yes, thats the problem... statistics and execution plan are the same....  the issues is becouse just queries with OUTER JOIN are slower.... others are faster


    sorry , did a mistake writing the title... is OUTER JOIN not ALTER JOIN

    ------------------------------
    Miguel Carbone
    ------------------------------



  • 5.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 08:35 AM

    Presumably the upgrade was on the same machine.

    O/S?

    DIRECT_IO in use or not? Was it before? Number of CPU VPs if in use? Number of AIO VPs if not in use?
    "OUTERS" will always read more than other queries (well, sort of ... cartesian joins will do loads of reads :) )



    ------------------------------
    Jon Ritson
    ------------------------------



  • 6.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 08:42 AM
    Jon,

    Yes,  we are using the same configuration, we are using DIRECT IO, 34 CPUVP´s (same before) - 3 AIO VPs (same before)... Operational System was Red Hat Linx 6.8 and Now RedHat 8.1.


    ------------------------------
    Miguel Carbone
    ------------------------------



  • 7.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 08:46 AM

    VMs in the mix somewhere ?

     






  • 8.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 09:04 AM
    Paul, We are using VMWARE... in both enviroments....

    ------------------------------
    Miguel Carbone
    ------------------------------



  • 9.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 09:54 AM
    Miguel:

    Ordinarily I would suggest that VM performance is always unpredictable and moving from one OS version to another has also impacted performance, however, if inner joins are consistently faster and outer joins are consistently slower, that's not caused by the VM or the difference in the OSes. The query plans are the same so the data distributions are not to blame. If inner queries on the same tables using the same indexes are faster then it's not fragmentation on one server versus the other. The only things left are two:
    1. Since outer joins may write temp tables which inner joins are less likely to, there will be more non-cached IO, so it might be slower storage. Check the onstat -g ioh output from both servers after bouncing them and running the outer join query two or three times to see the service times on the chunks holding the tables and any temp tables.
    2. HCL screwed something up in v14.10.
    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 10.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 09:59 AM

    Miguel

     

    If you can repo with small datasets then I would agree with Art and blame HCL J

     

    Cheers

    Paul

     






  • 11.  RE: Informix 14 - ALTER JOIN -

    Posted Fri July 23, 2021 08:38 AM
    Sqltrace will give more detail than set explain, did you try it?

    ------------------------------
    [eric] [Vercelletto] []
    [Founder]
    [kandooerp.org]
    [Pont l'Abbé] [France]
    [+33 626 52 50 68]

    Disclaimer: My own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
    ------------------------------



  • 12.  RE: Informix 14 - ALTER JOIN -

    Posted Mon July 26, 2021 04:21 AM
    Hi Miguel,

    If the query plan, CPU frequency and config are the same, the only thing left to consider is the I/O. So before/after the number of buffreads and phys. reads ? The best way to do this is in detail via the sysptprof. Just a gut feeling :-)

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021
    ------------------------------