Re: Remote ODBC Connection to IBMi Db2/iAccess Very Slow to Fetch Results
Zachary Menzies <[email protected]> Wed, 20 Mar 2024 20:12:55 +0000
| Newsgroups | gmane.comp.php.database |
|---|---|
| Message-ID | <DM6PR04MB3980402B00B081F51E7D156FDB332@DM6PR04MB3980.namprd04.prod.outlook.com> |
--_000_DM6PR04MB3980402B00B081F51E7D156FDB332DM6PR04MB3980namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello again, It looks like the most recent PHP version (8.3.4) fixed this slow result fe= tching problem, so long as the SQL_CUR_USE_ODBC option is passed to the odbc_connect() func= tion. I was going to post a bug report, when I tested it one last time, and lo-an= d-behold, the fetching performance is back to normal. So problem solved. Thank you, PHP developers. Zachary ________________________________ From: Zachary Menzies <[email protected]> Sent: March 12, 2024 9:54 AM To: [email protected] <[email protected]> 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). Ho= wever, none of the options (0 to 3) made any difference. Zachary ________________________________ From: Zachary Menzies <[email protected]> Sent: March 12, 2024 8:36 AM To: [email protected] <[email protected]> Subject: [PHP-DB] Remote ODBC Connection to IBMi Db2/iAccess Very Slow to F= etch Results 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_DM6PR04MB3980402B00B081F51E7D156FDB332DM6PR04MB3980namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"= > <style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo= ttom:0;} </style> </head> <body dir=3D"ltr"> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c= olor: rgb(0, 0, 0);"> Hello again,</div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c= olor: rgb(0, 0, 0);"> <br> </div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c= olor: rgb(0, 0, 0);"> It looks like the most recent PHP version (8.3.4) fixed this slow result fe= tching problem,</div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif;"> <span style=3D"font-size: 12pt; color: rgb(0, 0, 0);">so long as the </span= ><span style=3D"font-size: 16px; color: rgb(0, 0, 0); background-color: rgb= (255, 255, 255);">SQL_CUR_USE_ODBC option is passed to the odbc_connect() f= unction.</span></div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; c= olor: rgb(0, 0, 0);"> <span style=3D"background-color: rgb(255, 255, 255);"><br> </span></div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; c= olor: rgb(0, 0, 0);"> <span style=3D"background-color: rgb(255, 255, 255);">I was going to post a= bug report, when I tested it one last time, and lo-and-behold,</span></div= > <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; c= olor: rgb(0, 0, 0);"> <span style=3D"background-color: rgb(255, 255, 255);">the fetching performa= nce is back to normal.</span></div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; c= olor: rgb(0, 0, 0);"> <span style=3D"background-color: rgb(255, 255, 255);"><br> </span></div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; c= olor: rgb(0, 0, 0);"> <span style=3D"background-color: rgb(255, 255, 255);">So problem solved. Th= ank you, PHP developers.</span></div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; c= olor: rgb(0, 0, 0);"> <span style=3D"background-color: rgb(255, 255, 255);"><br> </span></div> <div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo= nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 16px; c= olor: rgb(0, 0, 0);"> <span style=3D"background-color: rgb(255, 255, 255);">Zachary</span></div> <div id=3D"appendonsend"></div> <hr style=3D"display:inline-block;width:98%" tabindex=3D"-1"> <div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" st= yle=3D"font-size:11pt" color=3D"#000000"><b>From:</b> Zachary Menzies <z= [email protected]><br> <b>Sent:</b> March 12, 2024 9:54 AM<br> <b>To:</b> [email protected] <[email protected]><br> <b>Subject:</b> Re: Remote ODBC Connection to IBMi Db2/iAccess Very Slow to= Fetch Results</font> <div> </div> </div> <style type=3D"text/css" style=3D"display:none"> <!-- p {margin-top:0; margin-bottom:0} --> </style> <div dir=3D"ltr"> <div class=3D"x_elementToProof"><span style=3D"font-family:Aptos,Aptos_Embe= ddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; = color:rgb(0,0,0)">I found the options for "odbc.default_cursortype&quo= t; here: <a href=3D"https://www.php.net/manual/en/odbc.configuration.php" id=3D"LPln= k381981">https://www.php.net/manual/en/odbc.configuration.php</a> (not= sure why I didn't see it before). However, none of the options (0 to 3) ma= de any difference.</span></div> <div class=3D"x_elementToProof"><span style=3D"font-family:Aptos,Aptos_Embe= ddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; = color:rgb(0,0,0)"><br> </span></div> <div class=3D"x_elementToProof"><span style=3D"font-family:Aptos,Aptos_Embe= ddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; = color:rgb(0,0,0)">Zachary</span></div> <div id=3D"x_appendonsend"></div> <hr tabindex=3D"-1" style=3D"display:inline-block; width:98%"> <div id=3D"x_divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" = color=3D"#000000" style=3D"font-size:11pt"><b>From:</b> Zachary Menzies <= ;[email protected]><br> <b>Sent:</b> March 12, 2024 8:36 AM<br> <b>To:</b> [email protected] <[email protected]><br> <b>Subject:</b> [PHP-DB] Remote ODBC Connection to IBMi Db2/iAccess Very Sl= ow to Fetch Results</font> <div> </div> </div> <style type=3D"text/css" style=3D"display:none"> <!-- p {margin-top:0; margin-bottom:0} --> </style> <div 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= )">Hello,</span> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">At work, we ha= ve an IBM Db2 database. Until recently, it lived within our local network,<= /span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,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 iSeries Acces= s</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">ODBC driver. F= or communication, I typically use PHP's ODBC interface (sometimes other</sp= an></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">languages).</s= pan></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">When the datab= ase was local, everything seemed to run smoothly; but now that it is off-si= te,</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">fetching resul= ts 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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">it was an issu= e on our host's side (or even our network), but all tests indicated otherwi= se. A</span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">proof of this = is that I made a C program to run the same query (using the same driver), a= nd it</span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">fetched the re= sults within milliseconds after the query completed.</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">I should perha= ps point out that running the queries is fast, it's only fetching the resul= t rows</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">that is slow.<= /span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br> </span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">The only comme= nts I could find that mentioned a similar issue was for people that were us= ing</span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">a Db2 driver: = if they passed the "SQL_CUR_USE_ODBC" option to the "odbc_co= nnect" function,</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">then their res= ult collection was sped up. I tried that, but it made no difference (perhap= s because</span></div> <div class=3D"x_x_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,Cal= ibri,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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">I'm not so sur= e that this is an error with PHP so much as a setting that needs to be twea= ked;</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">but I can't se= em to find what setting that might be. I can see there is a default_cursort= ype option</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,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 properly set t= he value.</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">I also tried u= sing PDO and ADOdb, but both resulted in the same slowness (I'm guessing be= cause</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">they just wrap= the ODBC interface).</span></div> <div style=3D"text-align:left; text-indent:0px; background-color:rgb(255,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br> </span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">So, as I my fi= rst choice is not to re-write a bunch of scripts into C, I was wondering if= any of you might</span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">know (or have = ideas as to) what the issue might be.</span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br> </span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">The PHP versio= ns I have been using are 8.3.2 and 8.3.3, and I've tried the test on both W= indows and</span></div> <div class=3D"x_x_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,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">Linux.</span><= /div> <div class=3D"x_x_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,Cal= ibri,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,25= 5,255); margin:0px"> <span style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cal= ibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">Thanks,</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"x_x_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> </div> </div> </body> </html> --_000_DM6PR04MB3980402B00B081F51E7D156FDB332DM6PR04MB3980namp_--