Part 9 - Pagination with Skip and Take vs Continuation

What are pages and how do we handle them?

When working with more than 10,000 records in an OData result, F&O will automatically split that chunk of records into a page. When we have more than 1 page in our result set, we have to find a way to loop though those pages. We have records stored in (up to) 10k pages with as many pages as is required for a query. However, if you're needing to paginate, you may want to look at another mechanism for inspecting your F&O data.

The Methods of Pagination

There are 2 primary ways of looping through pages. They are "Skip and Take" and Continuation.

Skip and Take

With skip and take, you are simply taking some amount of records while skipping some amount of records before you take the records returned. This would typically be requests similar to take 10000, skip 10000, take 10000, skip 20000, take 10000, skip 30000, and so on until you no longer have a full page. An example of what this would look like is:

            int skip = 0;
            int take = 10000;

            //get count of records without getting any other data
            int numberOfRecords = context.SalesOrderLines.Take(0).Count();

 

            for (skip = 0; skip <= numberOfRecords; skip += take)
            {
                context.SalesOrderLines.Skip(skip).Take(take).ToList();

            }

Continuation

When you have a result set that has more records than can fit in a page, F&O will provide an additional optional attribute in the result set with a link to the next step of records. You can detect if that attribute exists and if it has a value then follow that link to get to the next page. An example of how to do this is:

var response = context.SalesOrderLines.Execute() as QueryOperationResponse<SalesOrderLine>;

do
{

    if (nextLink != null)
    {
        response = context.Execute<SalesOrderLine>(nextLink) as QueryOperationResponse<SalesOrderLine>;
    }

    //forced loop to enumerate each item in the response
    foreach (SalesOrderLine salesLine in response)
    {
    }
}
// Loop if there is a next link
while ((nextLink = response.GetContinuation()) != null);

The Tests

In order to determine which style is the best in terms of performance, I setup the two different methods against the sale result set. I am using the Sales Order Lines in the Contoso/Sample dataset available for F&O. There are approximately 200k Sales Order Lines so that is more than enough data to see how each performs. We will be looping through all pages with both methods.

The Results

When looking at the sum of all durations for all interactions, the winner is clear.

You can see the ReadAllPagesQuery, which represents the continuation method, is faster. However, all of these measures are in milliseconds. Let's look in terms of minutes.

In minutes, you can see how much faster Continuation is as compared to skip and take. However, even when using the fastest method available, it took approximately 182 minutes to fetch and enumerate approximately 200k records. That is an approximate throughput of 1,100 records per second which is quite poor. 

Key Takeaways

  • Continuation is faster than skip and take
  • if you're paging, there is probably a better solution

All code can be found at https://github.com/NathanClouseAX/AAXDataEntityPerfTest/tree/main/Projec....

All Visuals can be found at https://github.com/NathanClouseAX/AAXDataEntityPerfTest/tree/main/Projec....

Blog Main Tag: