function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Ken_KoellnerKen_Koellner 

select count() and query row limits

I have the code below in an Apex test prior to Test.startTest().  I would have thought that this would bump the query rows by at most one row but it doesn't; it bumps the query rows by 201.  That statement gets executing a few times and I exceed the 500 query row limit.   In and RDBS, that query would return exactly 1 row.  What's going on?  Does it count every row scanned even though one value gets returned?  That would mean you could never execut select count() from account if you have more than 10,000 accounts.

 

 

                               Select count()
                              From AccountShare 
                              where Account.Office_Front_or_Back__c = 'Front Office'
                              and UserOrGroupId in :groupList
                              and Account.enrollment__c >= 5
                              and Account.enrollment__c <= 650 limit 201]

ThomasTTThomasTT

True. Live with it.

ThomasTT

apexsutherlandapexsutherland

Heh, seriously TT? That kind of response isn't going to win a lot of developer converts to the Salesforce.com platform...

ThomasTTThomasTT

Ok, I think I misunderstood the question (and the salesforce.com people didn't catch my misunderstanding as a bad signal...).

 

As the poster imagined, the # of Accounts which match the condition will be up to 201 rows.

If you put "LIMIT" clause, it returns only the number of records. So, was right, but his code wasn't.

 

"AccountShare" he was trying to select is made for 1 Account for 1 user. So even though there are only 201 Accounts hit, if the group has 1000 people, AccountShare can be 201,000 records. You can limit the Account, but you may not want to limit the people (or AccountShare)... so unless the poster adjust the requirement, it would be tough for the developers.

If the Ken thought "Account" hit more than 201, then no no no. Account is limited to 201 correctly, but AccountShere is not.

If the Ken understood that, but asking the 10,000 record limit... then true, you can't count more than 10,000 records (I meant this).

Sorry for that resonse. I will remove it for salesforce.

 

 ThomasTT

apexsutherlandapexsutherland

Haha, sorry ThomasTT, I mistook your handle for the product manager of Analytics at Salesforce.com (his first name is Thomas and I'm pretty sure his initials are TTT as well!).

 

Your response is fair, but one of my missions in life is to push Salesforce.com to provide solutions for problem like this, instead of forcing talented developers to waste their time developing clumsy, brittle, and often inadequate workarounds like batch jobs or AJAX pollers to do basic things like a SELECT COUNT() for record counts greater than 10,000. Check out the comments on this idea:

 

https://sites.secure.force.com/ideaexchange/ideaView?c=09a30000000D9xt&id=08730000000Br7T

 

Your suggestions for workarounds are accurate and the best that developers can do right now on the platform, but in my experience as a Force.com architect and developer for many large enterprises and Salesforce.com partners those types of "bridge" solutions are costly and shouldn't really be necessary.

 

With all of the development resources that Salesforce.com is pouring into their analytics engine these days, it would be quite reasonable for them to invest a few cycles in making their aggregate SOQL functions like COUNT() less frustrating for Apex coders to implement.

 

However, i appreciate you taking the time to outline some potential workarounds for this issue. The Force.com community finds responses like that much more helpful than "Live with it.".  :smileywink:

ThomasTTThomasTT

My bad. I misunderstood the question. See my updated answer. Thanks for the comment!

I also like to promote salesforce.com (so I made the strike on my previous comment).

 

ThomasTT

ThomasTTThomasTT

... and ouch, I've just removed my solutions... but if you search my all past posts, you will find it (I already mentioned so many times even with my demo site URL).

 

and

 

I was sorry, the real and big TTT!  I am just a talking tank!

 

Thomas TT

apexsutherlandapexsutherland

Cool, glad we're on the same side now! :-)

 

However, I think you should bring back into this thread your suggestions about how to work around the limits of SOQL SELECT COUNT(), they were right on the money and would be very helpful to newbie Force.com developers trying to figure out how to accomplish this normally simple operation...