Forum
Extremely Slow Load Time on Collection page
Hi,
I have a collection with 35 items in it which I don't think is a lot really. I want them all to display on an archive page of sorts but when I just use a simple call it takes over 14 seconds to load the page.
here is the call:
perch_collection('products', [
'template' => 'products/products_list.html',
]);
Here is my diagnostics:
Perch Runway: 3.0.8
Production mode: Development (10)
Installed apps: content (3.0.8), assets (3.0.8), categories (3.0.8), perch_blog (5.5.1), perch_forms (1.9.1), perch_twitter (3.7)
DB driver: PDO
DB tables: perch2_backup_plans (0), perch2_backup_resources (0), perch2_backup_runs (0), perch2_blog_authors (11), perch2_blog_comments (0), perch2_blog_index (6775), perch2_blog_posts (202), perch2_blog_posts_to_tags (0), perch2_blog_sections (1), perch2_blog_tags (0), perch2_blogs (3), perch2_categories (81), perch2_category_counts (16), perch2_category_sets (7), perch2_collection_index (329931), perch2_collection_items (1676), perch2_collection_revisions (296), perch2_collections (2), perch2_content_index (12869), perch2_content_items (2003), perch2_content_locks (0), perch2_content_regions (177), perch2_forms (5), perch2_forms_responses (759), perch2_menu_items (14), perch2_navigation (6), perch2_navigation_pages (28), perch2_page_routes (49), perch2_page_templates (45), perch2_pages (75), perch2_resource_log (22149), perch2_resource_tags (7), perch2_resources (2786), perch2_resources_to_tags (9), perch2_scheduled_tasks (30), perch2_settings (36), perch2_twitter_scheduled_tweets (0), perch2_twitter_settings (1), perch2_twitter_tweets (2422), perch2_user_passwords (4), perch2_user_privileges (43), perch2_user_role_privileges (55), perch2_user_roles (4), perch2_users (15)
Users: 15
App runtimes:
<?php
$apps_list = array(
'content',
'categories',
'perch_blog',
'perch_twitter',
'perch_forms',
);
Scheduled tasks for perch_blog: delete_spam_comments (1440 mins)
Scheduled tasks for perch_twitter: post_tweets (1 mins), update_tweets (60 mins)
Editor plug-ins: markitup
H1: 29bcdb2a19007f7b7d310447af8fe9cf
L1: 20240be7944a7944e62b3cd88072c3ae
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
headerColour: #e61e26
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 1
content_collapseList: 1
lang: en-gb
update_2.8.16: done
headerScheme: dark
update_runway_2.8.16: done
latest_version:
on_sale_version:
update_runway_2.8.19: done
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 1
content_frontend_edit: 0
perch_blog_update: 5.0.1
perch_blog_post_url: /{blogSlug}/news/{postSlug}
perch_blog_slug_format: %Y-%m-%d-{postTitle}
perch_blog_akismet_key:
perch_blog_max_spam_days: 1
perch_blog_comment_notify: 0
update_runway_2.8.23: done
perch_blog_site_name: Chicopee Solutions
logoPath: /admin/resources/1453300953_chicopee-logo.png
perch_twitter_update: 3.5
update_runway_2.8.24: done
update_runway_2.8.25: done
update_runway_2.8.28: done
update_runway_2.8.29: done
update_runway_2.8.32: done
update_runway_3.0.8: done
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SCHEDULE_SECRET: ####
PERCH_DB_SERVER: 88.150.168.46
PERCH_DB_USERNAME: /***/
PERCH_DB_DATABASE: /***/
PERCH_DB_PREFIX: perch2_
PERCH_YOUTUBE_API_KEY: /***/
PERCH_EMAIL_FROM: /***/
PERCH_EMAIL_FROM_NAME: /***/
PERCH_LOGINPATH: /admin
PERCH_PATH: /Users/mike/Sites/BERRY/chicopee/admin
PERCH_CORE: /Users/mike/Sites/BERRY/chicopee/admin/core
PERCH_RESFILEPATH: /Users/mike/Sites/BERRY/chicopee/admin/resources
PERCH_RESPATH: /admin/resources
PERCH_CLEAN_RESOURCES:
PERCH_HTML5: 1
PERCH_TZ: UTC
PERCH_DEBUG:
PERCH_PRODUCTION_MODE: 10
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: 5163d57ff611b3cf853b
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /Users/mike/Sites/BERRY/chicopee/admin/templates
PERCH_TEMPLATE_FILTERS:
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_XHTML_MARKUP:
PERCH_RWD: 1
PERCH_HTML_ENTITIES:
PERCH_SSL:
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_CUSTOM_EDITOR_CONFIGS:
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: 5.6.37
Zend: 2.6.0
OS: Darwin
SAPI: cgi-fcgi
Safe mode: not detected
MySQL client: mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $
MySQL server: 5.5.5-10.1.36-MariaDB
Free disk space: 837.18 GB
Extensions: Core, date, ereg, 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, mysqli, PDO, pdo_mysql, pdo_sqlite, Phar, posix, readline, Reflection, mysql, SimpleXML, soap, sockets, exif, tokenizer, wddx, xml, xmlreader, xmlwriter, xsl, zip, cgi-fcgi, imap, gettext, mcrypt, pgsql, pdo_pgsql, mhash
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
PHP_FCGI_CHILDREN: 4
PWD: /Applications/MAMP/fcgi-bin
PHP_FCGI_MAX_REQUESTS: 200
__CF_USER_TEXT_ENCODING: 0x1F5:0x0:0x2
ORIG_SCRIPT_NAME: /fcgi-bin/php5.6.37.fcgi
ORIG_PATH_TRANSLATED: /Users/mike/Sites/BERRY/chicopee/admin/core/settings/diagnostics/index.php
ORIG_PATH_INFO: /admin/core/settings/diagnostics/index.php
ORIG_SCRIPT_FILENAME: /Applications/MAMP/fcgi-bin/php5.6.37.fcgi
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
REQUEST_URI: /admin/core/settings/diagnostics/?extended
QUERY_STRING: extended
REQUEST_METHOD: GET
SERVER_PROTOCOL: HTTP/1.1
GATEWAY_INTERFACE: CGI/1.1
REDIRECT_URL: /admin/core/settings/diagnostics/index.php
REDIRECT_QUERY_STRING: extended
REMOTE_PORT: 57298
SCRIPT_FILENAME: /Users/mike/Sites/BERRY/chicopee/admin/core/settings/diagnostics/index.php
SERVER_ADMIN: you@example.com
DOCUMENT_ROOT: /Users/mike/Sites/BERRY/chicopee
REMOTE_ADDR: ::1
SERVER_PORT: 80
SERVER_ADDR: ::1
SERVER_NAME: chicopee.fishtank
SERVER_SOFTWARE: Apache
PATH: /usr/bin:/bin:/usr/sbin:/sbin
HTTP_COOKIE: _ga=GA1.2.1064802219.1540211115; _gid=GA1.2.892951116.1540211115; _gat=1; region=default; PHPSESSID=739r4lu7dg6anek2ogg0u05hil; cmsa=1
HTTP_ACCEPT_LANGUAGE: en-GB,en;q=0.9,en-US;q=0.8,la;q=0.7
HTTP_ACCEPT_ENCODING: gzip, deflate
HTTP_REFERER: https://chicopee.fishtank/admin/core/settings/diagnostics/?extended
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36
HTTP_UPGRADE_INSECURE_REQUESTS: 1
HTTP_CACHE_CONTROL: max-age=0
HTTP_CONNECTION: keep-alive
HTTP_HOST: chicopee.fishtank
REDIRECT_STATUS: 200
REDIRECT_HANDLER: php-fastcgi
FCGI_ROLE: RESPONDER
PHP_SELF: /admin/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1540211154.14
REQUEST_TIME: 1540211154
argc: 1
Hello,
If you haven't already, add
no-index="true"to fields you don't need to use for filtering or sorting:Sometimes I find applying a filter returns faster results than without any filters (even if the filter returns all items). Assuming you have a
statusfield, you may add one like so:So I sometimes add a filter that I know would return all items; it would still be faster than without any:
Also note that what you get in your development environment may not be identical to your staging/production depending on your setup.
If it's true that a filter that returns all items is faster, I would love to know why.
I'll give it a go and let you know how I get on.
It does seem a little odd that filtering items returns them faster.
If you only have 35 items in your collection, then you would appear to have a disproportionately large index table:
perch2_collection_index (329931)As Hussein has said, it could be that each item has a lot of fields - or potentially you've accidentally created a loop with relationships. Maybe just double-check any templates with related fields in, it could be causing you an issue.
Part of my fading memory seems to think that there may have been an update in one of the versions where Drew was going to code around relationship loops - I could be wrong though. Either way, it may be worth upgrading to the latest version, just to be sure...