« Certificates | Main | PHP Blob support »
August 20, 2003
ODBC fetch speed
While talking with Edin the other day, he complained about how painfully slow the PHP ODBC fetch system is for him. He provided me with some numbers and a sample query to back his complaints. While I don't remember the exact timings he had, it looks something like this:
Connecting - 0.5 seconds
Executing - 0.1 seconds
Fetching - 2.7+ seconds
Fetching is obviously what he was complaining about, as the query was simply returning 70 rows of single data elements. I suggested that he do one minor hack to his PHP source and recompile. After doing this hack, his fetching time went from that astronomically high number down to a lightning quick 0.1 seconds.
This hack is wonderful, and I actually tried to implement it back in the PHP 4.2.0 days, only to discover it broke a significant portion of Microsoft based clients.
You can implement this hack yourself too by changing your cursor type from SQL_CURSOR_DYNAMIC to SQL_CURSOR_FORWARD_ONLY within the php_odbc.c file (only the two instances of SQLSetStmt please). Mind you, I do not support such changes and cannot ask for anything more than feedback if it worked for you (via a TB or a Comment would be nice). You should see a dramatic change as well in your fetch speeds.
Supposedly with IBMs DB2 v8 (and greater) you can do this same alteration via a few words of SQL at the end of your query. For example adding in "FOR READ ONLY" will convert the cursor to a FORWARD_ONLY cursor and speed up your queries as well. The reason this works within the DB2 system is it seems they've dropped support for dynamic cursors awhile back. If anyone else has found that their DBs also support this change for cursor alterations, let me know.
Posted by Dan at August 20, 2003 06:36 PM