[PHP-DB] Remote ODBC Connection to IBMi Db2/iAccess Very Slow to Fetch Results
[email protected] (Zachary Menzies) Tue, 12 Mar 2024 12:36:15 +0000
| Newsgroups | php.db |
|---|---|
| Message-ID | <DM6PR04MB3980401325478C9B66C2A007DB2B2@DM6PR04MB3980.namprd04.prod.outlook.com> |
--_000_DM6PR04MB3980401325478C9B66C2A007DB2B2DM6PR04MB3980namp_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
At work, we have an IBM Db2 database. Until recently, it lived within our l=
ocal 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 (somet=
imes other
languages).
When the database was local, everything seemed to run smoothly; but now tha=
t 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 ind=
icated otherwise. A
proof of this is that I made a C program to run the same query (using the s=
ame 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 fetc=
hing the result rows
that is slow.
The only comments I could find that mentioned a similar issue was for peopl=
e that were using
a Db2 driver: if they passed the "SQL_CUR_USE_ODBC" option to the "odbc_con=
nect" function,
then their result collection was sped up. I tried that, but it made no diff=
erence (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 ne=
eds to be tweaked;
but I can't seem to find what setting that might be. I can see there is a d=
efault_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 wa=
s 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
--_000_DM6PR04MB3980401325478C9B66C2A007DB2B2DM6PR04MB3980namp_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Hel=
lo,</span>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">At =
work, we have an IBM Db2 database. Until recently, it lived within our loca=
l network,</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">but=
has since been moved off-site (to the cloud). We connect to it using an iS=
eries Access</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">ODB=
C driver. For communication, I typically use PHP's ODBC interface (sometime=
s other</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">lan=
guages).</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Whe=
n the database was local, everything seemed to run smoothly; but now that i=
t is off-site,</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">fet=
ching results from SELECT queries is very slow (12 seconds for 100 rows). I=
thought at first</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">it =
was an issue on our host's side (or even our network), but all tests indica=
ted otherwise. A</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">pro=
of of this is that I made a C program to run the same query (using the same=
driver), and it</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">fet=
ched the results within milliseconds after the query completed.</span></div=
>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">I s=
hould perhaps point out that running the queries is fast, it's only fetchin=
g the result rows</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">tha=
t is slow.</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">The=
only comments I could find that mentioned a similar issue was for people t=
hat were using</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">a D=
b2 driver: if they passed the "SQL_CUR_USE_ODBC" option to the &q=
uot;odbc_connect" function,</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">the=
n their result collection was sped up. I tried that, but it made no differe=
nce (perhaps because</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">I'm=
not using a Db2 driver, per se, but an iAccess driver).</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">I'm=
not so sure that this is an error with PHP so much as a setting that needs=
to be tweaked;</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">but=
I can't seem to find what setting that might be. I can see there is a defa=
ult_cursortype option</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">in =
the php.ini file, but I don't know what options are available or how to pro=
perly set the value.</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">I a=
lso tried using PDO and ADOdb, but both resulted in the same slowness (I'm =
guessing because</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">the=
y just wrap the ODBC interface).</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">So,=
as I my first choice is not to re-write a bunch of scripts into C, I was w=
ondering if any of you might</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">kno=
w (or have ideas as to) what the issue might be.</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">The=
PHP versions I have been using are 8.3.2 and 8.3.3, and I've tried the tes=
t on both Windows and</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Lin=
ux.</span></div>
<div class=3D"elementToProof" style=3D"text-align: left; text-indent: 0px; =
background-color: rgb(255, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br=
>
</span></div>
<div style=3D"text-align: left; text-indent: 0px; background-color: rgb(255=
, 255, 255); margin: 0px;">
<span style=3D"font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService,=
Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Tha=
nks,</span></div>
<div style=3D"background-color: rgb(255, 255, 255); margin: 0px;">
<div style=3D"background-color: rgb(255, 255, 255); margin: 0px;">
<p class=3D"elementToProof" style=3D"background-color: rgb(255, 255, 255); =
margin: 0px; font-family: Calibri, sans-serif; font-size: 11pt;">
<span style=3D"font-size: 12pt; color: rgb(0, 0, 0);">Zachary</span></p>
</div>
</div>
<div style=3D"background-color: rgb(255, 255, 255);"></div>
</body>
</html>
--_000_DM6PR04MB3980401325478C9B66C2A007DB2B2DM6PR04MB3980namp_--