Performance issue

[Krzysiaczek]

Hi

I've picked up Phalcon because of the best declared performance. I was trying to analyse my application and discovered something really weird. I am using version 4.1.0.

Please advise if I am doing something wrong in here or is it a bug in Phalcon - as it's what it seems to me.

In the controller I prepare (a vary standard way I guess) a variable for a view taken from the DB request (by PHQL in Model->Categories->getAvailableCategories()). This query is not very well optimised yet, so it returns the results in 60 msec right now.

$c = new Categories(); // Model
$availableCategories = $c->getAvailableCategories(... some optional variables ...);
$this->view->setVar('availableCategories', $availableCategories);

The problem I discovered is that when I run a loop in the phtml view I get another 60 msec to get content for the $availableCategores variable

// inside the PHTML view
<?php foreach ($availableCategories as $category): ?>

It takes another 60 msec to get this line in a view and I can see in the DB logs that this underlying query has been executed twice.

This sound like a bug to me.

I do not see anything in documentation to use a data prepared and collected already in the variable inside the controller. This make preparation of any data in a controller pointless if queries need to run again when requested from a view. It simply doubles the whole execution time.

[Krzysiaczek]

I've just updated to the recent version of php 7.4.28 and phalcon 4.1.2 and it's all the same.

[BeMySlaveDarlin]

@Krzysiaczek Please provide detailed code and logs.
getAvailableCategories isn't phalcon source code method, so we can't say anything cos we didn't see code inside.
Also would be helpfull, if you provide exact examples of code and queries (which called twice).

And thanks for report!

UPD. Right now phalcon orm doesn't provides eager loading features. You can look for phalcon eager loading through github packages for thirdparty implementation.

[Krzysiaczek]

The code inside the method getAvailableCategories is pretty basic

public function getAvailableCategories(... same variables ...){
     // prepare some dynamic $params
     $phql = "SELECT c.category_id, c.name, count(DISTINCT(".$count_by.".activity_id)) as how_many, c.home_page
        FROM App\Models\Categories c  .... etc "
     return $this->modelsManager->executeQuery($phql, $params);
}

and here is the problem that this way what is returned is the Phalcon\Mvc\Model\Resultset\Simple object which is not a data but the object which executes the query again when is called from the view.

I figured out that this could be easily fixed by adding ->toArray() at the end but the documentation doesn't mention anything like this.

Instead you can read things like this ...

$records  = $this
    ->modelsManager
    ->executeQuery($phql)
;

After reading that it's clear that $records variable should hold the results of the executed query.

Instead if you pass $records to a view and run foreach($records as $row) the query is executed again.

[BeMySlaveDarlin]

This is related to lazy loading behavior of Active Record paradigm, which is Phalcon ORM itself.
That is common thing about all AR based ORMs - requesting on-demand using generators (yield exec query) to lower memory consumption on big datasets.

As i mentioned before, you can look for eager loading libraries to wrap queries. This will allow you to cut of subrequests and boost request time perfomance, but you will pay with proccess memory consumption raise.

[niden]

You can always introduce caching in your method. Something like this:

public function getAvailableCategories(... same variables ...)
{
    $key = sha1($variables); // you can calculate that
    
    if (true !== isset($this->cacheMap[$key]) {
         // prepare some dynamic $params
         $phql = "SELECT c.category_id, c.name, count(DISTINCT(".$count_by.".activity_id)) as how_many, c.home_page
            FROM App\Models\Categories c  .... etc "
         
        $this->cacheMap[$key] = $this->modelsManager->executeQuery($phql, $params);
    }

    return $this->cacheMap[$key];
}

This will cache this result during this request, so you will not get duplicate queries.

Alternatively you can make use of relationships in your models and use the reusable => true parameter.