Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
Maybe what you are really seeking is the answer to this question. –  kevin cline Sep 3 '11 at 2:00
    
Actually, I was asking more about the performance costs of using an ORM solution vs. native SQL (asked more simply here: stackoverflow.com/questions/451845/orm-performance-cost). I infer that you suggest using an ORM solution over a roll-my-own solution, which may be worth while, but isn't really what I was asking. –  Doug Kress Sep 3 '11 at 17:04
2  
@Doug Kress: It's perfectly fine to have everything generated as long as you have the possibility to override it manually. ADO.NET does it for example (CommandBuilder in DataAdapters). It's just tedious to write all that standard stuff over and over again. The overhead can be neglected in most use-cases. –  Falcon Sep 3 '11 at 21:22
    
I don't understand how SQL generation impacts the use of prepared statements. BTW, prepared statements are cached at the database server to save the relatively high cost of parsing SQL. –  kevin cline Sep 4 '11 at 19:07
    
@DougKress Where do you do the connection? –  johnny Aug 26 at 19:09

2 Answers 2

up vote 1 down vote accepted

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.
share|improve this answer

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

No, it's not worth the CPU cycles and the additional code. Most data access frameworks I know generate queries if you don't supply a custom one.

Your intended use makes the database the bottleneck. Keep your generalized DAL extensible, so that you can override the default behaviour with something customized when the need arises.

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.

This is of no concern here. It's the database that manages such statements. Most of the time they are cached on the RDBMS, regardless of whether they were generated dynamically on the client or the connection was closed. Dynamic query generation and prepared statements do not exclude each other, so I encourage you to use them, at least for the sake of preventing SQL-injections.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.