How to optimize query

[regesh]

I got about 2 thousand records with repeater field for each. And i need to get some result from that. Could anybody help me with how to optimize query for better performance (not 6-7 seconds as now)
I'm executing getSubs() functions. Thanks a lot!

<?php
function getNextAction($clientId){
    $result = [];
    $today = new \Datetime(date('Y-m-d', time()));
    $today = $today->format("U");
    $action = wire('pages')->findOne('sort=action_start,template=action,selectedClient='.$clientId.',action_start>'.$today);
    if($action->id){
        pre($action);
        $a = $action->selectedTrainer->text1.' '.$action->selectedTrainer->text2;
        $b = strftime("%d %B",$action->action_start);
        $result = [$a,$b];
    }
    return $result;
}

function getSubs(){
    $result = [];
    $x = 0;
    foreach (wire('pages')->get(1080)->children() as $client) {
        foreach ($client->clientSubscriptions as $sub) {
            if( (int)$sub->number1 - (int) $sub->number2 <=5 ){
                 $result[$x]['fio'] = $client->title;
                 $result[$x]['left'] = round( (float)$sub->number1 - (float) $sub->number2 , 0, PHP_ROUND_HALF_DOWN);
                 $result[$x]['action'] = getNextAction($client->id) ?? false;
                $x++;
            }
        }
    }
    return $result;
}

 

[Jan Romero]

Not thinking about this too much, one obvious problem is that you’re querying the database for every item in your inner loop. This is problematic for two reasons:

1. It’s going to be a lot of individual queries
2. Each query seems to depend only on $client, but you’re running them in the subscriptions loop. So every client’s subscription fetches the same data redundantly.

Might be worthwhile to fetch all actions in one go and then select from those. So something like

pages()->find('template=action, selectedClient.parent=1080, action_start>'.$today)

or something. Although this might not work if there are many matching actions per client, since you only want the first one. ProcessWire does not support grouping selectors as far as I know, so this is kind of hitting the limits of the selector system.

Tbh I would do this entire thing in SQL and be done with it, since you’re not returning ProcessWire objects anyway. You may have to do some output formatting yourself, but only to $action->selectedTrainer->text1, $action->selectedTrainer->text2 and $client->title, and it will be super fast.

[regesh]

Thanks a lot!
Yes, I realized that requests in a loop (and a selection of repeaters are requests) the decision is fundamentally wrong. Thank you for reminding me of this again. But in the morning I realized it too)
I got one more querstion.
Let's say I have pages with a specific template, a repeater is attached to the template. Is it possible to immediately get an array of all repeaters for the specified pages and then, having already looped through these repeaters and finding the necessary information, determine which page this repeater is attached to.

Thanks again!)

[Jan Romero]

Hi, yeah, repeater items are just pages, so you should be able to load them the regular way using pages()->find(). I think every repeater field creates a hidden template named after the field, so in your case it should be

pages()->find('template=repeater_clientsubscriptions, include=all')

You probably have to use “include=all” to bypass the permission system.

When you have a repeater page, you can get the page it belongs to by calling $repeaterItem->getForPage(). Also, maybe check this out: https://processwire.com/blog/posts/processwire-3.0.95-core-updates/.

Also have a look at this blog post on some new performance features: https://processwire.com/blog/posts/find-faster-and-more-efficiently

What does this do?

Quote

pre($action);

 

[regesh]

On 1/12/2022 at 12:49 AM, Jan Romero said:

Hi, yeah, repeater items are just pages, so you should be able to load them the regular way using pages()->find(). I think every repeater field creates a hidden template named after the field, so in your case it should be

pages()->find('template=repeater_clientsubscriptions, include=all')

You probably have to use “include=all” to bypass the permission system.

When you have a repeater page, you can get the page it belongs to by calling $repeaterItem->getForPage(). Also, maybe check this out: https://processwire.com/blog/posts/processwire-3.0.95-core-updates/.

Also have a look at this blog post on some new performance features: https://processwire.com/blog/posts/find-faster-and-more-efficiently

What does this do?

 

Thanks a lot!
thats is just my helper custom function
 

function pre($data,$exit = false){
	echo '<pre>';
	var_dump($data);
	echo '</pre>';
	if($exit){
		exit();
	}
}