2

I am trying to insert new records into a custom object (production) via apex data loader. When I do so I get an error like

    ​ ReservationTrigger: 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.ReservationTrigger: line 11, column 1

My trigger is

trigger ReservationTrigger on Reservations__c (after insert,after update) {
// Get the IDs of related contacts
Set<Id> contactIds = new Set<Id>();
for(Reservations__c oneReservation:trigger.new){
    if(oneReservation.ReservationStatus__c == 'Confirmed'){
        contactIds.add(oneReservation.Email__c);
    }
}
// Count the distinct Reservation_Number__c from the Reservation objects of all related contacts
Map<Id, Integer> countDistinctReservationMap = new Map<Id, Integer>();
for (AggregateResult aggRes : [SELECT COUNT_DISTINCT(ReservationCode__c) resNum, Email__c conId FROM Reservations__c WHERE Email__c IN: contactIds GROUP BY Email__c ]) {
     Id conId = (Id) aggRes.get('conId');
     Integer resNum  = (Integer) aggRes.get('resNum');
     countDistinctReservationMap.put(conId, resNum);
}
// Now fetch the Contacts in a list
List<Contact> listContacts = [Select Id, customRollupField__c from Contact Where Id IN:contactIds];
if(listContacts.size()>0) {
     for(Contact con : listContacts) {
          // fetch or get the distinct count or rollup from the map and copy it to the contact's field
          con.customRollupField__c = countDistinctReservationMap.get(con.Id);
     }
}
// Update the contacts
update listContacts;
}

Any Ideas why?

6
  • 2
    i am guessing either Contact or Reservations__c has more than 100k records (can you confirm?) usually this error shows up when a non selective query is executed against large data sets. you might want to check this thread for further details: salesforce.stackexchange.com/questions/10873/… Apr 22 '15 at 8:16
  • @Rajiv I have checked the thread you are suggesting and is says to add and Contact__c != null . But where should I add this? Apr 22 '15 at 8:20
  • they have asked to remove the null check! in your case you do not have any null checks so you should be good. first we need to understand which query is causing the issue and thats the reason why we need to know the record count from each of those objects. Once we know which query is causing the issue, the next steps would be to identify how it could be made selective. For example: if the reservations__c query is causing the issue then probably you might want to add an index on the email__c field. Apr 22 '15 at 8:25
  • @RajivBhatt It seems from the error that the problem is when for (AggregateResult aggRes : [SELECT COUNT_DISTINCT(ReservationCode__c) resNu Apr 22 '15 at 8:32
  • I think there's an error in your query on line 11 - added another possible solution in my answer. Apr 22 '15 at 8:49
0
[SELECT COUNT_DISTINCT(ReservationCode__c) resNum, Email__c conId FROM Reservations__c WHERE Email__c IN: contactIds GROUP BY Email__c]

This is querying too much data. If possible, you need to add more filtering through additional requirements in your WHERE clause because Email__c IN: contactIds is not cutting it.

Note that Email__c conId is perfectly valid aliasing inside any SOQL or SQL statement. This is standard.

Why are you doing a distinct count on ReservationCode__c and putting it in to every row? Is this supposed to be a reservation number? Do you need this to be unique?

Below is a resource that quickly lays out the limits of SOQL and SOSL transactions and might give you some ideas as to how you might further optimize your query.

http://help.salesforce.com/help/pdfs/en/salesforce_query_search_optimization_developer_cheatsheet.pdf

1
  • The ReservationCode_c is something that is unique in each row. In some cases if an update was made on the reservation the detailid changes but the ResCode remains the same. I wanted this to dinstict each record from another in order to have a field update. Now my detail id I made it as an external field id but I dont know how to implement it into the code! Apr 23 '15 at 7:10
-1

that means it's taking too long. A couple ways to get more CPU time are to put the code in an @future method (you get 60 seconds of CPU instead of just 10 seconds). or else you can run this as batch apex and in this trigger you can make call to that batch apex along with passing list of contact Ids and other data required for this trigger.

1
  • 1
    Non selective error is not a CPU error and will happen in the future method as well
    – Eric
    Sep 22 '16 at 6:56

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