Bug #80027 [NEW]: Terrible performance using $query->fetch on queries with many bind parameters
From: dino dot pejakovic at voxdiversa dot hr
Operating system: Debian 10
PHP version: Irrelevant
Package: PDO related
Bug Type: Bug
Bug description:Terrible performance using $query->fetch on queries with many bind parameters
Description:
------------
(This is mostly pasted from my mail to php.internals)
I recently noticed some weird performance issues while doing bulk
inserts with prepared statements (single INSERT with a lot of VALUES)
and using RETURNING clause to get back IDs and other columns.
So I wrote a little benchmark to insert 8000 random rows (3 columns
each) into a table and spent some time tracking down why it's slow.
Suprisingly it seems that INSERT itself takes 100-200ms, but
fetch/fetchAll returning id and one of the columns takes 2-3 seconds.
After digging around PHP source code (pulled master branch), the problem
seems to be in PDO calling param_hook with PDO_PARAM_EVT_FETCH_PRE and
again PDO_PARAM_EVT_FETCH_POST for each fetched row, which causes
param_hook to be executed for each row x each param twice. In my little
benchmark inserting 8000 rows with 3 columns and returning 2 columns for
each row that means param_hook is called 8000x3x8000x2 = 384â000â000
times! So I took a look at pgsql_stmt_param_hook in
ext/pdo_pgsql/pgsql_statement.c and it doesn't seem to do anything for
PDO_PARAM_EVT_FETCH_PRE or PDO_PARAM_EVT_FETCH_POST. So if my
understanding is correct, it's calling a function that does nothing
meaningful 384â000â000 times, and this number grows exponentially
with the number of rows and columns.
Commenting out dispatch_param_event for PDO_PARAM_EVT_FETCH_PRE and
PDO_PARAM_EVT_FETCH_POST in ext/pdo/pdo_stmt.c makes fetchAll duration
go down from 2-3 seconds to ~5ms, as expected.
Test script:
---------------
Simple test script and database schema used:
https://gist.github.com/inoric/8e8716118d3113521005f56170d8da95
Expected result:
----------------
Consistent performance when calling fetch/fetchAll, not dependent on the
number of bind parameters, scaling linearly with number of rows.
Actual result:
--------------
Duration of fetch/fetchAll increasing with number of bind parameters.
--
Edit bug report at https://bugs.php.net/bug.php?id=80027&edit=1
--
Fix committed: https://bugs.php.net/fix.php?id=80027&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=80027&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=80027&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=80027&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=80027&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=80027&r=support
Expected behavior: https://bugs.php.net/fix.php?id=80027&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=80027&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=80027&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=80027&r=globals
PHP version support discontinued: https://bugs.php.net/fix.php?id=80027&r=phptooold
Daylight Savings: https://bugs.php.net/fix.php?id=80027&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=80027&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=80027&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=80027&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=80027&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=80027&r=mysqlcfg
Thread (5 messages)
- dino dot pejakovic at voxdiversa dot hr