PHP PDO: how does re-preparing a statement affect performance

Question

I'm writing a semi-simple database wrapper class and want to have a fetching method which would operate automagically: it should prepare each different statement only the first time around and just bind and execute the query on successive calls.

I guess the main question is: How does re-preparing the same MySql statement work, will PDO magically recognize the statement (so I don't have to) and cease the operation?

If not, I'm planning to achieve do this by generating a unique key for each different query and keep the prepared statements in a private array in the database object - under its unique key. I'm planning to obtain the array key in one of the following ways (none of which I like). In order of preference:

• have the programmer pass an extra, always the same parameter when calling the method - something along the lines of basename(__FILE__, ".php") . __LINE__ (this method would work only if our method is called within a loop - which is the case most of the time this functionality is needed)
• have the programmer pass a totally random string (most likely generated beforehand) as an extra parameter
• use the passed query itself to generate the key - getting the hash of the query or something similar
• achieve the same as the first bullet (above) by calling debug_backtrace

Has anyone similar experience? Although the system I'm working for does deserve some attention to optimization (it's quite large and growing by the week), perhaps I'm worrying about nothing and there is no performance benefit in doing what I'm doing?

Answer 1

MySQL (like most DBMS) will cache execution plans for prepared statements, so if user A creates a plan for:

SELECT * FROM some_table WHERE a_col=:v1 AND b_col=:v2

(where v1 and v2 are bind vars) then sends values to be interpolated by the DBMS, then user B sends the same query (but with different values for interpolation) the DBMS does not have to regenerate the plan. i.e. it's the DBMS which finds the matching plan - not PDO.

However this means that each operation on the database requires at least 2 round trips (1st to present the query, the second to present the bind vars) as opposed to a single round trip for a query with literal values, then this introduces additional network costs. There is also a small cost involved in dereferencing (and maintaining) the query/plan cache.

The key question is whether this cost is greater than the cost of generating the plan in the first place.

While (in my experience) there definitely seems to be a performance benefit using prepared statements with Oracle, I'm not convinced that the same is true for MySQL - however, a lot will depend on the structure of your database and the complexity of the query (or more specifically, how many different options the optimizer can find for resolving the query).

Try measuring it yourself (hint: you might want to set the slow query threshold to 0 and write some code to convert literal values back into anonymous representations for the queries written to the logs).

Answer 2

Believe me, I've done this before and after building a cache of prepared statements the performance gain was very noticeable - see this question: Preparing SQL Statements with PDO.

An this was the code I came up after, with cached prepared statements:

function DB($query)
{
    static $db = null;
    static $result = array();

    if (is_null($db) === true)
    {
        $db = new PDO('sqlite:' . $query, null, null, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
    }

    else if (is_a($db, 'PDO') === true)
    {
        $hash = md5($query);

        if (empty($result[$hash]) === true)
        {
            $result[$hash] = $db->prepare($query);
        }

        if (is_a($result[$hash], 'PDOStatement') === true)
        {
            if ($result[$hash]->execute(array_slice(func_get_args(), 1)) === true)
            {
                if (stripos($query, 'INSERT') === 0)
                {
                    return $db->lastInsertId();
                }

                else if (stripos($query, 'SELECT') === 0)
                {
                    return $result[$hash]->fetchAll(PDO::FETCH_ASSOC);
                }

                else if ((stripos($query, 'UPDATE') === 0) || (stripos($query, 'DELETE') === 0))
                {
                    return $result[$hash]->rowCount();
                }

                else if (stripos($query, 'REPLACE') === 0)
                {
                }

                return true;
            }
        }

        return false;
    }
}

Since I don't need to worry about collisions in queries, I've ended up using md5() instead of sha1().