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.