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!
In test I wrote a script to copy 25k records from a remote db2-db to MySQL — prior to seeing your notes regarding SQL_CUR_USE_ODBC the script took ~20 minutes to run! With your change it finished in ~3 minutes!
The only thing I lost was odbc_num_rows(), I was only using it for progress tracking, I can replace it with a `SELECT COUNT.`
Thanks!
Before i found your blog,I have big problem for fetch data from DB2 because it very slow about 300 in 90 sec.
Now,I have use SQL_CUR_USE_ODBC, I can fetch data 300 in 1 sec.
It’s very very cool. Thank you very much. ^_^