Some months ago a work mate asked me for the differences between using bind variables versus executing the SQL statement directly as a string throughout a PDO connection.
Basically the work-flow of almost all database drivers is the same: Prepare statement, execute and fetch results. We have the following small example with a simple update
$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$stmt = $dbh->prepare("UPDATE test.tbl1 set field1=:F1 where id=1");
$stmt->execute(array('F1' => $field1));
$dbh->commit();
And we also can get the same result with the following code:
$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$dbh->prepare("UPDATE test.tbl1 set field1='{$field1}' where id=1")->execute();
$dbh->commit();
What’s the best one? Both method work properly. The difference is how databases manage the operation internally. When we prepare a statement we are compiling the string into the database within the current connection. After that we can execute the statement and if our statement is ready to receive parameters we can bind those parameters with PHP values or variables. With this idea in mind we can guess that if we need to perform several executions of the same prepared statement is better to use bind parameters instead of compile and execute directly the string.
b
I have created a simple benchmark to show it.
<?php
error_reporting(-1);
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$time_start = microtime_float();
$dbh->beginTransaction();
$field1 = uniqid();
$stmt = $dbh->prepare("UPDATE test.tbl1 set field1=:F1 where id=1");
$stmt->execute(array('F1' => $field1));
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Single UPDATE with bind parameters: $time seconds</p>";
$time_start = microtime_float();
$dbh->beginTransaction();
$field1 = uniqid();
$stmt = $dbh->prepare("UPDATE test.tbl1 set field1='{$field1}' where id=1");
$stmt->execute();
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Single UPDATE without bind parameters: $time seconds</p>";
$time_start = microtime_float();
$dbh->beginTransaction();
$stmt = $dbh->prepare('UPDATE test.tbl1 set field1=:F1 where id=1');
foreach (range(1, 5000, 1) as $i) {
$field1 = $i;
$stmt->execute(array('F1' => $field1));
}
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Multiple UPDATE with bind parameters: $time seconds</p>";
$time_start = microtime_float();
$dbh->beginTransaction();
foreach (range(1, 5000, 1) as $i) {
$stmt = $dbh->prepare("UPDATE test.tbl1 set field1='{$field1}' where id=1");
$field1 = $i;
$stmt->execute();
}
$dbh->commit();
$time_end = microtime_float();
$time = round($time_end - $time_start, 4);
echo "<p>Multiple UPDATE without bind parameters: $time seconds</p>";
The output of this benchmark is the following one:
Single UPDATE with bind parameters: 0.2623 seconds Single UPDATE without bind parameters: 0.0195 seconds Multiple UPDATE with bind parameters: 4.1123 seconds Multiple UPDATE without bind parameters: 8.1732 seconds
As we can see in the output of the benchmark a single update is slightly faster without bind any parameter but if we need to execute the same update with different parameters the bind parameters way is significantly faster than parse+execute again and again.
There’s another benefit of using bind parameters. Databases normally have internally a cache system for our prepared statement. In theory they reuse the statements. The main problem for us (in our PHP world) is that normally we create a new connection to the database at the beginning of the script (or lazy connection) and close it at the end. We don’t have natively a connection pooling like J2EE. So I’m not 100% sure if using bind parameters helps to the database to reuse statements.
There’s another issue we must take into account. Without prepared statements the multiple update example may throw a database exception (depend on our database configuration). Too many open cursors in active transaction. That error appear because for our database each update is a different statement (5000 in the example) and with the another method there’s only one.
Gonzalo Ayuso - Web Architect 
Could you do the same test with $dbh->query instead of prepare and execute for the part without bind parameters?
Also there is no need to create your own microtime_float function. Just use microtime(true) to get the microtime as a float.
/Michael
I will test it and I will update the benchmark. But before doing anything I predict a similar result. But I will test it anyway (and maybe I will swallow my words 😉 ).
(knowledge updated with your microtime tip)
Finally I’ve tested it. Using query instead of parse and execute throw similar outcomes. A bit slower with “query()” in almost all of my tests, indeed.
I’m not sure but I suppose query is simply and alias of parsre+execute+fetch. As you can see in my benchmark I don’t use fetch command so it seems coherent that query() is slight slower than parse+execute.
That’s the output for 5000 updates. For this second test I’ve added a fetch function to compare fetch vs parse + execute + fetch :
Multiple UPDATE with bind parameters: 1.5784 seconds
Multiple UPDATE without bind parameters: 2.0155 seconds
Multiple UPDATE without bind parameters and query() instead prepare/execute: 1.9582 seconds
As you can see query is faster than parse+execute+fetch
Interesting. Thank you for doing the extra test. How come the tests are so much faster then before?
2 seconds vs 8 seconds for the one without bind params?
Don’t look at the absolute time. It’s irrelevant. Depends on the punctual situation and current load of my PC (not a great machine, different applications running, one test yesterday, another today). The important factor is the difference between the time inside each test, because each test runs within the same environment.
Probably the outcomes would be clear in percentages instead of absolute time.
Thanks for sharing. I particularly enjoy knowing these small usage differences and the right time to use each one of them.
Regards,
George
I think the more interesting test/benchmark is to measure PDO::query vs. PDO::prepare without any fetches – for single operations vs. multiple operations.
Nice, i was looking at something like this. Im trying doctrine, and because of the PDO layer i was wondering about perfomrance. Interesting
hi everybody,
i have just tested it, dans I have different results. (with autocommit)
with query :
$arrParams[0] = “sebbbbba” ;
$arrParams[1] = “1” ;
$result = $dbh->query(“update `testacc`.`application` set name='”. $arrParams[0] .$i.”‘ WHERE `application`.`ID` = “. $dbh->quote($arrParams[1] ) );
with prepare:
$result = $dbh->prepare(“update `testacc`.`application` set name=? WHERE `application`.`ID` = ? “) ;
$result->execute(array(“sebbbb”.$i,”1””));
after one test the result are very differents fom one time to another.
i put this code in a ‘for’ and execute it 4000 times.
the sum result after 4000 update for each technique is:
without prepare: 0.0050453384518623
with prepare: 0.0059936824440956
finaly.. I don’t understand. do you have some advise ?
thanks you,
Chris
Can you share the full example in a gist (for example)?
With a and without prepare
of course :
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
pdo_update
hosted with ❤ by GitHub
Did you see anything strange in my example ?
Ups. My mind had marked this comment as answered and it isn’t (good excuse, isn’t it? 🙂 )
Yes I can see something “wrong” in your gist. You are using prepared statements not in the correct way. The idea is create a prepared statement one time and execute many. You are creating the prepared statement and execute once time per loop. That’s not necessary. You can prepare it before loop and only execute it in the loop. You are doing almost the same in both modes (query() is more or less a shortcut of prepare() + execute()).
hehe, good excuse.
The fact is that the objective of the test is :
To test the time the difference for one call between :
single query
single prepare withtout binding
single prepare with binding
i made a loop to make a SUM of several ‘single update’. indeed, with only one call the results are very different from one execution to other execution.
my result is that “single prepare with bind” is almost the same than “single query” or “single prepare without bind”.
if i understand what you say :
“single prepare without” = “query”
but what about prepare with bind ?
Prepare with bind is the same. Prepare returns a prepared Statement. That means your DB compiles the statement in is own languaje (DB don’t speak SQL. SQL is the language that we use to speak with the databases. DB internally use is own structures). In this example you are creating the same prepared statement again and again in each loop step, and you don’t really need it. You can (must) create the prepared statement one time (outside the loop) and execute many times.
Databases normally try to reuse statements but in PHP world (everything die at the end of each request) this behaviour is rare to find.
You also must take into account that your example is different than mine. You are creating one transaction per update, and I use only one.
In this test, i only want to test the “single update”. I need to create the same prepared statement again and again to measure the average of each type of execution.
i can see in your results that :
1- Single UPDATE with bind parameters: 0.2623 seconds
2- Single UPDATE without bind parameters: 0.0195 seconds
(in these 2 updates, it’s one transaction per update, right?)
I have tried your script and sometimes, after one execution :
– I’ve got the same result as you
– or over times the same time response between 1 and 2
– or over times, the reverse time response : 1 < 2
i only made a loop to measure various executions.
the main thing i want to say you, is that it's impossible to me to get the same difference of time in my results.(you have almost a difference of 200% of time between 1 and 2).
sorry if i don't explain me well enough. and thank you for your patience.