Optimizing Query for a large result set in AEM

[P_V_Nair]

I have the below query which is used to get all the images added (in two of the paths) which were created between two dates.

SELECT * FROM [dam:Asset] AS a WHERE ((ISDESCENDANTNODE(a,[/content/dam/wknd/en/site])) OR (ISDESCENDANTNODE(a,[/content/dam/wknd/en/site2]))) AND a.[jcr:created] > CAST('2023-01-29T21:30:00.000Z' AS DATE) AND a.[jcr:created] < CAST('2023-01-30T21:30:00.000Z' AS DATE)

In local the query is working fine and giving results.

But in dev it gives the below error.

The query is already picking an index. I validated that using  query manager tool.

I have more than 3 million images residing in one path used in the query and around 100,000 images created between these dates.

How can I optimize this query to give results , even though I have 3 million images in those paths?

[Kiran_Vedantam]

Hi @P_V_Nair 

Check if this article helps you: https://blog.developer.adobe.com/optimizing-aem-search-queries-9049d272df3b

Hope it helps!

Thanks,
Kiran Vedantam

[Vijayalakshmi_S · Accepted solution]

@P_V_Nair 

You can try the following

• Given your existing assets set up, in one of the path itself you have the max result set - But the query checks both the paths (using OR)
  • Try with one path
• See if you can consider additional parameters for refining the search results.
  • Ex: Refining search results based on type of asset (jcr:mimeType / dc:format / any such property for that matter) that will return the results within the allowed limit. 
• If you have sub folders under the path, refine the path in the query accordingly

[indranilm]

Can you try tweaking the query Bit  although some more information is needed regarding DEV env

SELECT * FROM [dam:Asset] AS a WHERE ((ISDESCENDANTNODE(a,[/content/dam/wknd/en/site]))  (ISDESCENDANTNODE(a,[/content/dam/wknd/en/site2]))) AND a.[jcr:created] > CAST('2023-01-29T21:30:00.000Z' AS DATE) AND a.[jcr:created] < CAST('2023-01-30T21:30:00.000Z' AS DATE) 

|

SELECT * FROM [dam:Asset] AS a WHERE  (ISDESCENDANTNODE(a,[/content/dam/wknd/en/site2]))) AND a.[jcr:created] > CAST('2023-01-29T21:30:00.000Z' AS DATE) AND a.[jcr:created] < CAST('2023-01-30T21:30:00.000Z' AS DATE)

[Gaurav_Sachdeva_]

Hi @P_V_Nair 

You can increase the node traversal limit from below configuration and give it a try. 

Apache Jackrabbit Query Engine Settings Service

Hope it helps!

Thanks,

Gaurav