Good practice in querying very large data from different objects inside batch process

Question

Good day everyone, I have created an apex class which will save data from csv to account and contact object in salesforce. The concept of this class is that it will read the column in my csv and will try to process the data.

If a particular data already exist in salesforce, it will ignore the data but if it doesn't exist it will create a new account or contact. Let's just say that I use the Account Name column to check if an account exist and Contact Email to check if contact exist(Identifier for Contact and Account can be one or more fields). To further explain below is some part of my code.

public with sharing class DI_batchProcessor implements Database.batchable<String>, Database.Stateful {

   public DI_batchProcessor(){
        // some data initialization here 
    }

    public Iterable<String> start(Database.batchableContext batchableContext)
    { 
      // here I return my csv data to be process.
       return new DI_Utility_RowIterator(MY_CSV_FILE);
    }

    public void execute(Database.BatchableContext batchableContext, List<String> scope) {
      List<Account> tempAccount = [Select Id,AccountName from Account];
      List<Contact> tempContact = [Select Id,Email from Contact];

      Map<String,Id> AllAccount = new Map<String,Id>();
      Map<String,Id> AllContact = new Map<String,Id>();

      for(Account acc : tempAccount) {
        AllAccount.put(acc.AccountName,acc.Id);
      }
      for(Contact con : tempContact) {
        AllContact.put(con.Email,con.Id);
      }

      // here I loop the data return from the CSV and check 
      // if the data exist using the map created above.
      // If exist ignore, else process for insertion.
    }

    public void finish(Database.BatchableContext bc) {
    }
}

Everything is working fine at first. But when I try to run this in an instance where account and contact has so many record, let say for example more 50000. I will encounter the problem "First error: Too many query rows: 50001". I just want to ask what are the possible remedy for this problem. What are the parts of my code that needs modification ? Is there any way to query all those rows in contact and account without hitting those limits ? Am I doing this wrong ? I already did some research in this, but still I didn't find a solution. Please help.

Answer 1

There are a couple different ways to approach this issue. My understanding is that your basic problem is de-duplication. You could certainly implement built in duplicate management and then allow partial success on your insert operations. IMO OOB de-duplication is strongly preferable. If you are trying to de-duplicate on more than one field, you may run into selectivity issues once you top 100k records.

If you prefer to stick to your current solution, the core issue is that you need a WHERE clause when dealing with large tables. The question is how to build it. In this case I would collect all your Account.AccountName and Contact.Email values as a first step. If you implement Database.Stateful you could do something like:

final Map<String, Contact> contacts = new Map<String, Contact>();
final Map<String, Account> accounts = new Map<String, Account>();

public void execute(Database.BatchableContext bc, List<String> scope)
{
    // populate contacts map
    // populate accounts map
}

public void finish(Database.BatchableContext bc)
{
    for (Contact contact : [
        SELECT Email FROM Contact WHERE Email IN :contacts.keySet()
    ]){
        contacts.remove(contact.Email);
    }
    for (Account account : [
        SELECT AccountName FROM Account WHERE AccountName IN :accounts.keySet()
    ]){
        accounts.remove(account.AccountName);
    }
    insert contacts;
    insert accounts;
}

If you run into limits issues in the finish method, you could work through them by delegating your DML operations via Queueable. Note you can only execute one job from within another job.

To perform the above with more fields, you will probably be best off implementing a complex key. You can add as many key components as you like, and then map your Contact and Account records by this key. An example might look like:

public with sharing class ContactCsvKey
{
    public static Set<String> pluckEmails(Set<ContactCsvKey> keys)
    {
        Set<String> emails = new Set<String>();
        for (ContactCsvKey key : keys) emails.add(key.email);
        return emails;
    }
    public static Set<String> pluckLastNames(Set<ContactCsvKey> keys)
    {
        Set<String> lastNames = new Set<String>();
        for (ContactCsvKey key : keys) lastNames.add(key.LastName);
        return lastNames;
    }

    public final String email;
    public final String lastName;
    public ContactCsvKey(Contact contact)
    {
        this.email = contact.email;
        this.lastName = contact.LastName;
    }
    public Integer hashCode()
    {
        return (31 * email.hashCode()) ^ lastName.hashCode();
    }
    public Boolean equals(Object comparison)
    {
        if (comparison instanceOf ContactCsvKey)
        {
            ContactCsvKey that = (ContactCsvKey)comparison;
            return this.email == that.email && this.lastName == that.lastName;
        }
        return false;
    }
}

You can use pass your Map.keySet() results into the pluck methods above and merge the values into your query filters.

Answer 2

This is what you should do

public void execute(Database.BatchableContext batchableContext, List<String> scope) { 
    //Iterate over your scope and create a List of Account Name and List of Contact Emails 
    //Update your account query to filter by Account Names 
    List<Account> tempAccount = [Select Id,AccountName from Account Where Name=:accountNameList Limit :accountNameList.size()]; 
    List<Contact> tempContact = [Select Id,Email from Contact Where Email=:contactEmailList Limit :contactEmailList.size()]; 

    Map<String,Id> AllAccount = new Map<String,Id>(); 
    Map<String,Id> AllContact = new Map<String,Id>(); 

    for(Account acc : tempAccount) { 
        AllAccount.put(acc.AccountName,acc.Id); 
    } 

    for(Contact con : tempContact) { 
        AllContact.put(con.Email,con.Id); 
    } 


    // here I loop the data return from the CSV and check 

    // if the data exist using the map created above. 

    // If exist ignore, else process for insertion. 
}