Piotr Gajdowski Piotr Gajdowski - 6 months ago 51
SQL Question

how come PDO::bindParam is so slow?

SOLVED: The quoting of values in second test seemed to be the problem.
Instead doing 'VALUES ($id, $t1....)' I needed to do 'VALUES ("$id", "$t1".....), in the second test this was indeed throwing errors. which I couldn't see, only after printing them manually.

Hey there I've been working with bindParam lately and noticed the load time of my page went up dramatically.

So I spend a few hours to diagnose the problem and it seems that bindParam is using A LOT more processing time then the old fashion way (using parameters in query directly)

To make sure the results are valid, I did both tests twice.

Here is my test:

for ($j = 0; $j < 2; $j++) {
unset($t);
$dbh = new PDO('mysql:host=localhost;port=3306', 'root', 'root');
$dbh->query('USE Web_Amicrom_HQ');
$t['start'] = microtime(true);
for ($i = 0; $i < 50; $i++) {
$id = rand(1000000, 9999999);
$t1 = string_random(240);
$t2 = string_random(240);
$t3 = string_random(1000);
$ins = $dbh->prepare('INSERT INTO `test` (id, t1, dt, t2, t3) VALUES(:1, :2, now(), :3, :4)');
$ins->bindParam(':1', $id, PDO::PARAM_INT);
$ins->bindParam(':2', $t1, PDO::PARAM_STR);
$ins->bindParam(':3', $t2, PDO::PARAM_STR);
$ins->bindParam(':4', $t3, PDO::PARAM_STR);
$ins->execute();
}
$t['loop_fact'] = microtime(true);

echo "---- with bindParam ----\n";
$str_result_bench = mini_bench_to($t);
echo $str_result_bench; // string return
echo "\n\n";
}

for ($j = 0; $j < 2; $j++) {
unset($t);
$dbh = new PDO('mysql:host=localhost;port=3306', 'root', 'root');
$dbh->query('USE Web_Amicrom_HQ');
$t['start'] = microtime(true);
for ($i = 0; $i < 50; $i++) {
$id = rand(1000000, 9999999);
$t1 = string_random(240);
$t2 = string_random(240);
$t3 = string_random(1000);
$ins = $dbh->prepare("INSERT INTO `test` (id, t1, dt, t2, t3) VALUES($id, $t1, now(), $t2, $t3)");
$ins->execute();
}
$t['loop_fact'] = microtime(true);

echo "---- with parameter in query ----\n";
$str_result_bench = mini_bench_to($t);
echo $str_result_bench; // string return
echo "\n\n";
}


Results:

---- with bindParam ----
total time : 3136.148ms


---- with bindParam ----
total time : 2645.822ms


---- with parameter in query ----
total time : 41.693ms


---- with parameter in query ----
total time : 52.9752ms


Things I tried.

Change quotes from Double to Single - No difference

bindParam without parameter type (e.g PDO::PARAM_INT) - No difference

Changed all param names (e.g :1 to :id etc.) - No difference

This is a big performance difference, especially for just a few queries.

Answer

Q: Why is PDO::bindParam so slow?

A: PDO::bindParam isn't slow.

The benchmark test demonstrated in the question isn't a valid measure of bindParam performance.

The second test is going to throw errors for nearly every INSERT. That's because the string literals (?) are not enclosed in single quotes. I originally thought those would be interpreted as numeric. (Confusingly, we don't see a specification for the return from string_random(240). We don't see a definition of that function, or even any examples of what that returns.)

If that's returning a string of 240 characters which doesn't represent a valid numeric literal... the benchmark is comparing inserts of rows ~1500 characters (using bindParam) vs. no rows inserted.

My original answer (below) indicates modifications to the two tests so that they perform equivalent functions. This would be more valid comparison of bindParam vs not bindParam.


ORIGINAL ANSWER

For improved performance, call prepare and bindParam functions one time, before entering the loop. (It's not necessary to call prepare on the same SQL statement multiple times.)

    $sql = 'INSERT INTO `test` (id, t1, dt, t2, t3) VALUES(:1, :2, NOW(), :3, :4)'; 
    $ins = $dbh->prepare($sql);
    $id = 0;
    $t1 = 0;
    $t2 = 0;
    $t3 = 0;
    $ins->bindParam(':1', $id, PDO::PARAM_INT);
    $ins->bindParam(':2', $t1, PDO::PARAM_STR);
    $ins->bindParam(':3', $t2, PDO::PARAM_STR);
    $ins->bindParam(':4', $t3, PDO::PARAM_STR);
    for ($i = 0; $i < 50; $i++) {
        $id = rand(1000000, 9999999);
        $t1 = string_random(240);
        $t2 = string_random(240);
        $t3 = string_random(1000);
        $ins->execute();
    }

If we were using bindValue instead of bindParam, we would need to do the bindValue immediately before the execute, each time.


For the second test (not using bindParam), the code is vulnerable to SQL Injection, unlike the first test. To make this second test equivalent to the first, we'd need to ensure that the values are properly escaped so they are safe for inclusion in the SQL text.

    for ($i = 0; $i < 50; $i++) {
        $id = rand(1000000, 9999999);
        $t1 = string_random(240);
        $t2 = string_random(240);
        $t3 = string_random(1000);
        $sql = "INSERT INTO `test` (id, t1, dt, t2, t3) VALUES ( " 
             . $dbh->quote( $id ) . ", "
             . $dbh->quote( $t1 ) . ", "
             . "NOW(), "
             . $dbh->quote( $t2 ) . ", "
             . $dbh->quote( $t3 ) . ")";
        $ins = $dbh->prepare($sql);
        $ins->execute();
    }

Note that the quote function does more than just put quotes around values. It also "escapes" single quotes included within the text, and other characters that would be interpreted by MySQL. (In the original second test, we don't see single quotes around the values, so it looks like MySQL would have evaluated those in a numeric context. We'd expect a lot of those rows would have "0" in the t1, t2 and t3 columns.

I'd be interested in seeing the comparison in timing of these two (modified) tests.

Comments