Fastest way to count large numbers of records in APEX?

Question

I am migrating records to Salesforce.com, and therefore need to check total record counts frequently. Some objects have over 500,000 records.

The simple 'Count' functions max out at 50,000 records so I therefore created a quick generic Batchable class to help, which really works fine, and takes about 6 minutes to count the 500k records.

The question I have is whether there is a faster way of doing this? Salesforce knows how many batches would be required before execution, so in theory it should be possible to count the number of batches along with the count of records in the final batch for the total.

global class CountingExample implements Database.Batchable<sObject>, Database.Stateful {

    private Integer count = -1;
    private string objectName = '';

    global CountingExample(String newObjectName){
        objectName = newObjectName;
        count = 0;
   }

    global Database.QueryLocator start(Database.BatchableContext BC) {
        String query = '';

        query = 'SELECT ID FROM ' + objectName;

        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<sObject> scope) {

        count += scope.size();
    }

    global void finish(Database.BatchableContext BC) {

        try {
            // Record total count here
        } 
        catch(DmlException e)
        {
             System.debug('An unexpected error has occurred: ' + e.getMessage());
        }
    }

}

You can get much larger counts in Visualforce. Would that help? — sfdcfox, Jun 17 '16 at 22:24
I thought about doing that. I believe you can set the VisualForce query to 'read-only' to get a higher count? Would that allow me to count up to 1 million records in one go? — , Jun 17 '16 at 22:27
Yes, you can use the ReadOnly annotation on a remote method to grab up to a million, or you can set readOnly on the page, or you could even use the Ajax Toolkit to query up to 50,000,000 for the cost of an API call. — sfdcfox, Jun 17 '16 at 22:34
Great, and would that be faster? (i.e. less than the current 6 minutes I'm getting for 500k records) — , Jun 17 '16 at 22:45
Should be achievable in seconds. Try it and let us know! You can answer your own question with data to back up these suggestions. — Adrian Larson, Jun 17 '16 at 23:02
use the rest or soap APIs which have no limit, and do a select count() from account — superfell, Jun 17 '16 at 23:59
Yes, I will try this out with VisualForce and/or the API and report back :) — , Jun 19 '16 at 02:22

user3209853 · Answer 1 · 2016-06-18T12:39:20.167

5

In workbench, use query as Select count() from object. It works miraculously and gives exact count quickly. That 50,000 record limitations isn't there if we query via API.

It might get timeout the first time, but if you do that for 5-6 times simultaneously in different tabs it will cache and show you data.

edited Jun 18 '16 at 12:39

answered Jun 18 '16 at 12:33

user3209853

81
5

That does indeed work very quickly (first try took 15 seconds for 500k records). The problem with Workbench is that I want to get the value from about 25 different objects, which would take a while using this method. (i.e. select the object, paste in the query, and execute each time). It is a great solution, but doesn't strictly answer the question about doing this in Apex. – Jun 19 '16 at 02:21
Actually . I created a vf page that does this query job using Javascript . if it timeouts it automatically retries till success. So I could easily cache multiple objects in one go using this. Apex sync has 10sec limit nd async takes he'll lot of time. So using js nd sf rest api I achieved this. – user3209853 Jun 19 '16 at 04:22

score 2 · Answer 2 · edited Apr 13 '17 at 12:54

Firstly, consider the sage advice to use the SOAP API to run a SOQL query using count(). As mentioned, the SOAP API doesn't have a direct limit on the number of records returned. See SOAP API Call Limits.

Out of curiosity, it would interesting to see how a Select Id from Account with a Batch Size of 1 performs versus a Select count() from Account. With the former you could read the total number of records from the QueryResult.size. I'd expect the count() to be much faster, but it would be worth a quick check. Unfortunately I don't have an org where I can (professionally) randomly query large volumes of records.

I've found historical records where the SOQL approach may still fail - SOQL Count() query fails with OPERATION_TOO_LARGE. Why?. I think the short answer from that post is that you will need the "View All Data" permission to avoid problems.

There is another path, but it is fraught with peril. And by peril, I mean screen scraping the Storage Usage Current Data Storage Usage data.

This report gives you a breakdown to Record Count by Record Type. If you drill into the html source you will see that Salesforce were even so kind as to provide the key prefix for each class on the th.

Will this be the fastest way to get the total count for a single object? Probably not. You mentioned you wanted 25 different records. At some point it may be faster to parse the Admin page that make X number of SOAP API calls.

Another downside of this approach is that the user will need sufficient permissions to access this page. (“Manage Internal Users” and “Manage Users” permissions).

See also: Can we query the info from “Data Management -> Storage Usage” through SOAP API?

Fastest way to count large numbers of records in APEX?

2 Answers2