How do prepared statements work?

Question

I write several DB routines, and I use prepared statements. My PDO environment is with PHP5.

I understand that prepared statements mostly provide a performance advantage, as well as some auxiliary bonuses, such as the lack of manual SQL-escape input.

My question is about the performance part.

I have two implementations of the getPrice function below, which takes a product identifier and returns its price.

getPrice_A reuses the same PDOStatement object for subsequent calls within the same script execution. Is this necessary or recommended? If so, is there a way to avoid duplicating this extra code through each get * () in each individual model?

getPrice_B creates a new PDOStatement object for each call. Does the DBMS know that this expression has already been prepared and may still miss some work? In other words, does this implementation really take advantage of the performance of trained statements?

Having written all this and reading this, I believe that getPrice_B is fine, and getPrice_A provides little benefit on top of this, which may or may not be worth the extra complication.

I still would like to hear for sure from someone more famous, but.

Assume that $pdois a valid connected PDO in the examples below.

<?php
class Product {
    static function &getPrice_A($id) {
        static $stmt;
        if (!$stmt) {
            $stmt = $pdo->prepare('SELECT price FROM products WHERE id = ?');
        }
        $stmt->execute(array($id));
        return $stmt->fetchColumn(0);
    }

    static function &getPrice_B($id) {
        $stmt = $pdo->prepare('SELECT price FROM products WHERE id = ?');
        $stmt->execute(array($id));
        return $stmt->fetchColumn(0);
    }
}

// example usage:
$price = Product::getPrice(4982);
echo "Product 4982 costs $price\n";

Answer 1

, , SQL-, , , , . , Product::getPrice_A , , . , , , , , .

", " ( , ). , , - , , , (, , ... ).

, ( ) , ... memcached. .