[MGNLUI-7360] listView and thumbnailView are unusable for large repositories Created: 22/Mar/22  Updated: 08/Sep/23

Status: Open
Project: Magnolia UI
Component/s: None
Affects Version/s: 6.2.16
Fix Version/s: None

Type: Bug Priority: Medium
Reporter: Tim Molenaar Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File Count.groovy    
Issue Links:
Problem/Incident
Relates
relates to MAGNOLIA-9049 Improve search and findbar performance Open
Template:
Acceptance criteria:
Empty
Task DoD:
[ ]* Doc/release notes changes? Comment present?
[ ]* Downstream builds green?
[ ]* Solution information and context easily available?
[ ]* Tests
[ ]* FixVersion filled and not yet released
[ ]  Architecture Decision Record (ADR)
Bug DoR:
[ ]* Steps to reproduce, expected, and actual results filled
[ ]* Affected version filled
Date of First Response:
Epic Link: Grid performance
Team: Nucleus

 Description   

The listview (pages-app and assets-app) and thumbnailview (assets-app) both use the JcrDataProvider, which is, unfortunately, unfit for large repositories. This is probably because the executed Query is requesting information about all the nodes in the DAM-workspace. In order to verify the performance of the generated queries in JcrDataProvider::fetchFromBackEnd we created a Groovy-script that mimics the generated query for listview/thumbnailview-requests. To give you an idea of the performance of this script on our test-environment:

> Starting query [w:website, q:SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:page') ORDER BY SCORE(t), l:JCR-SQL2]
> Query execution [w:website, q:SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:page') ORDER BY SCORE(t), l:JCR-SQL2] took 53679ms for 1000 results
> Starting query [w:dam, q:SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:folder' OR t.[jcr:primaryType] = 'mgnl:asset') ORDER BY SCORE(t), l:JCR-SQL2]
> Query execution [w:dam, q:SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:folder' OR t.[jcr:primaryType] = 'mgnl:asset') ORDER BY SCORE(t), l:JCR-SQL2] took 165143ms for 1000 results

That's 53 seconds for retrieving all pages, and 165 seconds for retrieving all assets. And this is with a query-limit of 1000. If we remove the limit, the duration even increases with +- 50%. The actual results are: 158602 in the website-workspace, and 1102685 in the DAM-workspace.

Apparently the JCR-query is not optimized for large queries. A quick look on Google tells me that the respond-time can very much depend on how the query is written. I think the solution is in the direction of optimizing the query. I hope you can help us by doing so and thinking along with this problem, as it renders our listview and thumbnailview unusable at the moment.



 Comments   
Comment by Rico Jansen [ 15/Jul/22 ]

Added the count script we used for these result.

Generated at Mon Feb 12 09:45:27 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.