1

I am getting 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 a 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) Exception while trying to convert a Lead.

The exception occurred in the last line of the below code,

    List<Opportunity> oppsToProcess = new List<Opportunity>();
    Set<String> prospectIds = new Set<String>();
    for (Opportunity opp: newOpps) {
        if (opp.ConId__c != null) {
            opp.Prospect_Id__c = opp.ConId__c;
            opp.ConId__c = null;
        }
        
        if (!opp.IsClosed && opp.Prospect_Id__c != null && opp.SAL_Date__c != null) {
            oppsToProcess.add(opp);
            prospectIds.add(opp.Prospect_Id__c);
        }
    }

    if (oppsToProcess.isEmpty()) { return; }

    Set<Id> contactIds = new Set<Id>();
    String soql = 'SELECT ' + String.join(leadFields, ', ') + ' FROM Lead WHERE ProspectId__c IN :prospectIds';
    Map<String, Lead> mapLead = new Map<String, Lead>();
    for (Lead lead: (List<Lead>)Database.query(soql)) {
    }
  1. My org has 150000 Lead records only, In the worst case, it's less than 200000 record limit.
  2. I have another concern about the null check (ConId__c, Prospect_Id__c are Text fields), But I have checked that the null and the empty ('') acts in the same way.

Did I miss anything over there in my code?

  • 2
    To be sure, use the String.isNotBlank method instead of not null check. – Phil W Jul 10 at 4:48
  • Talk to the support. We had an issue last time where we were filtering on the ID with non nulls and were getting the same error. We raised a case with Salesforce after verifying a lot of things and they fixed something on their end. – manjit5190 Jul 10 at 6:36
  • @manjit5190, Are you referring to the custom indexing for the field by conducting Salesforce Support? – Hariprasath Jul 11 at 6:28
  • 1
    No, the field we were using were already indexed and we were sure that the record ID using which we were querying was not null and we were supposed to get only one record. I dont know what support did but it got resolved. That may or may not be the case with you but its doesn't hurt to ask support. – manjit5190 Jul 11 at 9:00
0

As per the following document

https://help.salesforce.com/articleView?id=000325257&type=1&mode=1

  1. Get the SOQL query and replace the list of fields with the count() operator:

    SELECT Count() FROM Lead WHERE ProspectId__c IN :prospectIds

  2. Check the number of active records in the table:

    Select count() from Lead

    #of records: totalLeads (for eg. take 200000)

    Threshold for standard indexes: 30% * 200000 (totalLeads) = 60000

    Threshold for custom indexes: 10% * 200000 (totalLeads) = 20000

  3. The only filter in the WHERE clause is ProspectId__c, so we will only consider the filter on this field.

    SOQL query: Select count() from Lead where ProspectId__c IN :prospectIds

    For example if the above SOQL returns 30000 records

    #of records 30000 > 20000 (threshold for custom indexes)

    Is the used filter selective? NOT

  4. If ProspectId__c is custom indexed, then it would be a selective filter if the total number of records returned by SOQL is less than 20000

For more understanding on Make SOQL query selective, check the above mentioned link

| improve this answer | |
  • The query cannot return more than 10 records. The problem maybe because of the null check in the String fields. – Hariprasath Jul 10 at 5:33
0

The issue had arisen because the ProspectId__c field was not indexed, So, If we run into a "Non-selective query against large object type" error when performing a SOQL query or it takes a long time to run, we may need to make it selective and if it filters by a Text field

  1. We need to make it as External ID.
  2. Or we can get the Salesforce support to make this field indexed from Salesforce.
| improve this answer | |

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.