Slow query problem

[mrjasongorman]

I have a website with a slow page load mainly due to a slow query on a listing. I think there might be a better way to query the data / arrange the data back end which is what's causing the query to be slow.

 

So the data is like this

Area -> Level -> Path, the path then links to a pool of units included within that path.

The units then have study locations listed as child pages for that unit with contact information etc.

I have a page where i list all the study locations, but because i'm going through every unit and then every child study location page it takes quite a while.

I have over 200 units with around 5 locations as sub pages.

Any way i can reorganise the data to make this listing faster to load?

 

[mrjasongorman]

I'll try and explain the data structure better

Area > Level > Path  ----------------> Unit > Study Location

The -------> is a page reference.

So the query is $pages->find("template=study-location, sort=unit_name_ref"); 

[bernhard]

1 hour ago, mrjasongorman said:

$pages->find("template=study-location, sort=unit_name_ref"); 

this query will not be slow, so it would be nice if you showed us your current code

[mrjasongorman]

This is the current code

$unitOptions = $pages->find("template=unit-option");

$temp_array = array();
foreach( $unitOptions as $item ){
  if( $item->get('unit_location') ):
    array_push($temp_array, $item->get('unit_location'));
  endif;
}
$locations = array_unique($temp_array, SORT_STRING);
sort($locations);

$myLocation = ( !empty($_GET['location']) ) ? $sanitizer->text($_GET['location']) : null;

$unitResults = null;

if( $myLocation ){
  $unitResults = $pages->find("template=unit-option, unit_location=$myLocation, sort=unit_name_ref");
}else{
  $unitResults = $pages->find("template=unit-option, sort=unit_name_ref");
}

 

[abdus]

Some points:

• I think you missed the part where you're using $locations, otherwise why are you getting all unit-options and their locations only to scrap all that and fetch it again?

  $unitOptions = $pages->find("template=unit-option"); // never used
  $unitResults = $pages->find("template=unit-option, sort=unit_name_ref");

   

• If you're dealing with large number of pages, avoid excessive assignment of large values to variables, it'll increase memory usage, this may fill up the ram and server may start using swap partitions, which is always slower.
• Instead of $_GET, there's $input->get and for direct sanitization $input->get->text() (and ->selectorValue if you're going to use it in selectors).

<?php namespace ProcessWire;
/** @var $pages Pages */
/** @var $input WireInput */

// $myLocation = ( !empty($_GET['location']) ) ? $sanitizer->text($_GET['location']) : null;
$myLocation = $input->get->selectorValue('location');

$unitResults = null;
if ($myLocation) {
    $unitResults = $pages("template=unit-option, unit_location=$myLocation, sort=unit_name_ref");
} else {
    $unitOptions = $pages->find("template=unit-option")
        ->explode('unit_location');

    // filter empty values and duplicates
    $unitOptions = array_unique(array_filter($unitOptions));
    $locations = join("|", $unitOptions);
    $unitResults = $pages("template=unit-option, unit_location=$locations, sort=unit_name_ref");
}

Other than these points, I'm not sure why you'd get slow results for several hundred pages. If you give a more complete picture of your page structure, we'd be able to help you better

[mrjasongorman]

Thanks for taking a look at this. I didn't realise $input had those options.

The thing is I automatically generate a list of location options based on the data provided in the unit-option's.

So I need to generate the full list of locations for a select box above the listings regardless of whether a $location has been provided or not.

It then renders listings of unit-options grouped by the unit they're children of, and sorted based on the unit name.

The ->explode() method is cool, I didn't realise it was in the API.

Thanks again!

[abdus]

WireCache can help you a lot when rendering the full list. You can use it to cache a strings, arrays, WireArrays, or PageArrays and retrieve back when you need it

https://processwire.com/api/ref/wire-cache/

<?php namespace ProcessWire; 

$options = $cache->get('unit-options');
if (!$options) {
    $options = $pages('template=unit-option');
    $cache->save('unit-options', $options, WireCache::expireDaily);
}

// generate your markup

// or cache your markup and use that instead

For the filtered list that you're building with GET parameter, if the parameter doesnt vary wildly and can only take a small number of values/combinations, than you can cache those results as well.

[mrjasongorman]

Ah brilliant, so kind of like transients in Wordpress.

Thanks for all the help!

[Robin S]

7 hours ago, abdus said:

If you're dealing with large number of pages, avoid excessive assignment of large values to variables, it'll increase memory usage, this may fill up the ram and server may start using swap partitions, which is always slower.

Hi @abdus,

Can you say more about this?

Does this...

$foo = $pages->find("template=foo");
foreach($foo as $foo_item) { //...

...use more memory than...

foreach($pages->find("template=foo") as $foo_item) { //...

...? I thought these would be same in terms of memory usage, or is memory able to be "released" later somehow if you are not assigning to a variable? 

[abdus]

I was reiterating the suggestion from PHP The Right Way, that I found out just yesterday.

Which got me curious, and did my own tests. RAM usage doesn't change, but on CPU time it has some effect.

// 8000 pages with title and body fields
$pp = $pages('template=basic, parent=1384');
$out = '';
foreach($pp as $p) {
    $t = $p->title . microtime();
    $b = $p->body . mt_rand(0, 1e5);
    $out .= $t . $b;
}
echo strlen($out);
// 18384.08ms, 25.00 MB

 

// 8000 pages with title and body fields
$pp = $pages('template=basic, parent=1384');
$out = '';
foreach($pp as $p) {
    $out .= $p->title . microtime();
    $out .= $p->body . mt_rand(0, 1e5);
}
echo strlen($out);
// 17617.05ms, 25.00MB

 

$out = '';
foreach($pages('template=basic, parent=1384') as $p) {
    $out .= $p->title . microtime();
    $out .= $p->body . mt_rand(0, 1e5);
}
echo strlen($out);
// 17927.9ms, 25.00MB

Verdict: Don't believe everything you read on the internet. Do your own tests.