PHP – DRY with Dynamic SQL vs. Prepared Statements

dryMySQLPHP

When dealing with data, one finds that, essentially, the same code is repeated in various incarnations:

-- MySQL:
CREATE TABLE users (
    id int NOT NULL auto_increment PRIMARY KEY,
    name varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    UNIQUE KEY akUser (email)
)

// PHP
class User extends DBModel {
    protected $id;
    protected $name;
    protected $email;

    function __construct() {
        $this->queries = array(
            'getById' => 'SELECT id, name, email FROM users WHERE id = :id',
            'addUser' => 'INSERT INTO users (name, email) VALUES (:name, :email)',
            'updateUser' => 'UPDATE users SET name = :name, email = :email WHERE id = :id',
        );
    }

    public function loadById($id) {
        $stmt = $this->getPreparedStatement('getById');
        $stmt->bindValue(':id', $id);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_INTO, $this);
        return $stmt->fetch();
    }
    public function save() {
        if ($this->id === NULL) {
            $this->add();
        }
        else {
            $this->update();
        }
    }
    public function add() {
        $stmt = $this->getPreparedStatement('addUser');
        $stmt->bindValue(':name', $this->name);
        $stmt->bindValue(':email', $this->email);
        $stmt->execute();
    }
    public function update() {
        $stmt = $this->getPreparedStatement('updateUser');
        $stmt->bindValue(':name', $this->name);
        $stmt->bindValue(':email', $this->email);
        $stmt->bindValue(':id', $this-id);
        $stmt->execute();
    }
}

If you notice, the fields 'name' and 'email' each occur 7 times throughout PHP and MySQL. While this is not much of a problem with this small example, with large tables, and a large number of tables, this gets out of hand fairly quickly.

As a quick side-note: with PHP running as a web service, the application lifetime is very short. Many of the queries that get executed — adding a user, for instance — would get executed only once in the application lifetime, so prepared statements really don't offer much of an advantage. Even with connection sharing, the individual instances don't have access to the previously prepared statement (unless I'm missing something), so it would re-prepare the statement anyway.

With some basic database-level reflection (the results of which are either cached, or automatically generated before deployment), SQL statements can be created automatically, eliminating the need to code the basic SELECT/INSERT/UPDATE/DELETE statements individually – cutting down on code considerably.

-- (table create statement, of course, stays the same)

class User {
    protected $id;
    protected $name;
    protected $email;

    public function getById($id) {
        return MyDb::getById('Users', $id, $this);
    }
    public function save() {
        return MyDb::save('Users', $this);
    }
}

My question: is the duplication of code worth the few CPU cycles and more complex code & maintenance needs?

Best Answer

Essentially you're asking whether code cleanliness is more important than performance; well, that can vary depending on the situation. Without really knowing more about the exact situation, I would say:

  • Performance is unlikely to be noticably worse with an ORM solution in this case. Unless CPU usage is a known bottleneck on your web server, your code is most likely spending orders of magnitude more time waiting for the DB to respond than it is preparing the SQL statement. When it comes to performance questions though, a good idea is to measure (the answer is often not what seems the most obvious).
  • I am a firm believer in ORM solutions. They go a long way to standardise how you access the DB, allow the rest of the system to be less DB oriented, take away the ability to make simple mistakes, and keep repetitions to a minimum. There are some (complex) cases where you will have to jump through hoops to make the ORM perform acceptably well, but querying a single table is not it (and in these cases, you can usually hand-code whatever you need). I don't know many people who have tried it and have gone back to not using them.
Related Topic