[SalesForce] Non-selective query against large object type in trigger

The Alert__c object has a Rule ID field, which is just text, and a lookup to the Master__c object. I have a trigger on Alert__c, which when runs after insert, update or delete. It collects the Master ids off all the triggering Alerts, then runs an aggregate query to count certain kinds of Alert that are attached to the Master, and stores those values in the Master object. Basically I need to know how many 183's there were on this Master in the last 7 days, how many 184's, 185's and 186's. I perform the following query, which worked until the Alert__c table got too big:

list<AggregateResult> ars = [select Master__c m, RULE_ID__c RuleId, grouping(RULE_ID__c) grpruleid, count(Id) cnt
from Alert__c
where Created_Within_Last_7_Days__c = 1 AND (RULE_ID__c='183' OR RULE_ID__c='184' OR RULE_ID__c='185' OR 
    RULE_ID__c='186') 
group by rollup(Resident__c, RULE_ID__c)
having Master__c IN :MasterIds];

Now I receive this error: 

Apex script unhandled trigger exception by user/organization: 00530000004s2HV/00D30000001H6e6
MdsAlertsRollUp: execution of AfterInsert

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.
Even if a field is indexed a filter might still not be selective when:
1. The filter value includes null (for instance binding with a list that contains null)
2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

Trigger.MdsAlertsRollUp: line 22, column 1

Created_Within_Last_7_Days__c is a formula field based on CreatedDate, and Rule_ID__c is configured as an External Id field. For 200 Master records, there aren't typically more than 300 Alert records.

Complicating this is that I can run this query in an Anonymous Apex window successfully, but not within a trigger.

Any ideas? For solution as well as why this is happening?

Best Answer

Instead of using the

having Master__c IN :MasterIds;

Try adding this filter to the where clause

where .... And Master__c in :MasterIds;

Right now, as I read this, you're essentially selecting through all of the alert__c records with those rules, and there are a lot of them (since I'm betting the Rule_ID_c field is external, but not unique. After that you're applying the Master_c filter - by which time it's too late. Having is generally used to filter on group terms - which you're not doing here.

If I'm right, moving the Master__c term into a Where clause may make the query sufficiently selective. I'll be honest though - I'm not a SOQL expert, so take this more as something to try than a committed answer.

Selectivity threshold can vary by type of context - which is why you can absolutely see something succeed in anonymous Apex and fail on a trigger.

Related Topic