mysqlperlprepared-statementdbidbd-mysql

Does $dbh->prepare() provide any performance benefits for MySQL access?


The DBI docs on prepare() state that it's behaviour differ for various drivers:

Drivers for engines without the concept of preparing a statement will typically just store the statement in the returned handle and process it when $sth->execute is called.

I have general query log enabled in MySQL and I don't see any SQL statements other than SELECTs or INSERTs that I pass to $sth->execute(). Does it mean that calling prepare() is a no-op for the DBI's MySQL driver and I can just use $dbh shortcuts (like $dbh->do() or $dbh->selectall_arrayref())? Or is there any other simple way to measure if adding the prepare step provides any benefits?

P.S. I mostly care about performance difference as the simpler $dbh shortcuts provide the same functionality for parameterization etc. Also, I'm asking specifically about MySQL, and don't see how this question is a duplicate of the PHP one which uses a different interpreter/DB library/Database server.


Solution

  • The default behavior of DBD::mysql is to emulate server-side prepared statements.

    To get real server-side prepared statements, you must explicitly set the option mysql_server_prepare=1 when you connect.

    For example:

    $dbh= DBI->connect("dbi:mysql:database=test;host=localhost:mysql_server_prepare=1", "", "");
    

    The performance benefits of server-side prepared statements are mostly when you re-execute a prepared statement many times. For example, if you INSERT with parameter placeholders, you may re-execute to insert many rows with different values, and this skips the overhead of re-parsing the SQL syntax.

    When you execute a statement only once, you don't get a performance benefit from prepared statements. Executing a prepared statement takes exactly as long as a non-prepared statement.

    Many years ago, some developers claimed that doing prepare and execute as separate calls costs overhead because it's two round-trips to the server, whereas when an emulated prepare makes prepare() a no-op, it eliminates the extra round-trip. But this overhead is insignificant on modern networks. If this is a performance problem for your app, it's likely that you're on a very slow or outdated network, and you can't expect high performance anyway.

    It's like asking if you should put premium or regular fuel in your 1973 Ford Pinto.