We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Many unneccesary count queries when using models slowing down the page load time

Hello,

I am using the Phalcon Model for my Application in combination with SQLite and the number of unneccesary count queries have some a great negative impact on the performance.

This Query for example took 1324 ms

SELECT si.* FROM customer si JOIN tag_to_customer tts ON si.id = tts.customer_id JOIN tag t ON t.id = tts.tag_id WHERE t.tag = ?1 EXCEPT SELECT si.* FROM tag_to_customer tts LEFT JOIN customer si ON si.id = tts.customer_id LEFT JOIN tag t ON t.id = tts.tag_id WHERE t.tag IN (?2) LIMIT 1

Afterwads a count query gets executed, hence I'm using the model's findByRawSql method, which took 1297 ms and makes no sense in this context:

 SELECT COUNT(*) "numrows" FROM (SELECT si.* FROM customer si JOIN tag_to_customer tts ON si.id = tts.customer_id JOIN tag t ON t.id = tts.tag_id WHERE t.tag = ?1 EXCEPT SELECT si.* FROM tag_to_customer tts LEFT JOIN customer si ON si.id = tts.customer_id LEFT JOIN tag t ON t.id = tts.tag_id WHERE t.tag IN (?2) LIMIT 1)

This doubles the Page loading time in this case by two.

Another example is this query which gets executed for loading a related model:

 SELECT "note"."id", "note"."inserted", "note"."updated", "note"."customer_id", "note"."create_user_id", "note"."user_id", "note"."message", "note"."due_date" FROM "note" WHERE "note"."id" = :APR0 LIMIT :APL0

Even here a count gets executed.

 SELECT COUNT(*) "numrows" FROM (SELECT "note"."id", "note"."inserted", "note"."updated", "note"."customer_id", "note"."create_user_id", "note"."user_id", "note"."message", "note"."due_date" FROM "note" WHERE "note"."id" = :APR0 LIMIT :APL0)

The relation is defined as a has one and an additional count does not makes sense here at all.

$this->hasOne('note_id', '\ACME\Model\Note', 'id', ['alias' => 'Note', 'reusable' => true]);

Therefore the number of querys needed for showing a single page is nearly doubled, only because of many needless counts.

Is there a way to tell Phalcon not to perform a count on every query executed by a model?



79.0k
edited Mar '17

Have you ever checked Doctrine's raw queries?

In general, Active Record (ORM) work in that fashion, and Phalcon's models are built on top of Active Record pattern.

I dislike this concept too, that's why I still rely on plain PDO (my own ORM version built on top of Phalcon MySQL adapter). https://docs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html



3.1k
in reply to Jonathan Aaron Steel

Have you ever checked Doctrine's raw queries?

I use Hibernate in my Java Project, which had a great influenece on Doctrine, and it does not perform unneccessary queries ;) .

In general, Active Record (ORM) work in that fashion, and Phalcon's models are built on top of Active Record pattern.

I'm not complaining the querys itself, but the counts are not necessary when loading a as has one related child for example. Hence I am searching for an way of disabling thoose count queries when they are not needed or if I know that no count is needed in my particular case.

I dislike this concept too, that's why I still rely on plain PDO (my own ORM version built on top of Phalcon MySQL adapter). https://docs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html

Writing a own ORM might be a lot of fun, but the Phalcon ORM is quite good in my cases beside thoose counts and a missing eager loading on related models.



3.1k
edited Mar '17

I finally found the reason why so many count gery gets fired, in https://github.com/phalcon/cphalcon/blob/master/phalcon/db/result/pdo.zep the numRows methods fires the count query if the PDO driver is not pgsql or mysql.

According to the documentation of PHP's PDO numRows are not returned by ever DBMs: https://php.net/manual/en/pdostatement.rowcount.php .

I try to build my own ResultSet Class which omits the let rowCount = result->numRows(); operation which should solve the problem with the count on the long running query. Maybe I find a way to solve the problem with the related models , too.



3.1k
Accepted
answer

This ResultSet class solves on of my problems:

<?php
namespace ACME\Model;

use Phalcon\Db;
use Phalcon\Mvc\Model\Resultset\Simple;

/**
 * Class OmitNumRowsResultSet
 *
 * A resultset which omits the expensive count queries using sqlite.
 *
 * @package ACME\Model
 */
class OmitNumRowsResultSet extends Simple {

    /**
     * OmitNumRowsResultSet constructor
     *
     * @param array $columnMap
     * @param \Phalcon\Mvc\ModelInterface|\Phalcon\Mvc\Model\Row $model
     * @param \Phalcon\Db\Result\Pdo|null $result
     * @param \Phalcon\Cache\BackendInterface $cache
     * @param boolean $keepSnapshots
     */
    public function __construct($columnMap, $model, $result, \Phalcon\Cache\BackendInterface $cache = null, $keepSnapshots = null)
    {
        $this->_model = $model;
        $this->_columnMap = $columnMap;
        $this->_keepSnapshots = $keepSnapshots;

        $this->_count = 0;
        $this->_rows = [];

        if (!is_object($result)) {
            return;
        }

        $this->_result = $result;

        if ($cache !== null ) {
            $this->_cache = $cache;
        }

        $result->setFetchMode(Db::FETCH_ASSOC);

        $rows = $result->fetchAll();
        if (is_array($rows)) {
            $this->_rows = $rows;
            $this->_count = count($this->_rows);
        }
    }
}

The usage is simple:

$sqlQuery = 'SELECT * FROM customer WHERE query_is_slow = 1'

$customer = new Customer();

return new OmitNumRowsResultSet(
    null,
    $customer,
    $customer->getReadConnection()->query($sqlQuery, $params)
);