Non selective query against large object type morethan 20,000 rows

Question

I am facing the error in trigger code:

Code in that place:

public void duplicateBusiness() {

set<id> bId = new set<id>();
Map<id,string> duplicateCheck = new Map<id,string>();
Map<string,BusinessDetail__c> DupMap = new Map<string,BusinessDetail__c> ();

for (BusinessDetail__c b : newList)
{
    bId.add(b.id);
    duplicateCheck.put(b.id,b.DuplicateBusinessCheck__c);
}

List<BusinessDetail__c > StaActiList = new List<BusinessDetail__c >();
StaActiList  = [Select Name,DuplicateBusinessCheck__c, BusinessUnit__c,BusinessAlias__c from BusinessDetail__c where DuplicateBusinessCheck__c IN: duplicateCheck.values()];  


for(BusinessDetail__c b : StaActiList)
{
    DupMap.put(b.DuplicateBusinessCheck__c,b);    

}        if(!StaActiList.isEmpty())
{
    for (BusinessDetail__c b : newList)  
    {

        if(DupMap.containsKey(b.DuplicateBusinessCheck__c))
        {
            if(b.id != DupMap.get(b.DuplicateBusinessCheck__c).id)
            {
                b.addError('This account already has a business detail associated to it for this business. Duplicate business details are not allowed.');   

            }
        }
    }   
}

}

Error Query:

StaActiList = [Select Name,DuplicateBusinessCheck__c, BusinessUnit__c,BusinessAlias__c from BusinessDetail__c where DuplicateBusinessCheck__c IN: duplicateCheck.values()];

Error Message:

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 (fo

could u please anyone help me how to rectify this error.

Answer 1

When querying against a large object type, your query must be selective - meaning it must be filtered using an indexed field. In your code, you're using a custom formula field which is not an indexed field by default.

You can open a case with Salesforce support and ask to index that specific field, provided it's a deterministic formula field.

The following rules have to be true in order to index a Formula Field:

• The formula contains fields from a single object only (not relationship fields).
• The formula field doesn't reference any non-deterministic functions (e.g. SYSDATE).
• The formula field doesn't reference any non-supported fields for including in indexes. This list isn't documented anywhere specifically (there are lots of special cases), but in Spring
  12(176), createdById was non-supported, but in Summer 12 (178), it is supported. Same story for CreatedDate.
• The formula field doesn't contain references to Primary Keys (e.g Id)
• The formula field does not use TEXT() function
• If the formula references any Lookup fields, the field must not have the option "What to do if the lookup record is deleted?" set to "Clear the value of this field."

If your formula field is non-deterministic per the criteria above, you'll have to get creative and find an indexed field to filter your query by.

Answer 2

please try

for (BusinessDetail__c b : newList)
{
    bId.add(b.id);
    duplicateCheck.put(b.id,b.DuplicateBusinessCheck__c);
}

change to

for (BusinessDetail__c b : newList) 
{
    if (String.isNotBlank(b.DuplicateBusinessCheck__c)) {
        bId.add(b.id);
        duplicateCheck.put(b.id,b.DuplicateBusinessCheck__c);
    }
}

Thanks