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
HSDM SupportHSDM Support 

Trigger causes CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, *ONLY* in Production.

Here is my full error message:
CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: T_TSC_Seek_Vehicle_Obj: execution of BeforeInsert
 
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.T_TSC_Seek_Vehicle_Obj: line 41, column 1
 
trigger T_TSC_Seek_Vehicle_Obj on Technical_Service_Campaign__c (before insert, before update) {

	//	list of Model, Chassis Number, Service Records
	//Set<String> tscModel		= new Set<String>();
	Set<String> tscChassis		= new Set<String>();
	Set<String> tscBrRepOrd		= new Set<String>();
	
	for (Technical_Service_Campaign__c tsc: Trigger.new) {
		//tscModel.add(tsc.Recall_Model_Code__c);
		tscChassis.add(tsc.Recall_Chassis__c);
		tscBrRepOrd.add(tsc.Recall_Branch_Code__c + tsc.Recall_Repair_Order__c);
	}

	//  TSC Recall Chassis => SFDC Vehicle Id, SFDC Product Id
	Map<String, Id> mapVehicle = new Map<String, Id>();
	Map<String, Id> mapProduct = new Map<String, Id>();
	Vehicle__c[] vs = [Select Id, EDMS_Chassis_Number__c, Product__r.Id
					   From Vehicle__c
					   Where EDMS_Chassis_Number__c In :tscChassis
					   Limit 200];
	for (Vehicle__c v: vs) {
		//	gather Vehicle Id, Model Id
		mapVehicle.put(v.EDMS_Chassis_Number__c, v.Id);
		mapProduct.put(v.EDMS_Chassis_Number__c, v.Product__r.Id);
	}

	//	TSC Recall Repair Order, Branch Code => SFDC Service Records
	Map<String, Id> mapSvcRec = new Map<String, Id>();
	Service_Record__c[] srs = [Select Id, Branch_Repair_Order__c
							   From Service_Record__c
							   Where Branch_Repair_Order__c In :tscBrRepOrd
							   Limit 200];

	for (Service_Record__c sr: srs) {
		system.debug('T_TSC_Seek_Vehicle_Obj: SR matched: ' + sr);
		mapSvcRec.put(sr.Branch_Repair_Order__c, sr.Id);
	}

	//	Update Technical Service Campaign object based on external Id	
	for (Technical_Service_Campaign__c tsc: Trigger.new) {
		if (mapVehicle.containsKey(tsc.Recall_Chassis__c)) {
			tsc.Vehicle__c = mapVehicle.get(tsc.Recall_Chassis__c);
			tsc.Model__c = mapProduct.get(tsc.Recall_Chassis__c);
		}

		if (mapSvcRec.containsKey(tsc.Recall_Branch_Code__c + 
								  tsc.Recall_Repair_Order__c)) {
			tsc.Service_Record__c = mapSvcRec.get(tsc.Recall_Branch_Code__c + 
								  				  tsc.Recall_Repair_Order__c);
		}
	}
}
This trigger runs fine in Sandbox. Problem occurs when new records are being Upsert in Production. Did a lot of searches, but most problems were related to Opportunity object. Mine is a Custom Object.

I have similar Trigger for other Custom Objects, which worked fine previously, until the recent update by SFDC (Fall 2015? I don't live in 4 seasons country.). Things start crumbling 1 by 1. I had to remark this whole piece of code in order for data to be loaded into Production. With that being said, the new records would have empty Lookup field, which should reference to respective Vehicles.

Records are upserted via batch job of 50 records.

Thank for your time.
Gaurav_SrivastavaGaurav_Srivastava
Hi HSDM Support,

Whole processing is taking too long. To get the more CPU time, put some part of code into @future method or implement it as batch jobs. The temporary work around is reduce the batch size. You can try with batch size = 10.

Thanks,
Gaurav
SRKSRK
There are some SOQL quries in this code in any of SOQL are you using formual field in where clause 

it not good idea to use formula field in where clause 

please confirm
HSDM SupportHSDM Support
@Gaurav_Srivastava, thanks for your reply.

Let me update a little on my post. Code remained the same, Stack Trace needs update:
10C120-KMHJU81BMBU233957 CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: T_TSC_Seek_Vehicle_Obj: execution of BeforeUpdate
 
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.T_TSC_Seek_Vehicle_Obj: line 29, column 1

So basically it throws exception when I'm doing SOQL query from my Service Records object. There are 357,050 records in Service History object. Do I have to break the Trigger function and put them into a batch instead? Afterall, it's for mapping Lookup field.
HSDM SupportHSDM Support
@SRK, thanks for your reply.

There's no formula field in Service Record object.
Gaurav_SrivastavaGaurav_Srivastava
Hi HSDM Support,

You can try following solutions-

Solution 1. Is it possible to make field "Branch_Repair_Order__c" unique external id on object "Service_Record__c" ? If yes, then it will make the query as Selective and trigger will run fine.

OR

Solution 2. Try to modify where clause so it will contain elements that will make it ‘Selective’. - See more at: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm

Thanks,
Gaurav
HSDM SupportHSDM Support
@SRK, my bad, Branch_Repair_Order__c was indeed a formula field. It's a concatenation of Branch_Code__c + Repair_Order_Number__c.

I needed the compounded values in my search criteria. I didn't realise it would come back to bite me in the bottom.

@Gaurav_Srivastava,

1) Seeing that I can't make Branch_Repair_Order__c (formula field) a unique ext. id, I have included an additional search criteria, which is Service_Type__c = 'Warranty' in this case. This should make the query fairly selective, at most it would return 3 records for each unique Branch_Repair_Order__c with Service_Type__c = 'Warranty'.

If this still doesn't work, I might want to concat Service_Type__c into Branch_Repair_Order__c field as well and fixes tscBrRepOrd list to include the 'Warranty' text. But I doubt it would make a different.

I'm deploying my update and see if the Trigger still throws me exceptions.

Thanks again.
 
HSDM SupportHSDM Support

Hi guys,

There are some improvements. Out of 5000 records, I'm getting 950 success (95 batches), while the rest were hit with the same exception. I have pick the first error record, it returns 3 records from Service_Record__c object for the criteria match. But even for a batch of 10, it would only query up to 30 records, yet I don't understand why does it hit a snag on this.

Any further tips for me?

 

Thank you.