Mysql PDO Prepared statement efficiency

Question

I am taking over a PHP app that uses MySQL PDO prepared statements for each time it runs an SQL statement. I know that Preparing SQL can be more efficient when the you are about to do many iterations of the same statement.

$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));
$red = $sth->fetchAll();
$sth->execute(array(175, 'yellow'));
$yellow = $sth->fetchAll();

However, the app I am taking over has an built a layer on top of PDO that calls a common "execute" function, and it appears that it prepares every single SQL query. For example:

$query = self::$DB->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute($bindvars);

If the app does many hundreds or thousands of "INSERT INTO ...... ON DUPLICATE KEY UPDATE" SQL statements, does the $DB->prepare() step create a significant overhead if it is run every single time?

Many thanks, Jason.

Answer 1

From the documentation:

Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information...

I 'm not really making any revelations here, but the opposite of "optimizes the performance" would indeed be "overhead". As to whether it's significant or not, why don't you run a loop either way and measure? You can then decide for yourself with hard data to back up your decision.

Answer 2

Besides query reuse, the primary reason to use prepared statements in PDO is to perform placeholder binding.

$query = self::$DB->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute($bindvars);

In this code, the question marks (or :named) placeholders present in the $sql variable are replaced with the values in the $bindvars array. This replacement ensures that the variables are properly quoted and escaped, making it much more difficult to perform SQL injection.

There may be a small amount of overhead in the prepare/execute, but that small overhead is nothing given the risk of SQL injection. The only other option is concatenating together the SQL string, and that can be a huge security risk unless it's done perfectly every time.

The previous developer knew what he or she was doing, at least in this specific case, and you should not undo the work he or she did here. Quite the opposite, you should continue using prepared statements in all of your future code.

(On the other hand, I can't vouch for MySQL's cursor performance...)

Answer 3

If memory serves, MySQL sees your prepared statement and expects that you're probably running an application that is likely to call the same statement multiple times. As such, it caches the statement string, so preparing it again isn't much overhead, though it's more than just keeping the reference to the statement in memory. It's still definitely better than parsing a whole new query from a string each time.

This is just from my vague memory of what I think I've heard, though. Here's the important bit: if these hundreds or thousands of inserts are running in the same request, consider refactoring the database class to prepare once and execute many times in situations like these. The only way to know how much of a difference it will make is to benchmark it yourself :/