+ Start a Discussion
Vaclav LukasekVaclav Lukasek 

Scrash on counting objects over 50000

system.debug([Select COUNT() from XXX__c]);

will raise:
FATAL_ERROR|System.LimitException: Too many query rows: 50001
David ZhuDavid Zhu
Your SOQL returning result hits governor limit.
Vaclav LukasekVaclav Lukasek
Ok, how to get count of object if have move then 50000 of them
Cory CowgillCory Cowgill
To give some more contect to David's answer.

Force.com is a multi-tenant platform, which means that all computing resources are shared across a mutiple tenants. The common analogy is an apartment or condo building. Each tenant has a room, but everyone shares the same infrastructure (plumbing, electrical, landscape, etc). In this case it means the CPU processing time is divided amongst the tenants.

To ensure that one tenant does not consume all the resource and degrade performance for the rest of the tenants, Force.com has a concept called "Governor Limits". These limits ensure that one client does not hog all the resources.

Database queries are some of the most time intensive operations an application can make. Therefore to ensure SFDC is able to transact billions of transaction a day they put the following limits on SOQL queries:

1. No More than 50K results in a resultset.
2. You can't do JOINS like you can in SOQL. You can do a subset of JOIN which SOQL calls Parent and Child Queries.
3. You can only make 100 SOQL queries in a Trigger transaction, 200 in an Batch Transaction.

I hope that helps give more color to David's answer above. All the limits are listed in David's response. 

Vaclav LukasekVaclav Lukasek
I undestand of intensivity of DB operation, and thats reason why exist indexes
function using agregation are not slow if use good concept and each db engine will not consume too much time to get SELECT COUNT() of something , but understand that conditions in this case can be problem
But still how can i get count of all objects if their count is over 50000
Cory CowgillCory Cowgill
It not just indexes. SFDC does not operate the same as a SQL database. Simply applying SQL logic will fail you on SFDC platform.

Again, SFDC is multitenant platform (https://developer.salesforce.com/page/Multi_Tenant_Architecture). You can still have indexes and still not scale with Indexes. It will still perform a Full Table Scan even with Indexes if they are not selective as defiend by the Force.com Optimizer. Please see this article if you need to understand how indexes work on the Force.com platform. They do not work the same as a SQL DB. database. https://help.salesforce.com/help/pdfs/en/salesforce_query_search_optimization_developer_cheatsheet.pdf

In short you cannot do an Aggregate SOQL like you want to do above on the platform. You need to do selective SOQL queries to filter you dataset to work within the confines of the platform in Apex.


Hope that material helps you.

Cory CowgillCory Cowgill
One thing I left out - you can look at using the @ReadOnly annotation. That will give you up to 1 million records:


Cory CowgillCory Cowgill
And you can also use Batch Apex to process over 50 million records asynchronously. If you use case falls outside those patterns you may need to look at porting the data externally and pumping in the operational data into SFDC. 
Only better way to do it is using VF page but this is also limited to 1 million record. If you have more that 1million record and if you just need a count then use https://workbench.developerforce.com or else use apex data loader export.
Visualforce page Code 
<apex:page controller="MyCountEx" showheader="false" sidebar="false" readOnly="True"> 

Controller Code 

public class MyCountEx{ 

public Integer myCount {get{ return countMethod();} } 

public static Integer countMethod() { 
Integer var = 0; 
var= [select count() from account]; 
return var; 
public boolean isValidRecordCount(){

    if(Limits.getQueries() > 50000){
        return false;
        return true;

Use this method before every SOQL so that can know query limit before use the other SOQL Call.
Vaclav LukasekVaclav Lukasek
So only way, hot to get count of for example contacts, if we have over 50000 records, is callout on Apex page with readonly flag? It looks like strange, but thanks for sample of workaround