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

Improve this question
3
  • The problem is probably that you're trying to query more of a certain % of the total amount of records. That does look like a very broad query, what's it purpose ?
    – Samuel De Rycke
    Apr 28, 2014 at 17:04
  • @SamuelDeRycke It's part of a trigger that counts all the completed tasks and events for an account so that we can keep an activity count for each account. The trigger fires whenever an Event or Task created, changed or deleted.
    – stmcallister
    Apr 28, 2014 at 17:10
  • 1
    Hmm, by instinct I'd say you should look into group by queries, but im not sure if that will help with your selectivity issue. Consider limiting your statistic to the last 12 months or a business timespan that is relevant.
    – Samuel De Rycke
    Apr 28, 2014 at 17:21

1 Answer 1

Reset to default
3

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:

  1. 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.
  2. 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.
  3. 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.
  4. Use a scheduled job and batch combo to periodcally reset the counter field to ensure it is correct.
Improve this answer
2
  • A non-selective operator in addition to a selective operator doesn't actually hurt selectivity. I'd suspect the root cause here is data skew on tasks related to :accountIds.
    – ca_peterson
    Apr 29, 2014 at 3:27
  • @ca_peterson So too many Tasks for each account for the AccountID to be within the required threshold? That might explain why it fails periodically. It would depend on the set of Account Ids.
    – Daniel Ballinger
    Apr 29, 2014 at 9:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .