List<Task> compTasks = [SELECT ID, AccountID FROM Task WHERE Status = 'Completed' AND AccountID IN :accountIds AND CreatedById NOT IN :exclusions];
I have this query in a class being called by a trigger. Periodically, I’m receiving an exception with the following message:
caused by: System.QueryException: Non-selective query against large
object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.
Wouldn’t filtering on AccountID be a standard index filter?
btw, this question is a spin-off of this one.
As a test, try removing the
AND CreatedById NOT IN :exclusions clause from the where query. This is most likely the part that is making the query non-selective.
From the Query & Search Optimization Cheat Sheet:
the Force.com query optimizer doesn’t use an index to drive queries containing
unselective filter conditions, even if the fields those filter conditions reference already
have indexes on them. (This cheat sheet’s “Index Selectivity Exceptions” section points
to several things that automatically make filter conditions unselective.)
The Index Selectivity Exceptions doesn’t explicitly list the
NOT IN operator, but I suspect it would be classed as “unselective”.
Depending on how many records you are dealing with, it might be easier to discard the exclusion UserIds from the resulting compTasks list.
A few ideas that might help alleviate the problem:
- Move the SOQL query and the resulting processing into a future method. If you can breakup the set of account ids into multiple future methods then you should have smaller collections of tasks.
- Move the SOQL query and the resulting processing into a batch job. You could set the batch size to 1 so only a single Account is worked on at a time. This is probably preferable to using future methods.
- Create a trigger on Task insertion/deletion that increments/decrements a counter field on the Account rather than try an recalculate the count with a trigger on Account. (Assuming this has some resemblance to what you are trying to achieve). You would need to seed the initial value of this field, but you could just track the changes over time from then on.
- Use a scheduled job and batch combo to periodcally reset the counter field to ensure it is correct.