Bug #53458 [Ana->Dup]: Non-linear performance degradation on certain prepared SELECT queries
Edit report at https://bugs.php.net/bug.php?id=53458&edit=1
ID: 53458
Updated by: cmb@php.net
Reported by: don at smugmug dot com
Summary: Non-linear performance degradation on certain
prepared SELECT queries
-Status: Analyzed
+Status: Duplicate
Type: Bug
Package: PDO MySQL
Operating System: CentOS 5.5
PHP Version: 5.3.3
-Assigned To:
+Assigned To: cmb
Block user comment: N
Private report: N
New Comment:
Closing as duplicate of bug #80027, because the other ticket is
currently being worked on.
Previous Comments:
------------------------------------------------------------------------
[2013-03-19 21:43:45] mike@php.net
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).
------------------------------------------------------------------------
[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] mike@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] wez@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)
------------------------------------------------------------------------
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
Thread (8 messages)