Non-Selective Query Exception on Task Query

Question

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?

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