Can prepared statement handles be stored in member variables?

Question

I have a PHP class that processes data and stores it in a MySQL database. I use prepared statements via PDO for security reasons when data is saved, but because the class is large these prepared statements are created inside different functions that are called thousands of times during the lifetime of the object (anywhere from one minute to thirty).

What I’m wondering is if there’s any reason I couldn't prepare the statements in the class constructor and save the handles in member variables to avoid the statements being prepared more than once.

Is there any reason this wouldn't work? I don’t see why not, but I've never seen it done before, which makes me wonder if doing this is a bad practice for some reason.

---

I.E. something like this:

Class MyClass {

    private stmt1;

    function __construct($dbh) {
        $this->stmt1 = $dbh->prepare('SELECT foo FROM bar WHERE foobar = :foobar');
    }

    private function doFoo() {
        $this->stmt1->execute(...)
        ...
    }
}

Answer 1

I use prepared statements via PDO for security reasons when data is saved, but because the class is large these prepared statements are created inside different functions that are called thousands of times during the lifetime of the object (anywhere from one minute to thirty).

Whenever I look at bounty questions I always ask myself, "Are they even solving the correct problem?" Is executing the same query with different parameters thousands of times during the lifetime of this object really the best way to go?

• If you are doing multiple SELECTs then maybe a better query that fetches more information at once would be better.
• If you are doing multiple INSERTs then maybe batch inserts would serve you better.

---

If after evaluating the above options you decide that you still need to call these statements thousands of times during the life of the object then yes, you can cache the result of a prepared statement:

1. Measure current performance.
2. Turn off emulated prepares.
3. Measure the performance impact.
4. Use a technique called memoization or lazy loading to cache the prepare but only prepare a query when it is actually used.
5. Measure the performance impact again.

This allows you to see the impact of each piece that you changed. I would suspect that if you are really calling these queries thousands of times then some or all of these changes will help you but you must measure before and after to measure to know.

Answer 2

Storing the statements as variables works on paper. Be wary about performance though.

In particular, there's a world of difference between real prepares (which are off by default for MySQL) or emulated prepares (default for MySQL, using PDO::ATTR_EMULATE_PREPARES).

An emulated prepared statement will parse the query locally. Upon getting executed, they'll replace the parameters by their value and ship the final SQL string to the client. Upon receiving it, the database will parse the query, come up with a query plan, execute it, and return rows.

A real prepared statement will ship the query to be prepared straight to the database. The latter will parse it, prepare a generic query plan based on the query and the unknown variables, and return a prepared statement for use by PHP. When PDO executes the statement, it ships the prepared statement back along with the parameters. The database then executes the prepared query plan and returns rows.

As you may have noted, a real prepared statement involves a lot of back and forth between PHP and the DB. This is offset by the fact that the query is planned once and for all. Sometimes this is desirable (a similar query is used many times); sometimes not (the query is used a single time).

A further caveat is that a real prepared statement's query plan may or may not be the best possible one owing to the variables involved. Suppose an b-tree index on foo (bar):

select bar from foo order by bar limit ?

If the variable is small, an index scan is desirable; if it's larger, a bitmap index scan makes sense if available; if it's huge, a seq scan becomes desirable. In the latter two cases, the planner will also need to pick a sorting method. But since the query planner is tasked with coming up with a plan, Murphy's law states that it'll occasionally pick the worst possible plan for your particular use case. And the next thing you know, you'll end up scanning the sorting the entire table to retrieve a couple of rows, or following the index on bar to retrieve the entire table.

Lastly, and as an aside, you might want to look into ORMs if you're not familiar with them already.

Answer 3

Technically it is possible, as you already know by simply trying or just reading:

The query […] can be executed multiple times.

I would consider preparing all statements in the constructor as a bad idea. I guess it will become unmaintainable if you got a bunch of SQL statements in the constructor without any context. Furthermore you might prepare more than you actually need.

One idea to overcome this is using a statement map:

private $statments = array();

public function getStatement($sql)
{
    if (! isset($this->statements[$sql])) {
        $this->statements[$sql] = $this->pdo->prepare($sql);
    }
    return $this->statements[$sql];
}

This will prepare statements only once and you got your SQL context in the right place.

But I would call this a premature optimization because your DBS' query cache is most likely doing this for you.