php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80027 Terrible performance using $query->fetch on queries with many bind parameters
Submitted: 2020-08-27 18:42 UTC Modified: 2020-08-27 18:50 UTC
From: dino dot pejakovic at voxdiversa dot hr Assigned: mbeccati (profile)
Status: Closed Package: PDO related
PHP Version: Irrelevant OS: Debian 10
Private report: No CVE-ID: None
 [2020-08-27 18:42 UTC] 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.

Patches

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-08-27 18:48 UTC] dino dot pejakovic at voxdiversa dot hr
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.
 [2020-08-27 18:50 UTC] mbeccati@php.net
-Status: Open +Status: Assigned -Assigned To: +Assigned To: mbeccati
 [2020-08-28 14:12 UTC] cmb@php.net
The following pull request has been associated:

Patch Name: Optimize dispatch_param_event allowing to skip unused pdo_param_event(s)
On GitHub:  https://github.com/php/php-src/pull/6047
Patch:      https://github.com/php/php-src/pull/6047.patch
 [2020-08-31 09:14 UTC] mbeccati@php.net
Automatic comment on behalf of mbeccati
Revision: http://git.php.net/?p=php-src.git;a=commit;h=44ade0e87547f3f9ab8fdffb7bb0c8d530a90543
Log: Fix #80027 Terrible performance using $query->fetch on queries with many bind parameters
 [2020-08-31 09:14 UTC] mbeccati@php.net
-Status: Assigned +Status: Closed
 [2020-08-31 09:14 UTC] mbeccati@php.net
Automatic comment on behalf of mbeccati
Revision: http://git.php.net/?p=php-src.git;a=commit;h=ee2506cc027c97c86db51173ec7019c58b161204
Log: Fix #80027 Terrible performance using $query->fetch on queries with many bind parameters
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Wed Oct 21 06:01:23 2020 UTC