[SalesForce] Workaround for Offset 2000 limit on SOQL Query

Problem

I'm trying to make a standard query pagination. I'm using SOAP API.

The logic is classic: LIMIT recordsByPage OFFSET (recordsByPage*pageNumber)+1,

It works fine, however, I'm facing the 2000 limit of OFFSET:

The maximum offset is 2,000 rows. Requesting an offset greater than
2,000 will result in a NUMBER_OUTSIDE_VALID_RANGE error.

I advice of SF to solve this limit is to create a QueryLocator and use the queryMore.

We have more than 100k records, and the recordsByPage ~50. If I need to go record #50000, It will take 1000 queryMore.
Instead of that, if I use the max (2000) batch size in the queryLocator then I will require 25 queryMore.

From my view this approach is inefficient.

Question

What is the most efficient (performance + less api calls) way to make a query pagination ?

Best Answer

QueryMore is the appropriate means of accomplishing this if you're getting data all at once. For pagination, such as in a website or portal, sort by some value then use filters. I would recommend a field with a high cardinality (e.g. many unique values) because it makes pagination that much easier.

I've posted this answer elsewhere, but I'll post it here, too. Let's say you're sorting by CreateDate or ID. Your first query would look like this:

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

At this point, you've got 2000 records to work with. Take the 2000th ID, and add it to your next query:

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate > "Last-Returned-Created-Date" ORDER BY CreatedDate LIMIT 2000

At this point, you'll get your next 2000 records. You can rinse and repeat as necessary. If you want to go back a page, just reverse the order (but you'll also have to reverse the results):

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate < "First-Returned-Created-Date" ORDER BY CreatedDate DESC LIMIT 2000

For better performance, you may just as well consider client-side caching with a periodic refresh of the data using "getUpdated()" and "getDeleted()" (SOAP API replication).

In any case, you can't just "go" to the 50,000th record initially; you have to step your way there. Caching would provide the biggest benefit in this regards and should be considered if you have large data sets.

Alternatively, you could also write a custom web service to quickly step through records using the method described above:

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

Since Apex Code has a limit of 50,000 records, you can skip 50,000 rows at a time using this technique, bringing your total efficiency up to 1 API call per 50,000 rows, plus one for the final query that gives you the results for the page.

Related Solutions

[SalesForce] How to query more than 50000 records in apex and bind it in vf page in jqgrid and paging

I've done jqGrid before in Apex with @RemoteActions and it's definitely the way to go. I think with Ajax toolkit and SOAP, you'll have too much XML bloat compared to JSON for large sets of records.

As for loading all 78000 records, I would say you should stick to the limits of loading 2000 records at a time and than breaking those into further subsets of 100 records / page on the client side.

To pagination with large record sets I would recommend using the standard set controller for pagination. Just make sure to include an order clause to keep the records returning in the same order.

Than load the next 2000 records in each subsequent call. And reduce the number of records you return from the server to speed things up in between 'set loads' as you get to the end of each set and need more pages.

You can also count the number of records and return it in a complex object from the @RemoteAction to keep your pagination accurate:

public class returnType{
  List<sObject> objects {get; set;}
  Integer totalRecords {get; set;}
  Integer nextPage {get; set;}
  Integer lastPage {get; set;}
}

UPDATE

Ok using the SSC to maintain pagination (at least this is the way I would do it with jqGrid:

@RemoteAction
global static returnType pagination(Integer pageNumber){
   ApexPages.StandardSetController ssc = new ApexPages.StandardSetController(Database.getQueryLocator([SELECT Id FROM sObject ORDER BY Id LIMIT 50000]);
   ssc.setPageNumber(pageNumber);
   ssc.Pagesize(2000);

   returnType retVal = new returnType();
   retVal.objects = ssc.getRecords();
   retVal.nextPage = pageNumber + 1;
   retVal.previousPage = pageNumber - 1;

   return retVal;
}

I don't have my original code in front of me, but generally, the SSC should be re-instantiated with each call. As for getting past 50k records, and to your total set, the query locator should continue to the end of the set reguardless of size taking the larger records towards the end of the set as you get to the end of [0..n-1] pages.

In practice I would use an ORDER BY clause to ensure your records are consistently returned in the same order to ensure that your getting all results.

[SalesForce] Does the “Total number of records retrieved by SOQL queries” governor limit apply to sub-selects

According to my knowledge Limit of 50k objects applies to each object query returns. So each element is counted as 1- no matter from query or nested query. Although list you used as filters (semi-join)are not returning any data and are not count against this limit.

There is no limit on the number of records matched in a subquery. 
Standard SOQL query limits apply to the main query.