0

The below code runs 57 batches out of 98 and then says the below message: enter image description here

global class MDUSquadRawDataBatchTest implements Database.Batchable<sObject>, Database.Stateful {  
    List<Address_Master__c> addressList = new List<Address_Master__c>();
    Set<String> addresses = new Set<String>();

    // Start Method
    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator('SELECT Street_Address__c,City_Name__c FROM MDU_Squad_Raw_Data__c');
    }   

    // Execute method
    global void execute(Database.BatchableContext BC, List<MDU_Squad_Raw_Data__c> rawData) {        
        for(MDU_Squad_Raw_Data__c mduRawData: rawData) {
            List<MDU_Squad_Raw_Data__c> addressData = new List<MDU_Squad_Raw_Data__c>([SELECT Street_Address__c,City_Name__c,Province_Code__c,Postal_Code__c,Internet_Service__c,Video_Service__c,Phone_Service__c FROM MDU_Squad_Raw_Data__c WHERE Street_Address__c=:mduRawData.Street_Address__c AND City_Name__c=:mduRawData.City_Name__c]);
            String fullAddress = addressData[0].Street_Address__c+' '+addressData[0].City_Name__c+' '+addressData[0].Province_Code__c+' '+addressData[0].Postal_Code__c;

            Address_Master__c theAddress = new Address_Master__c();
            if(!addresses.contains(fullAddress.substringBeforeLast(' '))) {
                theAddress.Name = addressData[0].Street_Address__c;
                theAddress.City_Name__c = addressData[0].City_Name__c;
                theAddress.Province_Code__c = addressData[0].Province_Code__c;
                theAddress.Postal_Code__c = addressData[0].Postal_Code__c; 
                fullAddress = addressData[0].Street_Address__c+' '+addressData[0].City_Name__c+' '+addressData[0].Province_Code__c+' '+addressData[0].Postal_Code__c;
                theAddress.Full_Address_Ext_Id__c = fullAddress;

                addresses.add(fullAddress.substringBeforeLast(' '));
                addressList.add(theAddress); 
            }                                                     
        }            
        Database.Upsert(addressList, Address_Master__c.Fields.Full_Address_Ext_Id__c, true);
    }
    // Finish Method    
    global void finish(Database.BatchableContext BC) {

    } 
}
7
  • As I mentioned on the comments in between the code on my original post, I tried using SOQL with count() to acquire all the numbers instead of going through the second for loop again, it said "Too many SOQL Queries" and failed the batch processing. – sumchans Mar 16 '20 at 3:40
  • 1
    String.toLowerCase() not necessary. == is case insensitive. for(...) for(...) if(...) can be fixed with a Map. Do not use dynamic string query when inline query will suffice. Aggregate data together and query it all at once--a query inside a loop is almost always the wrong answer. I'm not going to rewrite this all, there's a lot going on, but all you need to know is in the answers already here on SFSE if you do some searches. – sfdcfox Mar 16 '20 at 4:31
  • 1
    Also, as you surmised, database.stateful won't help here you here. You need to run a query to find all existing Address_Master__c records, and put them in to a Map as well. See my own answer on how to find duplicates using a composite key. The same technique can be used in your case as well. That alone should get you most of the way there. – sfdcfox Mar 16 '20 at 4:35
  • Thanks @sfdcfox, One more thing I was wondering, Is is possible that I could query for distinct values of a field in the START method instead of pulling all the records and then pass those values to the EXECUTE method and then query using another SOQL for each of those values received from the START method to summarize the values? The reason why I am asking is at some places I have read that having soql inside the EXECUTE method is not recommended. Really appreciate all the help provided. Thanks again. – sumchans Mar 16 '20 at 5:32
  • 2
    Whomever said that "soql in execute is not recommended" is mistaken. It is highly recommended that you do so to make sure you have the correct data. You can't get distinct values in a start method on millions of records, as you're limited to 2,000 group-rows in a query. Inevitably, you're going to have some redundant work done in your batch, and that's okay. – sfdcfox Mar 16 '20 at 6:16
0

Anybody who had the similar issues like mine, this is the way I was able to solve this. Actually I posted the same question to Salesforce developer forums, Andrew there helped me out. I will put in his explanation and his code below:

Andrews explanation:

Loop the raw data to build a list of elements that you want to use in your query. e.g. street and city once loop has finished, do the query using those elements, build a map using street and city as keys, loop the raw data and search the Map using the key, update the record if key found and add to list,once loop finishes, update the list . This if fairly standard process for bulking code where a query is involved in the update of records.

    //Loop1 - get street and city name data into two lists
list<string> streetList = new List<string>();
list<string> cityList = new List<string>();
for(MDU_Squad_Raw_Data__c mduRawData: rawData) {
  streetlist.add(mduRawData.StreetAddress__c);
  cityList.add(mduRawData.CityName__c);
}
//do query:
List<MDU_Squad_Raw_Data__c> addressData = [SELECT Street_Address__c, City_Name__c, Province_Code__c, Postal_Code__c, Internet_Service__c, Video_Service__c, Phone_Service__c 
    FROM MDU_Squad_Raw_Data__c 
    WHERE Street_Address__c IN :streetList 
        AND City_Name__c IN :cityList];

//Loop addressData to make Map with Key street~city
Map<string,MDU_Squad_Raw_Data__c> mapAddressData = new Map......
for(MDU_Squad_Raw_Data__c raw : addressData){
  //psuedo - if no key, then add
  if !(mapAddressData.containsKey(raw.Street_Address__c+'~'+raw.City_Name__c){
      mapAddressData.add(raw.Street_Address__c+'~'+raw.City_Name__c, raw);
  }
}

//now we loop the original list checking the map for the key and then update as required
list<MDU_Squad_Raw_Data__c > updateList = new....
for(MDU_Squad_Raw_Data__c raw: rawData) {
    if(mapAddressData.containsKey(raw.Street_Address__c+'~'+raw.City_Name__c)){
        MDU_Squad_Raw_Data__c address = mapAddressData.get(raw.Street_Address__c+'~'+raw.City_Name__c);
        //now update the raw record with address record
        raw.postalcode = addres.province code;
//etc
        updateList.add(raw);
     } //endi f
}//end loop
if (updateList.size()>0){
   update udpateList;
}

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.