The error message is quite detailed, the query needs to be more selective so that less data is returned. There's a lot of into on that online, and an answer on how to solve this can't really be given without detailed knowledge of your object, its fields and specific query use case.
Yet, in general you should not use !=
in soql because it can't utilize any table indexes and will require a full table scan. To start optimizing your query, consider changing
GE_HQ_CONTACT_STATUS__c != 'Inactive'
into
GE_HQ_CONTACT_STATUS__c IN ('Active','Other Status','Yet Another Status')
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.
Selective SOQL Query Criteria
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.
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)
{
//put the comparison criteria for records to be filtered.
opptyIds.add(o.Opportunity_Name__c);
}
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__c,
Product__r.Id,Delegate_Admin__c,CurrencyIsoCode
FROM Order__c
WHERE Opportunity_Name__c IN:opptyIds];
if(Ord.size()>0)
{
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);
}
}
}
Best Answer
This error message tells you everything you need to know! Take a closer look at these component parts:
That line number should correspond to this query:
So actually it's the
Lead
object that has too many records and requires a selective query.Well that explains why your sandbox environment wouldn't throw this error while your production environment would. Unless you're working with a full sandbox, it seems unlikely to have 200k records there.
Check if your
Seller_Id__c
andLive_to_site__c
fields are indexed, as indexed fields are more selective to filter on. If they are not, you may need to open a case to get them indexed.To my eye this point seems the most likely cause for this error. If you have an
Account
record whereAccount_Id__c = null
, then youractSellerIds
collection will containnull
. For this reason, I might prefer to useSet<String>
to make removal simpler:This point could cause the error you observe on
update
, but notinsert
. If you had a notable percentage of yourLead
records with one commonSeller_Id__c
, then you would fall outside the Selectivity Thresholds outlined in thisQuery And Search Optimization Cheat Sheet
.