+ Start a Discussion
matthew.w.hampton.ax1837matthew.w.hampton.ax1837 

Need Help With Non-Selective Query

Good Evening:

I am in need of some help/suggestions here on how to make this query work. I am getting the dreded non-selective query error when I fire the trigger.

Summary

I have custom object MTU_Billing_Account__c that has a lookup field Account_Name__c that looks up to a custom object. Account_Name__c has a custom field called BTN__c. MTU_Billing_Account__c also has a lookup to the standard Accounts object called Account__c.

I have a second custom object called Billing_Account__c that has a field on it called Billing_Account_ID__c. This field is an External ID field. Billing_Account__c also has a lookup relationship to the standard Accounts object.

What I am looking to do is the following:

Take field Account_Name__r.BTN__c

Loop through Billing_Account__c records where Account_Name__r.BTN__c = Billing_Account__c.Billing_Account_ID__c (and a few other filters)

Updated Account__c on MTU_Billing_Account__c with Billing_Account__c.Account__r.ID.

Billing_Account__c has 770,815 records. Billing_Account_ID__c is an External ID and the key. I am out of filter options. 

Any suggestions would be greatly appreciated.

Thanks,

Matt

trigger MTUBillingAccountAccountUpdate on MTU_Billing_Account__c (before update, before insert)
{
    Map<String, MTU_Billing_Account__c> mtuAccount = new Map<String, MTU_Billing_Account__c>();
    for ( MTU_Billing_Account__c mtu2 : Trigger.new )
    {
        mtuAccount.put( mtu2.Account_Name__r.BTN__c, mtu2);
      
}

    Set<String> keys = mtuAccount.keyset();

    Map<String, Billing_Account__c> billingAccountMap = new Map<String, Billing_Account__c>();
    for(Billing_Account__c account1:[Select ID, Name, Account__r.ID, Billing_Account_ID__c from Billing_Account__c where Billing_Account_ID__c in : keys and Billing_Account_Status__c = 'Live' and Billing_System__c = 'CRIS'])
        billingAccountMap.put(account1.Billing_Account_ID__c, account1);
 
    List<MTU_Billing_Account__c> accountsToUpdate = new List <MTU_Billing_Account__c>();
        for(MTU_Billing_Account__c a : trigger.new) {
            if(billingAccountMap.containskey(a.Account_Name__r.BTN__c)) {
                    a.Account__c = billingAccountMap.get(a.Account_Name__r.BTN__c).Account__r.ID;
    }
update accountsToUpdate;
}
}


Sonam_SFDCSonam_SFDC
Hi Matthew,

As you mentioned that the Billing_Account__c object has 770,815 records - as per Salesforce best practice - the where clause needs to subset 10% or less of the data which might not be happening in your case.

You have te following options which you can try:
- You may need a custom index on the field which ill narrow down the record count - rach out to salesforce support if you wish to go down this path
- Divide the set of records according to date of creation(as Created date is indexed automatically) and add the result to a list and then use the list to loop in the FOR()