Apex Bulkification -- Too many SOQL queries: 101

Question

I have an APEX Class that I have bulkified but is still giving me trouble with Batches of 200 Records. I was hoping to get assistance in making the APEX more efficient, any assistance/direction would be greatly appreciated.

I have a process Builder that Fires an Apex Class on the Lead Object when one of the following is met:

        ISCHANGED([Lead].MobilePhone),
        ISCHANGED([Lead].Phone),
        ISCHANGED([Lead].Home_Phone__c),
        ISCHANGED([Lead].Alternate_Phone__c),
        ISCHANGED([Lead].Alternate_Phone_2__c),
        ISNEW()

The Process Builder Passes the [Lead].Id to the APEX Class.

public class UpdatePhoneTrimFields {

    @InvocableMethod
    public static void invocableTrimPhoneNumbers(List<String> ids){

        String objectType = ids[0];

        if(objectType.startsWithIgnoreCase('00Q')){

            updateBulkifiedLeadPhoneTrimFields(ids); 
        }
        else if(objectType.startsWithIgnoreCase('003')){

            updateBulkifiedContactPhoneTrimFields(ids);
        } 
        else if(objectType.startsWithIgnoreCase('001')){

            updateBulkifiedAccountPhoneTrimFields(ids);
        } 
        else {

            system.debug('Unknown Record Type - Cannot Proceed');
        }

    }

    private static Decimal normalizePhoneNumber(String rawPhoneNumber){
        try{
            if(rawPhoneNumber != null){
                return Decimal.ValueOf(rawPhoneNumber.replaceAll('[^0-9]', ''));
            }
        } catch (Exception e){

            System.debug('An Unexpected error occurred trimming a phone number: ' + e.getMessage());
        }
        return null;
    }

    private static void updateBulkifiedLeadPhoneTrimFields(List<String> ids){
        
        system.debug('Attempting to Normalize the Phone numbers for a list of ' + ids.size() + ' leads.');

        list<Lead> leadsToUpdate = new List<lead>(); 

        for(Id recordId: ids){
            
            String stringRecordId = Id.ValueOf(recordId);

            if(stringRecordId.startsWithIgnoreCase('00Q')){
                try {
                    Lead l = [SELECT Phone, MobilePhone, Home_Phone__c, Alternate_Phone__c, Alternate_Phone_2__c, Phone_Trim__c, MobilePhone_Trim__c, Home_Phone_Trim__c, Alternate_Phone_Trim__c, Alternate_Phone_2_Trim__c FROM Lead WHERE Id =: recordId];

                    l.Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Phone);
                    l.MobilePhone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.MobilePhone);
                    l.Home_Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Home_Phone__c);
                    l.Alternate_Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Alternate_Phone__c);
                    l.Alternate_Phone_2_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Alternate_Phone_2__c);

                    system.debug('Adding Lead to List to Normalize: '+ l);
                    leadsToUpdate.add(l);
                }catch(Exception e){
                    System.debug('An Unexpected error occurred normalizing Lead Phone Fields: ' + e.getMessage());
                }
            } else {
                System.debug('Cannot Normalize Phone for this record: ' + recordId + '. Not a Lead');
            }   
        }

        try {
            system.debug('Updading a list of Leads with ' + leadsToUpdate.size() + ' records');
            system.debug(leadsToUpdate);
            update leadsToUpdate; 
        } catch (Exception e) {
            System.debug('An Unexpected error occurred normalizing Lead Phone Fields: ' + e.getMessage());
        }
    }

    private static void updateBulkifiedContactPhoneTrimFields(List<String> ids){

        system.debug('Attempting to Normalize the Phone numbers for a list of ' + ids.size() + ' Contacts.');

        list<Contact> contactsToUpdate = new List<Contact>();

        for(Id recordId: ids){

            String stringRecordId = Id.ValueOf(recordId);

            if(stringRecordId.startsWithIgnoreCase('003')){
                try{
                    Contact c = [SELECT Phone, MobilePhone, HomePhone, OtherPhone, Phone_Trim__c, MobilePhone_Trim__c, HomePhone_Trim__c, OtherPhone_Trim__c FROM Contact Where Id =: recordId];

                    c.Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(c.Phone);
                    c.MobilePhone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(c.MobilePhone);  
                    c.HomePhone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(c.HomePhone);
                    c.OtherPhone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(c.OtherPhone);

                    system.debug('Adding Contact to List to Normalize: '+ c);
                    contactsToUpdate.add(c);

                }catch(Exception e){
                    System.debug('An Unexpected error occurred normalizing Lead Phone Fields: ' + e.getMessage());
                }
            }else {
                System.debug('Cannot Normalize Phone for this record: ' + recordId + '. Not a Contact');
            }
        }

        try {
            system.debug('Updating List of Contacts with ' + contactsToUpdate.size() + ' records');
            system.debug(contactsToUpdate);
            update contactsToUpdate;
        } catch (Exception e) {
            System.debug('An Unexpected error occurred trimming Contact Phone Fields: ' + e.getMessage());
        }
    }

    private static void updateBulkifiedAccountPhoneTrimFields(List<String> ids){

        system.debug('Attempting to Normalize the Phone numbers for a list of ' + ids.size() + ' Accounts.');

        list<Account> accountsToUpdate = new List<Account>();

        for(Id recordId: ids){

            String stringRecordId = Id.ValueOf(recordId);

            if(stringRecordId.startsWithIgnoreCase('001')){
                try{
                    Account a = [SELECT Phone, Alternate_Phone__c, Alternate_Phone_2__c, Phone_Trim__c, Alternate_Phone_Trim__c, Alternate_Phone_2_Trim__c FROM Account WHERE Id =: recordId];
            
                    a.Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(a.Phone);
                    a.Alternate_Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(a.Alternate_Phone__c);
                    a.Alternate_Phone_2_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(a.Alternate_Phone_2__c);
            
                    system.debug('Adding Account to List to Normalize: '+ a);
                    accountsToUpdate.add(a);
                }catch(Exception e) {
                    System.debug('An Unexpected error occurred normalizing Account Phone Fields: ' + e.getMessage());
                }
            } else {
                System.debug('Cannot Normalize Phone for this record: ' + recordId + '. Not an Account');
            }
        }

        try {
            system.debug('Updating List of Accounts with ' + accountsToUpdate.size() + ' records');
            system.debug(accountsToUpdate);
            update accountsToUpdate;
        } catch (Exception e)   {
            System.debug('An Unexpected error occurred trimming Account Phone Fields: ' + e.getMessage());
        }      
    }

}

This Apex Class can be fired from three process builders related to the Lead/Account/Contact Object. So the Process builder calls an @invocablemethod that determines the Object Type. Based on the Object Type it will then call a private method.
This Private Method is updateBulkifiedLeadPhoneTrimFields. It loops through the array/list of records - looks up the appropriate information updates the record and pushes it to a new array. Once the Loop is done we update the list of records together.
The updates that are being done are the same across all of the methods - this updates to the phonetrim fields (normalize the phone fields). The Method Called is normalizePhoneNumber

updateBulkifiedLeadPhoneTrimFields

private static void updateBulkifiedLeadPhoneTrimFields(List<String> ids){

        system.debug('Attempting to Normalize the Phone numbers for a list of ' + ids.size() + ' leads.');

        list<Lead> leadsToUpdate = new List<lead>(); 

        for(Id recordId: ids){

            String stringRecordId = Id.ValueOf(recordId);

            if(stringRecordId.startsWithIgnoreCase('00Q')){
                try {
                    Lead l = [SELECT Phone, MobilePhone, Home_Phone__c, Alternate_Phone__c, Alternate_Phone_2__c, Phone_Trim__c, MobilePhone_Trim__c, Home_Phone_Trim__c, Alternate_Phone_Trim__c, Alternate_Phone_2_Trim__c FROM Lead WHERE Id =: recordId];

                    l.Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Phone);
                    l.MobilePhone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.MobilePhone);
                    l.Home_Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Home_Phone__c);
                    l.Alternate_Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Alternate_Phone__c);
                    l.Alternate_Phone_2_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Alternate_Phone_2__c);

                    system.debug('Adding Lead to List to Normalize: '+ l);
                    leadsToUpdate.add(l);
                }catch(Exception e){
                    System.debug('An Unexpected error occurred normalizing Lead Phone Fields: ' + e.getMessage());
                }
            } else {
                System.debug('Cannot Normalize Phone for this record: ' + recordId + '. Not a Lead');
            }   
        }

        try {
            system.debug('Updading a list of Leads with ' + leadsToUpdate.size() + ' records');
            system.debug(leadsToUpdate);
            update leadsToUpdate; 
        } catch (Exception e) {
            System.debug('An Unexpected error occurred normalizing Lead Phone Fields: ' + e.getMessage());
        }
    }

normalizePhoneNumber

    private static Decimal normalizePhoneNumber(String rawPhoneNumber){
        try{
            if(rawPhoneNumber != null){
                return Decimal.ValueOf(rawPhoneNumber.replaceAll('[^0-9]', ''));
            }
        } catch (Exception e){

            System.debug('An Unexpected error occurred trimming a phone number: ' + e.getMessage());
        }
        return null;
    }

When running in Batches of 200 I am receiving the following error: An Apex error occurred: System.LimitException: Too many SOQL queries: 101 ---

Question(s)

Is there a way I can make this more efficient? If So, How? My ultimate goal would be to have this be able to run in batches of 200 records.

Update

I made the following change and it looks to have worked. Any recommendations on any further updates?

        
        system.debug('Attempting to Normalize the Phone numbers for a list of ' + ids.size() + ' leads.');

            List<Lead> leadsToUpdate = [SELECT Id, Phone, MobilePhone, Home_Phone__c, Alternate_Phone__c, Alternate_Phone_2__c, Phone_Trim__c, MobilePhone_Trim__c, Home_Phone_Trim__c, Alternate_Phone_Trim__c, Alternate_Phone_2_Trim__c FROM Lead WHERE Id =: ids]; 

            for(Lead l: leadsToUpdate){
                try{
                    l.Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Phone);
                    l.MobilePhone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.MobilePhone);
                    l.Home_Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Home_Phone__c);
                    l.Alternate_Phone_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Alternate_Phone__c);
                    l.Alternate_Phone_2_Trim__c = UpdatePhoneTrimFields.normalizePhoneNumber(l.Alternate_Phone_2__c);
                } catch(Exception e){
                    System.debug('An Unexpected error occurred normalizing Lead with Id: ' + l.Id + '. Error' + e.getMessage());
                }
            }
            system.debug('Updading a list of Leads with ' + leadsToUpdate.size() + ' records');
            system.debug(leadsToUpdate);
            update leadsToUpdate; 
    }

Derek F · Accepted Answer · 2020-10-05 20:09:51Z

The issue is that you have queries inside of a loop.

In your attempt at bulkification, we see this

for(Id recordId: ids){
    String stringRecordId = Id.ValueOf(recordId);

    if(stringRecordId.startsWithIgnoreCase('00Q')){
        try {
            // This query is inside of a loop
            // That's a problem
            Lead l = [SELECT Phone, MobilePhone, Home_Phone__c, Alternate_Phone__c, Alternate_Phone_2__c, Phone_Trim__c, MobilePhone_Trim__c, Home_Phone_Trim__c, Alternate_Phone_Trim__c, Alternate_Phone_2_Trim__c FROM Lead WHERE Id =: recordId];
   // ...

Since you only have a single filter in your query, it's easy to pull out of the loop using the standard iterate-gather-query approach.

// First, set up a collection to hold the data you want to query against
Set<Id> recordIds = new Set<Id>();

// Iterate over your records to gather the data
for(SomeObject__c someObj :trigger.new){
    // gather your data
    recordIds.add(someObj.Relationship_Field__c);
}

// Query and store for later use
Map<Id, SObject> sobjMap = new Map<Id, SObject>([SELECT Id, Field1__c, Field2__c FROM OtherObject__c WHERE Id IN :recordIds);

// Iterate over the records again
// This time, you can pull the related data from the map you made earlier
for(SomeObject__c someObj :trigger.new){
    OtherObject__c relatedRecord = sobjMap.get(someObj.Relationship_Field__c);

    // do other work
}

You probably don't need to go through that much trouble though, since you're only working with 1 SObject at a time, there shouldn't be anything stopping you from just simply feeding a for loop with a query that uses your ids list directly.

for(Lead myLead :[SELECT Id, Field2__c, Field3__c FROM Lead WHERE Id IN :ids]){
    // do work
}

Other than that...

you may consider using the getSObjectType() method of the Id class to make your SObject detection more readable
- e.g. SObjectType sobjType = ((Id)ids[0]).getSObjectType(); and if(sobjType == Lead.SObjectType)
Swallowing exceptions is generally a bad thing. It makes it harder to detect when you have an issue, and makes it harder to debug the issue once you know you have one. Consider eliminating all of your try/catch statements

Stack Exchange Network

Apex Bulkification -- Too many SOQL queries: 101

1 Answer 1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
apex
process-builder
bulkification
bulk
.

Hot Network Questions

Apex Bulkification -- Too many SOQL queries: 101

1 Answer 1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged apexprocess-builderbulkificationbulk.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
apex
process-builder
bulkification
bulk
.