10

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;
}
  • If any of the below answers addressed your problem, please consider accepting one by clicking on the check mark/tick to the left of the answer, turning it green. This marks the question as resolved to your satisfaction, and awards reputation both to you and the person who answered. If you have >= 15 reputation points, you may also upvote the answer if you wish. There is no obligation to do either. – Adrian Larson Feb 8 '17 at 3:25
8

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.

  • 1
    +1 for using Set which will also automatically deduplicate duplicate Ids ;) – Novarg Jan 13 '17 at 13:58
  • The query is on the Lead object, but the trigger updates the Account object. I think he just phrased it incorrectly. – crmprogdev Jan 13 '17 at 14:08
  • 1
    This QueryException is thrown when a query is run. The only query in the OP is on Lead. Why does it matter what the trigger object is? That seems irrelevant. – Adrian Larson Jan 13 '17 at 14:09
  • I was referring to the comments you made prior to editing your answer. In essence, I didn't think it mattered either, but you seemed to place a lot of emphasis on it. – crmprogdev Jan 13 '17 at 14:13
6

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)
  • 1
    I didn't even see that part of the error message. Nice catch! Actually the error message lists data skew too which is great. It really couldn't be more descriptive or helpful in this case. – Adrian Larson Jan 13 '17 at 14:01
  • Thank you Novarg looks like it's one of the issue which I didn't observed – Laxman Jan 13 '17 at 14:10
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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.