Bug #80027 [Opn->Asn]: 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 Updated by: mbeccati@php.net Reported by: dino dot pejakovic at voxdiversa dot hr Summary: Terrible performance using $query->fetch on queries with many bind parameters -Status: Open +Status: Assigned Type: Bug Package: PDO related Operating System: Debian 10 PHP Version: Irrelevant -Assigned To: +Assigned To: mbeccati Block user comment: N Private report: N Previous Comments: ------------------------------------------------------------------------ [2020-08-27 18:48:16] 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: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