[PHP-DB] Re: Remote ODBC Connection to IBMi Db2/iAccess Very Slow to Fetch Results
[email protected] (Zachary Menzies) Tue, 12 Mar 2024 13:54:49 +0000
| Newsgroups | php.db |
|---|---|
| Message-ID | <DM6PR04MB3980B0EFB8E4F2BC258F3446DB2B2@DM6PR04MB3980.namprd04.prod.outlook.com> |
--_000_DM6PR04MB3980B0EFB8E4F2BC258F3446DB2B2DM6PR04MB3980namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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_DM6PR04MB3980B0EFB8E4F2BC258F3446DB2B2DM6PR04MB3980namp_ 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"><span style=3D"font-family: Aptos, Aptos_Embe= ddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 1= 2pt; color: rgb(0, 0, 0);">I found the options for "odbc.default_curso= rtype" 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"elementToProof"><span style=3D"font-family: Aptos, Aptos_Embe= ddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 1= 2pt; color: rgb(0, 0, 0);"><br> </span></div> <div class=3D"elementToProof"><span style=3D"font-family: Aptos, Aptos_Embe= ddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 1= 2pt; color: rgb(0, 0, 0);">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 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_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_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_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_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_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_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_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_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_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_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_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_elementToProof" style=3D"background-color:rgb(255,255,255); m= argin: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> </body> </html> --_000_DM6PR04MB3980B0EFB8E4F2BC258F3446DB2B2DM6PR04MB3980namp_--