Edit report at https://bugs.php.net/bug.php?id=53458&edit=1
ID: 53458
Updated by: m...@php.net
Reported by: don at smugmug dot com
Summary: Non-linear performance degradation on certain
prepared SELECT queries
Status: Analyzed
Type: Bug
Package: PDO related
Operating System: CentOS 5.5
PHP Version: 5.3.3
Block user comment: N
Private report: N
New Comment:
Nope, it's not obvious at the first glance, but if you fetch 10k rows with 10k
params you have N * 1e4 * 1e4 (Ne8) iterations, where N stands for the number
of
callbacks per fetched row.
While, if you fetch 1k rows with 1k params for 10 times, it's N * 10 * 1e3 *
1e3
(Ne7) (90% less).
Previous Comments:
------------------------------------------------------------------------
[2013-03-19 17:44:30] don at smugmug dot com
Sorry for the script getting lost, been awhile. :) Here's a gist with it:
https://gist.github.com/onethumb/5198291
The thing that still leaves me wondering after your analysis is why doing a
prepared SELECT on 1K rows, ten times, results in a >10X faster return than a
single SELECT on 10K rows? Presumably, we'd have the same # of calls both ways?
I just validated against 5.4.10, too. Same problem.
------------------------------------------------------------------------
[2013-03-19 16:34:04] m...@php.net
The test script is not accessible anymore, so I assume it is about
$stm->execute($a10kArray)
If that is the case, then the issue might be described as follows:
Each element in the array to stm->execute() is registered as a bound parameter,
for which the param_hook callback is called twice (normalize and alloc) and
once when everything is done (free).
So for a 10k array, this are 30k callbacks into the pdo driver.
When fetching a row, the param_hook callback is called twice for each param
(fetch_pre and fetch_post), i.e. 20k callbacks.
So for a 10k rowset add 200M calls.
In numbers: 200,030,000 indirect calls to the param_hook callback of the
underlying pdo driver.
Iterating over the 10k params twice for each of the 10k rows is nearly (twice
in a debug build) as expensive as calling the param_hook itself.
Add this two things up, and you easily wait a second for your result set.
------------------------------------------------------------------------
[2010-12-03 04:27:43] w...@php.net
from a twitter conversation with Don, the heart of this issue is that the there
appears to be a difference in the performance of fetch() or fetchAll()
depending
on whether query() or prepare() + execute() were used.
Given that query() is really just prepare() + execute() under the covers, it is
difficult to explain this difference.
------------------------------------------------------------------------
[2010-12-03 03:36:14] don at smugmug dot com
FYI, setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to true/false didn't have a
large impact. Still ~10X slower than any of the other methods either way.
(Buffered was slightly faster)
------------------------------------------------------------------------
[2010-12-02 23:39:10] don at smugmug dot com
Here's my PHP build configuration:
'./configure' '--enable-exif' '--with-gd' '--enable-gd-native-ttf' '--with-jpeg-
dir=/usr' '--with-png-dir=/usr' '--with-freetype-dir=/usr' '--with-zlib' '--
enable-inline-optimization' '--with-bz2' '--with-apxs2' '--with-xmlrpc' '--with-
curl' '--with-libdir=lib64' '--with-pdo-mysql=mysqlnd' '--with-mcrypt'
'--enable-
bcmath' '--with-gmp' '--enable-mbstring' '--with-mysql=mysqlnd'
'--with-openssl'
'--with-mysqli=mysqlnd'
------------------------------------------------------------------------
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=53458
--
Edit this bug report at https://bugs.php.net/bug.php?id=53458&edit=1
