Best way to convert SOQL when doing an import of data

Question

I was wondering what would be the best way to convert the below code ,which is importing price book entries, so it doesn't hit the governor limits.

   for (Integer i = 1; i < csvFileLines.size(); i++) {
                PricebookEntry prEnt = new PricebookEntry() ;
                 String[] csvRecordData = csvFileLines[i].split(',');
                 String csvName = csvRecordData[0];
                 ID productID = [SELECT Id from Product2 WHERE Name = :csvName LIMIT 1].Id;
                 prEnt.Product2 = productID;
                 priceEntry.add(prEnt); 
             }

I thought of using a set like the one below, but i am unsure on how to then find the id's based on the variable csvnames from the set.

             Set<String> productIDs = new Set<String>();
             for (Product2 p : [select Id, Name from Product2 where Name in :csvNames]) {
              productIDs.add(p.Id);
              productIDs.add(p.Name);
            }

All help is appreciated.

Answer 1

You should not put your query in the loop.

You need to collect the product names from the first column of the CSV file, and put them in a set, then just run one query by the product names set and store the query result by product name as the key (using a map):

Set<String> productNames = new Set<String>();
for (Integer i = 1; i < csvFileLines.size(); i++) {
    String[] csvRecordData = csvFileLines[i].split(',');
    String productName = csvRecordData[0];
    productNames.add(productName);
}

Map<String, Product2> productByName = new Map<String, Product2>();
for (Product2 prod : [SELECT Name FROM Product2 WHERE Name IN :productNames]) {
    productByName.put(prod.Name, prod);
}

Then you can retrieve the product ID

productByName.get(csvRecordData[0]).Id;

Answer 2

You should not have soql query inside loop, so you right that it should be taken outside the loop. Seems you need to have map from product name to Id

map<id, String> m_products = new map<Id, String>();

for (Product2 p : [select Id, Name from Product2 where Name in :csvNames]) { m_products.put(p.Name, p.Id); }

and then in when creating the price book entry: if(m_products.containsKey(csvRecordData[0])){ prEnt.Product2 = m_products.get(csvRecordData[0]); }