How can I optimize the codes in order to fix performance issue using Query Builder ?

[chung_yonge1684]

Hello Everyone,

Can I ask a question ?

I want to display menucollection and menuitem on restaurent page like the below that.

For Example,  In restarant1 Page.

1.Menu Collection 1

1.1.menu Item 1

1.2.menu Item 3

2. Menu Collection 3

2.1.menu Item 2

2.2.menu Item 3

menucollection has collection(ex:restaurants) for restaurants.

menuItem has collection(ex:menucollections) for menucollections.

Here are datas for restaurants, menucollections and menuitems.

[1] /content/site/shared/restaurants/restaurant1

/content/site/shared/restaurants/restaurant2

/content/site/shared/restaurants/restaurant3

restaurant1,restaurant2 and restaurant3 are CQ:Pages.

[2] /content/site/shared/menucollections/menucollection1/jcr:content/data

[Properties]

Name           / Type      / Values

restaurants / String[]  /  restaurant1,restaurant2

/content/site/shared/menucollections/menucollection2/jcr:content/data

[Properties]

Name        / Type      / Values

restaurants / String[]  /  restaurant2,restaurant3

/content/site/shared/menucollections/menucollection3/jcr:content/data

[Properties]

Name        / Type      / Values

restaurants / String[]  /  restaurant1,restaurant3

menucollection1,menucollection2 and menucollection3 are CQ:Pages.

[3] /content/site/shared/menuitems/menuitem1/jcr:content/data

[Properties]

Name        / Type      / Values

menucollections / String[]  /  menucollection1,menucollection2

/content/site/shared/menuitems/menuitem2/jcr:content/data

[Properties]

Name        / Type      / Values

menucollections / String[]  /  menucollection2,menucollection3

/content/site/shared/menuitems/menuitem3/jcr:content/data

[Properties]

Name        / Type      / Values

menucollections / String[]  /  menucollection1,menucollection3

menuitem1,menuitem1 and menuitem3 are CQ:Pages.

Here are my source using Query Builder.

1. DataRetriever.fetchMenuCollections(

(QueryBuilder)getService(QueryBuilder.class),

            getResourceResolver(),

            terms,

            relPaths,

            Constants.PATH_MENU_COLLECTIONS

        );

public static JSONArray fetchMenuCollections(QueryBuilder builder, ResourceResolver resolver, List<String> fulltextSearchTerm, List<String> fulltextRelPath, String path)

{

        final JSONArray list = new JSONArray();

        if (builder != null && resolver != null && fulltextSearchTerm != null && fulltextSearchTerm.size() > 0 && fulltextRelPath != null && fulltextRelPath.size() > 0) {

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

            SearchResult result = query(builder, session, fulltextSearchTerm, fulltextRelPath, path, "false");

            // iterating over the results

            for (Hit hit : result.getHits()) {

                try {

                    JSONObject object = new JSONObject();

                    Resource resource = hit.getResource() ;

                    Page page = resource.adaptTo(Page.class);

                    if (page != null) {

object.put("nodeName", page.getName());

object.put("title", page.getTitle());

object.put("path", page.getPath());

                        List<String> terms = new ArrayList<>();

terms.add(page.getName());

                        List<String> relPaths = new ArrayList<>();

relPaths.add(Constants.PROP_MENU_COLLECTIONS);

object.put(Constants.PROP_MENU_ITEMS, DataRetriever.fetchMenuItems(

                                builder,

                                resolver,

                                terms,

                                relPaths,

Constants.PATH_MENU_ITEMS

                        ));

                        list.add(object);

                    }

                }

                catch (RepositoryException e) {

                    log.error(e.getMessage(), e);

                }

            }         

        }

        return list;

}

public static JSONArray fetchMenuItems(QueryBuilder builder, ResourceResolver resolver, List<String> fulltextSearchTerm, List<String> fulltextRelPath, String path)

{

        return fetchData(builder, resolver, fulltextSearchTerm, fulltextRelPath, path);

}

private static SearchResult query(QueryBuilder builder, Session session, List<String> fulltextSearchTerm, List<String> fulltextRelPath, String path, String cond) {

        Map<String, String> map = new HashMap<>();

        map.put("path", path);

        map.put("type", Constants.PROP_CQPAGE);

        map.put("group.p.or", cond); // combine this group with OR

        for (int i = 0; i < fulltextSearchTerm.size(); i++) {

map.put("group."+(i+1)+"_fulltext", fulltextSearchTerm.get(i));

map.put("group."+(i+1)+"_fulltext.relPath", Constants.NODE_DATA + Constants.SLASH + "@" + fulltextRelPath.get(i));

        }

        map.put("p.offset", "0");

        map.put("p.limit", "-1");

        Query query = builder.createQuery(PredicateGroup.create(map), session);

        return query.getResult();

}

But I have a performance issue because of the below codes in fetchMenuCollections method.

object.put(Constants.PROP_MENU_ITEMS, DataRetriever.fetchMenuItems(

                                builder,

                                resolver,

                                terms,

                                relPaths,

Constants.PATH_MENU_ITEMS

));

How can I optimize the codes in order to fix the performance issue using Query Builder ?

[smacdonald2008]

Looks like you are iterating over the result set correctly. WHere is the issue.

[chung_yonge1684]

There are any performance issue in the below sources?

public static JSONArray fetchMenuCollections  {

    ...

       object.put(Constants.PROP_MENU_ITEMS, DataRetriever.fetchMenuItems(

                  builder,

                  resolver,

                  terms,

                  relPaths,

                  Constants.PATH_MENU_ITEMS

       ));

    ...

}

[smacdonald2008]

I recommend that you put your full code into GITHub so the community can look at it in full.

[Gaurav-Behl]

If you have already identified that the performance issue is in "DataRetriever.fetchMenuItems()" that means you are certain that there is no issue with the java code and the logic but the generated query. Is this assumption correct?

Try couple of things:

• Generate the query of fetchMenuItems() and run Explain Query to understand if that query is the bottleneck and hoe many times that query is executed within the loop? - http://localhost:4502/libs/granite/operations/content/diagnosistools/queryPerformance.html
• Identify what all indexes are being used in this query and how many nodes are traversed in the explain query report.
• Create index on specific properties like menucollections used in your query, if required.
• Modify the query to make it more specific, if required.
• If you have identified the query is still a bottleneck and there is no other way to fix it then
  • Reduce the number of times repository in nested loop otherwise,
  • fetch the entire data in a single query (or least number of queries) and perform the segregation/menu creation logic in java layer.

[chung_yonge1684]

I wonder how I can fetch the entire data in a single query with menucollections and menuitems.

Now menucollection and menuitem fetch each query.

[Gaurav-Behl · Accepted solution]

sample queries -

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE(s,'/content/site/shared/menucollections') OR ISDESCENDANTNODE(s,'/content/site/shared/menuitems')

or simply the root node for menucollection and menuitem -  SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE(s,'/content/site/shared')

or

fulltext=""

group.p.or=true

group.1_group.path=/content/geometrixx/en

group.1_group.type=cq:Page

group.2_group.path=/content/dam/geometrixx

group.2_group.type=dam:Asset

http://drfits.com/jcr-sql2-query-with-examples/ http://www.6dglobal.com/blog/9-jcr-sql-2-queries-every-aem-dev-should-know-2014-10-07

9 JCR-SQL2 Queries Every AEM Developer Should Know - Blog - 6D Global

you may write the query in either QueryBuilder or SQL2 or xpath based on use case. However, this may not fix the performance issue unless you reduce the number of queries by fetching entire content one time or apply index(es) on your properties or both.

You may also have to check the performance of new query that you plan to create. In addition to that, if you plan to fetch the entire content tree, keep other aspects of this design in mind. E.g. At what frequency would this content tree change and the number of content pages etc.

Hope that helps.