Mysql PDO Report Performance Report

Question

I am using a PHP application that uses prepared MySQL PDO statements for every SQL query run. I know that preparing SQL can be more efficient when you are going 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 application I use has a built-in layer on top of the PDO, which calls the general function "execute", and it seems that it prepares every SQL query. For instance:

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

If an application executes many hundreds or thousands of SQL INSERT INTO ... ...... ON DUPLICATE KEY UPDATE statements, does the $ DB-> prepare () step make significant overhead if it starts every time

Thanks a lot, Jason.

Answer 1

From the documentation:

Calling PDO :: prepare () and PDOStatement :: execute () for statements that will be issued several times with different parameter values ​​optimizes the performance of your application, allowing the driver to agree on client and / or server side caching of the request plan and meta information ...

I really don't make any revelations here, but the opposite of "performance optimization" will really be "overhead". As for how important or not this is, why don't you start the loop anyway and measure? Then you can decide for yourself, using hard data to back up your solution.

Answer 2

In addition to reusing the query, the main reason to use prepared statements in the PDO is to bind to the placeholder.

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

In this code, question mark tags (or :named ) present in the $sql variable are replaced with the values ​​in the $bindvars . This replacement ensures that the variables are correctly quoted and escaped, making SQL injection difficult.

There may be a small amount of overhead in preparation / execution, but small overhead does not pose a danger to SQL injection. The only other option is to concatenate the SQL string, and this can be a huge security risk, unless it performs perfectly every time .

The previous developer knew what he or she was doing, at least in this particular case, and you should not cancel the work that he or she did here. Otherwise, you should continue to use prepared statements in all of your future code.

(On the other hand, I cannot vouch for the performance of the MySQL cursor ...)

Answer 3

If memory is used, MySQL sees your prepared statement and expects that you are probably running an application that can invoke the same statement several times. Thus, it caches the instruction line, so preparing it again is not too complicated, although it is more than just storing the reference to the instruction in memory. This is still definitely better than parsing a whole new query from a string every time.

This is only from my vague memory of what I think I heard. Here's an important bit: if these hundreds or thousands of attachments work in a single query, consider refactoring the database class once and many times in such situations. The only way to find out what is the difference, whether it will be itself: /