Optimizing Query for a large result set in AEM | Community
Skip to main content
P
Adobe Champion
P_V_NairAdobe Champion
Adobe Champion
January 31, 2023
Solved

Optimizing Query for a large result set in AEM

  • January 31, 2023
  • 4 replies
  • 1752 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

Kiran_Vedantam
Community Advisor
Kiran_VedantamCommunity Advisor
Community Advisor
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

    Vijayalakshmi_S
    Vijayalakshmi_SAccepted solution
    Level 10
    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

     

      I
      indranilm
      Level 2
      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)

        Gaurav_Sachdeva_
        Gaurav_Sachdeva_
        Level 3
        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

         

         

         

        Terms & ConditionsAccessibility statement

        Loading footer...