ORM or SQL query

Isnin, 22 Ogos 2011, 12:50 am0

I’ve been developing website using PHP for quite a while now, and one thing come across my mind. The company I’m working with is using basic query function using PHP PDO:

$db->prepare('query...');
$db->execute();
$db->fetchAll();

(Well, not actual code, just to show we’re using PHP PDO). We are using our own custom framework, and not utilize any ORM pattern which most popular framework use, such as CakePHP, Symfony, Yii, DooPHP etc. I wonder if using ORM will improve the performance of the website, or it is just a tool to simplify the code.

Using ORM, we don’t need to write SQL queries – we treat a table in database as an object by mapping it with PHP model class. Select, insert, update and delete rows in the table is a matter of calling correct function. However, IMO ORM will cause some performance drawbacks, because the SQL generated is not all the time optimized – querying unnecessary columns, generate too many queries etc.

By writing the SQL ourselves and call query() function manually, we have flexibility to change the SQL to execute in optimum performance. The drawback of this is we have to write similar SQL multiple times if we use it in different controller classes, while using ORM, we just need to call get() function to return specific row from db.

Personally, I’d like to use manual SQL query more, since it offer better flexibility. To overcome the need to rewrite similar SQL is just by wrap in one static function and let other classes to call it.

static function get_data() {
    $sql = 'SELECT ...';
    $db->query($sql);
    return $db->fetch();
}

In other class:

...
$data = another_class::get_data();
...