Need help to in optimization an existing query

[Abhishekty]

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;

  }

[Kiran_Vedantam]

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.

[Ritesh_Mittal]

Hi @Abhishekty ,

If the frequency of query being executed is high then we must go for indexing, else the query runs for entire content. 

you can check-

https://experienceleague.adobe.com/docs/experience-manager-65/deploying/deploying/queries-and-indexi...

https://hashimkhan.in/2018/03/12/improvise-the-search-index/

[Kishore_Kumar_]

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. 

[Abhishekty]

Sure

[Abhishekty]

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?

[Kishore_Kumar_]

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.

[Rohit-Negi]

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.

[kautuk_sahni]

@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. 

[Vijayalakshmi_S]

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. 

[Abhishekty]

@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"
}
}

[Vijayalakshmi_S]

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.

[Abhishekty]

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) ";

    }

[Vijayalakshmi_S · Accepted solution]

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

"Explain Query" Response:

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

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.