3

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?

Improve this question

4 Answers 4

Reset to default
6

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.

Improve this answer
1

Use these ideas at your own risk. They may or may not be helpful. I don't know how the SOQL query rewrite and indexing works internally.

That said, the Created_Within_Last_7_Days__c formula field may be resulting in a table scan rather than an index seek or index scan (these are the terms I'd use with SQL server. I assume they are applicable to an Oracle DB). I.e. every possible row needs to be checked rather than relying on an index seek/scan. I have no proof for this, but it might be worth expressing the same restraint using CreatedDate in the where clause to see if it helps.

Also under the scatter shot approach of things to try, you could change the RULE_ID__c='183' OR RULE_ID__c='184' OR RULE_ID__c='185' OR RULE_ID__c='186' to be RULE_ID__c in ('183','184','185','186').

Improve this answer
1

First, I suspect that salesforce isn't using the index on CreatedDate because you're referencing it inside a formula. Since this query is in apex could you drop the use of the formula and just compare CreatedDate against an apex Date object:

where CreatedDate >= :Date.today().addDays(-7)

I suspect this will pick up the index on CreatedDate, and if you don't have a huge number of records in the last 7 days that alone might resolve this.

If not, your best bet is to request custom indexing on the RULE_ID__c field - this can be done through support and once you explain your use case should be pretty simple to push through.

Improve this answer
0

These are all good ideas. Unfortunately, I had to act quickly, so I moved the code from the trigger to a @future method on a separate Class, which is called from the Trigger. This is working fine, without changing the SOQL syntax at all.

I still would like to find out if the query itself is fixable, but I can't really mess around in this production environment, and it's hard to recreate the >100K rows situation outside of that.

Things to try next time:

  • Use 'where' for filtering rather than 'having' if possible.
  • Filter on a concrete value field rather than a formula
  • Use a set if possible rather than multiple OR phrases.
Improve this answer
3
  • Don't forget custom indexing. It's a key part of database performance, make good use of it!
    – ca_peterson
    Sep 14, 2012 at 19:11
  • Is there a practical difference between asking Salesforce for a custom index, and just checking "External Id" on a field?
    – Jeremy Nottingham
    Sep 14, 2012 at 19:32
  • Very little, external ID forces salesforce to index it, so performance wise they're identical. However there's a more strict limit on the number external ID fields, and some field types can be indexed but not made an external ID.
    – ca_peterson
    Sep 14, 2012 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.