2

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. enter image description here

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.

0

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.

| improve this answer | |
  • hmmm. It seems like this is a good idea. On the other hand, what if I have more than one field as an identifier ? As what i've added in my question, the identifier can be one or more fields. Let say for example in contact I will use Last Name and Email as identifier. I think it would not be ok if I will say in query "SELECT Email FROM Contact WHERE Email IN :contactsEmail.keySet() AND lastName IN :contactslastName.keySet() ". – Hope Feb 9 '16 at 6:07
  • That's why I recommend OOB de-duplication. The logic would become much more complex with multiple keys and almost certainly run into timeouts. – Adrian Larson Feb 9 '16 at 6:09
  • @Hope I added an idea around how you might approach this with more keys. – Adrian Larson Feb 9 '16 at 6:36
  • hmmmm. it seems like I didn't follow the connection between the this class and my query. can you give me a sample code if it is ok ? – Hope Feb 9 '16 at 7:14
-1

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. 
}  
| improve this answer | |
  • You realize they have to iterate every row to get those lists, yes? It seems to me this answer is far from complete. – Adrian Larson Feb 9 '16 at 4:13
  • He is already querying all accounts and contacts and processing them in a map. Also, this is a batch process and only limited records will be passed to the execute method. – Salesforcesmarty Feb 9 '16 at 4:16
  • @Salesforcesmarty I think this code will not get all those contacts and accounts . – Hope Feb 9 '16 at 4:38
  • most important point in answer is the scope-driven filter values which solves the 50,000 issue; note however there will be case sensitivity issues with the map keys versus the SOQL results – cropredy Feb 9 '16 at 4:43
  • @Hope, can you point out any specific scenario? Why do you even need all accounts and contacts? – Salesforcesmarty Feb 9 '16 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.