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
------------------------------
Original Message:
Sent: Sat October 16, 2021 12:38 PM
From: Garcia Benjamin
Subject: Informix with PHP performance
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
------------------------------
#Informix