Informix

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 

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 

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.

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 

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.

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 

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. 

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.

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 

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. 

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.

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 

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. 

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.

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