Adobe Experience Manager Sites & More

francescoc84066 · 9/30/16

Hi all,

i work on a big repository (AEM6.1) and some queries works on a lot of nodes. for example this query

select * from cq:PageContent where sling:resourceType='quattroruote/components/page/newspage' AND publishDate > TIMESTAMP '2013-01-01T00:00:00.000Z' AND publishDate < TIMESTAMP '2013-12-31T00:00:00.000Z' AND external IS NULL ORDER BY publishDate DESC

tooks about 1115ms the first time is run.

but if i have to count how many results the query return, time spent rise to severals seconds. in this case to 49.153 ms. (4457 nodes)

I attached the json results from Explain Query Tool.

Is there any way to get the counter using less time?

Thanks

Opkar_Gill · 10/2/16

Question was posted in the wrong forum, now moved to AEM Communities

Regards,

Opkar

Jörg_Hoh · 10/2/16

Hi,

Counting the size of the result set (also the ordering itself) requires that the QueryEngine goes through all the results and check if a result node is visible by the calling session. This requires an ACL check for each result, which can be a costly operation.

Jörg

Jitendra_S_Toma · 10/2/16

Hi,

Here is the good OAK document which describes how OAK query works in AEM. And also suggest possible optimization in query total results.

http://jackrabbit.apache.org/oak/docs/query/query-engine.html

AEM 6.1 uses OAK 1.2 which proposes as follows

Oak 1.2.x and newer supports a compatibility flag so that it works in the same way as Jackrabbit 2.x, by returning an estimate. See also OAK-2926. This is best configured as described in OAK-2977: When using Apache Sling, since Oak 1.3.x, add the following line to the file conf/sling.properties, and then restart the application:

oak.query.fastResultSize=true

francescoc84066 · 10/5/16

Thanks for yours anwers.

We have just an index on ordering property, and also if i delete the ordering condition, the counting time is too much. So AEM cannot have an acceptable counting time?