Expand my Community achievements bar.

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

Optimizing Query for a large result set in AEM

Avatar

Adobe Champion

 

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.

P_V_Nair_0-1675178437168.png

 

 

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?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

@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

 

View solution in original post

4 Replies

Avatar

Correct answer by
Community Advisor

@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

 

Avatar

Level 2

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)

Avatar

Level 4

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

 

Gaurav_Sachdeva1_0-1675375848977.png

Hope it helps!

 

Thanks,

Gaurav