Forum

Thread tagged as: Problem

Collection Template Slow Load

Hello,

For some reason one of my collections is loading the template noticeably slow, I've identified the template as the slow load from debug.

Is there anything I can do to see why it's loading so slow? (This is the only template in the entire site thats doing this).

Debug & Template Below

Time    Δ   Debug Message - Perch Runway 3.0.3
0.0492  0   [86] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate, pr.routeOrder, s.settingValue AS siteOffline FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID LEFT JOIN perch2_settings s ON s.settingID='siteOffline' UNION SELECT NULL AS pagePath, pr2.routePattern, pr2.routeRegExp, pr2.templatePath AS pageTemplate, pr2.routeOrder, NULL AS siteOffline FROM perch2_page_routes pr2 WHERE templateID!=0 ORDER BY routeOrder ASC, pagePath ASC
0.0624  0.0132  Matched page: /news/case-studies, so not using routes.
0.0625  0.0001  Using master page: /templates/pages/news/case_studies.php
0.0677  0.0052  [1] SELECT * FROM perch2_pages WHERE pagePath='/news/case-studies' LIMIT 1
0.0696  0.0019  Using template: /templates/pages/attributes/default.html
0.0698  0.0002  Using sub-template: /templates/pages/attributes/plugins.html
0.0749  0.0051  [70] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
0.0796  0.0046  Using template: /templates/forms/filter.html
0.0829  0.0033  [1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/news/case-studies' OR pageSortPath='/news/case-studies' LIMIT 1
0.0835  0.0006  [2] SELECT * FROM perch2_pages WHERE pageHidden=0 AND pageNew=0 AND pageTreePosition IN ('000-005-001', '000-005', '000') ORDER BY pageTreePosition
0.0842  0.0008  [2] Using template: /templates/navigation/breadcrumbs.html
0.0853  0.0011  [1] SELECT collectionID, collectionTemplate FROM perch2_collections WHERE collectionKey='Case Studies'
0.086   0.0007  [31] SELECT * FROM ( SELECT idx.itemID, ci.collectionID, ci.itemJSON, idx2.indexValue as sortval FROM perch2_collection_index idx JOIN perch2_collection_items ci ON idx.itemID=ci.itemID AND idx.itemRev=ci.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_revisions cr ON idx.itemID=cr.itemID AND idx.itemRev=cr.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='date' WHERE (idx.collectionID=7) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, itemJSON, sortval ORDER BY sortval DESC
0.8971  0.8111  [31] Using template: /templates/content/_ld/case_studies/list.html // THIS IS THE PROBLEM
0.9175  0.0204  Using template: /templates/pages/attributes/footer_plugins.html
0.9193  0.0018  [1] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/news/case-studies' OR regionPage='*' ORDER BY regionPage DESC
0.9205  0.0012  Request time: 0.9205
0.9205  0   Process time: 0.9191
0.9206  0   Memory: 4.6985
<perch:before>
  <div class="full-width">
    <div class="row">
</perch:before>

      <div class="four columns">
        <a href="/news/case-studies/<perch:content id="url_slug" />">
          <figure class="photo-block">
            <img src="<perch:content id="list_image" type="image" label="List Image" width="800" height="600" crop="true" />">
            <figcaption>
              <h2><perch:content id="title" /></h2>
              <perch:content id="text" chars="100" type="textarea" append="..." />
            </figcaption>
          </figure>
        </a>
      </div>

    <perch:every count="3">
      </div>
    <div class="row">
    </perch:every>

<perch:after>
  </div>
</div>
</perch:after>
Dexter Harrison

Dexter Harrison 29 points

  • 4 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

It's the database query rather than the template. Can you show us your diagnostics?

Ahh ok, it's on dev at the moment. Perhaps it something to do with that but it wasn't doing so before I made template changes. I did at perch:related tags which understandably I could see why this would increase it.

Diagnostics below

Perch Runway: 3.0.3
Production mode: Development (10)
Installed apps: content (3.0.3), assets (3.0.3), categories (3.0.3), perch_blog (5.5.1), dh_downloads (1), perch_events (1.9.5), perch_forms (1.9), dh_partners (0.1), dh_projects (1), chirp_seo (1), perch_members (1.6.1)
DB driver: PDO
DB tables: perch2_backup_plans (1), perch2_backup_resources (5839), perch2_backup_runs (57690), perch2_blog_authors (7), perch2_blog_categories (29), perch2_blog_comments (78), perch2_blog_index (7525), perch2_blog_posts (246), perch2_blog_posts_to_categories (618), perch2_blog_posts_to_tags (3604), perch2_blog_sections (1), perch2_blog_tags (446), perch2_blogs (1), perch2_categories (45), perch2_category_counts (25), perch2_category_sets (5), perch2_chirp_seo (2), perch2_collection_index (9734), perch2_collection_items (655), perch2_collection_revisions (143), perch2_collections (7), perch2_content_index (20977), perch2_content_items (3538), perch2_content_locks (0), perch2_content_regions (274), perch2_dh_downloads_access (2), perch2_dh_downloads_downloads (1), perch2_dh_projects_account_managers (4), perch2_dh_projects_addresses (35), perch2_dh_projects_contacts (37), perch2_dh_projects_customers (25), perch2_dh_projects_db_info (50), perch2_dh_projects_devices (38), perch2_dh_projects_emails (25), perch2_dh_projects_files (91), perch2_dh_projects_projects (33), perch2_dh_workbooks_access_log (91), perch2_dh_workbooks_addons (48), perch2_dh_workbooks_products (64), perch2_events (64), perch2_events_categories (1), perch2_events_to_categories (25), perch2_forms (10), perch2_forms_responses (2544), perch2_members (255), perch2_members_forms (2), perch2_members_member_tags (231), perch2_members_sessions (2), perch2_members_tags (7), perch2_menu_items (16), perch2_navigation (6), perch2_navigation_pages (21), perch2_page_routes (43), perch2_page_templates (55), perch2_pages (77), perch2_resource_log (14786), perch2_resource_tags (91), perch2_resources (3915), perch2_resources_to_tags (260), perch2_scheduled_tasks (150), perch2_settings (75), perch2_user_passwords (4), perch2_user_privileges (57), perch2_user_role_privileges (83), perch2_user_roles (6), perch2_users (6)
Users: 6
App runtimes:
<?php
    $apps_list = array(
        'content',
        'categories',
        'perch_events',
        'perch_blog',
        'perch_forms',
        'dh_partners',
        'perch_members',
        'dh_projects',
        'dh_downloads',
        '_dh_workbooks',
        'chirp_seo'
    );
Scheduled tasks for dh_partners: update_products (1440 mins), drop_old_logs (1140 mins)
Scheduled tasks for dh_projects: approval_reminder (1440 mins)
Scheduled tasks for perch_blog: delete_spam_comments (1440 mins)
Scheduled tasks for Backup: plan_5 (10 mins)
Editor plug-ins:
H1: be02bb319094fd8d2d5c07140e3d6eb2
L1: 9aefee7ac27c9c849e95dd9a3bb466d6
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
content_singlePageEdit: 1
siteURL: XXX
hideBranding: 1
content_collapseList: 1
lang: en-gb
update_2.7.2: done
headerScheme: dark
latest_version:
on_sale_version:
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 1
content_frontend_edit: 0
update_2.7.3: done
perch_events_update: 1.8
perch_gallery_update: 2.8.5
perch_events_detail_url: /news/exhibitions/{eventSlug}
perch_gallery_bucket_mode: dynamic
perch_gallery_bucket: gallery
perch_gallery_basicUpload: 0
perch_blog_update: 5.0.1
perch_blog_post_url: /blog/{postSlug}
perch_blog_akismet_key:
perch_blog_max_spam_days: 1
perch_blog_comment_notify: 0
perch_twitter_update: 3.5
update_2.7.4: done
logoPath: /admin/resources/logo-dashboard.png
update_2.7.9: done
update_2.7.10: done
headerColour: #dab6c7
helpURL:
perch_mailchimp_secret: 16a1aca53abb2f2801623b3da1336df5
perch_mailchimp_update: 2.0
perch_mailchimp_api_key: 582106520d9a144b8bbf0cf55cffb2f8-us7
perch_mailchimp_list_id: d45a01aa36
perch_mailchimp_campaign_url: /partners/newsletters/{campaignSlug}
update_2.8: done
update_2.8.1: done
update_2.8.2: done
perch_blog_slug_format: {postTitle}-{postID}
update_2.8.4: done
update_runway_2.8.7: done
update_runway_2.8.8: done
update_runway_2.8.9: done
dh_ga_view_id: 1145483
update_runway_2.8.13: done
update_runway_2.8.15: done
perch_members_update: 1.4
update_runway_2.8.17: done
update_runway_2.8.22: done
update_runway_2.8.23: done
update_runway_2.8.25: done
perch_members_login_page: /login
perch_blog_site_name:
update_runway_2.8.29: done
update_runway_2.8.30: done
update_runway_2.8.31: done
dh_projects_update: 1.3
update_runway_2.8.32: done
dh_projects.projects.update: 1.6
dh_projects.projects.migrate.contacts: 1
update_runway_2.8.34: done
update_runway_3.0: done
update_runway_3.0.1: done
update_runway_3.0.2: done
update_runway_3.0.3: done
chirp_seo_license_key: 
siteOffline: 0
content_skip_region_list: 0
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SCHEDULE_SECRET: 
PERCH_DB_PREFIX: perch2_
PERCH_DEBUG: 1
PERCH_DB_USERNAME: root
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: rebuild
PERCH_PRODUCTION_MODE: 10
PERCH_EMAIL_FROM: 
PERCH_EMAIL_FROM_NAME: 
PERCH_LOGINPATH: /admin
PERCH_PATH: /
PERCH_CORE: /
PERCH_RESFILEPATH: /
PERCH_RESPATH: /admin/resources
PERCH_HTML5: 1
PERCH_TZ: Europe/London
PERCH_RWD: 1
PERCH_YOUTUBE_API_KEY: 
PERCH_SSL: 1
PERCH_GMAPS_API_KEY: 
PERCH_RUNWAY: 1
PERCH_ERROR_MODE: DIE
PERCH_DATE_LONG: %d %B %Y
PERCH_DATE_SHORT: %d %b %Y
PERCH_TIME_SHORT: %H:%M
PERCH_TIME_LONG: %H:%M:%S
PERCH_RUNWAY_ROUTED:
PERCH_STRONG_PASSWORDS:
PERCH_ASSET_VERSION: ed79f7a54b300ef2b1e6
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /
PERCH_TEMPLATE_FILTERS:
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_XHTML_MARKUP:
PERCH_HTML_ENTITIES:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_PARANOID:
PERCH_FORCE_SECURE_COOKIES:
PERCH_DEFAULT_BUCKET: default
PERCH_TRANSLATION_ASSIST:
PERCH_PASSWORD_MIN_LENGTH: 6
PERCH_MAX_FAILED_LOGINS: 10
PERCH_AUTH_LOCKOUT_DURATION: 1 HOUR
PERCH_VERIFY_UPLOADS:
PERCH_PRIV_ASSIST:
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: markdown
Hosting settings

PHP: 7.0.13
Zend: 3.0.0
OS: Darwin
SAPI: apache2handler
Safe mode: not detected
MySQL client: mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $
MySQL server: 5.6.34
Free disk space: 826.30 GB
Extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bcmath, bz2, calendar, ctype, curl, dom, hash, fileinfo, filter, ftp, gd, SPL, iconv, intl, json, ldap, mbstring, session, standard, mysqlnd, PDO, pdo_mysql, pdo_sqlite, Phar, posix, Reflection, mysqli, SimpleXML, soap, sockets, exif, tokenizer, wddx, xml, xmlreader, xmlwriter, xsl, zip, apache2handler, imap, gettext, mcrypt, pgsql, pdo_pgsql, igbinary, memcached
GD: Yes
ImageMagick: No
PHP max upload size: 32M
PHP max form post size: 32M
PHP memory limit: 128M
Total max uploadable file size: 32M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
HTTPS: on
SSL_TLS_SNI: /
SSL_SERVER_S_DN_C: GB
SSL_SERVER_S_DN_ST: Essex
SSL_SERVER_S_DN_L: Colchester
SSL_VERSION_INTERFACE: mod_ssl/2.2.31
SSL_VERSION_LIBRARY: OpenSSL/1.0.2j
SSL_PROTOCOL: TLSv1.2
SSL_SECURE_RENEG: true
SSL_COMPRESS_METHOD: NULL
SSL_CIPHER: ECDHE-RSA-AES256-GCM-SHA384
SSL_CIPHER_EXPORT: false
SSL_CIPHER_USEKEYSIZE: 256
SSL_CIPHER_ALGKEYSIZE: 256
SSL_CLIENT_VERIFY: NONE
SSL_SERVER_M_VERSION: 1
SSL_SERVER_M_SERIAL: 8E60A9E433DF276E
SSL_SERVER_V_START: Nov 19 10:30:53 2015 GMT
SSL_SERVER_V_END: Nov 16 10:30:53 2025 GMT
HTTP_HOST: 
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
HTTP_CONNECTION: keep-alive
HTTP_COOKIE: CAN_SId=e27a8f8c8eceb53425da3202bab8e6dc!1496163370!195![]![]; _CAN_BId=92333eb215dab33755532c5ea6d6bda0; _ga=GA1.2.980415294.1495635688; _gid=GA1.2.1732169561.1496163365; __lc.visitor_id.6427331=S1495635690.4020ad9ff3; PHPSESSID=7fd3c19ca8d770d06d7e186381181a2b; cmsa=1
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_5) AppleWebKit/603.2.4 (KHTML, like Gecko) Version/10.1.1 Safari/603.2.4
HTTP_ACCEPT_LANGUAGE: en-gb
HTTP_REFERER: 
HTTP_ACCEPT_ENCODING: gzip, deflate
PATH: /usr/bin:/bin:/usr/sbin:/sbin
SERVER_SOFTWARE: Apache
SERVER_ADDR: 192.168.1.51
SERVER_PORT: 443
REMOTE_ADDR: 192.168.1.51
DOCUMENT_ROOT: /
SERVER_ADMIN: you@example.com
SCRIPT_FILENAME: /
REMOTE_PORT: 61967
GATEWAY_INTERFACE: CGI/1.1
SERVER_PROTOCOL: HTTP/1.1
REQUEST_METHOD: GET
QUERY_STRING: extended
REQUEST_URI: /admin/core/settings/diagnostics/?extended
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
PHP_SELF: /admin/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1496163569.63
REQUEST_TIME: 1496163569
argc: 1
Drew McLellan

Drew McLellan 2638 points
Perch Support

Have you checked that you've not created a circular relationship? You seem to have a lot of index entries compared to the number of items.

Hmm it wasn't a circular relationship but I've removed the related tags and re saved all collection items to test and that's halved the time.

Is there a way to clean up the collection index, as nearly 10k seems a bit high

Drew McLellan

Drew McLellan 2638 points
Perch Support

Are you indexing all fields? Do you need to? The only fields that are required for indexing are those you want to sort or filter by.

It looks like it, I wasn't aware you can specify what gets indexed. How do I change that?

Drew McLellan

Drew McLellan 2638 points
Perch Support

If there's content you don't want to index, you can add no-index="true" to its content tag in the master template.

Ah that would be where it's going wrong. Using Perch for years and seen that.

Thanks Drew, I should be able to fix this now