Module version(s) affected
5.4, 6
Description
I have observed performance issues that are caused by the use of the below code in ElementalPageExtension via the getEagerLoadedElements() method:
foreach (ElementalArea::get()->eagerLoad('Elements') as $elementalArea) {This method is private and accessed in the extension via getElementsForSearch() and getContentFromElementsForCmsSearch() methods.
For some reason when "eagerLoad" is called as above, the query created by DataList::fetchEagerLoadHasMany() fetches ALL Element records for ALL ElementalArea records in the database.
e.g for a test DB with 3 pages and 3 elemental areas:
WHERE ("Element_Live"."ParentID" IN (1, 2, 3))
Consider a DB with thousands of page records and that IN() then has thousands of values, and > thousands of records returned.
This doesn't make sense when a project just calls $page->getElementsForSearch(). The expected result when this happens is to query and return the elements attached to the ElementalArea relations of $page (there may be more than one ElementalArea on a page).
In worse case scenario this results in timeouts, e.g. if getElementsForSearch is called after a save/publish action.
How to reproduce
- Turn general query logging on, set a general_log_file
- Tail the general log file
- Run the below with e.g 3 pages with 3 elements
- Observe that all ElementalArea.ID values for all 3 pages are in the query created by DataList::fetchEagerLoadHasMany()
$id = 3
$page = \Page::get()->byId($id);
$val = $page->getElementsForSearch();
Possible Solution
Don't use eager loading?
I was able to workaround the situation by providing an extension that extends ElementalPageExtension and just get the elements for the current page for the request. Unfortunately as getEagerLoadedElements is private, it can't be overridden so the two methods that call it need to be duplicated.
Additional Context
No response
Validations
Module version(s) affected
5.4, 6
Description
I have observed performance issues that are caused by the use of the below code in ElementalPageExtension via the getEagerLoadedElements() method:
This method is private and accessed in the extension via getElementsForSearch() and getContentFromElementsForCmsSearch() methods.
For some reason when "eagerLoad" is called as above, the query created by DataList::fetchEagerLoadHasMany() fetches ALL Element records for ALL ElementalArea records in the database.
e.g for a test DB with 3 pages and 3 elemental areas:
Consider a DB with thousands of page records and that
IN()then has thousands of values, and > thousands of records returned.This doesn't make sense when a project just calls $page->getElementsForSearch(). The expected result when this happens is to query and return the elements attached to the ElementalArea relations of $page (there may be more than one ElementalArea on a page).
In worse case scenario this results in timeouts, e.g. if getElementsForSearch is called after a save/publish action.
How to reproduce
Possible Solution
Don't use eager loading?
I was able to workaround the situation by providing an extension that extends ElementalPageExtension and just get the elements for the current page for the request. Unfortunately as getEagerLoadedElements is private, it can't be overridden so the two methods that call it need to be duplicated.
Additional Context
No response
Validations
silverstripe/installer(with any code examples you've provided)