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;
}
Solved! Go to Solution.
Views
Replies
Total Likes
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
For index related approach
Please update this thread if you are looking for any specific inputs.
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.
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://hashimkhan.in/2018/03/12/improvise-the-search-index/
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.
Sure
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?
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.
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.
@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.
Views
Replies
Total Likes
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.
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"
}
}
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) ";
}
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
For index related approach
Please update this thread if you are looking for any specific inputs.
Views
Likes
Replies
Views
Likes
Replies