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.

Ralph Callaway
  • 24,529
  • 17
  • 112
  • 190
Robert Sösemann
  • 37,622
  • 26
  • 165
  • 495

3 Answers3

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.

Doug B
  • 11,442
  • 1
  • 36
  • 43
  • I raised a case. Thanks for this hint. Do you know if Salesforce is able to auto enable such an index for all customers of our packaged ISV field or just for single customer orgs using our software? – Robert Sösemann Jul 12 '13 at 11:37
  • Seems like Salesforce Support is only adding an index when the problematic query fullfils the "selectivity criteria". As my text field basically is a boolean which eigther return the first or second half of the 25 million records I guess they won't help me. Does this mean I am screwed?! – Robert Sösemann Jul 12 '13 at 12:35
  • 1
    @RobertSosemann: I think this might help, http://salesforce.stackexchange.com/questions/13954/best-way-to-do-a-count-from-a-custom-object-having-more-than-1-million-records – Sam Jul 12 '13 at 12:44
  • @theGreatDanton: Yeah, I know of readonly, but I don't want to make a page work I just need a record count.. – Robert Sösemann Jul 12 '13 at 13:09
  • 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
  • I guess you are right. But it feels strange to write a Batchable class just to find out how many records exist for plain analysis. – Robert Sösemann Jul 12 '13 at 13:17
  • 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
  • @sfdcfox: Would this require to be each value of this field to have a UNIQUE value? This is not the case here. I also was unable to make an already existing and packaged field an external id. :( – Robert Sösemann Jul 12 '13 at 13:36
  • 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
  • My mistake - thanks for clarifying - as you say it is possible as long as you aren't using it upsert. – Doug B Jul 12 '13 at 14:03
  • Also NOT IN where clauses are supposedly notorious. – techtrekker Jul 12 '13 at 19:44
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.

ca_peterson
  • 22,983
  • 7
  • 69
  • 123
Buyan Thyagarajan
  • 4,413
  • 6
  • 53
  • 91
  • 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
-1

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

Robert Sösemann
  • 37,622
  • 26
  • 165
  • 495