Why is the count() query in salesforce not counting the no. of query rows returned as 1?

Question

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????

Answer 1

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.

Answer 2

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.

Answer 3

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).

Answer 4

/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().

Answer 5

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

Answer 6

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.