Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Adobe Summit 2023 [19th to 23rd March, Las Vegas and Virtual] | Complete AEM Session & Lab list
SOLVED

Optimizing Query for a large result set in AEM

Avatar

Level 6

 

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 2

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