The million-dollar question is “Which tool is right for your application?” This article explores several pagination strategies to demystify when you should use each tool to achieve peak application performance.

About the examples

In the following examples, we will assume that your Web application needs to paginate over all open opportunities for accounts in California. These examples depict how you might retrieve the data using each pagination tool, but we won’t cover the specifics of building the pagination within this article.
Note: To ensure peak performance, always minimize your data set and ensure that your query is selective so that it can be appropriately indexed. To learn more about building selective queries, see:

Let’s look at each of the tools that Salesforce offers for building this pagination functionality.

The query and queryMore Force.com SOAP API calls

The query and queryMore Force.com SOAP API calls were designed to allow developers to retrieve and incrementally process a large result set. These calls allow only forward movement through the result set, meaning that each chunk of returned data meaning that you can move through the chunks of data only in the sequence in which they were returned by the query. When you use the query and queryMore calls to build pagination within your application, you’ll get a large data set returned to that application, or directly to the browser that will store the data and manage the pagination. The data that is managed by the application is held in memory as users move from one page to the next through the data set. While the size of the data set is limited only by the number of records that a SOQL query can return, client resources are usually the factor constraining the size of your overall data set.

It is also important to understand that queryMore holds a query locator for up to 15 minutes of inactivity. This essentially stores the query in cache until the 15-minute inactivity threshold has been reached, after which subsequent requests against the query locator return an error. Just as you could use query and queryMore within the Force.com SOAP API, you could also use SOQL queries in Apex to return large data sets to a Visualforce page that manages pagination internally and stores the data in the page’s view state. This isn’t a recommended approach, however.

Pros and cons of choosing this pagination tool

Pros

  • Can return extremely large data sets to your application if that application is capable of managing those data sets
  • Retrieves data more quickly than any of the other pagination tools

Cons

  • Allows data to be incrementally processed only from beginning to end
  • Depending on your implementation, might require the entire data set to be held by the client
  • Maintains a query locator for up to 15 minutes of inactivity, then returns an error on queryMore calls
  • Allows users to have only up to ten query cursors open at a time

Example: Retrieving data in Java to roll your own pagination

Visualforce StandardSetController

The StandardSetController is an extremely powerful tool with built-in functionality that you can use to greatly simplify the custom code in your Visualforce pages. Because the server returns only the data for the page being requested, the StandardSetController can significantly reduce view state, especially compared to the view state you would get while using SOQL.

Pros and cons of choosing this pagination tool

Pros

  • Manages data sets on the server, which reduces page state and increases performance
  • Allows you to paginate over large data sets that have up to 10,000 records
  • Includes built-in functionality such as next, previous, first, last, getResultSize, and other methods that can simplify your page
  • Allows you to paginate forward and backward or to any page within the result set
  • Uses a server-side cursor to cache the entire result set, so results will not change if data changes in the database while a user is paginating from one page to the next

Cons

  • Has built-in functionality that results in a very small increase in view state size
  • Can be used only in Apex

You can find more information about Standard List Controllers and the StandardSetController Apex class in the Visualforce Developer Guide.

Example: Retrieving data in Apex to paginate with the StandardSetController

OFFSET clauses within SOQL queries

With Salesforce SOQL, you can add an OFFSET clause, as well as a limit specifying a start point and a range of records to retrieve for a page, within your queries. This functionality allows you to implement your own pagination with SOQL queries without having to store the entire data set in memory. As a user moves from one page to the next, each page request executes a query to return the specific records required for that page.

Pros and cons of choosing this pagination tool

Pros

  • Can be used in SOQL executed within the SOAP API, the REST API, or Apex
  • Gives you the ability to build robust pagination using the Force.com APIs without having to store the entire data set in the Web application

Cons

  • Supports OFFSET clauses only for ranges of 2,000 or fewer records
  • No server-side cursor is created to cache the full result set for future OFFSET queries. This means that what you see in each page of results reflects what is in the database at the time that you submitted your request. So if someone modifies the underlying data while you’re paginating over large data sets, what you saw in the previous or following pages might have changed.
  • As users move from one page the next in the result set, each of their requests for page results executes a query against the database.
  • If you want to build advanced pagination functionality that displays a specific number of result pages, you must use a SOQL query to retrieve the total number of records before you execute the query containing the OFFSET clause. Executing this SOQL query to count the records affects performance and limits usage.

While many developers view OFFSET as the tool of choice for pagination, we will see that there are many cases in which it is not the tool of choice from a performance perspective.

To read more about OFFSET, see the OFFSET topic in the SOQL and SOSL Reference.

Example: Retrieving data with OFFSET to build custom pagination

This query returns the first 25 records of our result set.

When the user selects the next page in the result set, this query returns the next set of 25 records.

When to use each pagination tool

You have gathered the requirements for your Web application and are ready to begin building it, but which tool will work best for your particular use case? Let’s explore how each of these tools work under the hood.

The query and queryMore calls: under the hood

Before the creation of the OFFSET clause, the only way to paginate with the Force.com involved using the query and queryMore SOAP API calls. Although developers might think that the OFFSET clause has made the query and queryMore calls obsolete in pagination design, this assumption is only true in some cases. The query and queryMore calls still provide a performance edge when you’re paginating over large data sets because they hold a server-side cursor (batch), which in turn stores the data in the API Cursor Server (ACS).

The ACS is a server that manages cursors to allow application servers to perform optimally. Subsequent requests for additional data only need to hit the ACS to retrieve data, which is faster than executing queries for each set of data. Remember that when you use this tool to paginate, you can only retrieve data moving forward, which will either limit functionality in the page or require more memory usage. When building pagination on the Force.com platform with Visualforce, do not execute a query to retrieve data for pagination–the other tools can provide the same functionality and minimize page state, which increases page response time.

OFFSET clauses: under the hood

Salesforce released the OFFSET clause to give developers who were building pagination an alternative to the query and queryMore SOAP API calls. It allows you to write Web applications that paginate forward, backward, or to any page within the result set — without having to store the entire data set in the external application or in memory on your client browser.
When deciding whether this tool is right for your Web application, it is important to understand how it works. Each time you execute a query with an OFFSET clause, that query hits the application server and the database to retrieve the records specific to the page the user is requesting. Furthermore, when the user executes a query with an offset value, the database actually queries the entire result set starting from the first record, not the OFFSET value.

For example, assume that your pagination size is 100 and that you write the following two queries.

Your first query returns the first 100 rows as expected. Your second query is more expensive than the first. Essentially, it returns 200 rows but skips the first 100. If you tried to access a third page of results, the query would return 300 rows but skip the first 200, and so on. As a result, OFFSET works well in situations in which users seldom navigate past the first page of results because it returns that first page extremely efficiently. However, if users tend to navigate through pages, this tool will be more expensive than the others. One final thing to consider is that OFFSET allows you to build pagination with a slightly smaller view state than the StandardSetController does. As a result, a mobile application in which users don’t typically paginate and minimizing view state size is critical can be a very good use case for this tool.

The StandardSetController: under the hood

You can use the StandardSetController only in your Force.com Visualforce pages, but it provides the strongest performance of the three pagination tools when you’re building a Visualforce page in which you expect users to navigate past the first page of results. While the query and queryMore calls give you about the same query performance as the StandardSetController does, the built-in features of the StandardSetController give you better page performance because you have to manage only a single page of data within your Web application at any given time.

The StandardSetController also has an edge over OFFSET queries in that it uses the ACS, which increases the response time of each page that is requested after the initial page is loaded. Let’s look at the same use case we used for OFFSET.

In this example, we will instantiate a StandardSetController using the same query.

Let’s assume that your pagination size is still 100.

  1. The first call returns 2000 IDs and stores them temporarily on the ACS.
  2. In the second call, a queryMore call is issued to fetch the required fields based on the IDs that the system already stored.

In this example, the first request is slightly more expensive than it would be if you had used one of the other pagination tools because it is fetching the first 2000 IDs and storing them on the ACS. However, subsequent page requests will be faster because the record IDs are already stored on the ACS and only the fields need to be fetched. In most cases, users are likely to page through the results in well-built Web applications. As a result, we usually recommend this tool to people when they’re developing Web applications on the Force.com platform.

Conclusion

Since the release of the OFFSET clause, many developers have come to think of it as the default solution for building pagination in their Web applications. Although it does provide more flexibility than the query and queryMore calls when you use them with the SOAP API, don’t consider it the only alternative for building pagination in your Web applications.

  • Use OFFSET for pages in which users typically do not move beyond the first page or results, or for pages in which the SOAP API is used to retrieve a relatively small data set for pagination.
  • Use the StandardSetController for most Web applications built on the Force.com platform, except mobile applications and other applications in which users are not likely to move past the first page of results.
  • Use the query and queryMore SOAP API calls to increase performance when you’re returning large data sets while using the API for pagination.

The next time you build a Web application that requires pagination, revisit this article so that you can architect a solution that maximizes your application’s performance and user adoption.

Related resources

About the author and CCE Technical Enablement

Sean Regan is an Architect Evangelist within the Technical Enablement team of the salesforce.com Customer-Centric Engineering group. The team’s mission is to help customers understand how to implement technically sound salesforce.com solutions. Check out all of our resources on the Architecture Developer Center.

Get the latest Salesforce Developer blog posts and podcast episodes via Slack or RSS.

Add to Slack Subscribe to RSS