18.2. Tweaking the Model

In symfony, the model layer has the reputation of being the slowest part. If benchmarks show that you have to optimize this layer, here are a few possible improvements.

18.2.1. Optimizing Propel Integration

Initializing the model layer (the core Propel classes) takes some time, because of the need to load a few classes and construct various objects. However, because of the way symfony integrates Propel, these initialization tasks occur only when an action actually needs the model — and as late as possible. The Propel classes will be initialized only when an object of your generated model is autoloaded. This means pages that don't use the model are not penalized by the model layer.

If your entire application doesn't require the use of the model layer, you can also save the initialization of the sfDatabaseManager by switching the whole layer off in your settings.yml:

all:
  .settings:
    use_database: off

The generated model classes (in lib/model/om/) are already optimized — they don't contain comments, and they benefit from the autoloading system. Relying on autoloading instead of manually including files means that classes are loaded only if it is really necessary. So in case one model class is not needed, having classes autoloaded will save execution time, while the alternative method of using include statements won't. As for the comments, they document the use of the generated methods but lengthen the model files — resulting in a minor overhead on slow disks. As the generated method names are pretty explicit, the comments are turned off by default.

These two enhancements are symfony-specific, but you can revert to the Propel defaults by changing two settings in your propel.ini file, as follows:

propel.builder.addIncludes = true   ; Add include statements in generated classes
                                    ; Instead of relying on the autoloading system
propel.builder.addComments = true   ; Add comments to generated classes

18.2.2. Limiting the Number of Objects to Hydrate

When you use a method of a peer class to retrieve objects, your query goes through the hydrating process (creating and populating objects based on the rows of the result of the query). For instance, to retrieve all the rows of the article table with Propel, you usually do the following:

$articles = ArticlePeer::doSelect(new Criteria());

The resulting $articles variable is an array of objects of class Article. Each object has to be created and initialized, which takes time. This has one major consequence: Contrary to direct database queries, the speed of a Propel query is directly proportional to the number of results it returns. This means your model methods should be optimized to return only a given number of results. When you don't need all the results returned by a Criteria, you should limit it with the setLimit() and setOffset() methods. For instance, if you need only the rows 10 to 20 of a particular query, refine the Criteria as in Listing 18-1.

Listing 18-1 - Limiting the Number of Results Returned by a Criteria

$c = new Criteria();
$c->setOffset(10);  // Offset of the first record returned
$c->setLimit(10);   // Number of records returned
$articles = ArticlePeer::doSelect($c);

This can be automated by the use of a pager. The sfPropelPager object automatically handles the offset and the limit of a Propel query to hydrate only the objects required for a given page. Refer to the pager documentation for more information on this class.

18.2.3. Minimizing the Number of Queries with Joins

During application development, you should keep an eye on the number of database queries issued by each request. The web debug toolbar shows the number of queries for each page, and clicking the little database icon reveals the SQL code of these queries. If you see the number of queries rising abnormally, it is time to consider using a Join.

Before explaining the Join methods, let's review what happens when you loop over an array of objects and use a Propel getter to retrieve details about a related class, as in Listing 18-2. This example supposes that your schema describes an article table with a foreign key to an author table.

Listing 18-2 - Retrieving Details About a Related Class in a Loop

// In the action
$this->articles = ArticlePeer::doSelect(new Criteria());

// Database query issued by doSelect()
SELECT article.id, article.title, article.author_id, ...
FROM   article

// In the template
<ul>
<?php foreach ($articles as $article): ?>
  <li><?php echo $article->getTitle() ?>,
    written by <?php echo $article->getAuthor()->getName() ?></li>
<?php endforeach; ?>
</ul>

If the $articles array contains ten objects, the getAuthor() method will be called ten times, which in turn executes one database query each time it is called to hydrate one object of class Author, as in Listing 18-3.

Listing 18-3 - Foreign Key Getters Issue One Database Query

// In the template
$article->getAuthor()

// Database query issued by getAuthor()
SELECT author.id, author.name, ...
FROM   author
WHERE  author.id = ?                // ? is article.author_id

So the page of Listing 18-2 will require a total of 11 queries: the one necessary to build the array of Article objects, plus the 10 queries to build one Author object at a time. This is a lot of queries to display only a list of articles and their author.

If you were using plain SQL, you would know how to reduce the number of queries to only one by retrieving the columns of the article table and those of the author table in the same query. That's exactly what the doSelectJoinAuthor() method of the ArticlePeer class does. It issues a slightly more complex query than a simple doSelect() call, but the additional columns in the result set allow Propel to hydrate both Article objects and the related Author objects. The code of Listing 18-4 displays exactly the same result as Listing 18-2, but it requires only one database query to do so rather than 11 and therefore is faster.

Listing 18-4 - Retrieving Details About Articles and Their Author in the Same Query

// In the action
$this->articles = ArticlePeer::doSelectJoinAuthor(new Criteria());

// Database query issued by doSelectJoinAuthor()
SELECT article.id, article.title, article.author_id, ...
       author.id, author.name, ...
FROM   article, author
WHERE  article.author_id = author.id

// In the template (unchanged)
<ul>
<?php foreach ($articles as $article): ?>
  <li><?php echo $article->getTitle() ?>,
    written by <?php echo $article->getAuthor()->getName() ?></li>
<?php endforeach; ?>
</ul>

There is no difference in the result returned by a doSelect() call and a doSelectJoinXXX() method; they both return the same array of objects (of class Article in the example). The difference appears when a foreign key getter is used on these objects afterwards. In the case of doSelect(), it issues a query, and one object is hydrated with the result; in the case of doSelectJoinXXX(), the foreign object already exists and no query is required, and the process is much faster. So if you know that you will need related objects, call a doSelectJoinXXX() method to reduce the number of database queries — and improve the page performance.

The doSelectJoinAuthor() method is automatically generated when you call a propel-build-model because of the relationship between the article and author tables. If there were other foreign keys in the article table structure — for instance, to a category table — the generated BaseArticlePeer class would have other Join methods, as shown in Listing 18-5.

Listing 18-5 - Example of Available doSelect Methods for an ArticlePeer Class

// Retrieve Article objects
doSelect()

// Retrieve Article objects and hydrate related Author objects
doSelectJoinAuthor()

// Retrieve Article objects and hydrate related Category objects
doSelectJoinCategory()

// Retrieve Article objects and hydrate related objects except Author
doSelectJoinAllExceptAuthor()

// Synonym of
doSelectJoinAll()

The peer classes also contain Join methods for doCount(). The classes with an i18n counterpart (see Chapter 13) provide a doSelectWithI18n() method, which behaves the same as Join methods but for i18n objects. To discover the available Join methods in your model classes, you should inspect the generated peer classes in lib/model/om/. If you don't find the Join method needed for your query (for instance, there is no automatically generated Join method for many-to-many relationships), you can build it yourself and extend your model.

18.2.4. Avoid Using Temporary Arrays

When using Propel, objects are already hydrated, so there is no need to prepare a temporary array for the template. Developers not used to ORMs usually fall into this trap. They want to prepare an array of strings or integers, whereas the template can rely directly on an existing array of objects. For instance, imagine that a template displays the list of all the titles of the articles present in the database. A developer who doesn't use OOP would probably write code similar to what is shown in Listing 18-6.

Listing 18-6 - Preparing an Array in the Action Is Useless If You Already Have One

// In the action
$articles = ArticlePeer::doSelect(new Criteria());
$titles = array();
foreach ($articles as $article)
{
  $titles[] = $article->getTitle();
}
$this->titles = $titles;

// In the template
<ul>
<?php foreach ($titles as $title): ?>
  <li><?php echo $title ?></li>
<?php endforeach; ?>
</ul>

The problem with this code is that the hydrating is already done by the doSelect() call (which takes time), making the $titles array superfluous, since you can write the same code as in Listing 18-7. So the time spent to build the $titles array could be gained to improve the application performance.

Listing 18-7 - Using an Array of Objects Exempts You from Creating a Temporary Array

// In the action
$this->articles = ArticlePeer::doSelect(new Criteria());

// In the template
<ul>
<?php foreach ($articles as $article): ?>
  <li><?php echo $article->getTitle() ?></li>
<?php endforeach; ?>
</ul>

If you feel that you really need to prepare a temporary array because some processing is necessary on objects, the right way to do so is to create a new method in your model class that directly returns this array. For instance, if you need an array of article titles and the number of comments for each article, the action and the template should look like Listing 18-8.

Listing 18-8 - Using a Custom Method to Prepare a Temporary Array

// In the action
$this->articles = ArticlePeer::getArticleTitlesWithNbComments();

// In the template
<ul>
<?php foreach ($articles as $article): ?>
  <li><?php echo $article[0] ?> (<?php echo $article[1] ?> comments)</li>
<?php endforeach; ?>
</ul>

It's up to you to build a fast-processing getArticleTitlesWithNbComments() method in the model — for instance, by bypassing the whole object-relational mapping and database abstraction layers.

18.2.5. Bypassing the ORM

When you don't really need objects but only a few columns from various tables, as in the previous example, you can create specific methods in your model that bypass completely the ORM layer. You can directly call the database with Creole, for instance, and return a custom-built array. Listing 18-9 illustrates this idea.

Listing 18-9 - Using Direct Creole Access for Optimized Model Methods, in lib/model/ArticlePeer.php

class ArticlePeer extends BaseArticlePeer
{
  public static function getArticleTitlesWithNbComments()
  {
    $connection = Propel::getConnection();
    $query = 'SELECT %s as title, COUNT(%s) AS nb FROM %s LEFT JOIN %s ON %s = %sGROUP BY %s';
    $query = sprintf($query,
      ArticlePeer::TITLE, CommentPeer::ID,
      ArticlePeer::TABLE_NAME, CommentPeer::TABLE_NAME,
      ArticlePeer::ID, CommentPeer::ARTICLE_ID,
      ArticlePeer::ID
    );
    $statement = $connection->prepareStatement($query);
    $resultset = $statement->executeQuery();
    $results = array();
    while ($resultset->next())
    {
      $results[] = array($resultset->getString('title'), $resultset->getInt('nb'));
    }

    return $results;
  }
}

When you start building these sorts of methods, you may end up writing one custom method for each action, and lose the benefit of the layer separation — not to mention the fact that you lose database-independence.

18.2.6. Speeding Up the Database

There are many database-specific optimization techniques that can be applied regardless of whether you're using symfony. This section briefly outlines the most common database optimization strategies, but a good knowledge of database engines and administration is required to get the most out of your model layer.

Table queries are often based on non-primary key columns. To improve the speed of such queries, you should define indexes in your database schema. To add a single column index, add the index: true property to the column definition, as in Listing 18-10.

Listing 18-10 - Adding a Single Column Index, in config/schema.yml

propel:
  article:
    id:
    author_id:
    title: { type: varchar(100), index: true }

You can use the alternative index: unique syntax to define a unique index instead of a classic one. You can also define multiple column indices in schema.yml (refer to Chapter 8 for more details about the indexing syntax). You should strongly consider doing this, because it is often a good way to speed up a complex query.

After adding an index to a schema, you should do the same in the database itself, either by issuing an ADD INDEX query directly in the database or by calling the propel-build-all command (which will not only rebuild the table structure, but also erase all the existing data).

Unless specified otherwise, each request uses a single database connection in symfony, and the connection is closed at the end of the request. You can enable persistent database connections to use a pool of database connections that remain open between queries, by setting persistent: true in the databases.yml file, as shown in Listing 18-11.

Listing 18-11 - Enabling Persistent Database Connection Support, in config/databases.yml

prod:
  propel:
    class:          sfPropelDatabase
    param:
      persistent:   true
      dsn:          mysql://login:passwd@localhost/blog

This may or may not improve the overall database performance, depending on numerous factors. The documentation on the subject is abundant on the Internet. Make sure you benchmark your application performance before and after changing this setting to validate its interest.