Apex Code Development You need to sign in to do that
Don't have an account?
tom_k SOQL Count() query fails with OPERATION_TOO_LARGE. Why?
Hi,
I am developing a mobile app and using the REST API to interface with Salesforce.
One of our customer data sets has a huge "bulge" of notes so big that I cannot even
issue a Count() query on it.
This seems really odd, not to even be able to count the items in a Table. If this were
an actual query to pull records, I would try to narrow the query. But having to narrow
a Count() query seems like there is some deeper fundamental problem (especially
for such a small value as 20000 records).
Unfortunately, I don't have easy access to the customer's account to experiment with.
Any suggestions appreciated. [I would love a "cold call" from a salesforce support
engineer for this one.]
Thanks,
Tom
Here is the failure:
SOQL "select Count() from Note"
==>
{
"message" : "exceeded 20000 distinct ids",
"errorCode" : "OPERATION_TOO_LARGE"
}
A Salesforce engineer was kind enough to reply, so I thought I would post the answer here for everyone to benefit.
I will summarize what confused me about this problem. Since it's just a Count() query, I expected salesforce to be able to handle an unlimited size in O(1) time. After all, it just needs to return the last row number. But depending on settings, salesforce may need to do a security calculation for each row, so internally it actually has to visit each row in case some of them are culled from my view.
From SFDC engineering:
OPERATION_TOO_LARGE
The query has returned too many results. Some queries, for example those on objects that use a polymorphic foreign key like Task (or Note in your case), if run by a user without the "View All Data" permission, would require sharing rule checking if many records were returned. Such queries return this exception because the operation requires too many resources. To correct, add filters to the query to narrow the scope, or use filters such as date ranges to break the query up into a series of smaller queries.
In your case a count() query is the same as returning every record at the DB level so if your count returns > 20K records then it is really the same as returning all that data from the DB perspective. After all, the access grants still have to be calculated to return an accurate count.
All Answers
Hi Tom,
You can use Readonly annotation to retrieve more than 50,000 records.
Read more here:
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_classes_annotation_ReadOnly.htm
http://www.salesforce.com/us/developer/docs/pages/index_Left.htm#StartTopic=Content/pages_controller_readonly_context.htm
Hi Jitendra,
Thank you for your suggestion. Unfortunately, I don't think this will help me.
I am not using apex or visualforce, I am using the REST API directly. I am
not aware of any Readonly annotation in this context. (Actually, as far as I
can tell, every REST SOQL query is read only, and the system perhaps
knows that implicitly).
Also, there are tables (note: in a different org) which return a Count() of over
100,000 records without issue. Note that I am not querying for the data of
each of the 100,000 records, just the count.
So, something funky is going on in this particular case, and I'm still searching
for clues about what that is...
Thanks,
Tom
A Salesforce engineer was kind enough to reply, so I thought I would post the answer here for everyone to benefit.
I will summarize what confused me about this problem. Since it's just a Count() query, I expected salesforce to be able to handle an unlimited size in O(1) time. After all, it just needs to return the last row number. But depending on settings, salesforce may need to do a security calculation for each row, so internally it actually has to visit each row in case some of them are culled from my view.
From SFDC engineering:
OPERATION_TOO_LARGE
The query has returned too many results. Some queries, for example those on objects that use a polymorphic foreign key like Task (or Note in your case), if run by a user without the "View All Data" permission, would require sharing rule checking if many records were returned. Such queries return this exception because the operation requires too many resources. To correct, add filters to the query to narrow the scope, or use filters such as date ranges to break the query up into a series of smaller queries.
In your case a count() query is the same as returning every record at the DB level so if your count returns > 20K records then it is really the same as returning all that data from the DB perspective. After all, the access grants still have to be calculated to return an accurate count.
I know it has been almost a year but, I'm planning to use the readonly annotation and we were curious if we can create a custom apex class and method that is marked with @readonly and call this from the webservice. Would that return the count without any limitation?
Thanks!