0

I have a batch apex that reads a CSV file line by line, and then imports the data in it.

Some of the fields of the data are related to other Salesforce objects, so when I read a line from the CSV (using a WHILE cycle), I use some of its fields to look up these related Salesforce objects, and use their ID's when inserting.

Naturally, this caused a "Too many SOQL Queries 201" error, which is incredibly annoying.

Is there a good way to query these related record IDs BEFORE I start going thru the lines of the CSV file?

This is how my batch apex looks:

global void execute(Database.BatchableContext context, List<SObject> records) {
    List<List<String>> csvRecords = parseCSV.parseCSV(attachment.Body.toString(), true); 
    import (csvRecords);
}

global void import(List<List<String>> csvRecords) {
    ...
    List<MyObject__c> myItems = new List<MyObject__c>();
    while (csvRecords.size() > 0) {
        // Read the CSV values
        List<String> values = csvRecords.remove(0);
        ...
        String AccountId = getAccountIdFromRelatedId(values[4]);
        String ContactId = getContactIdFromRelatedId(values[5]);
        String ThirdId = getThirdIdFromRelatedId(values[6]);
        MyObject__c newItem = new MyObject__c();
        newItem.Account__c = AccountId;
        newItem.Contact__c = AccountId;
        newItem.Third__c = AccountId;
        newItem.SomeOtherField__c = ....
        ....
        myItems.add(newItem);
    }

    Database.UpsertResult[] srList = Database.upsert(myItems, false); 

    //error handling for the Upsert
    ...
}

global String getAccountIdFromRelatedId(String RelatedId) {
    if (RelatedId != '') {
        List<Account> accs = [SELECT Id From Account where Whatever =: RelatedId];
        if (accs.size() > 0) {
            return accs[0].Id;
        }
    }
    return null;
}

Now you can see that in my execute function I get the CSV file, and I call the import method with that List> object passed as a parameter, which I then read line by line, and on each line I make 3 SOQL queries to get some related record Ids.

Is there a way, where I can get all the IDs and attach it to the List> object before calling the import method?

4
  • do getAccountIdFromRelatedId , getContactIdFromRelatedId and getThirdIdFromRelatedId uses SQOL internally? Dec 12 '19 at 12:40
  • Yes! Good point, I forgot to include example of those in the main question. Will edit asap. Thank you Pranay
    – Laureant
    Dec 12 '19 at 12:43
  • The standard "loop over your data once to gather the things you want to query, then query, then loop over your data again" approach could be applied here too. Is there a reason why you couldn't do that?
    – Derek F
    Dec 12 '19 at 12:43
  • sfdcfox.blogspot.com/2017/03/… This will be a great point to aggregate data beforehand and then do mass update Dec 12 '19 at 12:45
2

You can reduce total SOQL queries to 3.

1) Store your values in Sets, for example:

Set<String> values4 = new Set<String>();
for (List<String> row_i : csvRecords) {
   values4.add(row_i[4]);
}

Make the same for other values.

2) make SOQL query to your objects with Id IN :values4, something like:

Map<Id, RelatedObject__c> idToRelated4Value = new Map<Id, RelatedObject__c>([SELECT Id, RelatedField__c FROM RelatedObject__c WHERE Id IN :values4]);

3) Run through your csvRecords again and instead of quering fields with SOQL, get them from maps you've created.

2
  • Thank you for the code example! I will try to use this, but I have a question first: can this structure also be used when the lookup is based on another field, and not an ID?
    – Laureant
    Dec 13 '19 at 9:29
  • @Laureant Yes, sure, just store that field in Map manually, make SOQL query selection into list, run through it and Map<your_field, your_object>. Dec 13 '19 at 9:51

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.