Investigating
Persisted Queries: Use locale param to filter the internal SQL query results in order to improve performance
| Request for Feature Enhancement (RFE) Summary: | Use locale param to filter the internal SQL query results in order to improve performance |
| Use-case: | On any multilanguage project, the same type of content fragments will be potentially created under each language. When a persisted query is executed to retrieve all the content fragments for a specific model, filtering also by dates and locale, the locale is not used on the internal SQL, causing the query to be really slow. Sample internal SQL Query: SELECT main.* FROM [dam:IndexedFragmentData] AS main WHERE ISDESCENDANTNODE(main, '/content/dam') AND main.[@string@model] = '/conf/mysite/settings/dam/cfm/models/news' AND (name() = 'master') AND ((main.[calendar@publicationDate] >= cast('2023-07-05T08:30:57.266Z' AS date) AND main.[calendar@publicationDate] <= cast('2023-07-08T08:30:57.266Z' AS date))) ORDER BY main.[jcr:path] OPTION (INDEX TAG[contentFragments], TRAVERSAL FAIL) As a workaround, we have built a new version of the persisted query where we specify the path to the specific language as a filter, then the query only takes a few milliseconds, but this should be part of the internal functionality when a locale is used, the front might know the language but not the content path. |
| Current/Experienced Behavior: | Locale is not used in the internal SQL filter |
| Improved/Expected Behavior: | Locale should be used as part of the internal SQL filter |
| Environment Details (AEM version/service pack, any other specifics if applicable): | AEM CS 2023.4 |
| Customer-name/Organization name: | Real Madrid Club de Futbol |
| Screenshot (if applicable): | |
| Code package (if applicable): |