php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #46471 Performance problem when reading XML columns
Submitted: 2008-11-03 14:20 UTC Modified: 2021-05-16 04:22 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (50.0%)
From: rgpublic at gmx dot net Assigned: cmb (profile)
Status: No Feedback Package: OCI8 related
PHP Version: 5.2SVN-2009-10-19 (snap) OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please — but make sure to vote on the bug!
Your email address:
MUST BE VALID
Solve the problem:
37 - 24 = ?
Subscribe to this entry?

 
 [2008-11-03 14:20 UTC] rgpublic at gmx dot net
Description:
------------
Reading an XML column seems to be unnecessarily slow.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-11-03 14:24 UTC] 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.
 [2009-10-19 15:00 UTC] jani@php.net
Please try using this snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2009-10-19 21:12 UTC] rgpublic at gmx dot net
Still happens with recent snapshot.
 [2009-10-20 10:19 UTC] 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-20 17:44 UTC] 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.
 [2010-03-12 21:00 UTC] sixd@php.net
-Assigned To: +Assigned To: sixd
 [2017-10-24 07:52 UTC] kalle@php.net
-Status: Assigned +Status: Open -Assigned To: sixd +Assigned To:
 [2021-05-03 12:53 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2021-05-03 12:53 UTC] cmb@php.net
Is this still an issue with any of the actively supported PHP
versions[1]?

[1] <https://www.php.net/supported-versions.php>
 [2021-05-16 04:22 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 11:01:27 2024 UTC