Duplicated Queries

[yannkost]

I have seen in the Yii:Debug Toolbar that there are a lot of duplicated queries when rendering some content from entries, especially when there are nested matrix fields and neo blocks. Now this can certainly be optimized to some degree with eagerloading and intelligent twig templates. Still, through the underlying Craft entryqueries, there will be duplicates (may it be element_sites owners, assets etc...).

Now, what if we, during the same request (and only during the same request), we cache all SELECT statements (No inserts/updates etc...)?

I had the following approach for this:

• Use a singleton class that caches the requests
• Overwrite the queryInternal Function from craft\db\Command (that itself overwrites the Command Class from Yii).

Singleton

class DbCacheModel {
    private static $instance = null;
    private $data = [];

    private function __construct() {
    }

    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    public function get($sql) {

        if (isset($this->data[$sql])) {
            return $this->data[$sql];
        }
        return null;
    }

    public function set($sql, $value) {
        $this->data[$sql] = $value;
    }
}

Class Overwrite

use Yii;
use yii\db\DataReader;
use yii\db\Exception;
use craft\db\Command as TempCommand;

class Command extends TempCommand  {

    /**
     * Performs the actual DB query of a SQL statement.
     * @param string $method method of PDOStatement to be called
     * @param int|null $fetchMode the result fetch mode. Please refer to [PHP manual](https://www.php.net/manual/en/function.PDOStatement-setFetchMode.php)
     * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used.
     * @return mixed the method execution result
     * @throws Exception if the query causes any problem
     * @since 2.0.1 this method is protected (was private before).
     */
    protected function queryInternal($method, $fetchMode = null) {
        $dbCache = \yoo\projectextension\models\DbCacheModel::getInstance();
        $rawSql = $this->getRawSql();
        $result = $dbCache->get($rawSql);
        if ($result !== null) {
            return $result;
        }

        list($profile, $rawSql) = $this->logQuery('yii\db\Command::query');

        if ($method !== '') {
            $info = $this->db->getQueryCacheInfo($this->queryCacheDuration, $this->queryCacheDependency);
            if (is_array($info)) {
                /* @var $cache \yii\caching\CacheInterface */
                $cache = $info[0];
                $cacheKey = $this->getCacheKey($method, $fetchMode, '');
                $result = $cache->get($cacheKey);
                if (is_array($result) && array_key_exists(0, $result)) {
                    Yii::debug('Query result served from cache', 'yii\db\Command::query');
                    return $result[0];
                }
            }
        }

        $this->prepare(true);

        try {
            $profile and Yii::beginProfile($rawSql, 'yii\db\Command::query');

            $this->internalExecute($rawSql);

            if ($method === '') {
                $result = new DataReader($this);
            } else {
                if ($fetchMode === null) {
                    $fetchMode = $this->fetchMode;
                }
                $result = call_user_func_array([$this->pdoStatement, $method], (array)$fetchMode);
                $this->pdoStatement->closeCursor();
            }

            $profile and Yii::endProfile($rawSql, 'yii\db\Command::query');
        } catch (Exception $e) {
            $profile and Yii::endProfile($rawSql, 'yii\db\Command::query');
            throw $e;
        }

        if (isset($cache, $cacheKey, $info)) {
            $cache->set($cacheKey, [$result], $info[1], $info[2]);
            Yii::debug('Saved query result in cache', 'yii\db\Command::query');
        }

        $dbCache->set($rawSql, $result);

        return $result;
    }
}

Then in the app.php I have specified the overwrite:

return [
    'id' => App::env('CRAFT_APP_ID') ?: 'CraftCMS',
    'components' => [
        'db' => function () {
            $config = craft\helpers\App::dbConfig();
            $config['commandClass'] = \yoo\projectextension\overrides\Command::class;
            return Craft::createObject($config);
        },
    ],
];

Using this approach I could reduce the duplicates to mostly 0, except for some custom queries or queries from Plugins that do not use the Craft Utilites for their queries.

Now I think this apporach is still pretty immature, as is relies on overwriting the Class from the CMS itself, but I wonder if there is an more accomplished way, to do the same thing?

As a side note: I also realized, that there are good performance wins with the caching of the db schema:

$config['enableSchemaCache'] = true;
$config['schemaCacheDuration'] = 60 * 60 * 24; // 1 day

Any feedback or opinions to this subject would be very welcome :)

[brandonkelly]

Caching is tricky. Too much caching can have a net negative impact on performance, and leads to bugs if the cache isn’t getting invalidated when it should. Caching every database query would certainly result in both of those.

Element queries do provide a cache() method though, which can be used to opt into caching them on a per-query basis. It automatically sets a tag dependency on the cache that ensures it will get cleared whenever something in the results may have changed.

Your first goal should be to reduce the number of queries that are getting executed in the first place, regardless of whether they’re cached, using eager-loading. Craft 5 makes that a lot easier thanks to lazy eager-loading with eagerly(). (Like with caching, eager-loading can have a net-negative impact if it’s overused, so limit it to areas where it’s actually making a significant impact.)

Then if you have areas where eager-loading isn’t helping, you can add cache() to reduce duplicates.

I did find one place in Craft where duplicate queries were getting executed by no fault of the developer – when calling elements’ getPrev() and getNext() methods. So those are now using cache() internally, as of the next release. (b8db7a7)

As a side note: I also realized, that there are good performance wins with the caching of the db schema:

$config['enableSchemaCache'] = true;
$config['schemaCacheDuration'] = 60 * 60 * 24; // 1 day

Yeah, Craft automatically enables schema caching when Dev Mode is disabled. No need to set that yourself.

[brandonkelly]

Craft 4.13.7 and 5.5.7 are out with that getPrev()/getNext() fix.