+ Start a Discussion
tom_ktom_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"

}

 

Best Answer chosen by Admin (Salesforce Developers) 
tom_ktom_k

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

tom_ktom_k

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

 

tom_ktom_k

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.

 

 

This was selected as the best answer
Nickname7Nickname7
Hi Tom,

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!
Jim HornJim Horn
Having the same issue in SSIS where I have an ETL that moves 40+ worth of Salesforce.com objects into SQL Server tables.  I can get the 'SELECT COUNT(id) row_count FROM {object name}' for all 40+ except for the Note table, where I get the 'OPERATION_TOO_LARGE' error.