Workaround for Offset 2000 limit on SOQL Query

Question:

I am implementing standard query pagination using the Salesforce SOAP API. The logic I use involves the following structure:

LIMIT recordsByPage OFFSET (recordsByPage * pageNumber) + 1

While this works well for smaller datasets, I encounter the 2000 OFFSET limit when dealing with larger datasets. The error encountered is:

NUMBER_OUTSIDE_VALID_RANGE.

Salesforce recommends using a QueryLocator with queryMore to fetch large datasets. However, this approach appears inefficient for navigating records deep into the dataset. For instance, to fetch record #50,000 with a page size of 50, I would need 1,000 calls to queryMore. Even increasing the batch size to the maximum of 2,000 would still require 25 calls.

I am looking for an alternative that balances performance with minimizing API calls. What is the most efficient approach for query pagination under these conditions?

Answer:

Using queryMore is the standard approach for handling large datasets when fetching all data at once. However, for efficient pagination, especially in use cases like websites or portals, alternative strategies can be employed.

Unlock your Salesforce potential enroll now for a demo of our expert training in Pune and Salesforce Training start your journey to success!!!

One effective method involves sorting the dataset by a high-cardinality field (e.g., CreatedDate or Id) and using filters to retrieve specific records. Below are examples demonstrating this approach:

Step 1: Fetch the first page

To fetch the first page of records, use a query with a LIMIT and an ORDER BY clause:

SELECT Id, Name, CreatedDate FROM Account ORDER BY CreatedDate LIMIT 2000

This query retrieves data from the Account object in Salesforce. Here’s a detailed breakdown of its components:

1. SELECT Clause: SELECT Id, Name, CreatedDate specifies the fields to be retrieved from the Account object:
   • Id: The unique identifier for the account record.
   • Name: The name of the account.
   • CreatedDate: The date and time when the account record was created.
2. FROM Clause:
   • FROM Account indicates that the data is being queried from the Account object in Salesforce.
3. ORDER BY Clause:
   • ORDER BY CreatedDate sorts the results based on the CreatedDate field in ascending order (earliest records first).
   • This is particularly useful for pagination or retrieving records in a chronological sequence.
4. LIMIT Clause:
   • LIMIT 2000 restricts the number of records returned by the query to a maximum of 2000.
   • This is important in Salesforce as queries have limits on the number of rows they can return, and the LIMIT clause ensures the query stays efficient and within governor limits.

Purpose of the Query

This query is often used to:

• Fetch the most recent 2000 records created in the system (if combined with ORDER BY CreatedDate DESC).
• Implement pagination by retrieving a subset of records from a large dataset.
• Avoid hitting the governor limit on the maximum number of rows returned in a single query (50,000 rows). The LIMIT clause ensures only the specified number of records is fetched.

Example Use Case

Suppose you are building a web application to display account records in pages. This query can be used to retrieve the first 2000 records in the Account table. By tracking the CreatedDate of the last record in this batch, you can construct the next query to fetch the subsequent set of records, allowing for efficient and scalable pagination.

Step 2: Fetch the next page

After retrieving the first page, note the CreatedDate (or the chosen field) of the last record. Use this value as a filter to fetch the next page:

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate > :lastReturnedCreatedDate ORDER BY CreatedDate LIMIT 2000

The provided SOQL query is used to fetch records from the Account object in Salesforce. Below is a detailed explanation of each part of the query:

Query:

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate > :lastReturnedCreatedDate ORDER BY CreatedDate LIMIT 2000

Explanation:

1. SELECT Id, Name, CreatedDate
   • This specifies the fields to retrieve from the Account object.
     • Id: The unique identifier for each Account record.
     • Name: The standard field that represents the name of the account.
     • CreatedDate: The date and time when the record was created.
2. FROM Account
   This specifies the object from which the data is being queried. In this case, it is the Account object.
3. WHERE CreatedDate > :lastReturnedCreatedDate
   • This applies a filter to the query to include only records where the CreatedDate is greater than the value of :lastReturnedCreatedDate.
   • :lastReturnedCreatedDate is a bind variable in Apex. It is dynamically passed from the Apex code to the query at runtime, representing the CreatedDate of the last record retrieved in the previous query.
   • This condition ensures that the query fetches the next set of records after the previously retrieved batch, supporting efficient pagination.
4. ORDER BY CreatedDate
   • This sorts the records in ascending order based on the CreatedDate field.
   • Sorting by CreatedDate ensures that records are retrieved in a consistent and predictable order.
5. LIMIT 2000
   • This restricts the number of records returned by the query to a maximum of 2000.
   • Limiting the results helps avoid exceeding governor limits in Salesforce and adheres to pagination logic.

How it works in pagination:

• This query is typically used in scenarios where records are retrieved in batches for paginated navigation.
• After fetching the initial batch (e.g., the first 2000 records), the CreatedDate of the last record is stored as :lastReturnedCreatedDate.
• The next query uses this value to fetch the subsequent batch of records where CreatedDate is greater than the previously retrieved value.
• This eliminates the need for an OFFSET and avoids its associated limitations.

Example in Apex:

// Initial query to get the first batch
List<Account> accounts = [SELECT Id, Name, CreatedDate FROM Account ORDER BY CreatedDate LIMIT 2000];

// Get the CreatedDate of the last record in the batch
Datetime lastReturnedCreatedDate = accounts.isEmpty() ? null : accounts[accounts.size() - 1].CreatedDate;

// Use the lastCreatedDate in the next query for pagination
if (lastReturnedCreatedDate != null) {
    List<Account> nextBatch = [SELECT Id, Name, CreatedDate 
                               FROM Account 
                               WHERE CreatedDate > :lastReturnedCreatedDate 
                               ORDER BY CreatedDate LIMIT 2000];
}

This approach ensures efficient data retrieval for pagination while respecting Salesforce governor limits.

Step 3: Fetch the previous page

To navigate back to the previous page, reverse the ORDER BY clause and results:

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate < :firstReturnedCreatedDate ORDER BY CreatedDate DESC LIMIT 2000

This approach avoids hitting the OFFSET limit and allows efficient navigation through large datasets.

Enhancements for Performance:

For better performance and reduced API calls, consider implementing client-side caching. Periodically refresh the cached data using getUpdated() and getDeleted() from the SOAP API, which minimizes the need to repeatedly query the same records.

Alternative Solution:

You can also create a custom Apex web service to step through large datasets efficiently. The following example demonstrates how to skip large numbers of rows:

global webservice Id findOffset(String query) {
    SObject[] results = Database.query(query);
    return results[results.size() - 1].Id;
}

Here’s an explanation of the provided Apex code:

Code Breakdown:

global webservice Id findOffset(String query) {
    SObject[] results = Database.query(query);
    return results[results.size() - 1].Id;
}

1. Method Declaration

global webservice Id findOffset(String query)

• global: This keyword makes the method accessible across different namespaces, such as from external systems or packages. It is necessary for exposing the method as a web service.
• webservice: This keyword makes the method callable via SOAP-based API requests. This allows external systems to invoke the method.
• Id: The method’s return type, which is the Salesforce Id of a record.
• findOffset(String query): The method accepts a single parameter:
  • query: A dynamic SOQL query provided as a string. This query specifies the dataset to be queried.

2. Query Execution

SObject[] results = Database.query(query);

• Database.query(query): Executes the dynamic SOQL query passed as the input parameter.
• Dynamic SOQL: Allows the method to process different queries based on the input, making it versatile.
• SObject[] results: Stores the results of the query in a list of SObject. The SObject type is generic and can hold any Salesforce object, such as Account, Contact, etc.

3. Accessing the Last Record

return results[results.size() - 1].Id;

• results.size(): Returns the total number of records retrieved by the query.
• results.size() - 1: Index of the last record in the list. Lists in Apex are zero-based, meaning the first element is at index 0, and the last element is at index size() - 1.
• results[results.size() - 1]: Retrieves the last SObject from the list.
• .Id: Extracts the Id field from the last SObject. In Salesforce, every record has a unique Id.

4. Returning the Result

The method returns the Id of the last record from the query result.

Use Case

This method is useful when implementing custom pagination in large datasets. By querying a batch of records and returning the Id of the last record, you can determine the offset for the next batch of records efficiently. For instance:

1. Query the first 50,000 rows (using the findOffset method).
2. Use the returned Id as a starting point for the next query, effectively bypassing Salesforce’s 2,000 OFFSET limit.

This approach ensures you navigate large datasets efficiently while minimizing API calls and avoiding the governor limits.
Using this approach, you can skip up to 50,000 rows at a time due to the Apex Code governor limits. This technique reduces the number of API calls required to navigate large datasets, providing a significant performance boost.

By combining these techniques—sorting, filtering, caching, and custom Apex services—you can implement a highly efficient pagination mechanism that overcomes the OFFSET limitation in SOQL queries.

Unlock your potential with Salesforce training in Pune at CRS Info Solutions. Our expert-led courses cover Admin, Developer, and AI modules, offering real-world, hands-on experience. Learn through live projects, personalized mentorship, and thorough interview preparation. We ensure you’re job-ready with detailed class notes and practical skills. Enroll for our Salesforce training in Pune and kickstart your Salesforce career today!

Summing Up

The 2000 OFFSET limit in SOQL queries presents a significant challenge when implementing pagination for large datasets. While Salesforce recommends using queryMore with a QueryLocator to navigate datasets, this approach can be inefficient for deep pagination due to the high number of API calls required. An effective alternative involves sorting the dataset by a high-cardinality field, such as CreatedDate or Id, and using filters in subsequent queries to retrieve specific records. This method bypasses the OFFSET limitation and allows for efficient forward and backward navigation through data, especially when combined with client-side caching or periodic data refreshes.

For advanced scenarios, a custom Apex web service can enhance efficiency by leveraging the ability to dynamically query and return the last record’s Id. This technique enables skipping up to 50,000 rows at a time, significantly reducing the number of API calls needed. By combining these strategies—dynamic filtering, caching, and custom Apex logic—developers can build robust pagination mechanisms that handle large datasets while maintaining optimal performance and minimizing resource usage.