AEM 6.5 paginated graphql query

[costyfax]

Hello everyone,

I'm having a problem in understanding how graphql paginated queries are working in AEM 6.5.

From the documentation https://experienceleague.adobe.com/en/docs/experience-manager-learn/getting-started-with-aem-headles... I can see that Adobe suggests to use paginated query type for large results (without saying how much these results should be...).

In my case, I have a folder with around 3000 content fragments (divided in subfolder not to having too much element under the same folder), all with the same content type; my query gets these elements selecting only the resource path, here is the query code:

query myQuery {
	elementPaginated(
		first: 5
		sort: "dateField DESC"
		filter: {_path: {_expressions: {value: "/content/dam/project/root-folder/", _operator: STARTS_WITH}}}
	) {
		pageInfo {
	  		hasNextPage
	  		endCursor
		}
		edges {
	  		node {
	  			_path
	  		}
		}
	}
}

Only selecting the path the query is very slow (around 10 sec to execute); from the logs I saw that the SQL2 repository query itself is very fast (100 ms) but after the resources are selected (the query selects all the 3000 elements) for each element the content fragment schema is loaded, even if the query says that I want only the first 5 elements.

The Java class that loads the schema is com.adobe.cq.dam.cfm.graphql.AbstractFetcher; I have all the suggested indexes for assets and content fragments.

Has anyone faced this situation before?

Thank you in advance!

[h_kataria]

Have you already performed this activity https://experienceleague.adobe.com/en/docs/experience-manager-65/content/implementing/developing/hea... ?
And do you see any difference if you try to use limit/offset based query ? 

[costyfax]

Thank you for your answer; I can see difference using limit/offset but only with small offset values, when offset is, fo example, 3000 the query is slow.

I also performed what you suggested but I cannot see any difference.

From the logs, I can see that the sql query is executed ordering the results are already ordered:

com.adobe.cq.dam.cfm.graphql.ContentFragmentsFetcher Content Fragments GraphQL Query: SELECT main.* FROM [dam:IndexedFragmentData] AS main WHERE ISDESCENDANTNODE(main, '/content/dam') AND main.[@string@model] = '/conf/project/settings/dam/cfm/models/myModel' AND (name() = 'master') AND ((ISDESCENDANTNODE(main, '/content/dam/project/elements'))) ORDER BY main.[calendar@publicationDate] DESC, main.[jcr:uuid] OPTION (INDEX TAG[contentFragments])

but after the query is executed and the data is managed by PaginatedFragmentsFetcher Java class the log says

08.07.2024 16:26:37.002 *DEBUG* [[0:0:0:0:0:0:0:1] [1720448796966] POST /content/cq:graphql/project/endpoint.json HTTP/1.1] com.adobe.cq.dam.cfm.graphql.sorting.IndexedFragmentDataSorting Adding field for hybrid query: publicationDate DESC
08.07.2024 16:26:37.002 *DEBUG* [[0:0:0:0:0:0:0:1] [1720448796966] POST /content/cq:graphql/project/endpoint.json HTTP/1.1] com.adobe.cq.dam.cfm.graphql.sorting.IndexedFragmentDataSorting Adding field for hybrid query: _uuid ASC
08.07.2024 16:26:37.002 *DEBUG* [[0:0:0:0:0:0:0:1] [1720448796966] POST /content/cq:graphql/project/endpoint.json HTTP/1.1] com.adobe.cq.dam.cfm.graphql.sorting.AbstractSorting Sort field not available for hybrid query: _variation ASC
08.07.2024 16:26:37.002 *DEBUG* [[0:0:0:0:0:0:0:1] [1720448796966] POST /content/cq:graphql/project/endpoint.json HTTP/1.1] com.adobe.cq.dam.cfm.graphql.sorting.AbstractSorting Sorting mode: HYBRID
08.07.2024 16:26:41.060 *DEBUG* [[0:0:0:0:0:0:0:1] [1720448796966] POST /content/cq:graphql/project/endpoint.json HTTP/1.1] com.adobe.cq.dam.cfm.graphql.sorting.AbstractSorting Sorting result set (2076 items) in memory.

and the calculated sorting mode is "HYBRID" so at the end the elements are sorted in memory.

I was searching how to maintain the JCR sort and skip the memory sorting; I saw from the code that the right value for the sorting mode is "JCR"; with this value, the fragment schema is fetched only for the required elements (in my case 5).

Any idea to force the JCR sorting?

Thank you!

[rahulpandey0102]

Refer this - https://graphql.org/learn/pagination/

[kautuk_sahni]

@costyfax Did you find the suggestion helpful? Please let us know if you require more information. Otherwise, please mark the answer as correct for posterity. If you've discovered a solution yourself, we would appreciate it if you could share it with the community. Thank you!