+ Start a Discussion
Zachary Alexander 30Zachary Alexander 30 

RollupSummaryUtility -- Non Selective Query Problem

Hello All,

I found some code online to help me create a custom Rollup-Summary solution. It works great in Sandbox. But the system says that one of the queries is non-selective (at least when I did a big delete event).

How is it non-selective? The WHERE clause is based upon a lookup field (which is about as specific an index as possible).

Here is the code:
 
public class RollUpSummaryUtility {
    //the following class will be used to house the field names
    //and desired operations
    public class fieldDefinition {
        public String operation {get;set;}
        public String childField {get;set;}
        public String parentField {get;set;}
        public fieldDefinition (String o, String c, String p) {
            operation = o;
            childField = c;
            parentField = p;
        }
    }
    public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
                                     list<sObject> records, String childObject, String childParentLookupField,
                                     String parentObject, String queryFilter) {
                                         //Limit the size of list by using Sets which do not contain duplicate
                                         //elements prevents hitting governor limits
                                         set<Id> parentIds = new set<Id>();
                                         for(sObject s : records) {
                                             parentIds.add((Id)s.get(childParentLookupField));
                                         }
                                         //populate query text strings to be used in child aggregrator and
                                         //parent value assignment
                                         String fieldsToAggregate = '';
                                         String parentFields = '';
                                         for(fieldDefinition d : fieldDefinitions) {
                                             fieldsToAggregate += d.operation + '(' + d.childField + ') ' +
                                                 ', ';
                                             parentFields += d.parentField + ', ';
                                         }
                                         //Using dynamic SOQL with aggergate results to populate parentValueMap
                                         String aggregateQuery = 'Select ' + fieldsToAggregate +
                                             childParentLookupField + ' from ' + childObject + ' where ' +
                                             childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +
                                             ' group by ' + childParentLookupField;
                                         //Map will contain one parent record Id per one aggregate object
                                         map<Id, AggregateResult> parentValueMap =
                                             new map <Id, AggregateResult>();
                                         for(AggregateResult q : Database.query(aggregateQuery)){
                                             parentValueMap.put((Id)q.get(childParentLookupField), q);
                                         }
                                         //list of parent object records to update
                                         list<sObject> parentsToUpdate = new list<sObject>();
                                         String parentQuery = 'select ' + parentFields + ' Id ' +
                                             ' from ' + parentObject + ' where Id IN :parentIds';
                                         //for each affected parent object, retrieve aggregate results and
                                         //for each field definition add aggregate value to parent field
                                         for(sObject s : Database.query(parentQuery)) {
                                             Integer row = 0; //row counter reset for every parent record
                                             for(fieldDefinition d : fieldDefinitions) {
                                                 String field = 'expr' + row.format();
                                                 AggregateResult r = parentValueMap.get(s.Id);
                                                 //r will be null if no records exist
                                                 //(e.g. last record deleted)
                                                 if(r != null) {
                                                     Decimal value = ((Decimal)r.get(field) == null ) ? 0 :
                                                     (Decimal)r.get(field);
                                                     s.put(d.parentField, value);
                                                 } else {
                                                     s.put(d.parentField, 0);
                                                 }
                                                 row += 1; //plus 1 for every field definition after first
                                             }
                                             parentsToUpdate.add(s);
                                         }
                                         //if parent records exist, perform update of all parent records
                                         //with a single DML statement
                                         if(parentsToUpdate.Size() > 0) {
                                             update parentsToUpdate;
                                         }
                                     }
}

Can somebody tell me what I am missing?
Best Answer chosen by Zachary Alexander 30
Alain CabonAlain Cabon
Hi,

The WHERE clause is based upon a lookup field (which is about as specific an index as possible).

A lookup field is indexed (correct) but there are other rules with complicated relative thresholds (no fixed number of targeted records excepted 333,333 for a custom index and 1 million for a standard index).

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm

Verify simple filter is selective

Determine if it has an index.
  • If the filter is on a standard field, it'll have an index if it is a primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate,   SystemModstamp).
  • Custom fields will have an index if they have been marked as Unique or External Id
  • If the filter doesn't have an index, it won't be considered for optimization.
  • If the filter has an index, determine how many records it would return:
  • For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million. In addition, the selectivity threshold for a standard index maxes out at 1 million total targeted records, which you could reach only if you had more than 5.6 million total records.
  • For a custom index, the selectivity threshold is 10 percent of the first million targeted records and 5 percent all records after that first million.  In addition, the selectivity threshold for a custom index maxes out at 333,333 targeted records, which you could reach only if you had more than 5.6 million records.
  • If the filter exceeds the threshold,it won't be considered for optimization.
  • If the filter doesn't exceed the threshold, this filter IS selective, and the query optimizer will consider it for optimization.

https://help.salesforce.com/articleView?id=How-to-make-my-SOQL-query-selective&language=en_US&type=1
 
//Using dynamic SOQL with aggergate results to populate parentValueMap
  String aggregateQuery = 'Select ' + fieldsToAggregate +
  childParentLookupField + ' from ' + childObject + ' where ' +
  childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +
    ' group by ' + childParentLookupField;
 //Map will contain one parent record Id per one aggregate object
     map<Id, AggregateResult> parentValueMap =
                          new map <Id, AggregateResult>();
  for(AggregateResult q : Database.query(aggregateQuery)){
       parentValueMap.put((Id)q.get(childParentLookupField), q);
  }


The problem is not the number of records in the result (very few for AggregateResult) but the the total number of record you need to read to get this result in Apex with SOQL requests.

AggregateResult[] arList = [select count(id) mycount from Account];  // doesn't work in Apex all the time according the thresholds above
System.debug(LoggingLevel.INFO, arList[0].get('mycount'));