ODBC for UDB and PHP - How I increased performance by 400%

August 2nd, 2007 by Aaron

Plans start at less than $10 month. Many freebies included! Click Here Build your own Store.

In our current setup at (”the triangle”), we have to use odbc connections to access our db2-udb database - and I don’t like it. But we have to stick with it - and thats the way life is. The main reason I don’t like it is the immense overhead and time it takes to execute queries. Well, I did some research and found out some interesting things. The most important of which was a cursor setting that allowed me to gain up to 400% performance. Find out how:

The dynamic scrollable cursor used to fetch data from the database in odbc is not supported by db2, so db2 downgrades the cursor to a dynamic keyset driven cursor. This is by default. Performance is gained by downgrading to a forward cursor only - which is faster than the scrollable cursor.

To test this, you can use the odbc_connect constant SQL_CUR_USE_ODBC as the 4th parameter of your connection (previously I wasn’t specifying a 4th param). This is the code I used to test it:

Remember, try the test once with the constant, and once without.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

<?php
$dsn = 'DRIVER={iSeries Access ODBC Driver};SYSTEM=SYS1;';
$username = 'USERNAME';
$password = 'PASSWORD';
$sql = "select * from library.file fetch first 200 rows only";
 
$db = odbc_connect($dsn, $username, $password, SQL_CUR_USE_ODBC) or die(odbc_error());
 
$start = microtime(true);
 
$result = odbc_e xec($db, $sql);
while ($row = odbc_fetch_array($result)) {
}
 
$stop = microtime(true);
 
print $stop - $start;
?>

Of course, remember to swap out the proper credentials and make a legitimate sql call for testing.

If anyone has any reasons or pointers why this setting could be bad, let me know. Thanks!

Bookmark:

Tags: PHP, SQL

This entry was posted on Thursday, August 2nd, 2007 at 9:17 am and is filed under PHP • SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply