Reducing Query Calls with Caching in ZF2

To improve performance on our apps, we’ve been working on a combination of optimizing queries to require fewer additional calls just to get a single bit of data (like the name of who added a record) and using caching to access data that doesn’t change frequently.

For example, core pick lists used in apps often are lists that change quite infrequently, such as user roles, countries, states, counties, formats, etc, but that are often still stored in a database for convenience in querying and for those rare occasions when they do change.  As such, they are ripe data points to reduce DB hits on by caching them using ZF2’s built in functions.

Here is an example for use with getting states.  Note the separate caches for the individual state and the query results of states.

public function getState($state_id)
{
    $target_key = self::$base_key . '_states_' . $state_id;
    $was_get_successful = false;

    // check to see if the cache has this content
    $query_results = $this->getMonthCache()->getItem($target_key, $was_get_successful);

    if (!$was_get_successful || empty($query_results)) {
        $select_query = new Sql\Select();
        $select_query
            ->from('static_states')
            ->columns(array(
                'state_id',
		'state_abbreviation',
                'state_name',
            ))
            ->where(array('state_id' => $state_id));

        $prepared_db_statement = self::$sql->prepareStatementForSqlObject($select_query);
        $query_results = $prepared_db_statement->execute()->current();

        // add our new results to our cache for future pulling
        $this->getMonthCache()->setItem($target_key, $query_results);
    }

    $state = new stdClass();

    if (!empty($query_results)) {
        $hydrator = new ObjectProperty();
        $hydrator->hydrate($query_results, $state);
    }

    return $state;
}

public function getStates()
{
    $target_key = self::$base_key . '_all';

    $was_get_successful = false;

    // check to see if the cache has this content
    $query_results = $this->getMonthCache()->getItem($target_key, $was_get_successful);

    if (!$was_get_successful || empty($query_results)) {
        $select_query = new Sql\Select();
        $select_query
            ->from('static_states')
            ->columns(array(
                'state_id',
		'state_abbreviation',
                'state_name',
            ))
            ->order('state_name')
        ;

        $prepared_db_statement = self::$sql->prepareStatementForSqlObject($select_query);
        $query_results = $prepared_db_statement->execute();

        $result_set = new ResultSet\ResultSet();
	$result_set->initialize($query_results);

	$query_results = $result_set->toArray();

        // add our new results to our cache for future pulling
        $this->getMonthCache()->setItem($target_key, $query_results);
    }

    $entity_prototype = new stdClass();
    $hydrator = new ObjectProperty();
    $states = new ResultSet\HydratingResultSet($hydrator, $entity_prototype);
    $states->initialize($query_results);

    return $states;
}    

Now, of course, you may notice that it’s referencing a function called getMonthCache. That’s where the actual caching happens. We have ones for differing time periods, but here is the month one:

public function getMonthCache()
{
    $cache = StorageFactory::factory(array(
		'adapter' => array(
			'name' => 'filesystem',
			'options' => array(
			    'cache_dir' => './data/cache/indexes',
			    'namespace' => 'sitename_1m_cache',
			    'ttl' => 60 * 60 * 24 * 7 * 5, // 60 seconds * 60 minutes * 24 hours * 7 days * 5 weeks
			    'dir_permission' => '0764',
			    'file_permission' => '0664',
			),
		),
		'plugins' => array(
			'serializer',
			'exception_handler' => array(
				'throw_exceptions' => true
			),
		)
	));

	return $cache;
}

Doing this kind of caching, we’ve seen large-record page loads drop from 30-40 seconds to as little as 2, simple because its going from hitting the database some 200 times to just 1.  This post only covers caching of queries, but you can also use the same to cache file content, arrays, XML content, etc, even view output, which can also greatly reduce loads.  I’ll post a second article on that example a bit later. 🙂 For now those, this is a quick, simple example of how easy it is to implement basic caching in Zend that can help reduce unneeded DB calls.