paging crm dynamics results above 5k "Requires PagingCookie"

Question

In a method that takes the parameters int pageNum and int pageSize I am trying to return data from dynamics based on that specific page and size.

I am using a QueryExpression and I set the exp.PageInfo page number and page size to achieve this which works fine until page 51 with a page size of 100 which produces the error "Paging cookie is required when trying to retrieve a set of records on any high pages"

This brings me to near duplicate question territory (such as Dynamics CRM - How to get the second page of a fetchXml query beyond the 5000 elements? ) but the claims "you do not need the paging cookie" are just not correct at all it seems, there is nothing I can do for results beyond 5k that do not produce that error.

I am now paging over -the entire result set- (which allows me to get the PagingCookie from the previous results to pass to the next page request) and then returning the data I want from that set, but that is super slow. I have made it faster by dynamically altering the query in the paging loop so it only returns columns if the current data is in the requested page range which has shaved about 30secs off the query, but still very slow for such a large data set.

So, is there A) some thing that will enable me to get these high results without a paging cookie? Is this a QueryExpression limitation for example? or B) a faster way of handling this problem than iterating over all results until the page I want?

Thanks.

Answer 1

Unfortunately there's no way to "fast forward" to high pages in your query result.
You'll have to use the paging cookie just the way you are doing right now.
If you knew the last and next record from a previous query you could try creating a high-page-cookie to start with.

As @Aron suggest in his answer, the only improvement might be gained from sorting and/or filtering/partitioning the data (by createdon, etc.).

Answer 2

Filtering the data set to return fewer pages might be something to investigate. Or, if the desired page is closer to the end of the data set, reverse the sort order.