7

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.

4

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.

  • 1
    @RobertSosemann: I think this might help, salesforce.stackexchange.com/questions/13954/… – Sam Jul 12 '13 at 12:44
  • 1
    I think if the pure length of time that the query is taking is causing a timeout, and indexing isn't a viable option, then you do have a problem. You can still work through the records with a querylocator as that can deal with up to 50 million records. What do you need the actual count for? Does it need to be in real time or could you build it in a daily batch? – Doug B Jul 12 '13 at 13:15
  • 1
    You can enable a few fields as External ID fields. This will cause somefield to be indexed, and then you can query with a filter against those fields quickly. – sfdcfox Jul 12 '13 at 13:25
  • 1
    External IDs have to be unique. Salesforce can index other fields if they feel there is a case, but as you say if there are very few possible values then the indexing probably isn't going to help. – Doug B Jul 12 '13 at 13:41
  • 1
    External ID values do not need to be unique, actually, unless you actually plan on using it as an External ID (e.g. via Upsert). – sfdcfox Jul 12 '13 at 13:57
1

There is a concept of skinny table which can be provided access by salesforce support. This is an indexing table which salesforce uses for most frequent queries and fields for each object. See if you can use them which would reduce the response time. 1. You would want to do an apex batch and get the count and populate it in a custom field for a long term solution.

  • 1
    Skinny tables are used to cut down on joins between tables on the backend (if you often query data from a related record like Account.name) and aren't much help on queries like this. – ca_peterson Jul 12 '13 at 18:04
0

The Inside the Force.com Query Optimizer Webinar explains it all :)

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.