Batch Apex - Too many SOQL Queries 201 while reading CSV file to import data

Question

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?

Answer 1

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.