Problem with trigger when Inserting new records on custom object [duplicate]

Question

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?

Answer 1

[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

Answer 2

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.