Native Informix Driver for PHP

Hi, We're currently thinking about Informix and PHP for a very specific project in our company. As you know, the connector offered by PHP is a PDO maintained by IBM (pecl) and uses the Informix ODBC library. We've already adapted it a little so that we can use at least persistent connections to avoid "connecting" at each request, but the performance compared with PHP's native PgSQL driver isn't exceptional. The main performance problem is linked to the use of the ODBC library rather than the native informix library, which creates a significant overlay in the passage of data from the server into PHP structures, and if my analysis is correct, then the next question would be "how much effort would be required to develop a native connector (could we start from the source of the PgSQL connector and make the adaptations to ifx, or is it more complex than that? )". @Sebastien Flaesch could also give me his opinion as he has a lot of experience with multi-base drivers, I think. Thanks to all ------------------------------ Garcia Benjamin ------------------------------

Could you write an ESQL/C middleware service to talk to Informix and pass data back to PHP via pipes or message queues? I wrote a similar service so that FORTRAN applications could query the database many years ago. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Just checking, are you using PDO_ODBC or PDO_INFORMIX? Also, how are you fetching the data, are you doing bindcolumn or just a standard fetch into an object/array? I haven't benchmarked against PgSQL, but when I did earlier benchmarks fetching vs direct fetches using dbaccess it was very close in performance. As you said, the connection overhead is real if you are doing a connect on each request, but it sounds like you have a solution for that. ------------------------------ Thomas Beebe Vice President xDB Systems, Inc Woodbridge VA 5713399029 ------------------------------

Hi. We use a modified version of pdo_informix. Server side informix is quicker than psql. Client side the interface between php and the driver we can clearly see that psql is must quicker. The overhead of binding and field description of the odbc driver is a bottelneck.... Regards Sent from Nine

I am not sure if this is helpful but I just put together a speed test here which had pretty consistent results. It was fetching 25mil rows from a table of fake contacts into a php array. This was fetching it into FETCH_NUM vs FETCH_OBJ vs FETCH_BOUND All were select first 25000000 * from names; Except for array by name which was spelling out the field names rather than just using *. The times do count the connect and disconnect from the database. I included the memory used just out of curiosity. Results: Array: 35s - Memory: 18877336832 OBJ: 41s - Memory: 28145764120 Array by name: 29s - Memory: 19145764120 By Bound Column: 26s - Memory: 268834832 Interestingly doing "unload to /dev/null select * from names" in dbaccess took 45 seconds. I did not do an equivalent esqlc program to do a full apples to apples. ------------------------------ Thomas Beebe Vice President xDB Systems, Inc Woodbridge VA 5713399029 ------------------------------

"Interestingly doing "unload to /dev/null select * from names" in dbaccess took 45 seconds." Double buffering? Scott Pickett IBM Informix Product Manager spickett@us.ibm.com 617-899-7549

Possibly, I didn't dig much into that one, it was just a quick comparison test so I kind of discarded it as not useful. ------------------------------ Thomas Beebe Vice President xDB Systems, Inc Woodbridge VA 5713399029 ------------------------------

Hi, The issue is not so much the execution of a single SQL query that returns thousands of records, but rather the execution of hundreds of SQL operations in the shortest possible time. With an ODBC-type driver, a great deal of time is lost because of the SQLbind/SQLDesc that are performed in order to prepare the memory spaces for sending the parameters and receiving the results. This processing is not optimised in the driver (no dictionary cache, for example). So for each request sent to the server, a few ms are lost ... if you don't multiply 100 requests, you quickly lose 100 or 200 ms ... With a native driver this overhead doesn't exist, hence my question why IBM (HCL) doesn't offer a native driver ... Regards ------------------------------ Garcia Benjamin ------------------------------