Skip to content

Instantly share code, notes, and snippets.

@inoric
Created August 27, 2020 10:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save inoric/8e8716118d3113521005f56170d8da95 to your computer and use it in GitHub Desktop.
Save inoric/8e8716118d3113521005f56170d8da95 to your computer and use it in GitHub Desktop.
PHP PDO slow fetch with a lot of bind parameters benchmark
<?php
declare(strict_types=1);
function genData(int $cnt): array {
$data = [];
for($i = 0; $i < $cnt; ++$i) {
$data[] = [
'str1' => uniqid(),
'str2' => uniqid(),
'num' => rand(),
];
}
return $data;
}
$data = genData(8000);
$db = new PDO('pgsql:host=localhost;dbname=test', 'test', 'test');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$vals = [];
foreach($data as $row) {
$vals[] = $row['str1'];
$vals[] = $row['str2'];
$vals[] = $row['num'];
}
$valSql = \implode(', ', \array_fill(0, \count($data), '(?, ?, ?)'));
$sql = <<<SQL
INSERT INTO thingies (str1, str2, num)
VALUES {$valSql}
RETURNING id, str2
SQL;
$prepStart = \microtime(true);
$query = $db->prepare($sql);
$execStart = \microtime(true);
$query->execute($vals);
$fetchStart = \microtime(true);
$ret = $query->fetchAll();
echo 'Got rows: '.\count($ret).PHP_EOL;
$end = \microtime(true);
$prepDuration = \number_format($execStart - $prepStart, 3);
$execDuration = \number_format($fetchStart - $execStart, 3);
$fetchDuration = \number_format($end - $fetchStart, 3);
// var_dump($ret);
echo "[Timings] prep: {$prepDuration}, exec: {$execDuration}, fetch: {$fetchDuration}".PHP_EOL;
CREATE TABLE thingies (
id SERIAL PRIMARY KEY,
str1 TEXT NOT NULL,
str2 TEXT NOT NULL,
num INTEGER NOT NULL
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment