LimitException: Too many query rows: 50001 from with count() aggregate function

Question

I have a Visualforce page where I'd like to display a count of the number of records in a particular sObject table.

In the Visualforce page I'd have something fairly simple, like:

<p>Client Account Count: {!ClientAccountCount}</p>

Then in the controller:

// Return the number of clients
public integer getClientAccountCount() {
    return [Select count() from Account where SomeCustomField__c = 'Client' limit 50000];
}

I thought with the limit clause in the SOQL I'd be fine as it would only every return a maximum of 50,000. However, in practice I still get this exception in the production org:

09:29:12:179 SOQL_EXECUTE_BEGIN [108]|Aggregations:0|select count() from Account where SomeCustomField__c = 'Client' limit 50000

09:29:12:331 EXCEPTION_THROWN [108]|System.LimitException: Too many query rows: 50001

Is there a safe way to perform this query that won't result in an exception that I can't catch?

Oddly, if I try the following as anonymous apex in production it works just fine and returns 50,000.

integer count = [select count() from Account where SomeCustomField__c = 'Client' limit 50000];

Perhaps the issue is the cumulative number of query rows across all operations that is causing the problem and I need to check the Limits in code before running the query?

---

There is a similar post on the Force.com discussion boards - Too many query rows on COUNT(*) function. I can't set the VF page to read only to increase the query row limit.

Solution

Doh! I'm pretty sure I need to check the cumulative number of records retrieved by SOQL queries for the request. So while one SOQL query could get up to 50,000 records two can't do 50,000 each.

Guess I can use Limits.getQueryRows() and Limits.getLimitQueryRows() to disable the SOQL querys if required.

---

I've changed the way the getClientAccountCount() method works. I figure it is only every going to be able to give an indication of how many rows there are as the aggregate functions are being limited.

// Return the number of ad book clients
public string getClientAccountCount() {
    System.debug(LoggingLevel.Debug, 'getClientAccountCount() - Current Query Rows: ' + Limits.getQueryRows() + '/' + Limits.getLimitQueryRows());
    integer recordCount = [Select count() from Account where SomeCustomField__c = 'Client' limit 1001];
    if(recordCount == 1001) { return '1000+'; }
    return string.valueOf(recordCount);
}

---

This idea - Count the SOQL count() query as a single row query seems worth promoting.

Comments

[Kirill Yunussov]

Yea I agree. Just promoted that idea also. So, what's the way to find out the number of records in a SObject table using a SOQL query?

[Daniel Ballinger]

@KirillYunussov. You could create a batch job that iterates through all the records in a table and accumulates the total record count. It wouldn't be very efficient or fast, but you would ultimately get the total record count (less any changes that occured as the process was running).