Bug #46471 [Opn->Fbk]: Performance problem when reading XML columns

Edit report at https://bugs.php.net/bug.php?id=46471&edit=1 ID: 46471 Updated by: cmb@php.net Reported by: rgpublic at gmx dot net Summary: Performance problem when reading XML columns -Status: Open +Status: Feedback Type: Bug Package: OCI8 related Operating System: Linux PHP Version: 5.2SVN-2009-10-19 (snap) -Assigned To: +Assigned To: cmb Block user comment: N Private report: N New Comment: Is this still an issue with any of the actively supported PHP versions[1]? [1] <https://www.php.net/supported-versions.php> Previous Comments: ------------------------------------------------------------------------ [2009-10-20 17:44:49] rgpublic at gmx dot net Thank you for your answer. Results vary depending on the performance of the database server. As I have written I can generally say the simple approach always takes about twice the time. I assume you mean with "a lot more function calls" that oci_fetch_array is called in a while-loop causing round-trips to the database-server. That is right of course, but oci_set_prefetch should solve this at least partically, and it does not seem to have any influence. And without LOB columns oci_fetch_array is MUCH faster. The actual payload data (i.e. without internal overhead) transferred between server and client is obviously the same with both approaches. So that leaves us with the following facts: 1) The reason for the slowness is not that reading the lobs is slow. Otherwise the PL/SQL script wouldnt be able to get it faster 2) The reason is not that the network speed is limited. Otherwise both aprroaches would have the same speed IMO this leaves the whole chain down to the OCI module out of the picture. So the reason for this slowness lies in the OCI module. That's why I filed this bug. When you use Oracle's XML features to store lots of XML documents in a large table and want to retrieve many of them this slowness is causing a lot of problems. Simply reading back the data exactly as you have stored it in the database before shouldnt cause such a huge performance penalty IMHO. ------------------------------------------------------------------------ [2009-10-20 10:19:51] jani@php.net Exactly what results do you get? And why do you really think it should be any faster, considering you're doing a lot more function calls with the "simple" approach? ------------------------------------------------------------------------ [2009-10-19 21:12:49] rgpublic at gmx dot net Still happens with recent snapshot. ------------------------------------------------------------------------ [2009-10-19 15:00:08] jani@php.net Please try using this snapshot: http://snaps.php.net/php5.2-latest.tar.gz For Windows: http://windows.php.net/snapshots/ ------------------------------------------------------------------------ [2008-11-03 14:24:12] rgpublic at gmx dot net Example source code: http://oberon.q-one-hosting.com/ocidemo.txt This script does the following: Create a table with an XML column and fills it with data. Now, the simple approach to read back the data would be: SELECT mytab.xml.getClobVal() AS xml FROM xmltest mytab; This approach takes about twice the time as the second example which does the reading inside a PL/SQL function and concatenates the result separated by a chr(0)-character i.e. transferring the whole data in a single string. I'm wondering why there is such a loss of performance. ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=46471 -- Edit this bug report at https://bugs.php.net/bug.php?id=46471&edit=1