Created
August 27, 2020 10:57
-
-
Save inoric/8e8716118d3113521005f56170d8da95 to your computer and use it in GitHub Desktop.
PHP PDO slow fetch with a lot of bind parameters benchmark
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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