1

I keep reading conflicting answers about how many rows an SOQL query can return in an APEX method. Some say 200, some say 2000, some say 50,000, and some say 1 million (read only).

I am looking for an answer in regards to a Lightning Web Component that uses an AuraEnabled APEX method, i.e.:

myLWC.js

import myApexMethod from '@salesforce/apex/myApexController.myApexMethod'; 

export default class myLWC extends LightningElement {

  loadMyData() {
    return myApexMethod()
      .then(data => {
        // Do something with the data
      }
  }

}

myApexController.cls

public with sharing class myApexController {
  @AuraEnabled
  public static List<MyObject__c> myApexMethod() {
    // (1)
    return [SELECT Id, Relationship__r.Name FROM MyObject__c];

    // (2)
    Integer recordLimit = 2000;
    Integer offset = 0;
    String recordCountQuery = 'SELECT Count() FROM MyObject__c';
    String recordQuery 'SELECT Id, Relationship__r.Name FROM MyObject LIMIT :recordLimit OFFSET :offset'

    Integer recordCount = Database.countQuery(recordCountQuery);
    List allRecords = new List<MyObject__c>();
    while (allRecords.size() != recordCount) {
       List<MyObject__c> records = Database.query(recordQuery);
       allRecords.addAll(records);
       offset += recordLimit;
    }
    return records;

  }
}

As you can see in my APEX method, myApexMethod, I have written out two options:

  • Option 1 just simply makes one SOQL call and returns the results. However, I believe that this will be restricted to the SOQL governor limit (2,000???), therefore if I have over 2000 records it will only return the first 2000 (or however many SOQL queries are limited to.
  • Option 2 takes a different approach and first gets the total number of records, and then makes multiple queries (limited at 2000) to construct a list longer than 2000 items and then returns that list.

My questions are:

  • What is the limit of records an SOQL statement will return?
  • Is there a limit on the number of records the APEX method will return? (i.e. even if apex code constructed a list of, say, 5000 records, will the method only return 2000?) If so, what is the limit of records an APEX method will return, regardless of the number of records it's queries inside constructed together.
  • If there is a limit on the number of records the APEX method will return, assuming there are more records than an APEX method will return, how can you wait for all the records to be retrieved (i.e. call your apex method x amount of times and wait for all to return) before moving on in JS land? In other words, how can my loadMyData JS function load all records from a table that has more records than the apex method will return, before it returns?
3

Technically in a single execution context apex can query upto 50K rows.Note that it is per context limit.However in your scenario since you want to get these rows to front end you will see reduction in performance . I believe response size limit above 5MB starts causing issues in browser ability to handle them on client side (I don't remember whats the specific number.)

AuraEnabled method does not have a built in pagination .

I would say if you want to process the data , do all the processing on apex end and make sure the response that is returned returns less no of records as possible (within in limits of ability of browser to handle the response) .

Instead of client side paginating , prefer server side pagination so you can limit the rows of data returning.

  • For what it's worth, I've returned as many as 67k records, it all depends on how many fields you need to return. Oh course, the 50k limit is the hard limit, but it's possible to return more. – sfdcfox Mar 21 at 17:21
  • I would be curious why one would need to return so many records .Showing more than 1K records on the screen seems to be already too much (that too with 1K i am hoping one has client side filtering and sorting) . – Mohith Shrivastava Mar 21 at 17:23
  • 1
    If you're using infinite scrolling, loading small chunks seems the way to go, but if you want pagination up front, then you could return all records at once. The tradeoff is primarily navigation speed vs loading speed. – sfdcfox Mar 21 at 17:49
  • 1
    I've never been able to find a document stating a limit on the size of the response payload. The request payload is capped at 4MB. – David Reed Mar 21 at 17:51
  • @DavidReed Good point . I believe its browser ability to handle this than any limit on salesforce . Note if it was aura framework the aura:iteration starts to cause performance degradation with 1K rows . – Mohith Shrivastava Mar 21 at 18:01
2

You're confusing the OFFSET limit with the SoQL limit. While @Mohith correctly points out above that your SoQL limits are very high, I see you're doing an OFFSET in your query.

Regardless of how many rows you return, you cannot use an OFFSET value greater than 2000.

if you're trying to paginate then I recommend you leverage StandardSetController for this (see this page for a quick example). Using that, you can send your controller a page number and a page size and then return the appropriate records without having to worry about the OFFSET clause.

PS: StandardSetController offers much more than that (filtering by listviews, etc), so be sure to explore all the method to extract as much possible functionality out of it.

PPS: If you're in lightning, explore the <lightning:datatable> infinite loading capability to lazy-load more records when a user scrolls instead of loading them all at once.

  • 2
    +1 on this .Did not realize that there is an offset in query . – Mohith Shrivastava Mar 21 at 17:55
  • 1
    Every time I hear somebody talk about a limit of 2000 (such a weird number in SF world) I look for an offset. ;-) – Sebastian Kessel Mar 21 at 17:56
  • @SebastianKessel not being able to use an OFFSET value greater than 2000 makes zero sense. If your offset can't be greater than 2000, then you would only ever be able to load the first 2000 records... Is the StandardSetController available for use in Lightning Web Components? – BlondeSwan Mar 21 at 19:07
  • I am not sure why the limit is 2000, but we don't get a vote on it. ;-). StandardSetController is an apex class and will work on any apex class, even controllers for LWC components – Sebastian Kessel Mar 21 at 19:09
  • 1
    Although, I'm not surprised Salesforce put in some stupid restriction that limits what you can do. It's their specialty to make things complicated – BlondeSwan Mar 21 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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