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