12

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'));
  • Why don't you use the Roll up summary field for this problem? Any issues with roll up summary? – Subhash Dec 10 '15 at 10:08
  • you can't get roll up summary on same object. object should be a child of a parent object. – Himanshu Dec 10 '15 at 10:16
  • 1
    Since you are in a batch, simply use stateful and use a [Select ID From Account] in your start method query. then increment a count in the execute method. In the finish method it will be the final result. Not optimal but works and will not break limits – Eric Dec 10 '15 at 12:56
25

(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!

  • 3
    Wow, great catch biggy! – Sergej Utko Dec 10 '15 at 10:46
  • 5
    You can use only count(Id) in query so you don't have to use group by AggregateResult lst: [SELECT count(Id) total FROM someSObject] – Ratan Paul Dec 10 '15 at 10:49
  • @bigassforce. This is aweomse. It would have been great if this was document and confirmed by salesforce. I was able to count 40 lakhs of record. Is there any max limit on this? – Kiran Machhewar Dec 10 '15 at 11:40
  • @KiranMachhewar - You should NOT do this as it is undocumented and will likely be patched (Since non documented features get removed quicker than bugs get fixed lol)..Although a neat exploit it could break without warning causing you lots of issue in the future – Eric Dec 10 '15 at 12:54
  • @Eric Yes, Agreed this should not be used. But yes for the time being at least good to go with its use in anonymous block. :) – Kiran Machhewar Dec 10 '15 at 14:41
5

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).

  • Woah that's huge if true. – Adrian Larson Apr 17 '18 at 21:37
  • Maybe I should prefix it with a caveat that it first needs to be confirmed against an org with a sufficient number of records. It's hard to test in a developer edition org. – Daniel Ballinger Apr 17 '18 at 21:45
  • Yeah I think that context is clear. – Adrian Larson Apr 17 '18 at 21:46
  • I don't see any Summer '18 releases before May 4. – Adrian Larson Apr 17 '18 at 21:58
  • Yes. Will need to revisit it once it comes out of pre-release. Or do you think it is inappropriate to post based on what can be done in pre-release orgs? No one care really use it yet and things might change by the time it reaches sandboxes and production orgs. – Daniel Ballinger Apr 17 '18 at 22:03
4

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.

  • 1
    if i am inside a class which implements batchable and this batch class is scheduled to run at specific times to delete some records. I think i will not be able to use @ReadOnly annotation in this case to find record count. – Walker Dec 10 '15 at 11:00
3

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

enter image description here


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

enter image description here

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.