QueryException: Non-selective query - Production But Not Sandbox

Question

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 1

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

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)

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.

Answer 2

The other reason that could explain it is written in the error:

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

It seems possible that acctSellerIds does contain a null value, so your query will look for all Leads where seller_id__c is null or one of the ids from this list.

The possible solution could be by adding a check on Account_Id__c when you are adding them to the list:

if (acct.Items_w_Inv_in_Portal__c > 25 && acct. Account_id__c != null)

Answer 3

Your error message is telling you that you have a "non-selective query". If possible, you'll want to use the Query Optimization Tool" in the Developer Console to tweak your query to find indexed fields to that will make it more selective.

Essentially, it seems the fields you're using seller_id__c and Live_to_site__c aren't indexed. You'll probably need to file a case asking Salesforce to index at least one of these fields for you.

The reason it works for you in your sandbox is because you don't have enough data there for it to be "non-selective".

Refer to these two documents: Working with Very Large SOQL Queries and Improve performance with Custom indexes using Selective SOQL.

Answer 4

When amount of object records stored in Salesforce more than 200000 you should query it with using indexed fields. Please log a case to salesforce if you need to add index on the fields. You can read here about indexes: https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm