AEM search - say goodbye to slow queries
Slow queries are a common problem faced by many AEM developers. There can be various reasons for your AEM queries not being lightning fast, ranging from inefficient queries to sub optimal index definitions.
In this talk, you will learn about:
- Common problems that can lead to slow queries
- Methods to identify the root cause of these slow queries
- Corrective measures that can be taken to optimise your overall search experience.
The talk will focus on specific queries to show examples of various problems and steps to identify and rectify the same.
So are you ready to make your queries lightning fast?
Robert Munteanu
Is it possible to return query results directly from the indexes, without touching other parts of the repository? (sometimes called covering indexes)
royteeuwen
I also tried achieving this, but it currently doesn't work because ACL rules aren't included in the indexes :/, so you could be getting results that you aren't allowed to read
Dennis Fisch
AEMaaCS uses MongoDB as persistence backend. Locally and with RDEs or 6.5 (AMS, on-prem) this is different. Aren't recommendations and index-tuning specific to the persistence backend?
Nilesh Mali
Any best solution for the most common issue, "The query read or traversed more than 20,000 nodes" ?
puradawid
Plus, why LIMIT 1000 does not help? :-(
Jurgen Brouwer - AmeXio
@puradawid because a limit is often applied after fetching all the results? Edit: let's say you limit a query descending on dates. How would it know which 1000 records to return before getting all the sorted results first?
puradawid
Yeah, for these cases it may not work, but AFAIK it won't work for even simple unordered queries
Jurgen Brouwer - AmeXio
@puradawid as far as I know a query is always sorted somehow, even if it's not visible (or obvious) to you
Dennis Fisch
For AEMaaCS, the first stage any of this can be tested on is a real dev stage. Any idea of when RDEs will use MongoDB in order to be more in-line with actual AEMaaCS stages?
royteeuwen
In the slow queries it tells you about the read optimization percentage. What is this and how can you make this better?
Nitin Gupta
The read optimization is the ratio of number of nodes read vs the number of nodes returned from the index. For instance, if for a query, index returns 100 nodes, but then query engine further filters out 50 nodes (because some constraints were not handled by index) - the read optimization here would only be 50 %. To see how to fix this, the index traversal section of the presentation along with the flow chart can be referred. We would need to make sure that the index actually starts serving all the constraints of the query.
Nilesh Mali
Query debugger Xpath vs SQL2, which one is better to use?
Jelle Bouwmans - AmeXio
Performance will be approximately equivalent as XPath queries are translated into JCR-SQL2.
Jurgen Brouwer - AmeXio
Nitin Gupta
Either of the query language is fine, most important factor while trying to optimise queries is to ensure that the index being used serves all the constraints of the query.
Fabian
Is there a way to split indices for nodes without special primary type? I.e. we couldn't achieve a separate index for collections, as they don't have a specific primary type like assets do (dam:Asset). Which meant we had to merge it with the nt:base index which can cause a major impact on performance for large node sets
Konrad Windszus
Why does /oak:index/cqPageLucene in AEMaaCS only consider properties up to level 4 (aggregation). That index is even used for incoming references rail in AEM 6.5...
Nicolas
If using pagination, how can you get the total number of pages without exceeding the limit?
Diego
Devide the value SearchResults.getTotalMatches() with the value you pass to the p.limit parameter
Konrad Windszus
Where is some documentation about properties which may contain references which are indexed with /oak:index/pathReference/indexRules/nt:base/properties? The names look rather arbitrary (prop_51)
Nitin Gupta
I am not sure if there is a documentation around the various index definitions in AEM - but for the specific query here , the prop_51 in the pathReference has a regex set to ^[^\/]*$, which means it indexes all the properties. However the index also has a valueRegex set at index level, which restricts the type of values from all the properties to be indexes to just "paths". So in effect, prop_51 actually indexes all the properties which have paths (/a/b/c) as values.