I am getting the following error on my trigger where my Limit is 1:
StartFlow:
execution of AfterUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 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.StartFlow: line 7, column 1
See My trigger:
trigger StartFlow on Order__c (after update ) {//after insert,
public Flow.Interview.Create_Renewal_OppLineItems_from_Invoice_record DummyFlow {get; set;}
if(Trigger.isAfter ){//&& Trigger.isInsert
for(Order__c o : Trigger.New) {
List<Order__c> Ord =[SELECT ID,Account_Name__r.Customer_Profile_Pricebook__c,End_Date__c,Year_Enddate_1_day__c,
Journal_Reader_Code__c,Number_of_Users__c,Renewal_Number__c, Opportunity_Name__r.Id,
Product__r.Id,Delegate_Admin__c,CurrencyIsoCode
FROM Order__c
WHERE Opportunity_Name__r.Id=:o.Opportunity_Name__r.Id
LIMIT 1];
string[] value0 = new string[]{Ord[0].Account_Name__r.Customer_Profile_Pricebook__c};
date[] value1 = new date[]{Ord[0].End_Date__c};
double[] value2 = new double[]{Ord[0].Year_Enddate_1_day__c};
string[] value3 = new string[]{Ord[0].Journal_Reader_Code__c};
double[] value4 = new double[]{Ord[0].Number_of_Users__c};
double[] value5 = new double[]{Ord[0].Renewal_Number__c};
Id[] value6 = new Id[]{Ord[0].Opportunity_Name__r.Id};
Id[] value7 = new Id[]{Ord[0].Product__r.Id};
string[] value8 = new string[]{Ord[0].Delegate_Admin__c};
string[] value9 = new string[]{Ord[0].CurrencyIsoCode};
Map<String, Object> myMap = new Map<String, Object>();
myMap.put('VarCustomerProfile', value0);//Customer_Profile_Pricebook__c
myMap.put('VarInvoiceEnddate', value1); //End_Date__c
myMap.put('VarInvoiceEnddateYear', value2); //Year_Enddate_1_day__c
myMap.put('VarInvoiceJRC', value3); //Journal_Reader_Code__c
myMap.put('VarInvoiceNumberOfUsers', value4); //Number_of_Users__c
myMap.put('VarInvoiceRenewalNumber', value5);//Renewal_Number__c
myMap.put('VarOppId', value6);//OpportinityID
myMap.put('VarProductId', value7);//ProductId
myMap.put('VarDelegateAdmin', value8);//Delegate_Admin__c
myMap.put('VarInvoiceCurIsoCode ', value9);//CurrencyIsoCode
DummyFlow = new Flow.Interview.Create_Renewal_OppLineItems_from_Invoice_record(myMap);
}
}
}
I am filtering with LIMIT 1 why do I get this error. When I try the SOQL in the editor It works with no errors?
Solution:
trigger StartFlow on Order__c (after update )
{
Set<Id> opptyIds = new Set<Id>();
public Flow.Interview.Create_Renewal_OppLineItems_from_Invoice_record DummyFlow {get; set;}
if(Trigger.isAfter && Trigger.isUpdate)
{
for(Order__c o : Trigger.New)
{
if((o.Opportunity_Name__c!=Null) && (o.Opportunity_Name__c != Trigger.oldMap.get(o.Id).Opportunity_Name__c)){
//if(o.Opportunity_Name__c!=Null){
string value0 = o.Account_Name__r.Customer_Profile_Pricebook__c;
date value1 = o.End_Date__c;
double value2 = o.Year_Enddate_1_day__c;
string value3 = o.Journal_Reader_Code__c;
double value4 = o.Number_of_Users__c;
double value5 = o.Renewal_Number__c;
Id value6 = o.Opportunity_Name__c;
Id value7 = o.Product__c;
string value8 = o.Delegate_Admin__c;
string value9 = o.CurrencyIsoCode;
Map<String, Object> myMap = new Map<String, Object>();
myMap.put('VarCustomerProfile', value0);//Customer_Profile_Pricebook__c
myMap.put('VarInvoiceEnddate', value1); //End_Date__c
myMap.put('VarInvoiceEnddateYear', value2); //Year_Enddate_1_day__c
myMap.put('VarInvoiceJRC', value3); //Journal_Reader_Code__c
myMap.put('VarInvoiceNumberOfUsers', value4); //Number_of_Users__c
myMap.put('VarInvoiceRenewalNumber', value5);//Renewal_Number__c
myMap.put('VarOppId', value6);//OpportinityID
myMap.put('VarProductId', value7);//ProductId
myMap.put('VarDelegateAdmin', value8);//Delegate_Admin__c
myMap.put('VarInvoiceCurIsoCode', value9);//CurrencyIsoCode
DummyFlow = new Flow.Interview.Create_Renewal_OppLineItems_from_Invoice_record(myMap);
DummyFlow.start();
}
}
}
}
Best Answer
I think your Order object contains more than 200,000 records. That's why it is complaining.
Secondly you are using SOQL inside a for loop, that's also need to be avoided.
More Efficient SOQL Queries
For best performance, SOQL queries must be selective, particularly for queries inside triggers. To avoid long execution times, the system can terminate nonselective SOQL queries. Developers receive an error message when a non-selective query in a trigger executes against an object that contains more than 200,000 records. To avoid this error, ensure that the query is selective.
A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.
The selectivity threshold is 10% of the first million records and less than 5% of the records after the first million records, up to a maximum of 333,333 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold can be higher. Also, the selectivity threshold is subject to change.
Refer Working with Very Large SOQL Queries
Approach will be like this:
Also it be recommended to create a Trigger handler to put your processing logic.