php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #66875 Increasing performance of LOB fetching
Submitted: 2014-03-10 09:37 UTC Modified: 2014-03-11 08:25 UTC
Votes:1
Avg. Score:3.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: perrier dot p at gmail dot com Assigned: sixd
Status: Closed Package: OCI8 related
PHP Version: Irrelevant OS: irrelevant
Private report: No CVE-ID:
 [2014-03-10 09:37 UTC] perrier dot p at gmail dot com
Description:
------------
chunk_size from a lob is loaded for each column and each line, but can be defined only for each column in ORACLE, so we can make a cache of chunk_size in column object instead of fetching it on every lob.

With this change you can increase by a factor x2 the lob fecthing part of a query.

Patch is made from PHP 5.5.5 source code.


Patches

Lobfecthing (last revision 2014-03-10 09:41 UTC) by perrier dot p at gmail dot com)

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-03-10 23:47 UTC] sixd@php.net
Automatic comment on behalf of sixd
Revision: http://git.php.net/?p=php-src.git;a=commit;h=601367ee75c570b52ac8f28c7b26d5166cad2989
Log: Fixed Bug #66875 (Improve performance of multi-row OCI_RETURN_LOB queries)
 [2014-03-10 23:47 UTC] sixd@php.net
-Status: Open +Status: Closed
 [2014-03-10 23:58 UTC] sixd@php.net
-Summary: Increasing performance in lob fecthing by x2 +Summary: Increasing performance of LOB fetching -Assigned To: +Assigned To: sixd
 [2014-03-10 23:58 UTC] sixd@php.net
The performance benefit will be system dependent.  I had to fetch 4K
rows before I could begin to measure any time improvement from caching
OCILobGetChunkSize()'s value.  Since OCILobCharSetId doesn't involve a
network trip, caching its value seems to add more obscurity then it's
worth.

A patch was merged to PHP 5.6+. See http://news.php.net/php.cvs/76429
and
https://github.com/php/php-src/commit/de4ff2c5a42980645baf731200840c56d6a75275
Can you check it before I release OCI8 2.0.8 on PECL?

Patch description: The patch caches the result of OCILobGetChunkSize()
to avoid a DB round trip for the second and subsequent rows fetched
when using PHP's OCI_RETURN_LOBS.  This will help overall scalability.

Implementing similar caching for LOB locator fetches using
OCI-Lob::read() and OCI-Lob::load() might be possible, but would
require a more complex patch.
 [2014-03-11 08:25 UTC] perrier dot p at gmail dot com
Great job, 2.0.8 with lob patch go 13% faster than 5.5.5 with lob patch for me.

respectively for vanilla, my patch, your patch and oci 2.0.8 my test page take
2.6s , 1.66s, 1.45s 

Fetching time with many lob column go down from 1569ms to 556ms

Tahnks.
 [2014-03-11 08:56 UTC] ab@php.net
Automatic comment on behalf of sixd
Revision: http://git.php.net/?p=php-src.git;a=commit;h=601367ee75c570b52ac8f28c7b26d5166cad2989
Log: Fixed Bug #66875 (Improve performance of multi-row OCI_RETURN_LOB queries)
 [2014-03-11 08:58 UTC] ab@php.net
Automatic comment on behalf of sixd
Revision: http://git.php.net/?p=php-src.git;a=commit;h=601367ee75c570b52ac8f28c7b26d5166cad2989
Log: Fixed Bug #66875 (Improve performance of multi-row OCI_RETURN_LOB queries)
 [2014-04-10 04:47 UTC] tyrael@php.net
Automatic comment on behalf of sixd
Revision: http://git.php.net/?p=php-src.git;a=commit;h=601367ee75c570b52ac8f28c7b26d5166cad2989
Log: Fixed Bug #66875 (Improve performance of multi-row OCI_RETURN_LOB queries)
 
PHP Copyright © 2001-2015 The PHP Group
All rights reserved.
Last updated: Mon Mar 02 16:02:05 2015 UTC