Do I need to persist a prepared statement?

Question

I have the following code:

public function getDefinitions($wordID) {
    $query = $this->dbc->prepare('SELECT * FROM definitions WHERE wordID = ?');

    $query->bind_param('i', $wordID);
    $query->execute();

    // ...

    $query->close();

    return $result;
}

It would seem that this would recreate the prepared statement for each invocation. That does not seem to take advantage of the full benefit of prepared statements. Especially in the case these prepare statements were stored server side. Is that true?

If so, should I store the prepare statement (in this case as a property) to persist it between invocations. Is there a way to persist the prepared statement between requests? I assume that's essentially a stored procedure?

Currently I am using MySQLi. Please note any API difference if I were using another driver (e.g. PDO).

I would say persisting the $query would make a lot of sense, otherwise you gain no benefit from using a prepared statement, especially if you know you are going to call it many times within the same execution of a single script. — RiggsFolly
You do gain one benefit still, which is the use of bind variables; but a simple approach to persisting the prepared statement might be to make $query a static — Mark Baker
other than placeholders/parameters, the only time prepared statements really should be used is if you're going to use the SAME query over and over. the extra overhead to prepare a one-shot query is definitely "useless". but proper use of placeholders to prevent sql injection attacks massively outweighs that overhead. — Marc B
@Marc B, understood. Is there an alternative to prepared statements but still provides the placeholder benefit? — Jason McCreary
On the other hand, speaking of the native prepared statements, surely there is an alternative called "emulated prepared statement" which utilizes the idea of placeholder without two-trip preparations. And it is implemented in a number of libraries, starting from PDO. — Your Common Sense

Your Common Sense Your Common Sense · Accepted Answer · 2014-05-29T16:28:22

should I store the prepare statement (in this case as a property) to persist it between invocations.

I wouldn't say you "should". You may, if you foresee a lot of consequent invocations (which you cannot group up to call at once). Either way, you scarcely will be able to notice the real life difference.

Is there a way to persist the prepared statement between requests?

In PHP - no. Prepared statements are connection-based, connection is closed along with request, and persistent connections won't help you either, as mysqli is using designated mechanism to clean connection state before reuse.

Please note any API difference if I were using another driver (e.g. PDO).

PDO has an advantage over mysqli as it can emulate prepared statements, eliminating even such a negligible overhead of two database calls, which makes it more suitable for the real life usage (beside other key benefits).

In a nutshell, the "prepare once-multiple execute" feature of native prepared statements can have noticeable effect only for a huge number of queries, starting from thousands.

Do I need to persist a prepared statement?

2 Answers