Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.

Counting results from a SQL Query is very slow

Avatar

Level 1

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

5 Replies

Avatar

Employee

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

Regards,

Opkar

Avatar

Employee Advisor

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

Avatar

Level 9

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

Avatar

Level 1

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?

Avatar

Level 10

Also - did you watch the GEMS session on indexing: 

https://docs.adobe.com/ddc/en/gems/oak-lucene-indexes.html

THis may help too.