Performance issue - cp entries list - super slow

[peet86]

Describe the bug

When the plugin is enabled the /admin/entries cp panel page takes couple of seconds to load. It is running 823 database queries which require 5000ms+ to finish VS. when the plugin disabled: 44 queries (16ms)

I've tried to debug the issue with Yii profiler. The main difference is that for some reason when the plugin is enabled the page is executing approx 800 times "elements"-related queries (see an example below). (VS 2-3 times when the plugin is disabled)

The queries are not slow (only a few ms per query) but after a few hundred of them MySQL pukes (hits some limits?) and one of the queries (not always the same one) takes 3000-4000 sec to execute. Then it continues..

`SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_xxxxx`, 
 ----- all the fields ------
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `ut_elements` `elements`
INNER JOIN `ut_entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `ut_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `ut_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`entries`.`sectionId`='9') AND (`elements_sites`.`siteId`=16) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `entries`.`postDate` DESC
LIMIT 1) `subquery`
INNER JOIN `ut_entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `ut_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `ut_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `ut_content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `entries`.`postDate` DESC

Steps to reproduce

I'm not sure how you can reproduce this.. probably it's an edge case which happens only when the instance has lots of fields in singles.. This is a bigger site with 9 languages, 30+ sections, 300+ fields. Also matrix and neo fields are used heavily.

Craft CMS version

3.8 Pro

Plugin version

1.2

Multi-site?

Yes

Additional context

It happens both in dev and prod envs.

[engram-design]

So the plugin does add some extra queries, but they're to fetch the single entries themselves, for each site. That's largely unavoidable, and for me, changes 88 queries to 126 for x3 singles over 9 sites. It's at least unavoidable in the sense that's how element queries work and on your end, it's probably exacerbated by the number of custom fields. But we also only need to fetch these entries to get the site-specific cpEditUrl.

Fixed for the next release. To get this early, run composer require verbb/expanded-singles:"dev-craft-4 as 2.0.1".

[peet86]

Awesome! I can confirm your change solves the issue. Thank you so much for this improvement.

I hope you don't mind that I "ported this back" to the craft-3 branch and created a pull request with the very same change.

[d-karstens]

I think this change may break the redirectToEntry setting, as the site-urls value isn't a link to the entry anymore

[engram-design]

Should be fixed in 2.0.3