Propel Query Optimization Tips

by Alvaro

Below I will like to share some snippets for optimizing Propel queries.

Replace MyTablePeer::retrieveByPk() with a custom query.

If in our code we have something like:

if(MyTablePeer::retrieveByPk($id))
{
  //do something here
}

Where we can see that the result from the retrieveByPk() call is used as a boolean, it is better to replace it with a custom query like this:

public static function recordExists($id)
{
  $c = new Criteria();
  $c->add(self::ID, $id);
  return self::doCount($c) > 0;
}

This method will be added to the MyTablePeer class. Asuming that self::ID is the primary key of the table this will return true if there is a record entry with that id on the table -you should adapt the Criteria to your needs.

Then in our code we can do like this:

if(MyTablePeer::recordExists($id))
{
//do something here
}

Instead of retrieving the whole row from the database and asking Propel to populate an object, we just get a boolean based on the result of the doCount.

Use custom queries when possible.

In this tip remember the when possible and add a when needed. Here is an example to clarify my point:

Let say we are retrieving 50 rows from the database to iterate over them and display one field in a snippet like this:

<?php foreach($users as $user): ?>
  <?php echo $user->getNickname(); ?>
<?php endforeach; ?>

Then is better to perform a custom query to retrieve the nickname column instead of getting the whole row for each object. Just keep in mind that this kind of change should we performed on a per need basis. If this action is getting slow or raising the database load, then we have to optimize it. If this is not the case, then we should think twice before doing this kind of changes, where the code get little bit more complicated. Why? Because if we start to have custom queries everywhere then we lose the ease and power of Propel.

Here is the example:

public static function retrieveActiveNicknames()
{
  $c = new Criteria();
  $c->clearSelectColumns();
  $c->addSelectColumn(UserPeer::NICKNAME);
  $c->add(UserPeer::ACTIVE, true);
  return UserPeer::doSelectRS($c);
}

This method is added to the UserPeer class. As you can see, after creating the Criteria object, we clear the select columns. Then we provide the where clauses. The return will be a Propel ResultSet object. In the propel installation folder you can check the ResultSet Interface to see the available methods.

To use this code we can do the following inside an action:

$this->nicknames = UserPeer:: retrieveActiveNicknames();
$this->nicknames->setFetchMode(ResultSet::FETCHMODE_ASSOC);

Then in a template:

<?php while($nicknames->next()):?>
  <?php $nickname = $nicknames->getRow();?>
  <?php echo $nickname['nickname']; ?>
<?php endwhile; ?>

In the last snippet I also show how to handle the case when we are retrieving more than one column. In this case to get the current row from the resultset we call the method getRow(). It will return an array whose structure will vary according to the fetch mode we are using.

If we need just a few columns and we know the order that they will be returned from the database then we can do the following:

<?php while($nicknames->next()):?>
  <?php echo $nicknames->getString(1);?>
<?php endwhile; ?>

For the last snippet to work we have to set the fetch mode to ResultSet::FETCHMODE_NUM. Keep in mind that the column index in a resultset start at 1. Inside the ResultSet interface we can see the available methods to retrieve different data types like getInt(), getFloat(), etc.

Conclusion

After we improved an action with this techniques we saw that from over 20 MB of memory usage we went to less than 10 MB performing the same amount of queries. While we shouldn’t go wild and start adding custom queries all over the place, I suggest you to keep an eye on the database load and memory usage raised by Propel to improve the performance of your website.

Posted in Development and tagged . Bookmark the permalink.


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Join Our Team in Shanghai

Now hiring PHP Developer in Shanghai