Counting results from a SQL Query is very slow | Community
Skip to main content
F
francescoc84066
September 30, 2016
Question

Counting results from a SQL Query is very slow

  • September 30, 2016
  • 5 replies
  • 1921 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

5 replies

O
Adobe Employee
ogillAdobe Employee
Adobe Employee
October 2, 2016

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

Regards,

Opkar

joerghoh
Adobe Employee
joerghohAdobe Employee
Adobe Employee
October 2, 2016

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
Jitendra_S_Toma
Level 10
October 3, 2016

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
F
francescoc84066Author
October 5, 2016

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
smacdonald2008
Level 10
October 5, 2016

Also - did you watch the GEMS session on indexing: 

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

THis may help too. 

Terms & ConditionsAccessibility statement

Loading footer...