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

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

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

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

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

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

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

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

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