14

Sample code on a custom object, Asset__c, containing 78,000 rows:

Integer number1 = [SELECT count()  FROM Asset__c ];
system.debug('Query Rows used by count():'+Limits.getQueryRows());

If i do this.. Since i have over 50,000 records a govenor limit is getting exceeded and it's throwing too many query rows : 50001 error. If run on a object with under 50,000 rows you'll still notice the debug output is much higher than the 1 row that's expected.

Please see this idea as well in SF.... "Count the SOQL count() query as a single row query"

https://sites.secure.force.com/success/ideaView?id=08730000000Br7TAAS

Please promote this guys... This is really ridiculous!!!!

Is there any other alternative for querying the count????

6 Answers 6

3

Create a summary object, who's sole purpose is to maintain summary counts of the various objects in different configuraitons. Run a batch job every hour to refresh the counts.

6

Depending on what you're doing, you may be able to use the @ReadOnly annotation to allow queries up to 1 million rows. If this is for a Visualforce component, for example, or a webservice.

If you need this information in a trigger, it will require some kind of workaround, such as storing the total number of Assets in a Custom Setting that's updated every time an Asset is created or deleted.

5

unlike MySQL, if you look at databases like DB2 or Oracle you (typically) don't get count()'s for free. (let's assume, not going to argue if certain versions of DB2/Oracle 9+ do give this function) I imagine that its the same case here. if you want to know the count you have to scan the table. Scanning the table is going to increment against your limit. I dont see this changing anytime soon (again, assuming that table counts aren't free at the DB level).

5
  • Can you suggest some other option for me? I need to show the count in an apex:tab and inside that i need to show the grid with that data with pagination. Even if the data rows are less. Say my count is 78000, and my page has to display only 100.. the total query rows returned becomes 78100. So how do i overcome this? I cant even query it in a list and get the list size, as its more than 50000 records and also the heap size goes up higher than 3MB
    – Sathya
    Aug 24, 2012 at 11:36
  • 3
    Do you really need to have the exact number? Could you do your pagination and just say 50,000+ for the total? Inside Salesforce, if I choose to view All Accounts it says "2000+" for the record count.
    – Mike Chale
    Aug 24, 2012 at 11:40
  • 50000+ is also fine @ebt but unfortunately when i try to display count of all different categories of assets in one page to allow people to drill down, the no of rows returned says it goes beyond 50000 in a transaction
    – Sathya
    Aug 24, 2012 at 16:46
  • 1
    @Sathya I'd suggest using a much lower limit, maybe 10,000. You can use a LIMIT clause on a count query to avoid it using all of your available query rows. I've used queries like SELECT COUNT() FROM object__c LIMIT 10001 before. If the result > 10000 then display "10,000+" otherwise show the value. Aug 24, 2012 at 17:02
  • Yeah @ca_peterson I agree but its a dashboard kind of stuff in the sites home page that the client wants to display.. So There are about 7 to 8 categories with different conditions and each of them having data of more than 15 to 16000 records. So When I do a count for each category from different objects the count of query rows exceeds 50000 as its somewhere around 15000*8 = 120000 query rows but its only 8 queries :( even if i do a limit it would be 80000 :(
    – Sathya
    Aug 24, 2012 at 18:58
1

/services/data/v26.0/query/?q=Select+id+From+Account

This API first attribute is totalsize and this will definately give the exact count .

eg("totalSize" : 37)

All you need is use a REST API call within the salesforce with token as userinfo.sessionId().

1

This has changed with Summer '18. The Limits Governor now counts Count() (and other aggregate functions) more in the way you'd expect.

0

I've never worked with record sets this large and am not sure if this would work but if nothing else, maybe it will give you some ideas. OK, get ready for janky code time:

What if you used multiple count queries and added the results together. You'd have to find an arbitrary field that you could use in a WHERE clause to block data off so that no one query was bigger than 50k rows.

For example, if you there was a Name field you could use, you might be able to do something like this:

Integer count1 = [SELECT COUNT() FROM Asset__c WHERE Name < 'C'];
Integer count2 = [SELECT COUNT() FROM Asset__c WHERE Name > 'C' AND Name < 'E'];

etc., etc.

Then add them all together.

I'm not sure if the governor is 50k rows in a single query or 50k rows in an execution path. It's obviously a bit inefficient and probably slow, but it could work.

Perhaps a faster way would be to store the data off somewhere in it's own record. Say, Asset_Count_c that has one fields - Size. You get the original size using a method similar to something above, and then write a trigger against Asset_c so that whenever you add a new record or delete an old one you update the Size field of Asset_Count__c. Then you can just query for the Size field.

3
  • Thanks for the info Ryan but even if i have multiple count queries.. the total no. of query rows returned per transaction goes more than 50000 as all the count query checks and an actual data pull of about 100 records happen inside one function..
    – Sathya
    Aug 24, 2012 at 16:05
  • OK, I wasn't clear on if the limit was per transaction or per query. The second option should work if you can get the initial value stored. Aug 24, 2012 at 16:11
  • yup thats what we are doing :) Anyways thanks for the info :)
    – Sathya
    Aug 24, 2012 at 16:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .