How to query More than 50000 records from sObject

Question

i need to count total number of records in an sObject which can have around one million records. Will the code below work ? If no, how can this be done ? I dont need to use this in a vf page.

I will be using this COUNT() SOQL Query in a batch class which is scheduled to run at specific time.

AggregateResult results = database.query('SELECT COUNT(id) result FROM someSObject');    
System.debug('No of ids are: '+results.get('result'));

Answer 1

(comical but unsupported)

You can wedge your aggregate query inside of a FOR loop. This expands the ceiling beyond the 50,000 limit. Ultimately undocumented behavior, that probably shouldn't be relied upon.

Integer total = 0;    

for (AggregateResult result : [
    SELECT COUNT(Id)
    FROM SomeObject__c
]) total += (Integer)result.get('expr0');

System.assert(false, total); //big number

Ratan confirms that even a GROUP BY clause is not needed in the query. Neat!

Answer 2

With the Summer '18 release there is a change to how aggregate functions contribute towards the Query Rows limit.

Test code:

System.debug('Query Rows Before:' + Limits.getQueryRows());
Integer count = [Select Count() from Account];
System.debug('Count: ' + count);
System.debug('Query Rows After:' + Limits.getQueryRows());

Before (v42.0):

14:32:00.19 (20287760)|USER_DEBUG|1|DEBUG|Query Rows Before:0
14:32:00.19 (22108329)|SOQL_EXECUTE_BEGIN|2|Aggregations:0|SELECT COUNT() FROM Account
14:32:00.19 (26610451)|SOQL_EXECUTE_END|2|Rows:1416
14:32:00.19 (26721793)|USER_DEBUG|[3]|DEBUG|Count: 1416
14:32:00.19 (26771194)|USER_DEBUG|[4]|DEBUG|Query Rows After:1416

After (v43.0):

14:32:58.2 (3046168)|USER_DEBUG|1|DEBUG|Query Rows Before:0
14:32:58.2 (3902224)|SOQL_EXECUTE_BEGIN|2|Aggregations:0|SELECT COUNT() FROM Account
14:32:58.2 (6145693)|SOQL_EXECUTE_END|2|Rows:13
14:32:58.2 (6233887)|USER_DEBUG|[3]|DEBUG|Count: 13
14:32:58.2 (6300098)|USER_DEBUG|[4]|DEBUG|Query Rows After:1

Note how the aggregate query only counts for 1 query row with the Summer '18 release. I'll need to test this in a sandbox with more than 50,000 records to confirm, but I suspect this will give you the ability to count more than 50,000 records directly.

---

This corresponds to the idea Count the SOQL count() query as a single row query and is tentatively scheduled for Summer `18 (As per the slides from TDX).

Answer 3

You could use readOnly="true" in your <Apex:Page> tag.

See here for more information as there will be some limitations to doing this, for example:

While Visualforce pages that use read-only mode for the entire page can’t use data manipulation language (DML) operations, they can call getter, setter, and action methods which affect form and other user interface elements on the page, make additional read-only queries, and so on.

But this will relax the limit as it says:

Normally, queries for a single Visualforce page request may not retrieve more than 50,000 rows. In read-only mode, this limit is relaxed to allow querying up to 1 million rows.

Edit Seeing as you editted your question to say:

I dont need to use this in a vf page.

I will be using this COUNT() SOQL Query in a batch class which is scheduled to run at specific time.

There is also a @readonly annotation you could take a look at which:

allows you to perform unrestricted queries against the Force.com database.

Again, with some limitations:

All other limits still apply. It's important to note that this annotation, while removing the limit of the number of returned rows for a request, blocks you from performing the following operations within the request: DML operations, calls to System.schedule, calls to methods annotated with @future, and sending emails.

Hopefully this might give you some ideas on how to meet your requirement.

Answer 4

Use @ReadOnly to count

@ReadOnly only work with JS remoting. If you direct call the method using class instance then it wouldn't work

Here is the example(IN this example I have 97000 Task records.)

Controller

public class TestVFpage {
    @RemoteAction
    @readOnly
    public static list<Task> getTask(){
        return [SELECT Id FROM Task];
    }
}

VF page

<apex:page controller="TestVFpage">
   <div id="totalAccounts"></div>
   <script>
      Visualforce.remoting.Manager.invokeAction(
          '{!$RemoteAction.TestVFpage.getTask}',
              function(result){
                  alert('Total Records: '+result.length);
              }

          );
   </script>
</apex:page>

Alert Image

---

Another way

Use jquery with rest API

<apex:page>
   <input name="getList" class="btn" value="Get Account list" type="button" onclick="getSobjectList()" />
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
   <script>
      var $j = jQuery.noConflict();
      var sessionId = '{!$Api.Session_ID}';
      function getSobjectList() {
      $j.ajax({
      type: "GET",
      url: "/services/data/v35.0/query?q="+''+"{!URLENCODE('SELECT count(Id) totalTask FROM Task')}",
      headers: {"Authorization" : "OAuth " + sessionId},
      crossDomain : true,
      dataType: 'application/json',
      success: function (responseData) {
      console.log(responseData);
      },
      error: function (request, status, error) {
      console.log(request.responseText);
      }
      });
      }
   </script>
</apex:page>

check browser console