Counting results from a SQL Query is very slow

[francescoc84066]

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]

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

Regards,

Opkar

[Jörg_Hoh]

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]

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]

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?

[smacdonald2008]

Also - did you watch the GEMS session on indexing: 

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

THis may help too.