Expand my Community achievements bar.

SOLVED

Need help to in optimization an existing query

Avatar

Level 4

Brief: I need optimization in an existing query which is for fetching all the promo from /etc/promos.get.promoid.allPromos.json call

Issue/Feedback: JCR query seems to be expensive and need some optimization for better performance.

I got the feedback: consider creating an index or changing the query, 

So, I need help how in modifying (creating an index or changing the query) the code and by which I'll get the same result that I got earlier (all promo)?

Code: currently I am gerneting the query like below code:

 

@CheckForNull

  private Query generateSearchQuery(String searchfield, String searchCriteria, String searchPath, ResourceResolver resourceResolver)

    throws RepositoryException {

    Query query = null;

    String queryString = "";

    String generateQuerySearchCriteria = searchCriteria;

    if (generateQuerySearchCriteria.equalsIgnoreCase(SprintConstants.ALL_PROMOS_SELECTOR)) {

      queryString = QUERY_BASE_PROMO + searchPath + "])) and (identifier is not null) ";

    } else if(searchfield.equalsIgnoreCase(SprintConstants.OFFERS_PROMOID)){

      generateQuerySearchCriteria = '%' + generateQuerySearchCriteria + '%';

    queryString = QUERY_BASE_DEVICE_AND_PROMO + searchPath + QUERY_BRACKETS + SprintConstants.PROPERTY_IDENTIFIER + QUERY_LIKE + generateQuerySearchCriteria + QUERY_UNIVERSAL_PROMO;

    }

    else{

      generateQuerySearchCriteria = '%' + generateQuerySearchCriteria + '%';

    queryString = QUERY_BASE_DEVICE_AND_PROMO + searchPath + QUERY_BRACKETS + searchfield + QUERY_LIKE + generateQuerySearchCriteria + QUERY_UNIVERSAL_PROMO;

    }

    

    Session session = resourceResolver.adaptTo(Session.class);

    if (session != null) {

      QueryManager queryManager = session.getWorkspace().getQueryManager();

      query = queryManager.createQuery(queryString, Query.JCR_SQL2);

    }

    return query;

  }

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @Abhishekty,

Getting it now. Since you are getting the results from nt:base in the query, you can amend the existing OOTB ntBaseLucene something like below. 

Lucene property index definition for the property named "identifier" and given that it is used for not null check, we need to make use of the property named "notNullCheckEnabled" set to true. 

Reindex ntBaseLucene index and you should see the query using ntBaseLucene

Vijayalakshmi_S_0-1631031279017.png

"Explain Query" Response:

I added a property named identifier to one of the content pages for testing. 

Vijayalakshmi_S_1-1631031933260.png

 

I suggest to go about this change in the following sequence:

Query Revise approach

  • Explore the content structure under the desired path (in this case, offers path) and property constraints/results that we are trying to achieve. See if you can change/revise the query. If there is no way you can amend the query/it is already refined, then consider index. 

For index related approach

  • You have shared the queryString for "if" condition. Similarly frame the queryString for other two scenarios (elseif and else)
  • Execute all three queries one by one in Query Performance -> Explain Query
  • Then decide on the index (if we can make use of OOTB or create completely new custom definition)

Please update this thread if you are looking for any specific inputs.

View solution in original post

13 Replies

Avatar

Community Advisor

Hi @Abhishekty,

 

Need of indexing as per the documentation:

"Unlike Jackrabbit 2, Oak does not index content by default. Custom indexes need to be created when necessary, much like with traditional relational databases. If there is no index for a specific query, possibly many nodes will be traversed. The query may still work but probably be very slow."

 

You can configure the indexes as mentioned here: https://experienceleague.adobe.com/docs/experience-manager-65/deploying/deploying/queries-and-indexi...

 

By doing this, your response can be quick. Hope this helps!

 

Thanks,

Kiran Vedantam.

Avatar

Community Advisor

Hi @Abhishekty ,

 

Copy and paste your query in this https://oakutils.appspot.com/generate/index It will generate a index in XML/JSON format. and put the same in oak:index folder. 

Avatar

Level 4

Hi @Kishore_Kumar_,

 

I have gernated the query and I have created the same node structure in oak:index folder.

 

 

Query:

 

Filter(query=SELECT [promoid] FROM [nt:base] AS s WHERE (ISDESCENDANTNODE(s, [/etc/commerce/products/sprint/en/offers])) and (identifier is not null) , path=/etc/commerce/products/sprint/en/offers//*, property=[identifier=[is not null]]);

 

Gernated Result: 

 

{
"compatVersion": 2,
"async": "async",
"jcr:primaryType": "oak:QueryIndexDefinition",
"type": "lucene",
"indexRules": {
"jcr:primaryType": "nt:unstructured"
}
}

 

 

So anything else is required or it will fix my original issue?

Avatar

Community Advisor

Hi @Abhishekty ,

 

Please add includedPaths property as well if you want to include only certain paths while indexing.

 

https://jackrabbit.apache.org/oak/docs/query/lucene.html#include-exclude 

 

For reference please check any OOTB index.

Avatar

Level 4

Hi @Abhishekty 

 

First of all check the traversing cost of your query, Use the link: http://<server>:<port>/libs/granite/operations/content/diagnosis/tool.html/granite_queryperformance

If it comes more than 10,000 then go ahead and create index for that else creating indexes won't be of much difference.

For query optimization: Make sure the search area is minimum and the node type mentioned in query should be the one which have the data.

For creating index: Though your query is not clear, But Index should always be the one which is having some condition OR the data contained in that index is unique, else it won't be of much help.

 

 

Avatar

Administrator

@Rohit-Negi  Good to see you back inside the AEM Community. Keep the great work of assisting others going. Looking forward to more contributions from you. 



Kautuk Sahni

Avatar

Community Advisor

Hi @Abhishekty 

Could you please log the "queryString"  and share the exact query that you have as part of snippet. I will share the sample index definition considering the same. 

 

Avatar

Level 4

@Vijayalakshmi_S 

 

I have gernated the query and I have created the same node structure in oak:index folder.

 

 

Query:

 

Filter(query=SELECT [promoid] FROM [nt:base] AS s WHERE (ISDESCENDANTNODE(s, [/etc/commerce/products/sprint/en/offers])) and (identifier is not null) , path=/etc/commerce/products/sprint/en/offers//*, property=[identifier=[is not null]]);

 

Gernated Result: 

 

{
"compatVersion": 2,
"async": "async",
"jcr:primaryType": "oak:QueryIndexDefinition",
"type": "lucene",
"indexRules": {
"jcr:primaryType": "nt:unstructured"
}
}

Avatar

Community Advisor

Hi @Abhishekty,

I saw your comment already, was just looking for exact query that is framed per the code snippet.

Example : identifier that you are using. (property constraint) 

Also, query is framed based on certain conditions (example : could guess it is a "LIKE" string) 

In short, if you could print that "queryString" from the code snippet (considering all possible cases that you have coded), we can decide accordingly.

Avatar

Level 4

Hi @Vijayalakshmi_S ,

If I am understand you correctly you are looking below one : 

 

SELECT [promoid] FROM [nt:base] AS s WHERE (ISDESCENDANTNODE(s, [/etc/commerce/products/sprint/en/offers])) and (identifier is not null)

 

This gernated from my below code:

 

if (generateQuerySearchCriteria.equalsIgnoreCase(SprintConstants.ALL_PROMOS_SELECTOR))

{

      queryString = QUERY_BASE_PROMO + searchPath + "])) and (identifier is not null) ";

    }

 

 

Avatar

Correct answer by
Community Advisor

Hi @Abhishekty,

Getting it now. Since you are getting the results from nt:base in the query, you can amend the existing OOTB ntBaseLucene something like below. 

Lucene property index definition for the property named "identifier" and given that it is used for not null check, we need to make use of the property named "notNullCheckEnabled" set to true. 

Reindex ntBaseLucene index and you should see the query using ntBaseLucene

Vijayalakshmi_S_0-1631031279017.png

"Explain Query" Response:

I added a property named identifier to one of the content pages for testing. 

Vijayalakshmi_S_1-1631031933260.png

 

I suggest to go about this change in the following sequence:

Query Revise approach

  • Explore the content structure under the desired path (in this case, offers path) and property constraints/results that we are trying to achieve. See if you can change/revise the query. If there is no way you can amend the query/it is already refined, then consider index. 

For index related approach

  • You have shared the queryString for "if" condition. Similarly frame the queryString for other two scenarios (elseif and else)
  • Execute all three queries one by one in Query Performance -> Explain Query
  • Then decide on the index (if we can make use of OOTB or create completely new custom definition)

Please update this thread if you are looking for any specific inputs.