QueryException: Non-selective query – Production But Not Sandbox

I am getting the following error

Error:Apex trigger Lead_LTS_Update caused an unexpected exception, contact your administrator: Lead_LTS_Update: 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.Lead_LTS_Update: line 11, column 1

Actually it’s working fine in Sandbox, but throwing above error in prod. Think the error is due to too much of data(in Accounts) in prod while executing but we are not comparing all existing Accounts(please correct me if am wrong).

My goal is when there is a match between

  • account_id__c in Account object and seller_id__c in lead object
  • and Items_w_Inv_in_Portal__c > 25 in Account object,

it should update live_to_site field(picklist) in lead object from ‘No’ to ‘Yes’.

Here’s my trigger:

trigger Lead_LTS_Update on Account (after update, after insert)
 {

    List<String> acctSellerIds = new List<String>();
    for(Account acct : trigger.new) {

      if(acct.Items_w_Inv_in_Portal__c > 25)
      {
        acctSellerIds.add(acct.Account_id__c);
      }  
    }

    List<Lead> listLead = [SELECT Id, Live_to_site__c, seller_id__c FROM Lead WHERE seller_id__c IN :acctSellerIds AND Live_to_site__c = 'No'];
    for(Lead lead : listLead){
        lead.Live_to_site__c = 'Yes';
    }
    update listLead;
}

Answer

This error message tells you everything you need to know! Take a closer look at these component parts:

Trigger.Lead_LTS_Update: line 11, column 1

That line number should correspond to this query:

SELECT ... FROM Lead

So actually it’s the Lead object that has too many records and requires a selective query.

Non-selective query against large object type (more than 200000 rows)

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.

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:

Check if your Seller_Id__c and Live_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.

  1. The filter value includes null (for instance binding with a list that contains null)

To my eye this point seems the most likely cause for this error. If you have an Account record where Account_Id__c = null, then your actSellerIds collection will contain null. For this reason, I might prefer to use Set<String> to make removal simpler:

Set<String> sellerIds = new Set<String>();
for (Account record : trigger.new)
    if (record.Items_w_Inv_in_Portal__c > 25)
        sellerIds.add(record.Account_Id__c);
sellerIds.remove(null);
  1. 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)

This point could cause the error you observe on update, but not insert. If you had a notable percentage of your Lead records with one common Seller_Id__c, then you would fall outside the Selectivity Thresholds outlined in this Query And Search Optimization Cheat Sheet.

Attribution
Source : Link , Question Author : Laxman , Answer Author : Adrian Larson

Leave a Comment