Optimizing Query for a large result set in AEM | Community
Skip to main content
P
P_V_Nair
New Participant
January 31, 2023
Solved

Optimizing Query for a large result set in AEM

  • January 31, 2023
  • 4 replies
  • 1746 views

 

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?

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

@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

 

4 replies

Gaurav_Sachdeva_
Gaurav_Sachdeva_
New Participant
February 2, 2023

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

 

 

 

I
indranilm
New Participant
February 1, 2023

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)

    Vijayalakshmi_S
    Vijayalakshmi_SAccepted solution
    New Participant
    February 1, 2023

    @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

     

      Kiran_Vedantam
      Kiran_Vedantam
      New Participant
      January 31, 2023

      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

        Terms & ConditionsAccessibility statement

        Loading footer...