3

I'm looking for a way to automate the process of updating emails of all active contacts in sandboxes (mask them with the .dev or .test). Number of records to update after filtering: 900,000

Things I've tried:

  1. Build a batch job to update the emails, works in our dev instance, but fails in out TEST instance; error: CPU time limit, there really isn't anything that takes up CPU time limit, checked on dev console, the DB call takes too long for 900,000 records and I'm not doing anything complicated in my code.
  2. Tried using JsForce to create a batch job and the bulk API keeps giving me problems.

I need to find a way to automate updating contact emails, example: contact email: [email protected] , I'll be updating to [email protected] in the dev instance for 900,000 records

Was hoping to automate this as we refresh sandboxes pretty often and have many instances.

Thanks!

Here is the code:

global class MassUpdateContactEmails implements Database.Batchable<sObject>
{  
    global final String query;
    global final String SPECIAL_CHAR_PATTERN = '[^\\w\\*]';

    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        return Database.getQueryLocator('SELECT Id, Email FROM Contact WHERE Email != NULL AND FirstName != NULL');
    }

    global void execute(Database.BatchableContext BC, List<SObject> scope)
    {
        List <Contact> lstContacts = new list<Contact>();

        String orgAbbreviation = '.' + UserInfo.getUserName().substringAfterLast('.');

        for(Sobject s : scope)
        {
            Contact a = (Contact) s;
            if(a.Email != NULL && !checkSpecialCharacter(a.Email)){
                if(a.Email.countMatches(orgAbbreviation) == 2){
                    a.Email = a.Email.replace(orgAbbreviation + orgAbbreviation, orgAbbreviation);
                }else if(a.Email.countMatches(orgAbbreviation) == 3){    
                    a.Email = a.Email.replace(orgAbbreviation + orgAbbreviation + orgAbbreviation, orgAbbreviation);
                }else if(a.Email.countMatches(orgAbbreviation) == 4){
                    a.Email = a.Email.replace(orgAbbreviation + orgAbbreviation + orgAbbreviation + orgAbbreviation, orgAbbreviation);
                }else if(a.Email.countMatches(orgAbbreviation) == 0){
                    a.Email = a.Email + orgAbbreviation;
                }
                lstContacts.add(a);    
            }
        }
        Update lstContacts;
    }

    global Boolean checkSpecialCharacter(String email){
        Boolean isSpecial = false;
        Pattern p = Pattern.compile(SPECIAL_CHAR_PATTERN);
        Matcher pm = p.matcher(email);
        while (pm.find()) {
            if(pm.group(0) != '.' && pm.group(0)!= '@'){
                isSpecial = true;   
            }
        }

        return isSpecial;
    }

    global void finish(Database.BatchableContext BC)
    {

    }
}
3
  • 1
    with batch this can be done easily. Can you post your batch class code?
    – Himanshu
    Commented May 23, 2016 at 18:51
  • Are there any triggers on the contact that could be taking up time ? Try disabling them when running jobs?
    – addy
    Commented May 23, 2016 at 20:57
  • 1
    I suspect you will either need to reduce the batch scope size or optimize the checkSpecialCharacter method. Reducing the size of each batch iteration would certainly help with the CPU limit. Compiling the Regex pattern with each call to checkSpecialCharacter is most likely slowing things down. Commented May 23, 2016 at 21:20

1 Answer 1

1

If the problem is occurring within the execute method, then you can try reducing the batch scope to process fewer records. See Using the Database.executeBatch Method to Submit Batch Jobs and Database.executeBatch

Integer scope = 100;
MassUpdateContactEmails theBatch = new MassUpdateContactEmails();
ID batchprocessid = Database.executeBatch(theBatch, scope);

Another thing to consider with the CPU processing time in the execute method is the use of regular expressions. You could try having one instance of Pattern p so that it doesn't need to be recreated for every call to checkSpecialCharacter.

Rather than checking a.Email.countMatches(orgAbbreviation) in each if condition, evaluate it once and keep it in a variable.


The other problem might be getting the batch job started and the selectivity of the SOQL query. The != null operations aren't supported operators against an index for the query plan. See Developing Selective Force.com Queries through the Query Resource Feedback Parameter Beta

3
  • I've tried it with batch scope 100, problem persists.
    – Sham PTC
    Commented May 24, 2016 at 14:19
  • I dont think the Pattern is a problem because I removed the logic for the pattern search and try to run it and problem persists
    – Sham PTC
    Commented May 24, 2016 at 14:19
  • By the way thanks @Daniel Ballinger for suggesting Explain Query link, I'm trying to debug my query and I know its the query that is causing the problem because I observed in dev console that the DB transaction causes the CPU time limit error , its not the code. However, this bring me to the question where I'm not able to optimize the query any better, since it retrieves close to 900k records and I've observed that more the records retrieved, slower the query (observed through the Explain Query tool), any suggestions on how to optimize a query that you know will retrieve 900k records??
    – Sham PTC
    Commented May 24, 2016 at 15:41

You must log in to answer this question.

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