Hello again,
It looks like the most recent PHP version (8.3.4) fixed this slow result
fetching problem,
so long as the SQL_CUR_USE_ODBC option is passed to the odbc_connect() function.
I was going to post a bug report, when I tested it one last time, and
lo-and-behold,
the fetching performance is back to normal.
So problem solved. Thank you, PHP developers.
Zachary
From: Zachary Menzies
Sent: March 12, 2024 9:54 AM
To: php-db@lists.php.net
Subject: Re: Remote ODBC Connection to IBMi Db2/iAccess Very Slow to Fetch
Results
I found the options for "odbc.default_cursortype" here:
https://www.php.net/manual/en/odbc.configuration.php (not sure why I didn't see
it before). However, none of the options (0 to 3) made any difference.
Zachary
From: Zachary Menzies
Sent: March 12, 2024 8:36 AM
To: php-db@lists.php.net
Subject: [PHP-DB] Remote ODBC Connection to IBMi Db2/iAccess Very Slow to Fetch
Results
Hello,
At work, we have an IBM Db2 database. Until recently, it lived within our local
network,
but has since been moved off-site (to the cloud). We connect to it using an
iSeries Access
ODBC driver. For communication, I typically use PHP's ODBC interface (sometimes
other
languages).
When the database was local, everything seemed to run smoothly; but now that it
is off-site,
fetching results from SELECT queries is very slow (12 seconds for 100 rows). I
thought at first
it was an issue on our host's side (or even our network), but all tests
indicated otherwise. A
proof of this is that I made a C program to run the same query (using the same
driver), and it
fetched the results within milliseconds after the query completed.
I should perhaps point out that running the queries is fast, it's only fetching
the result rows
that is slow.
The only comments I could find that mentioned a similar issue was for people
that were using
a Db2 driver: if they passed the "SQL_CUR_USE_ODBC" option to the
"odbc_connect" function,
then their result collection was sped up. I tried that, but it made no
difference (perhaps because
I'm not using a Db2 driver, per se, but an iAccess driver).
I'm not so sure that this is an error with PHP so much as a setting that needs
to be tweaked;
but I can't seem to find what setting that might be. I can see there is a
default_cursortype option
in the php.ini file, but I don't know what options are available or how to
properly set the value.
I also tried using PDO and ADOdb, but both resulted in the same slowness (I'm
guessing because
they just wrap the ODBC interface).
So, as I my first choice is not to re-write a bunch of scripts into C, I was
wondering if any of you might
know (or have ideas as to) what the issue might be.
The PHP versions I have been using are 8.3.2 and 8.3.3, and I've tried the test
on both Windows and
Linux.
Thanks,
Zachary