Query optimization using custom index | Community
Skip to main content
S
suyog
June 27, 2022
Solved

Query optimization using custom index

  • June 27, 2022
  • 1 reply
  • 827 views

Hi,
I am running a custom query (plz see below) to fetch around 2million records(from past 10 years) from DAM, but due to extensive traversing thru nodes it is reaching threshold limit and throwing an exception. I tried to create custom lucene index to optimize the query result but no luck and getting the same exception. Any pointers to achive this would be helpful if anyone has implemented it before.
query in Xpath: 

/jcr:root/content/dam/abc/product//element(*, dam:Asset)
[
((jcr:content/@jcr:lastModified > xs:dateTime('2010-03-14T00:00:00.000Z')
and jcr:content/@jcr:lastModified < xs:dateTime('2022-06-23T00:00:00.000Z')))
]

 

@14414417 @1429423 

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 vanegi

Hi @suyog,

Can you try creating the custom index with below definition:

- compatVersion = 2
- async = "async"
- jcr:primaryType = oak:QueryIndexDefinition
- evaluatePathRestrictions = true
- type = "lucene"
+ indexRules
+ dam:Asset
+ properties
+ lastModified
- name = "jcr:content/jcr:lastModified"
- propertyIndex = true

 

You can also tune the threshold for node traversal via the OSGi QueryEngineSettings.

More information available under : https://jackrabbit.apache.org/oak/docs/query/query-engine.html#Slow_Queries_and_Read_Limits

Thanks!!

 

1 reply

vanegi
Adobe Employee
vanegiAdobe EmployeeAccepted solution
Adobe Employee
June 28, 2022

Hi @suyog,

Can you try creating the custom index with below definition:

- compatVersion = 2
- async = "async"
- jcr:primaryType = oak:QueryIndexDefinition
- evaluatePathRestrictions = true
- type = "lucene"
+ indexRules
+ dam:Asset
+ properties
+ lastModified
- name = "jcr:content/jcr:lastModified"
- propertyIndex = true

 

You can also tune the threshold for node traversal via the OSGi QueryEngineSettings.

More information available under : https://jackrabbit.apache.org/oak/docs/query/query-engine.html#Slow_Queries_and_Read_Limits

Thanks!!

 

    S
    suyogAuthor
    July 8, 2022

    Hi, 

    We try to implement the above solution given but still got the traversal error as it is reaching the threshold of 100000 nodes and aborting the query. Can you please suggest anything for this.

    Thanks!!

    Terms & ConditionsAccessibility statement

    Loading footer...