8

I am trying to execute the following SOQL query to count number of notes in Salesforce instance: SELECT Count(Id) notes_count FROM Note over the API.

And getting OPERATION_TOO_LARGE: exceeded 20000 distinct ids error as a response.

  1. why I am seeing this response (i.e. I have previously made counts of notes where result was much larger than 20000 notes)?
  2. How can I fix my count query to overcome this?
6

There are couple of options that I can think of to solve this problem.

  • Use ReadOnly annotation so that the limit on the data that is returned is increased. Check out this link. Here is one more.
  • Use filters to narrow down the resultset
  • Check out this post from boards.developerforce.com. To quote from this post,

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.

Hope this helps.

  • Wow. Beat me to it. Haha, Nice work +1 – Chris Duncombe Oct 8 '13 at 16:28
  • @tech-matrix The last bullet with link to a post on boards.developerforce.com is answer I was looking for. Thanks! – antonlitvinenko Oct 8 '13 at 16:54
  • 1
    Actually, from that article this was a key bit that helped me to understand this: 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. – antonlitvinenko Oct 8 '13 at 17:04
3

You can just get the size from the result list:

Integer noteCount = [SELECT Id FROM Note].size();

This places the upper limit at 50,000 records for normal Apex Code, 1,000,000 for "read-only" pages, and 50,000,000 in the Data Loader, etc (but then you have to download a huge file just to get a count).

You probably want to actually filter this query to be relevant to a specific record or group of records.

1

This question has been asked before on teh developer forum, and was answered by a SF engineer. As sfdcfox suggests, filtering the query is your best bet.

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

From the question on the dev forum

http://boards.developerforce.com/t5/General-Development/SOQL-Count-query-fails-with-OPERATION-TOO-LARGE-Why/td-p/474661

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.