SSbits - Home page
Site by Carbon Crayon
Submit a Post >

Snippets - Little bits of code to make you happy

Optimizing Complex Models

The SilverStripe ORM engine (Sapphire) makes working with your database a breeze, but that simplicity comes at a cost. Each database query contributes to slower page load times and a complicated data structure can create significant performance issues.

Here are a number of strategies that can be employed to improve your website query performance:

Cached Method Calls

Any subclass of ViewableData (including DataObjects, Pages and their Contorllers) can utilize cached method calls. Normally, a method is executed at the result returned fresh every time it is called:

class MyObject extends DataObject {

  function BigQuery() {
    return DataObject::get('OtherObject'); // lots of data!
  }

}

If you're using the result of the query multiple times and you know the data is unlikely to change between calls it is very simple to cache the result after the first call by appending an underscore to the method name:

class MyObject extends DataObject {

  function _BigQuery() {
    return DataObject::get('OtherObject'); // lots of data!
  }

}

Sapphire will store the result of the method in memory so that subsequent calls to BigQuery() return result without hitting the database. No other changes are required - you call BigQuery() without the underscore as usual.

De-normalizing Your Model

A completely normalized model is great for maintaining data integrity and makes CMS editing a breeze, but there are times where you just wish you had direct access to certain related fields without the need to traverse to the related object (with the additional database overhead that entails). For example, in a model where Employee objects belong to a single Company object, building a company directory for a large number of Employee objects might be time consuming, because the Company table has to be queried for each Employee.

class Employee extends DataObject {
   static $db = array(
      'Name' => 'Varchar',
      'Position' => 'Varchar',
   );
   static $has_one = array(
      'Company' => 'Company',
   );
}
class Company extends DataObject {
   static $db = array(
      'Name' => 'Varchar',
   );
   static $has_many = array(
      'Employees' => 'Employee',
   );
}
class Employee extends DataObject {

   static $db = array(
      'Name' => 'Varchar',
      'Position' => 'Varchar',
   );

   static $has_one = array(
      'Company' => 'Company',
   );

}

class Company extends DataObject {

   static $db = array(
      'Name' => 'Varchar',
   );

   static $has_many = array(
      'Employees' => 'Employee',
   );

}

By de-normalizing our model we bring the key Company descriptor fields into the Employee Object:

class Employee extends DataObject {

   static $db = array(
      'Name' => 'Varchar',
      'Position' => 'Varchar',
      // De-normalized fields:
      'CompanyName' => 'Varchar',
   );

   static $has_one = array(
      'Company' => 'Company',
   );

}

This is all well and good, but how do we retain data integrity? There are a couple of rules we want to enfoce on the new CompanyName field:

  1. It should be updated when the relevant Company is updated.
  2. Employee object is updated.
  3. It should not be editable directly from the Employee object.

We can use the onBeforeWrite and onAfterWrite DataObject methods to handle case 1 and 2, and implement a property setter method to intercept case 3:

class Employee extends DataObject {

   static $db = array(
      'Name' => 'Varchar',
      'Position' => 'Varchar',
       // De-normalized fields:
      'CompanyName' => 'Varchar',

   );

   static $has_one = array(
      'Company' => 'Company',
   );

   function setCompanyName($newName) {
      return false; // or throw an error.
   }

   function onBeforeWrite() {
      if($company = DataObject::get_by_id("Company", $this->record['CompanyID']))
         $this->record['CompanyName'] = $company->Name;
      else
         $this->record['CompanyName'] = "";
      parent::onBeforeWrite();
   }

}

class Company extends DataObject {

   static $db = array(
      'Name' => 'Varchar',
   );


   static $has_many = array(
      'Employees' => 'Employee',
   );

   function onAfterWrite() {
      foreach($this->Employees() as $employee)
         $employee->write(true); // forces all employees
             // of this company to update, pulling down the
             // company name.
      parent::onAfterWrite();
   }

}

These are just two strategies for improving performance for complex models, without sacrificing the ORM features baked into SilverStripe.

Hamish Campbell avatar

Hamish Campbell

Hamish is a web developer for Opus International Consultants in New Zealand. He creates tools for managing data online, with a particular interest in geographical information systems. He is also an active contributor to the SilverStripe open source project.

  • Martijn van Nieuwenhoven
    04/11/2010 5:12pm (4 years ago)

    Nice one on the cached underscore call!

    But what if you need to update hundreds or even thousands of Employees?

    In this example you will query each Employee (Object) in onAfterWrite, which is getting the complete Company object in each write again.

    How about using raw SQLQuery?

    //Employee onbeforewrite()
    $sqlQuery = new SQLQuery("Name","Company","ID=".$this->CompanyID);
    $this->CompanyName = $sqlQuery->execute()->value();

    //Company onAfterWrite()
    DB::query("UPDATE Employee SET CompanyName='".$this->Name."' WHERE CompanyID=".$this->ID);

  • Frank Mullenger
    05/11/2010 1:00am (4 years ago)

    That is a super handy tip about the underscore prefix! cheers

  • alex li
    05/11/2010 1:24am (4 years ago)

    Very useful tricks for imporving the performance for doing the DataObject retrieving..
    Thanks for sharing

  • Francisco Arenas
    05/11/2010 3:56am (4 years ago)

    thanks for sharing the tip Hamish, i have a question: when BigQuery is updated in memory?

  • dalesaurus
    15/11/2010 4:14pm (4 years ago)

    Wow, I just checked out the caching code for the Underscore Trick. Anything that is based on the ViewableData class has this built in for all methods (which is setup on instantiation in the defineMethods() method).

    It will use cachedCall on ANY method in the object (you can view in Dev mode with the ?debug_profile=1 url variable)

    So an important note to developers is BE CAREFUL USING UNDERSCORES IN YOUR FUNCTION NAMES! Especially if they return dynamic data, as cachedCall will only update on the first call.

  • Hamish Campbell
    15/11/2010 7:08pm (4 years ago)

    FYI, the underscore behaviour is documented in the wiki here:

    http://doc.silverstripe.org/objectmodel#caching

    That has information about clearing the cached value for a particular method, method and arguments, or the all methods on an object.

  • MRKDevelopment
    24/11/2010 2:30am (4 years ago)

    This is when I really wish we have the containable and recursive functions that cakePHP has.

    It really does make control over your query easy and doesn't depend on on hacks like the above method. Lets face it, storing the company in two areas in a database is just not the best method of dealing with code. It when the framework is making things harder no easier.

    In Sapphire we should be able to have param's when we query on an object to say what fields, related models and conditions we want in the results by using an array.

    This would be very cakePHP ish but is just a good way to deal with the ORM side of things.

  • Roman Schmid
    02/12/2010 3:30pm (4 years ago)

    I wonder if anybody successfully applied the caching mechanisms using the underscore prefix?
    I'm pretty sure there's a bug in the code which I reported here: http://open.silverstripe.org/ticket/6241

  • chris_bryer
    23/01/2011 6:31pm (4 years ago)

    Hamish, the new documentation doesnt have caching documented.. does it still exist?

    http://doc.silverstripe.org/objectmodel#caching

  • SamTheJarvis
    17/06/2011 1:04pm (3 years ago)

    Why have they removed 90% of their old documentation? Surely they haven't removed the caching functionality, why remove the documentation for it?

    Awesome post anyway Hamish.

  • John Black
    10/08/2011 10:59pm (3 years ago)

    More caching is discussed at
    http://www.balbus.tk/caching-dataobjectsets/

    That post indicates that the cache for an underscore method can be flushed by:
    $obj->flush('FunctionName')

    Hamish, or anyone, does that sound correct?

    Also -- there is a bug discussed there that would seem to make all this moot. Can you comment?

    -John

Post a comment ...

You cannot post comments until you have logged in. Login Here.

Advertisement

Site of the Month

Find SSbits on

Top Contributers

Rank Avatar Name
1 article image Aram Balakjian
2 article image Daniel Hensby
3 article image Marcus Dalgren
4 article image Hamish Campbell
5 article image Ty Barho
6 article image Martijn van Nieuwenhoven
7 article image Darren-Lee
8 article image Roman Schmid
9 article image Matt Clegg
10 article image dalesaurus

View full leaderboard


Advertisement