Graphql query performance optimization

[ravir73578276]

I am using Graphql on AEM 6.5 to get the content-fragments. I have a  high number of Content Fragments that share the same model due to which the graphql list query has become really slow. How can I improve the performance of graphql query in AEM ? 

Note:- I am already using the persisted grahphql query and chaching it. But the initial load of the query results is really slow before it gets chached.

[Saravanan_Dharmaraj]

@ravir73578276 Please use Explain query tool and see which index is using or is it traversing without index. Please see the related post on indexing and indexing will help in speeding up the query performance

https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/issue-with-indexes-with-gr...

[ravir73578276]

It is picking up the correct index. But I don't see significant improvement in query performance.

[krati_garg · Accepted solution]

@ravir73578276 Can you share the statistics?
-GraphQL Query
-Result Count

-Time Taken for query response

-Explain Query Screen shot, when you say correct index is being picked up.

[Preston]

Can you advise how you get statistics in AEM on GraphQL query performance? I can't see anything in the Query Recorder to grab onto, nor in the Query Performance tool. 

[Preston]

I'm still wondering about this. 

Anything, @krati_garg ?

[sarav_prakash]

@Preston , this is the secret sauce. Add debug logger to "org.apache.jackrabbit.oak.query.QueryImpl". Everything you see on query peformance tools are coming from this class. Your logs will spit every details of query breakdown. 

[sarav_prakash]

@ravir73578276, @krati_garg  can u pls clarify how this answer helped to solve? We too are facing exact same issue above.

If any documentation or troubleshooting shared offline, please do share for community awareness.

[Preston]

I can't speak for @krati_garg or @ravir73578276 , but eventually what helped us was to take the SQL2 query generated under the hood by AEM and work with that. You can find recent or slow GraphQL queries under /libs/granite/operations/content/diagnosistools/queryPerformance.html. You'll notice them by the filter you used and things like that. Once you learn how to find them you can use the SQL2 query and start running the explain plan on that. 

[sarav_prakash]

Thanks @Preston , do we know if AEM is really converting graphql into SQL2? From above example, I understand he was rewriting his graphql in SQL2 language and comparing. The search indices for lucene based query builder is not same used in graphql. So wondering, how to convert graphql to SQL2. 

Is there specific packages to add into loggers? I am in AEMaaCS and hadn't any luck with logging known graphql packages. 

If you know how to convert graphql to SQL2, pls share.

[Preston]

If you're performing a GraphQL query for content fragments, under the hood our experience is that it creates a SQL2 query performed against a special index (dam:IndexedFragmentData) taht I believe is suited to this purpose. This is an example of what we see under /libs/granite/operations/content/diagnosistools/queryPerformance.html when we perform our GraphQL query in GraphiQL. 

SELECT main.* FROM [dam:IndexedFragmentData] AS main WHERE ISDESCENDANTNODE(main, '/content/dam') AND main.[@string@model] = '/conf/modelpath' AND (name() = 'master') AND ((ISDESCENDANTNODE(main, '/content/yourcontentpath')) AND ORDER BY main.[jcr:path] OPTION (INDEX TAG[contentFragments], TRAVERSAL FAIL)

If you 

[Preston]

Sorry. For some reason editing is broken right now. Anyway if you navigate to the Query Performance tool you should be able to clear out the history, then navigate to GraphiQL and run your GraphQL query. After refreshing the Query Performance tool - > Popular Queries tabe you should be able to figure out which query there is produced by your GraphQL query. 

[sarav_prakash]

You are right @Preston . In cloud world its different compared to AEM6.5 Doesn't automagically bring up the graphql queries into the query performance tool. Instead after lot of digging, figured, I had to enabled debug logging to this class "com.adobe.cq.dam.cfm.graphql.ContentFragmentsFetcher". This is indeed a game changer. Graphql uses SQL2 to fetch the initial list of results in-memory into JVM. And then starts applying filtering, sorting, pagination, cursors etc. This initial resultset is always the culprit. Large volume of content, the initial set fails and for us, it is crashing entire AEM server. This is good lead. I am next chasing how to add more indices or optimize my initial result set query. Cheers!

[Preston]

That's good news and a good tip on setting up logging. Good luck with debugging this further.