5

I have the following three SOQL queries in a class that is being called by a Trigger. The Trigger counts the number of completed tasks and events for a given account.

List<Account> accounts = [SELECT ID, activity_count__c FROM Account WHERE ID IN :accountIds];
List<Task> compTasks = [SELECT ID, AccountID FROM Task WHERE Status = 'Completed' AND
                        AccountID IN :accountIds AND CreatedById NOT IN :exclusions];
List<Event> events = [SELECT ID, AccountID FROM Event WHERE AccountID IN :accountIds AND
                     CreatedById NOT IN :exclusions];

Periodically, I'm getting a 'caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows)' error on the compTasks query. I've learned from our admin that this occurs when Leads are converted.

In searching for a solution I found a suggestion to add 'ORDER BY CreatedDate DESC LIMIT 10000' since CreatedDate is an index, and it will keep my search within the bounds of the large object limit.

Should I be concerned about limiting my query to 10000? Could I potentially be missing possible tasks to count? Or, is there a better way to make my count while avoiding the Non-selective query Exception?

  • 1
    Not central to your question, but using an aggregate query for the Task and Event queries (i.e. with group by AccountId and count() in the select) would save you having to do any looping to get the counts per Account id. – Keith C Apr 26 '14 at 17:01
  • That's a good point. Thanks, Keith C! Although, even using an aggregate query will still cause me to run into the Non-selective Query Exception, right? – stmcallister Apr 26 '14 at 20:41
  • Yeah I assume so as the same set of rows need to be found; the aggregation just returns the data ready grouped. – Keith C Apr 26 '14 at 23:10
  • 1
    I'm not sure if this should be asked as a separate question, but isn't accountid an external ID and therefore indexed? In which case why is it throwing a Non-selective query error? – RohanC Apr 27 '14 at 8:25
  • @RohanC I had similar thoughts. I'll post that as a separate question and see what I can discover. – stmcallister Apr 28 '14 at 16:44
1

Although you're querying on an indexed field, your clause needs to sufficiently reduce the number of records in the resulting data set. As SF themselves put it:

Selective SOQL Query Criteria

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

The selectivity threshold is 10% of the records for the first million records and less than 5% of the records after the first million records, up to a maximum of 333,000 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold may be higher. Also, the selectivity threshold is subject to change.

See https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

Exactly how it all works is a bit hairy and undocumented.

An alternative approach would be to write a trigger on Task (insert, update, delete) that updates a counter on Account. i.e. Update the count as Tasks are inserted/deleted/completed rather than try to query the large dataset.

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.