CODEWITHSUNDEEP
mysqlsqlperformancepostgresqlprepared-statement
crazedsanity
crazedsanity

Reputation: 3

Performance Problems When Preparing Statement Multiple Times

I've got a PHP application that uses PDO with prepared statements, both in PostgreSQL and MySQL, and I'm wondering if there's a performance hit when preparing the exact same statements each time before executing it.

In pseudo-code, an example would be something like:

for ($x=0; $x<100; $x++) {
    $obj = PDO::prepare("SELECT x,y,z FROM table1 WHERE x=:param1 AND y=:param2");
    $obj->execute(array('param1'=>$param1, 'param2'=>$param2));
}

As opposed to preparing once and executing multiple times:

$obj = PDO::prepare("SELECT x,y,z FROM table1 WHERE x=:param1 AND y=:param2");
for ($x=0; $x<100; $x++) {
    $obj->execute(array('param1'=>$param1, 'param2'=>$param2));
}

I've searched for this question many times, but can't seem to find a reference to it in PHP, nor PostgreSQL, nor MySQL.

Upvotes: 0

Views: 1009

Answers (3)

Jānis Gruzis
Jānis Gruzis

Reputation: 995

Why not doing this? It will of course execute solver. But since no connection to DB or heavy calculation is done at preparation (since query is short), speed change will be insignificant, but that`s a bad style.

$obj = PDO::prepare("SELECT x,y,z FROM table1 WHERE x=:param1 AND y=:param2");
for ($x=0; $x<100; $x++) {
    $obj->execute(array('param1'=>$param1, 'param2'=>$param2));
}

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562671

Yes, there's a performance hit. The prepare step has to parse the SQL string and analyze it to decide which tables it needs to access and how it's going to do that (which indexes to use, etc.). So if you prepare() once before the loop begins, and then just execute() inside the loop, this is a cost savings.

This is an example of a more general programming principle: you shouldn't put code inside a loop if the result of that code is the same for every iteration of the loop.

PS: You shouldn't use PDO::prepare(), you should create a PDO object and call $pdo->prepare().

PPS: You should also check the return value of prepare() and execute(). They return false on errors. Or else configure PDO to throw exceptions on error.

Upvotes: 0

Marc B
Marc B

Reputation: 360762

Yes, it somewhat defeats the purpose of using prepared statements. Preparing allows the DB to pre-parse the query and get things ready for execution. When you do execute the statement, the DB simply slips in the values you're providing and performs the last couple steps.

When you prepare inside a loop like that, all of the preparation work gets thrown out and re-done each time. What you should have is:

$obj = PDO::prepare("SELECT x,y,z FROM table1 WHERE x=:param1 AND y=:param2");
for ($x=0; $x<100; $x++) {
    $obj->execute(array('param1'=>$param1, 'param2'=>$param2));
}

prepare once, execute many times.

Upvotes: 1

Related Questions