Level 4

Solved

Need help to in optimization an existing query

Forum|Forum|4 years ago
September 6, 2021
5 replies
2502 views

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;

}

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

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

}

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.

Kiran_Vedantam

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-indexing.html?lang=en

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

Thanks,

Kiran Vedantam.

Ritesh_Mittal

Community Advisor and Adobe Champion

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-indexing.html?lang=en

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

Kishore_Kumar_

Level 9

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.

AbhishektyAuthor

Level 4

Sure

AbhishektyAuthor

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?

Rohit-Negi

Level 3

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

Community Manager

@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

Vijayalakshmi_S

Level 10

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.

AbhishektyAuthor

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

AbhishektyAuthor

Level 4

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.

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

}

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded