37

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 ?

  • Do you care about the order of records? – endrju Dec 19 '13 at 15:34
  • 1
    Yes, if the records are not sorted, the pagination will not work. – Martin Borthiry Dec 19 '13 at 15:36
  • I really doubt if this can be accomplished in general on force.com. Can you explain your use case in more details? I bet the pagination won't be used by humans - who would be patient enough to review more than a few hundreds of records? IF the ordered column is unique, you could use the last seen value as a token for the WHERE clause. You could also replicate database to another SQL server, or... simply change your design. It just depends on the use case. – endrju Dec 19 '13 at 15:53
  • Are you using .NET to access the API? I figured out a way to efficiently do random access paging via the SOAP API, but it is bundled as part of a product that my company sells. I'll ask around to see if I'm allowed to share the technique publicly. – Daniel Ballinger Dec 19 '13 at 18:15
29

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.

  • 1
    Excellent approach the findOffset method. – Martin Borthiry Dec 19 '13 at 19:08
  • 3
    @sfdcfox using WHERE CreatedDate > "Last-Returned-Created-Date", what would happen if there was more than one object created at that exact date? The first object was #2000 in batch 1. For batch 2, this means that using this where criteria would skip the remaining items created at that exact time, right? – Yaakov Ellis Jan 5 '14 at 14:08
  • 1
    @YaakovEllis For the former, you need to check ID values for possible duplicates; consider sorting/filtering by ID if you want to avoid duplicates precisely. SOAP API can only return 2000 rows at once; Apex Code can return 50000 rows at once. – sfdcfox Jan 5 '14 at 17:03
  • 3
    This looks like the best workaround there is, which just proves how massively deficient Salesforce is. – Paul Taylor Oct 25 '16 at 13:04
  • 1
    @PranayJaiswal Yes, so long as you remember to order by Id to make sure you don't miss any records. – sfdcfox Jan 21 at 20:09
1

You can use custom pagination instead of offset because offset have 2000 records limitation.

    public class ContactPaginationController {

public List <Contact> AllContacts = new list<Contact>();

public List<Contact> ContactsToShow{get;set;}

Integer counter = 0;
Integer limitSize = 100;
Integer totalSize =0; 


public ContactPaginationController(){

     ContactsToShow = new list<Contact>();

    AllContacts = [select id , firstname,lastname,email from contact];

    totalSize = AllContacts.size();


    if((counter+limitSize) <= totalSize){
        for(Integer i=0;i<limitSize;i++){
            ContactsToShow.add(AllContacts.get(i));
        }
    }else{
        for(Integer i=0;i<totalSize;i++){
            ContactsToShow.add(AllContacts.get(i));
        }
    }

}



public void beginning(){

    ContactsToShow.clear();
    counter=0;
    if((counter + limitSize) <= totalSize){

        for(Integer i=0;i<limitSize;i++){
            ContactsToShow.add(AllContacts.get(i));
        }   

    } else{

        for(Integer i=0;i<totalSize;i++){
            ContactsToShow.add(AllContacts.get(i));
        }       

    }

}

public void next(){

    ContactsToShow.clear();
    counter=counter+limitSize;

    if((counter+limitSize) <= totalSize){
        for(Integer i=counter-1;i<(counter+limitSize);i++){
            ContactsToShow.add(AllContacts.get(i));
        }
    } else{
        for(Integer i=counter;i<totalSize;i++){
            ContactsToShow.add(AllContacts.get(i));
        }
    }
}

public void previous(){

    ContactsToShow.clear();

    counter=counter-limitSize;       

    for(Integer i=counter;i<(counter+limitSize); i++){
        ContactsToShow.add(AllContacts.get(i));
    }
}

public void last (){

    ContactsToShow.clear();

    if(math.mod(totalSize , limitSize) == 0){
        counter = limitSize * ((totalSize/limitSize)-1);
    } else if (math.mod(totalSize , limitSize) != 0){
        counter = limitSize * ((totalSize/limitSize));
    }

    for(Integer i=counter-1;i<totalSize-1;i++){
            ContactsToShow.add(AllContacts.get(i));
    }

}

public Boolean getDisableNext(){

    if((counter + limitSize) >= totalSize )
        return true ;
    else
        return false ;
}

public Boolean getDisablePrevious(){

    if(counter == 0)
        return true ;
    else
        return false ;
} 
}
-1

Here is a vf page and controller based on Sergej's code. It paginates Leads but can easily be changed to query a different object:

    <apex:page Controller="PaginationExample2Controller">

    <apex:form id="form">
 <apex:pageBlock >
 <apex:pageBlockTable value="{!showRows}" var="rw">
 <apex:column headerValue="Name">
 <apex:outputField value="{!rw.Name}"/>
 </apex:column>
 <apex:column headerValue="Email">
 <apex:outputField value="{!rw.Email}"/>
</apex:column>
<apex:column headerValue="Company">
<apex:outputField value="{!rw.Company}"/>
</apex:column>
</apex:pageBlockTable>
<apex:outputLabel >Total no of recs:{!totalRecs}</apex:outputLabel>

<div align="center">
 <apex:commandButton value="First Page" action="{!firstBtn}" disabled="{!disablePrv}" reRender="form"/>
 <apex:commandButton value="PreviousPage" action="{!prvBtn}" disabled="{!disablePrv}" reRender="form"/>
 <apex:commandButton value="NextPage" action="{!nxtBtn}" disabled="{!disableNxt}"  reRender="form"/>
 <apex:commandButton value="LastPage" action="{!lstBtn}" disabled="{!disableNxt}" reRender="form"/>
 </div>
 Display records per page <apex:selectList value="{!numRowsToShow}" size="1" >
   <apex:selectOption itemLabel="1" itemValue="1" ></apex:selectOption>
   <apex:selectOption itemLabel="2" itemValue="2"></apex:selectOption>
   <apex:selectOption itemLabel="5" itemValue="5"></apex:selectOption>
   <apex:selectOption itemLabel="10" itemValue="10"></apex:selectOption>
   <apex:selectOption itemLabel="20" itemValue="20"></apex:selectOption>
   <apex:selectOption itemLabel="100" itemValue="100"></apex:selectOption>
   <apex:selectOption itemLabel="1000" itemValue="1000"></apex:selectOption>
   <apex:actionSupport event="onchange" reRender="form" action="{!updatePage}"/>
  </apex:selectList><br/>
 </apex:pageBlock>
 </apex:form>   

</apex:page> 


    public with sharing class PaginationExample2Controller {

    public List<Lead> allRows = new List<Lead>();
    public List<Lead> showRows {get; set;}

    public Integer totalRecs  { get; set; }
    public Integer numRowsToShow {get; set;}

    Integer counter;

    //Constructor
    public PaginationExample2Controller()
    { 
        numRowsToShow= 10;
        counter = 0;
        showRows = new List<Lead>();
        allRows = [SELECT Name,Email,Company FROM Lead];
        totalRecs = allRows.size();

        if((counter+numRowsToShow) <= totalRecs){
            for(Integer i=0;i<numRowsToShow;i++){
                showRows.add(allRows.get(i));
            }
        }else{
            for(Integer i=0;i<totalRecs;i++){
                showRows.add(allRows.get(i));
            }
        }
    }//PaginationExample2Controller




    public void updatePage() {
        firstBtn();
    }
    public PageReference firstBtn() {
        counter=0;
        showRows.clear();
        if((counter + numRowsToShow) <= totalRecs){

            for(Integer i=0;i<numRowsToShow;i++){
                showRows.add(allRows.get(i));
            }   

        } else{

            for(Integer i=0;i<totalRecs;i++){
                showRows.add(allRows.get(i));
            }       

        }

        return null;
    }//firstBtn

    public PageReference prvBtn() {

        showRows.clear();
        counter=counter-numRowsToShow;       

        for(Integer i=counter;i<(counter+numRowsToShow); i++){
            showRows.add(allRows.get(i));
        }
        return null;
    }//prvBtn

    public PageReference nxtBtn() {
        showRows.clear();
        counter=counter+numRowsToShow;

        if((counter+numRowsToShow) <= totalRecs){
            for(Integer i=counter-1;i<(counter+numRowsToShow);i++){
                showRows.add(allRows.get(i));
            }
        } else{
            for(Integer i=counter;i<totalRecs;i++){
                showRows.add(allRows.get(i));
            }
        }


        return null;
    }//nxtBtn

    public PageReference lstBtn() {
        showRows.clear();       
        if(math.mod(totalRecs , numRowsToShow) == 0){
            counter = numRowsToShow * ((totalRecs/numRowsToShow)-1);
        } else if (math.mod(totalRecs , numRowsToShow) != 0){
            counter = numRowsToShow * ((totalRecs/numRowsToShow));
        }

        for(Integer i=counter-1;i<totalRecs-1;i++){
            showRows.add(allRows.get(i));
        }

        return null;
    }//lstBtn


    public Boolean getDisableNxt() {
        if((counter + numRowsToShow) > totalRecs)
            return true;
        else
            return false;

    }
    public Boolean getDisablePrv() {
        if(counter == 0)
            return true;
        else
            return false;

    }


}//PaginationExampleController
  • This is not answering the original question which is about SOAP integration. – Martin Borthiry Feb 17 at 19:31
  • That's a bit mean. The post was about the limitation of using offset. Sergej showed a way to overcome this limitation which I enhanced. – Dave Roberts Feb 19 at 15:15
  • @Martin Borthiry . Can I have my reputation point back, please? – Dave Roberts Nov 6 at 16:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.