function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Tommy GeorgiouTommy Georgiou 

Problem with trigger when Inserting new records on custom object

Hello,


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?
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi Tommy Georgiou,

It is long running query ,try to put this in future method (you get 60 seconds  nstead of just 10 seconds of Execution time) or put it in a batch 
job and minimize batch scope.

Hope this will help you.
Tommy GeorgiouTommy Georgiou
Hi Ashish,

How do you do that on the apex data loader?

User-added image

 
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi ,

Try with small number in Batch size, lets say 30 or 50 .

Let us know if it helps.
Tommy GeorgiouTommy Georgiou
Hi Ashish,

Now some of the records succeed and the most of them are failing. Changed the batch to 30. Had 16k records. 6 k succeed and the rest 10k failed
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi Tommy,

What is the error ,please check error file , if it is because of 'System.QueryException: Non-selective query against large object type (more than100000 rows).' then reduce batch size to 5 or 10 .

Let us know if it helps you.

Please mark it as BEST ANSWER if it is helpful.
Rahul BorgaonkarRahul Borgaonkar
Hi,

You can try this code. Let me know how it goes.
 
for (List<AggregateResult> list_aggRes : [SELECT COUNT_DISTINCT(ReservationCode__c) resNum, Email__c conId FROM Reservations__c WHERE Email__c IN: contactIds GROUP BY Email__c ]) 
{
	for( List<AggregateResult> aggRes : List<AggregateResult> list_aggRes)
	{
		Id conId = (Id) aggRes.get('conId');
		Integer resNum = (Integer) aggRes.get('resNum');
		countDistinctReservationMap.put(conId, resNum);
	}
}
Please find more details on link below
https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

Thanks,

Rahul
 
Tommy GeorgiouTommy Georgiou
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
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi Tommy ,

Please reduce batch size to say 5 and try.
 
Rahul BorgaonkarRahul Borgaonkar
Sorry updated code
for (List<AggregateResult> list_aggRes : [SELECT COUNT_DISTINCT(ReservationCode__c) resNum, Email__c conId FROM Reservations__c WHERE Email__c IN: contactIds GROUP BY Email__c ]) 
{
	for( AggregateResult aggRes : List<AggregateResult> list_aggRes)
	{
		Id conId = (Id) aggRes.get('conId');
		Integer resNum = (Integer) aggRes.get('resNum');
		countDistinctReservationMap.put(conId, resNum);
	}
}

Thanks
Tommy GeorgiouTommy Georgiou
Hi Rahul,

When trying to insert your code I get an error like Error: Compile Error: unexpected token: 'List' at line 13 column 40

@Ashish

Reduced the batch at 10 still in progress 970 succeesses and 3710 errors
 
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi Tommy,

Reduce it to very minimum say 2-5 ,let's see now.
It its still error ,you have to modify your code.

 
Rahul BorgaonkarRahul Borgaonkar
Changed code
for (List<AggregateResult> list_aggRes : [SELECT COUNT_DISTINCT(ReservationCode__c) resNum, Email__c conId FROM Reservations__c WHERE Email__c IN: contactIds GROUP BY Email__c ])

 {
     for( AggregateResult aggRes : list_aggRes) // removed List<AggregateResult>.
     {
         Id conId = (Id) aggRes.get('conId');
         Integer resNum = (Integer) aggRes.get('resNum');
         countDistinctReservationMap.put(conId, resNum);
     }
 }
Tommy GeorgiouTommy Georgiou
Hi Ashish,

Tried a couple of times and now only 1466 are failing even with batch size to 2.

@Rahul

I've tried to insert using your code but still getting error for all of the records
Tommy GeorgiouTommy Georgiou
Hi Rahul

The code is not working
Tommy GeorgiouTommy Georgiou
@Rahul

Can I insert an external field id in the query above? I have a ReservationDetailID__c as unique external id field that is indexed now.