Bug #80027 [Opn]: Terrible performance using $query->fetch on queries with many bind parameters
Edit report at https://bugs.php.net/bug.php?id=80027&edit=1
ID: 80027
User updated by: dino dot pejakovic at voxdiversa dot hr
Reported by: dino dot pejakovic at voxdiversa dot hr
Summary: Terrible performance using $query->fetch on queries
with many bind parameters
Status: Open
Type: Bug
Package: PDO related
Operating System: Debian 10
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
Forgot to note: I selected PHP Version: Irrelevant because I don't know exactly which versions
are affected.
I discovered this on 7.4.9 (as packaged by arch and fedora on my workstations) after noticing
slowness on servers with 7.3.x. I then reproduced this on the master branch. I do remember
experiencing weird slowness around this earlier but usually blamed the database doing it's
thing so I didn't find the problem then.
Previous Comments:
------------------------------------------------------------------------
[2020-08-27 18:42:59] dino dot pejakovic at voxdiversa dot hr
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 this bug report at https://bugs.php.net/bug.php?id=80027&edit=1
Thread (5 messages)