Non-Selective Query Exception on Task Query

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

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.

Attribution
Source : Link , Question Author : stmcallister , Answer Author : Aidan

Leave a Comment