[SalesForce] Unable to perform a COUNT() query on object with many million records

I have a custom object with around 25.000.000 records and need to perform a single count query to find those records where a field has a certain value.

I could get none of the following queries to run due to limitations. I know all of them but how should one do it then?!

SELECT Count() 
FROM LargeObject__c
WHERE txt_State__c = 'Running'

or

SELECT txt_State__c, Count(Id) 
FROM LargeObject__c
GROUP BY txt_State__c

I tested this in all available tools:

  • the new Dev Console query editor
  • Anonymous code query
  • SoqlXplorer (uses bulk API)

but they all time out or throw limit errors.

Best Answer

I think that the issue is the where clause which is causing the problem. For example, I just ran

SELECT Count() 
FROM LargeObject__c

and got the result back (5million) in <10 seconds. When I add on a WHERE clause

SELECT Count() 
FROM LargeObject__c
WHERE somefield > 1   //somefield is not an indexed field

it increased the time taken to 280 seconds. You could try raising a case with Salesforce and asking them to index your txt_State__c field, this might help with the performance and result in the query running in an acceptable length of time.

Related Topic