Jump to content

Slow query problem


mrjasongorman
 Share

Recommended Posts

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?

 

Link to comment
Share on other sites

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");
}

 

Link to comment
Share on other sites

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

  • Like 2
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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.

  • Like 4
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Similar Content

    • By Noel Boss
      ProcessWire & Vue.js — a Lovestory
      Introducing the all new ICF Conference Website
        The new ICF Conference Page — Fearless
      » What would happen if we were equipped to fearlessly face the daily challenges and live a life without fear? «
      This question is at the core of our next ICF Conference in 2019 in Zurich. Its also the question we set out to answer in terms of developing the new website; the all new ICF Conference website is our most advanced website in terms of technology, designed to take advantage of the latest web-technologies.
      Its a brand new design powered by a lean setup, using ProcessWire for easy content management and a slick frontend based on Vue.js, Quasar and a heavily customized Uikit theme.
        Technology-stack — From backend to frontend, technologies that are fun, easy and fast to develop with We built on the ICF Ladieslounge website as a solid foundation and took our learnings from building our last Conference Booklet PWA (Progressive Web App) and applied it to the new website.
      Some highlights of the new ICF Conference website:
      Completely decoupled backend and frontend Custom design based on Uikit frontend framework Changing of languages happens instantly, no page-reload required Easy content updates thanks to ProcessWire All data is transferred using a single request returning custom JSON



      » Continue reading on Medium
      And please don't forget to clap and share: 

       
    • By Noel Boss
      Page Query Boss
      Build complex nested queries containing multiple fields and pages and return an array or JSON. This is useful to fetch data for SPA and PWA.
      You can use the Module to transform a ProcessWire Page or PageArray – even RepeaterMatrixPageArrays – into an array or JSON. Queries can be nested and contain closures as callback functions. Some field-types are transformed automatically, like Pageimages or MapMarker.
      Installation
      Via ProcessWire Backend
      It is recommended to install the Module via the ProcessWire admin "Modules" > "Site" > "Add New" > "Add Module from Directory" using the PageQueryBoss class name.
      Manually
      Download the files from Github or the ProcessWire repository: https://modules.processwire.com/modules/page-query-builder/
      Copy all of the files for this module into /site/modules/PageQueryBoss/ Go to “Modules > Refresh” in your admin, and then click “install” for the this module. Module Methods
      There are two main methods:
      Return query as JSON
      $page->pageQueryJson($query); Return query as Array
      $page->pageQueryArray($query); Building the query
      The query can contain key and value pairs, or only keys. It can be nested and 
      contain closures for dynamic values. To illustrate a short example:
      // simple query: $query = [ 'height', 'floors', ]; $pages->find('template=skyscraper')->pageQueryJson($query); Queries can be nested, contain page names, template names or contain functions and ProcessWire selectors:
      // simple query: $query = [ 'height', 'floors', 'images', // < some fileds contain default sub-queries to return data 'files' => [ // but you can also overrdide these defaults: 'filename' 'ext', 'url', ], // Assuming there are child pages with the architec template, or a // field name with a page relation to architects 'architect' => [ // sub-query 'name', 'email' ], // queries can contain closure functions that return dynamic content 'querytime' => function($parent){ return "Query for $parent->title was built ".time(); } ]; $pages->find('template=skyscraper')->pageQueryJson($query); Keys:
      A single fieldname; height or floors or architects 
      The Module can handle the following fields:
      Strings, Dates, Integer… any default one-dimensional value Page references Pageimages Pagefiles PageArray MapMarker FieldtypeFunctional A template name; skyscraper or city
      Name of a child page (page.child.name=pagename); my-page-name A ProcessWire selector; template=building, floors>=25
      A new name for the returned index passed by a # delimiter:
      // the field skyscraper will be renamed to "building": $query = ["skyscraper`#building`"]  
      Key value pars:
      Any of the keys above (1-5) with an new nested sub-query array:
      $query = [ 'skyscraper' => [ 'height', 'floors' ], 'architect' => [ 'title', 'email' ], ]  
      A named key and a closure function to process and return a query. The closure gets the parent object as argument:
      $query = [ 'architecs' => function($parent) { $architects = $parent->find('template=architect'); return $architects->arrayQuery(['name', 'email']); // or return $architects->explode('name, email'); } ] Real life example:
      $query = [ 'title', 'subtitle', // naming the key invitation 'template=Invitation, limit=1#invitation' => [ 'title', 'subtitle', 'body', ], // returns global speakers and local ones... 'speakers' => function($page){ $speakers = $page->speaker_relation; $speakers = $speakers->prepend(wire('pages')->find('template=Speaker, global=1, sort=-id')); // build a query of the speakers with return $speakers->arrayQuery([ 'title#name', // rename title field to name 'subtitle#ministry', // rename subtitle field to ministry 'links' => [ 'linklabel#label', // rename linklabel field to minlabelistry 'link' ], ]); }, 'Program' => [ // Child Pages with template=Program 'title', 'summary', 'start' => function($parent){ // calculate the startdate from timetables return $parent->children->first->date; }, 'end' => function($parent){ // calculate the endate from timetables return $parent->children->last->date; }, 'Timetable' => [ 'date', // date 'timetable#entry'=> [ 'time#start', // time 'time_until#end', // time 'subtitle#description', // entry title ], ], ], // ProcessWire selector, selecting children > name result "location" 'template=Location, limit=1#location' => [ 'title#city', // summary title field to city 'body', 'country', 'venue', 'summary#address', // rename summary field to address 'link#tickets', // rename ticket link 'map', // Mapmarker field, automatically transformed 'images', 'infos#categories' => [ // repeater matrix! > rename to categories 'title#name', // rename title field to name 'entries' => [ // nested repeater matrix! 'title', 'body' ] ], ], ]; if ($input->urlSegment1 === 'json') { header('Content-type: application/json'); echo $page->pageQueryJson($query); exit(); } Module default settings
      The modules settings are public. They can be directly modified, for example:
      $modules->get('PageQueryBoss')->debug = true; $modules->get('PageQueryBoss')->defaults = []; // reset all defaults Default queries for fields:
      Some field-types or templates come with default selectors, like Pageimages etc. These are the default queries:
      // Access and modify default queries: $modules->get('PageQueryBoss')->defaults['queries'] … public $defaults = [ 'queries' => [ 'Pageimages' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'focus', ], 'Pagefiles' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'filesize', 'filesizeStr', 'hash', ], 'MapMarker' => [ 'lat', 'lng', 'zoom', 'address', ], 'User' => [ 'name', 'email', ], ], ]; These defaults will only be used if there is no nested sub-query for the respective type. If you query a field with complex data and do not provide a sub-query, it will be transformed accordingly:
      $page->pageQueryArry(['images']); // returns something like this 'images' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'focus'=> [ 'top', 'left', 'zoom', 'default', 'str', ] ]; You can always provide your own sub-query, so the defaults will not be used:
      $page->pageQueryArry([ 'images' => [ 'filename', 'description' ], ]); Overriding default queries:
      You can also override the defaults, for example
      $modules->get('PageQueryBoss')->defaults['queries']['Pageimages'] = [ 'basename', 'url', 'description', ]; Index of nested elements
      The index for nested elements can be adjusted. This is also done with defaults. There are 3 possibilities:
      Nested by name (default) Nested by ID Nested by numerical index Named index (default):
      This is the default setting. If you have a field that contains sub-items, the name will be the key in the results:
      // example $pagesByName = [ 'page-1-name' => [ 'title' => "Page one title", 'name' => 'page-1-name', ], 'page-2-name' => [ 'title' => "Page two title", 'name' => 'page-2-name', ] ] ID based index:
      If an object is listed in $defaults['index-id'] the id will be the key in the results. Currently, no items are listed as defaults for id-based index:
      // Set pages to get ID based index: $modules->get('PageQueryBoss')->defaults['index-id']['Page']; // Example return array: $pagesById = [ 123 => [ 'title' => "Page one title", 'name' => 123, ], 124 => [ 'title' => "Page two title", 'name' => 124, ] ] Number based index
      By default, a couple of fields are transformed automatically to contain numbered indexes:
      // objects or template names that should use numerical indexes for children instead of names $defaults['index-n'] => [ 'Pageimage', 'Pagefile', 'RepeaterMatrixPage', ]; // example $images = [ 0 => [ 'filename' => "image1.jpg", ], 1 => [ 'filename' => "image2.jpg", ] ] Tipp: When you remove the key 'Pageimage' from $defaults['index-n'], the index will again be name-based.
       
      Help-fill closures & tipps:
      These are few helpfill closure functions you might want to use or could help as a
      starting point for your own (let me know if you have your own):

      Get an overview of languages:
          $query = ['languages' => function($page){         $ar = [];         $l=0;         foreach (wire('languages') as $language) {             // build the json url with segment 1             $ar[$l]['url']= $page->localHttpUrl($language).wire('input')->urlSegment1;             $ar[$l]['name'] = $language->name == 'default' ? 'en' : $language->name;             $ar[$l]['title'] = $language->getLanguageValue($language, 'title');             $ar[$l]['active'] = $language->id == wire('user')->language->id;             $l++;         }         return $ar;     }]; Get county info from ContinentsAndCountries Module
      Using the [ContinentsAndCountries Module](https://modules.processwire.com/modules/continents-and-countries/) you can extract iso
      code and names for countries:
          $query = ['country' => function($page){         $c = wire('modules')->get('ContinentsAndCountries')->findBy('countries', array('name', 'iso', 'code'),['code' =>$page->country]);         return count($c) ? (array) $c[count($c)-1] : null;     }]; Custom strings from a RepeaterTable for interface
      Using a RepeaterMatrix you can create template string for your frontend. This is
      usefull for buttons, labels etc. The following code uses a repeater with the
      name `strings` has a `key` and a `body` field, the returned array contains the `key` field as,
      you guess, keys and the `body` field as values:
          // build custom translations     $query = ['strings' => function($page){         return array_column($page->get('strings')->each(['key', 'body']), 'body', 'key');     }]; Multilanguage with default language fallback
      Using the following setup you can handle multilanguage and return your default
      language if the requested language does not exist. The url is composed like so:
      `page/path/{language}/{content-type}` for example: `api/icf/zurich/conference/2019/de/json`
       
          // get contenttype and language (or default language if not exists)     $lang = wire('languages')->get($input->urlSegment1);     if(!$lang instanceof Nullpage){         $user->language = $lang;     } else {         $lang = $user->language;     }     // contenttype segment 2 or 1 if language not present     $contenttype = $input->urlSegment2 ? $input->urlSegment2 : $input->urlSegment1;     if ($contenttype === 'json') {         header('Content-type: application/json');         echo $page->pageQueryJson($query);         exit();     } Debug
      The module respects wire('config')->debug. It integrates with TracyDebug. You can override it like so:
      // turns on debug output no mather what: $modules->get('PageQueryBoss')->debug = true; Todos
      Make defaults configurable via Backend. How could that be done in style with the default queries?
      Module in alpha Stage: Subject to change
      This module is in alpha stage … Query behaviour (especially selecting child-templates, renaming, naming etc)  could change
    • By Sergio
      Hello fellow PW devs!
      This is a short story from the server management trenches.
      These past couple of days trying to solve an unexpected problem: after DigitalOcean patched the droplets in NYC3 region last week, my client's droplet became almost useless and went down a couple of times. The droplet has 2GB RAM and was running Ubuntu 16.04 that was updated to kernel 4.4.0-116 after the patch. The server was provisioned using Forge (forge.laravel.com).
      After sshing into it, and running "top" I've noticed the cause: "php-fpm7.1" processes (3-5 instances) were spiking the CPU to 100%. This was very odd, as the CPU usually kept around 33% most of the time.
      The site uses ProCache and markupCache and was getting around 800-1000 visits/day last week. I checked everything on PW's side and nothing seemed out of place, so I went restarting PHP and Nginx but the problem continued.  I checked access logs and no suspicious activity shown up. I upgraded PHP to 7.2 to see if anything will changed but the problem continued.
      My only guess after all that is that the droplet in question got screwed up somehow, because I didn't see any complaints on the web of other people getting the same problem on DO (But I confess that I did a quick Google search only).
      So in the end I decided to create a new droplet, now with 2 CPU cores and kept the 2GB (1 extra core and $5 cheaper ). Reinstalled PW there and pointed the floating IP to this new server.
      The installation went smooth but to one issue: error log started to show messages of MySQL showing "to many files" error when the users were searching. I've never encountered this message before, so after reading some StackOverflow posts, I changed mysql.services config file to remove its file limit (https://stackoverflow.com/a/36807137)
      Everything is normal now, but I think I'll never discover what truly happened. Anyone else had this kind of problem with MySQL before?
       
    • By Ahmad
      I'm building a site and i noticed that loading time is very slow when we enter the site address for the first time. It takes about 1 minute or more  just to start loading files and displaying the content. 
      However, after that period it's very fast and everything is ok. Once the page(s) is/are loaded, i can refresh them quickly and without any delay.
      any one faced like this problem? and what can i do to resolve it.
      more details:
      ProcessWire 3.0.80
      hosted type: VPS
      this message appear in admin dashboard: " Warning: your server locale is undefined and may cause issues. Please add this to /site/config.php file (adjust “en_US.UTF-8” as needed): setlocale(LC_ALL,'en_US.UTF-8');"
      The site: http://almanassah.net
    • By creativejay
      I'm displaying a list of products which are found by their templates, but the pages are taking a very long time to load. At first, I blamed it on my image rendering (using PIM2), but even with all those images now stored in the file tree, the page is taking abysmally long to load. ProCache seems to help but I don't feel as though what I'm trying to do should be gnawing the bones of my resources quite so long.
      The variable for the selector is defined in my header include:
      $productCatList="prod_series|prod_series_ethernet|prod_series_access|prod_series_accessories|prod_series_fiber|prod_series_pwr_supplies|prod_series_pwr_systems|prod_series_wireless"; $getCurrentProdOptions="template=$productCatList, prod_status_pages!=1554|1559|1560|4242"; Then in the template for the page upon which the directory loads:
      $products = $pages->find("$getCurrentProdOptions"); include_once("./prod-list-row.inc"); echo $out; And the prod-list-row.inc foreach (which is on every page that's exhibiting the slowdown):
      <?php $sum = 0; $out =""; $out .= "<div class='span_12_of_12'>\n"; foreach($products as $p){ $sum += 1; if ($sum % 2 == 0) { $bgcolor = '#fff'; } else { $bgcolor = '#e4e4e4';} $par = $p->parent; $out .="<div class='section group' style='background: $bgcolor ; min-height: 110px'>\n"; $img = $p->prod_image; $thumb = $img->pim2Load('squarethumb100')->canvas(100,100,array(0,0,0,0),'north',0)->pimSave()->url; $out .="<div data-match-height='{$p->title}' class='col span_2_of_12 hide'>"; $out .="<a href='{$p->url}'><span class='product-image-box'><img src='{$thumb}' alt='{$p->title}' title='{$p->title}'></span></a>"; $out .= "</div>"; $out .= "<div data-match-height='{$p->title}' class='col span_6_of_12'>"; $out .= "<div class='prod-list-name-label'><a href='{$p->url}'>{$p->title}</a></div>"; if($page!=$par) { $out .= "<div class='prod-list-category-label' style='font-size: .7em;'>Category: <a href='{$par->url}'>{$par->title}</a></div>"; } $out .= "<div class='list-headline' style='font-size: .8em;'>{$p->headline}</div>"; $out .="<div class='learn-more-buttons-sm'>"; $out .="<a href='{$p->url}' title='Product Specs and Documentation'><span class='find-out-more-button' style='font-size: .8em;'><i style='font-size: .8em;' class='fa fa-lightbulb-o' ></i> &nbsp; Learn More</span></a>"; $out .="</div>"; $out .="</div> \n"; $out .= " <div data-match-height='{$p->title}' class='col span_4_of_12'>"; if(count($p->prod_feat_imgs) >0 ){ $out .= "<div class='featured-icons-list' margin: 2em .5em;'>"; foreach($p->prod_feat_imgs as $feat){ $icon = $pages->get("$feat->prod_featicon_pages"); if($icon->image) { if($feat->prod_feat_textlang) { $icontitle = $feat->prod_feat_textlang;} else {$icontitle = $icon->title;} $out .= "<img src='".$icon->image->size(35,35,$imgOptions)->url . "' alt='" . $icontitle . "' title='" . $icontitle . "' class='listing-feat-icon' style='margin-right: .5em;' />"; } } $out .= "</div>"; if($p->prod_product_line){ foreach($p->prod_product_line as $pline) if($pline->image) { $out .= "<div style='height: 35px;'>\n"; $out .= "<img src='{$pline->image->size(75,35,$imgOptions)->url}' alt='{$pline->title}' />"; $out .= "</div>"; } } } $out .= "</div>"; $out .="</div>"; } $out .= "</div>";  
      Is there a clear culprit here of what I'm doing that's so stressing the system?
      I turned off TracyDebugger because I saw another thread about that causing slowdown (even though I'm using the latest), but that had no effect. Every time I thought I found the culprit and commented it out, nothing changed.
      Would appreciate some more eyes on this. Thank you!
      ETA: prod_feat_imgs is a repeater field which contains a Page reference field (from which I pull the image and title) and a multilanguage textfield (to override the page reference title if it exists). Could that be the problem?
×
×
  • Create New...