Details
-
Bug
-
Resolution: Unresolved
-
Medium
-
None
-
6.2.16
-
None
-
None
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.
Checklists
Attachments
Issue Links
- relates to
-
MAGNOLIA-9049 Improve search and findbar performance
-
- Open
-