System.QueryException: Non-selective query against large object type (more than 200000 rows)

Question

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();
            }
        }
    }
}

Answer 1

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);
        }
    }
}

Answer 2

The LIMIT clause does not affect the selectivity of the query. To make the query selective, you must change the filters as described in the error message. More information is found in the Apex Developer Guide's Working with Very Large SOQL Queries article.

Answer 3

In addition to all other answers and I believe this to be the immediate issue:

use __c instead of __r.id. The __r may not be indexed where the __c is

Answer 4

Tossing my hat into the ring here as well, because why not?

As Santanu Boral pointed out, you have a query in a loop. That's not the cause of your current issues, but it will cause you trouble in the future if not removed now. Queries in loops is such a common topic that I'd be surprised if you haven't seen the suggested practice before. But, just in case, here's the general pattern

// Step 1: Declare a collection to hold Id values
Set<Id> targetIds = new Set<Id>();

// Step 2: Iterate over trigger.new (or some other collection of records) to gather Ids
for(MyObject__c rec: Trigger.new){
    targetIds.add(rec.my_lookup__c);
}

// Step 3: Perform your target query, and iterate over the results
for(OtherObject__c otherRec :[SELECT <some fields> FROM OtherObject__c WHERE Id IN :targetIds){
    // interesting code here
}

The thing that no one else has mentioned to this point is index skew. Massively oversimplifying things here, indexes work on an assumption of a uniform-ish distribution (or rather, that the distribution of records with particular indexed values is not super 'peaky').

When a particular indexed value is used in significantly more records than average, you have 'index skew'. Index skew reduces the benefit that indexes provide (a lightning-quick way to pull a subset of records in for futher consideration in a query). The point where you can say "yes, this object has index skew" is not very well defined. I, myself, have run into issues when an average Account is referenced by about 30 Assets and some outlier Accounts had between 10,000 and 100,000 Assets related to them.

At any rate, using the developer console and the query editor tab (with the query plan tool, which you can enable through: help -> preferences -> enable query plan (in the dev console window) ) is important in being able to figure out where your issues are (in the query plan, if you see any cost < 1.000, your query will generally be considered 'selective').

You can't bind variables in queries you run through the query editor, so things like WHERE OpportunityId = :myOpp.Id need to become WHERE OpportunityId = '006000000000001', and WHERE OpportunityId IN :oppIds needs to become WHERE OpportunityId IN ('006000000000001', '006000000000002', '006000000000003', ...)

That said, we may be able to avoid this issue altogether

I'll assume that your business rule is that an Opportunity is only related to a single Order__c record. Even if this isn't the case, you don't seem to be taking much care in selecting precisely which Order__c record you want to use here. So using the records from Trigger.new should suffice.

With that assumption in place, I look at your query and see that there is only 1 piece of "related data" that you're trying to query for, Account_Name__r.Customer_Profile_Pricebook__c (Product__c is equivalent to Product__r.Id, Opportunity__c is equivalent to Opportunity_Name__r.Id). Everything else is data stored on the Order__c record itself.

If you can create a formula field to pull the value from the related Account into your Order__c, then you can remove your query completely (and obviously replace ord[0] with o). No query = no worrying about selectivity or index skew.

Answer 5

I also had the same issue. The actual Query was not returning many records at max 10, but the Query looked like it can throw many records. As the code was in Trigger so Salesforce was not allowing it. So to resolve what I did was moved my Query and logic into the Future method so the query will run asynchronously and it worked for me.

Other solution may be converting you Where clause fields as indexed, you can do that by updating them as External Id.