Informix

  • 1.  Informix with PHP performance

    Posted Sat October 16, 2021 12:38 PM
    Hello to all,
    I am facing a very annoying dilemma. We have been using Informix for 25 years with 100% satisfaction on its robustness and performance with applications developed in Informix 4gl, delphi and 4js genero.
    We would like to use PHP/Symphony with PDO for 100% WEB applications. Our problem is that the performances in comparison with PostgreSQL or Mysql are 2 times less good. We have the feeling that the bottelneck is not the engine but rather the client part which is PDO->ODBC->IFX CLIENT->SERVER. Does anyone have experience to share with us regarding this dilemma because it would mean that informix in 2021 is no longer a basis for developing applications with new programming languages.
    All ideas are welcome ...

    Thanks in advance

    Translated with www.DeepL.com/Translator (free version)

    ------------------------------
    Garcia Benjamin
    ------------------------------


  • 2.  RE: Informix with PHP performance

    Posted Sun October 17, 2021 06:13 PM


    Hi,

    What fetch buffer size are you using in the application? How does that compare to Postgres/Mysql?

    https://www.ibm.com/docs/en/informix-servers/12.10?topic=performance-manage-fetch-buffer-size

    What is the query plan for the session? How does that compare to Postgres/Mysql?

    Regards,

    David.



    ------------------------------
    David Williams
    ------------------------------



  • 3.  RE: Informix with PHP performance

    Posted Mon October 18, 2021 02:20 AM
    Hi Benjamin,

    the PHP <-> Informix Back-End working fine, normally. Don't worry, you will get the dialog answers back in realtime, similar to 4GL, if the config is correct :-) Check the front-end configuration and if the server gets the same SQL with the same session parameters and maybe if you have a difference in cursor handling.

    Good Luck
    Henri

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



  • 4.  RE: Informix with PHP performance

    Posted Mon October 18, 2021 06:38 AM
    Hello,

    Thanks for the answers, the FETCH_BUFFER_SIZE is already activated. We have done some tests to understand better and we find that using select with aliases (select col as col_alias) has a very big impact on performance (125%) if we do a select without aliases.

    Has anyone experienced this before?

    Our dev team uses (symphony/doctrine) and therefore SQL generated with column aliasing and so is there a parameter in the informix ODBC driver that would allow to optimize this because the postgresql driver is not sensitive to this...

    Thanks in advance


    ------------------------------
    Garcia Benjamin
    ------------------------------



  • 5.  RE: Informix with PHP performance

    Posted Mon October 18, 2021 07:15 AM
    Garcia:

    Can you post one of the queries that are slower when issued to Informix along with query plans from Informix and possibly from PostgreSQL? I have some thoughts, but I want to look at the query and plans. Also if you could supply clock time runtime in addition to the timings in the query plan output that would help.

    Art

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



  • 6.  RE: Informix with PHP performance

    Posted Tue October 19, 2021 04:53 AM
    Hi,

    Hereby sqlexplain + studio sql trace


    QUERY: (OPTIMIZATION TIMESTAMP: 10-19-2021 10:44:06)
    ------
    SELECT t0.serwsa , t0.codwsa , t0.typart , t0.sermsuart , t0.flgcre
    , t0.flgmod , t0.flgsup , t0.codmtr , t0.datmtr , t0.datsys
    , t0.serart , t12.serart , t12.typart , t12.numart , t12.serartgen
    , t12.artweb , t12.libfra , t12.liball , t12.libeng , t12.oriart , t12.codvis
    , t12.cofunt21 , t12.cofunt31 , t12.cofuntven , t12.cofmsu , t12.poivar1 , t12.poivar2
    , t12.poivar3 , t12.mediane , t12.flgstk , t12.gesstk , t12.perdeb , t12.perfin
    , t12.codblo , t12.joura , t12.heura , t12.dlvgarcli , t12.grmmin , t12.grmmax
    , t12.grmplr , t12.grmdft , t12.qtemin , t12.qtemax , t12.qteplr
    , t12.payint , t12.flgdch , t12.flgmat , t12.tautva , t12.codtmp , t12.catprx
    , t12.prcper , t12.codtridrv , t12.datsys , t12.untven , t12.untmsu , t12.untweb1
    , t12.untweb2 , t12.untweb3 , t0.serwsalnk
    FROM twsartweb t0 LEFT JOIN twsarterp t12 ON t0.serart = t12.serart
    WHERE t0.codwsa IN ('460648', '702928', '774316', '706195', '708037', '708077', '707997', '798780', '702995', '725280', '710099', '798763')

    Estimated Cost: 13
    Estimated # of Rows Returned: 12

    1) root.t0: INDEX PATH

    (1) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '460648'

    (2) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '702928'

    (3) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '774316'

    (4) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '706195'

    (5) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '708037'

    (6) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '708077'

    (7) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '707997'

    (8) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '798780'

    (9) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '702995'

    (10) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '725280'

    (11) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '710099'

    (12) Index Name: wss.i01_twsartweb
    Index Keys: codwsa (Serial, fragments: ALL)
    Lower Index Filter: root.t0.codwsa = '798763'

    2) root.t12: INDEX PATH

    (1) Index Name: wss.i00_twsarterp
    Index Keys: serart (Serial, fragments: ALL)
    Lower Index Filter: root.t0.serart = root.t12.serart
    NESTED LOOP JOIN


    Query statistics:
    -----------------

    Table map :
    ----------------------------
    Internal name Table name
    ----------------------------
    t1 t0
    t2 t12

    type table rows_prod est_rows rows_scan time est_cost
    -------------------------------------------------------------------
    scan t1 12 12 12 00:00.00 4

    type table rows_prod est_rows rows_scan time est_cost
    -------------------------------------------------------------------
    scan t2 12 27948 12 00:00.00 1

    type rows_prod est_rows time est_cost
    -------------------------------------------------
    nljoin 12 12 00:00.00 13


    STudio sql trace



    Regards



    ------------------------------
    Garcia Benjamin
    ------------------------------



  • 7.  RE: Informix with PHP performance

    Posted Mon October 18, 2021 10:09 AM
    Edited by Kat Jarvis Mon October 18, 2021 12:11 PM
    Just following up on this, what is the value of FET_BUF_SIZE at?
    Do you have any statistics from the Informix server during the heavy load times to see if it is a bottleneck at the engine versus it being a network or a PHP issue?

    Also, which version of the Informix ODBC/Client SDK are you using?

    ------------------------------
    Thomas Beebe
    ------------------------------



  • 8.  RE: Informix with PHP performance

    Posted Mon October 18, 2021 10:43 AM

    There is a measureable delta with the select col as name syntax (or within SPL return int as name) but it is not significant – certainly not the 125% you are seeing. First came across this with Java developers when they want to rename the columns, or "<expr>" at the SQL layer rather than in the Java layer.

     

    Cheers

    Paul

     






  • 9.  RE: Informix with PHP performance

    Posted Tue October 19, 2021 03:03 AM
      |   view attached
    Hello,

    Informix Server 14.10FC5 on a Debian (VM)
    CSDK/ODBC : IBM Informix CSDK Version 4.50, IBM Informix-ESQL Version 4.50.FC6
    FETCH_BUFFER_SIZE = 32768

    Bootleneck isn't the engine. Informix is quicker than Postgresql.
    The bootleneck seems the ODBC driver.
    Hereby 3 tests with same select with different flavour on define columns part. You will see that "select *" is quicker
    You can see that postgress is less sensible ...
    I've also attached the php test code.

    --
    -- First test using a "select *" (result in ms)
    --
    -- BEST
    --
    select *
    FROM twsartweb t0 LEFT JOIN twsarterp t12 ON t0.serart = t12.serart
    WHERE t0.codwsa IN ('460648','702928','774316','706195','708037','708077','707997','798780','702995','725280','710099','798763')

    POSTGRES                                           INFORMIX
    ["Lazy"]=> float(79.934814453125)            ["Num"]=> float(90.4990234375)
    ["Num"]=> float(89.378662109375)            ["Both"]=> float(92.975830078125)
    ["Both"]=> float(90.006103515625)            ["Assoc"]=> float(93.50146484375)
    ["Assoc"]=> float(90.98779296875)           ["Lazy"]=> float(96.87548828125)
    ["Obj"]=> float(101.130126953125)            ["Obj"]=> float(97.87939453125)

    --
    -- Second test using a "select with aliases" (result in ms) : this select is generated by Symphony/Doctrine
    --
    -- WORST ...
    --
    select t0.serwsa AS SERWSA_1, t0.codwsa AS CODWSA_2, t0.typart AS TYPART_3
    , t0.sermsuart AS SERMSUART_4, t0.flgcre AS FLGCRE_5, t0.flgmod AS FLGMOD_6
    , t0.flgsup AS FLGSUP_7, t0.codmtr AS CODMTR_8, t0.datmtr AS DATMTR_9
    , t0.datsys AS DATSYS_10, t0.serart AS SERART_11, t12.serart AS SERART_13
    , t12.typart AS TYPART_14, t12.numart AS NUMART_15, t12.serartgen AS SERARTGEN_16
    , t12.artweb AS ARTWEB_17, t12.libfra AS LIBFRA_18, t12.liball AS LIBALL_19
    , t12.libeng AS LIBENG_20, t12.oriart AS ORIART_21, t12.codvis AS CODVIS_22
    , t12.cofunt21 AS COFUNT21_23, t12.cofunt31 AS COFUNT31_24, t12.cofuntven AS COFUNTVEN_25
    , t12.cofmsu AS COFMSU_26, t12.poivar1 AS POIVAR1_27, t12.poivar2 AS POIVAR2_28
    , t12.poivar3 AS POIVAR3_29, t12.mediane AS MEDIANE_30, t12.flgstk AS FLGSTK_31
    , t12.gesstk AS GESSTK_32, t12.perdeb AS PERDEB_33, t12.perfin AS PERFIN_34
    , t12.codblo AS CODBLO_35, t12.joura AS JOURA_36, t12.heura AS HEURA_37
    , t12.dlvgarcli AS DLVGARCLI_38, t12.grmmin AS GRMMIN_39, t12.grmmax AS GRMMAX_40
    , t12.grmplr AS GRMPLR_41, t12.grmdft AS GRMDFT_42, t12.qtemin AS QTEMIN_43
    , t12.qtemax AS QTEMAX_44, t12.qteplr AS QTEPLR_45, t12.payint AS PAYINT_46
    , t12.flgdch AS FLGDCH_47, t12.flgmat AS FLGMAT_48, t12.tautva AS TAUTVA_49
    , t12.codtmp AS CODTMP_50, t12.catprx AS CATPRX_51, t12.prcper AS PRCPER_52
    , t12.codtridrv AS CODTRIDRV_53, t12.datsys AS DATSYS_54, t12.untven AS UNTVEN_55
    , t12.untmsu AS UNTMSU_56, t12.untweb1 AS UNTWEB1_57, t12.untweb2 AS UNTWEB2_58
    , t12.untweb3 AS UNTWEB3_59, t0.serwsalnk AS SERWSALNK_60
    FROM twsartweb t0 LEFT JOIN twsarterp t12 ON t0.serart = t12.serart
    WHERE t0.codwsa IN ('460648','702928','774316','706195','708037','708077','707997','798780','702995','725280','710099','798763')

    POSTGRES                                             INFORMIX
    ["Both"]=> float(89.9814453125)                 ["Lazy"]=> float(224.744140625)
    ["Num"]=> float(97.2041015625)                 ["Obj"]=> float(227.64697265625)
    ["Lazy"]=> float(98.202880859375)             ["Num"]=> float(227.99853515625)
    ["Obj"]=> float(105.7158203125)                 ["Assoc"]=> float(233.634521484375)
    ["Assoc"]=> float(112.408203125)               ["Both"]=> float(239.45361328125)

    --
    -- Third test using a "select columns without aliases" (result in ms)
    --
    -- MIDDLE ...
    --
    SELECT t0.serwsa,t0.codwsa,t0.typart,t0.sermsuart,t0.flgcre,t0.flgmod
    ,t0.flgsup,t0.codmtr,t0.datmtr,t0.datsys,t0.serart,t12.serart
    ,t12.typart,t12.numart,t12.serartgen,t12.artweb,t12.libfra,t12.liball
    ,t12.libeng,t12.oriart,t12.codvis,t12.cofunt21,t12.cofunt31,t12.cofuntven
    ,t12.cofmsu,t12.poivar1,t12.poivar2,t12.poivar3,t12.mediane,t12.flgstk
    ,t12.gesstk,t12.perdeb,t12.perfin,t12.codblo,t12.joura,t12.heura
    ,t12.dlvgarcli,t12.grmmin,t12.grmmax,t12.grmplr,t12.grmdft,t12.qtemin
    ,t12.qtemax,t12.qteplr,t12.payint,t12.flgdch,t12.flgmat,t12.tautva,t12.codtmp
    ,t12.catprx,t12.prcper,t12.codtridrv,t12.datsys,t12.untven,t12.untmsu,t12.untweb1
    ,t12.untweb2,t12.untweb3,t0.serwsalnk
    FROM twsartweb t0 LEFT JOIN twsarterp t12 ON t0.serart = t12.serart
    WHERE t0.codwsa IN ('460648','702928','774316','706195','708037','708077','707997','798780','702995','725280','710099','798763')
    POSTGRES                                          INFORMIX
    ["Both"]=> float(86.582275390625)           ["Obj"]=> float(127.802978515625)
    ["Num"]=> float(88.0263671875)               ["Both"]=> float(135.5546875)
    ["Assoc"]=> float(89.347900390625)         ["Lazy"]=> float(135.583251953125)
    ["Obj"]=> float(89.641357421875)             ["Num"]=> float(135.982177734375)
    ["Lazy"]=> float(91.70654296875)             ["Assoc"]=> float(137.793212890625)


    ------------------------------
    Garcia Benjamin
    ------------------------------

    Attachment(s)

    txt
    postgres_php.txt   4 KB 1 version