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 SELECT
s or INSERT
s 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.
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.