function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Chelsea LukowskiChelsea Lukowski 

Batch job takes forever to run

I have a batch job that seems to take a long time to process. Is there anyway I can fix the job so that it does not take so long to update, is there something I can change to fix this?
 
global class batchAccountUpdate implements Database.Batchable<sObject>,Database.Stateful{
    
    String query;
     
     Public static void RunMe(){
        batchAccountUpdate fbatch = new batchAccountUpdate();
        Id batchInstanceId = Database.executeBatch(fbatch, 200);
    }

    global Database.QueryLocator start(Database.BatchableContext BC) {
        //String query = 'select id,Account_Column_Key__c,Account__c,Previous_Year_Volume_YTD__c from Commission_Account__c ';
        return Database.getQueryLocator('select id,Account_Column_Key__c,Account__c,Previous_Year_Volume_YTD__c from Commission_Account__c');
    }
   
    global void execute(Database.BatchableContext BC, List<Commission_Account__c> scope) {
        List<Commission_Account__c> commAcctList=new List<Commission_Account__c>();
        for(Commission_Account__c commAcct:scope){
            try{
                decimal ii=0;
            
                List<Titan_Invoice__c> invList=[select id,Commission_Key__c,quantity__c,invoice_date__c from Titan_Invoice__c where Commission_Key__c=:commAcct.Account_Column_Key__c];
                
                for(Titan_Invoice__c inv:invList){
                    date d=date.valueof('2015-01-01');
                    integer day=system.now().day();
                    integer month=system.now().month();
                    system.debug('=11===='+day);
                    
                    string ytdDate='2015-'+month+'-'+day;
                    date e=date.valueof(ytdDate);
                    
                    if(inv.Invoice_Date__c>=d && inv.Invoice_Date__c<=e){
                    ii=ii+inv.quantity__c;
                    
                    }
           		}
        	commAcct.Previous_Year_Volume_YTD__c=ii;
        	commAcctList.add(commAcct);
            }catch(exception e){
            
            }
        }
        database.update(commAcctList,false);
    }

    global void finish(Database.BatchableContext BC) {
    }
 }

 
Best Answer chosen by Chelsea Lukowski
UC InnovationUC Innovation
Hi.

Right off the bat I can say that it is generally bad practice to place a query inside of a for loop. This is due to governor limits on queries. You can read more on those here (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm). That being said modifications had to be done so accordingly. Since i dont know how many records of each object there are and the amount of impact triggers have in this org i had to make a best guess assumptions on how the batch is going to be run. Looking at your code I saw that it is run in batches of 200 which is the default so whith that in mind this is the modified code. Let me know if this helps. I made light comments to explain my reasoning. Questions are welcome.
 
// sample run on apex anon:
// 					Id batchInstanceId = Database.executeBatch(new batchAccountUpdate());
//
global class batchAccountUpdate implements Database.Batchable<sObject>,Database.Stateful{
    final String query;
	
	// constructor
	// common practice
    gloabal batchAccountUpdate(){
		query = 'SELECT Id, ' +
				       'Account_Column_Key__c, ' +
					   'Account__c, ' +
					   'Previous_Year_Volume_YTD__c ' +
				'FROM Commission_Account__c';
    }

    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(query);
    }
   
    global void execute(Database.BatchableContext BC, List<Commission_Account__c> scope) {
        List<Commission_Account__c> commAcctList = new List<Commission_Account__c>();
		Map<String, List<Titan_Invoice__c>> accountColumnKeyToInvoiceListMap = new Map<String, List<Titan_Invoice__c>>();
		
		// map for use later
		for (Commission_Account__c commAcct : scope) {
			accountColumnKeyToInvoiceListMap.put(commAcct.Account_Column_Key__c, new List<Titan_Invoice__c>());
		}
		
		// moved out of for loop.
		// Rule of thumb is to never put a query in a loop
		List<Titan_Invoice__c> invList = [SELECT Id,
												 Commission_Key__c,
												 Quantity__c,
												 Invoice_Date__c
										  FROM Titan_Invoice__c 
										  WHERE Commission_Key__c IN :accountColumnKeyToInvoiceListMap.keySet()
										  ORDER BY Commission_Key__c, Invoice_Date__c];
		
		// fill in the map with relevant records
		for (String accountColumnKey : accountColumnKeyToInvoiceListMap.keySet()) {
			List<Titan_Invoice__c> mappedInvoiceList = accountColumnKeyToInvoiceListMap.get(accountColumnKey);
			
			for (Integer index = 0; index < invList.size(); i++) {
				if (invList.get(index).Commission_Key__c == accountColumnKey) {
					// will be added in order by Invoice_Date__c
					mappedInvoiceList.add(invList.remove(index));
				} 
			}
		}
		
		for (Commission_Account__c commAcct : scope){
            try{
                decimal ii=0;
                
				// loop through relevant records
                for (Titan_Invoice__c inv : accountColumnKeyToInvoiceListMap.get(commAcct.Account_Column_Key__c)){
                    Date d = date.valueof('2015-01-01');
                    
					DateTime now = System.now();
					Integer day = now.day();
                    Integer month = now.month();
                    
                    date e = date.valueof('2015-' + month + '-' + day);
                    
                    if(inv.Invoice_Date__c >= d && inv.Invoice_Date__c <= e){
						ii += inv.quantity__c;
                    } else if (inv.Invoice_Date__c > e) {
						// since in order then we will be permanantly out of the accepted range
						break;
					}
           		}
				
				commAcct.Previous_Year_Volume_YTD__c = ii;
				commAcctList.add(commAcct);
            } catch(exception e){}
        }
        database.update(commAcctList,false);
    }

    global void finish(Database.BatchableContext BC) {}
}

If your triggers allow it you can try on bigger batch sizes in execute anonomouse like so:
 
Id batchInstanceId = Database.executeBatch(new batchAccountUpdate(), 500);

Hope this helps!

AM
 

All Answers

UC InnovationUC Innovation
Hi.

Right off the bat I can say that it is generally bad practice to place a query inside of a for loop. This is due to governor limits on queries. You can read more on those here (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm). That being said modifications had to be done so accordingly. Since i dont know how many records of each object there are and the amount of impact triggers have in this org i had to make a best guess assumptions on how the batch is going to be run. Looking at your code I saw that it is run in batches of 200 which is the default so whith that in mind this is the modified code. Let me know if this helps. I made light comments to explain my reasoning. Questions are welcome.
 
// sample run on apex anon:
// 					Id batchInstanceId = Database.executeBatch(new batchAccountUpdate());
//
global class batchAccountUpdate implements Database.Batchable<sObject>,Database.Stateful{
    final String query;
	
	// constructor
	// common practice
    gloabal batchAccountUpdate(){
		query = 'SELECT Id, ' +
				       'Account_Column_Key__c, ' +
					   'Account__c, ' +
					   'Previous_Year_Volume_YTD__c ' +
				'FROM Commission_Account__c';
    }

    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(query);
    }
   
    global void execute(Database.BatchableContext BC, List<Commission_Account__c> scope) {
        List<Commission_Account__c> commAcctList = new List<Commission_Account__c>();
		Map<String, List<Titan_Invoice__c>> accountColumnKeyToInvoiceListMap = new Map<String, List<Titan_Invoice__c>>();
		
		// map for use later
		for (Commission_Account__c commAcct : scope) {
			accountColumnKeyToInvoiceListMap.put(commAcct.Account_Column_Key__c, new List<Titan_Invoice__c>());
		}
		
		// moved out of for loop.
		// Rule of thumb is to never put a query in a loop
		List<Titan_Invoice__c> invList = [SELECT Id,
												 Commission_Key__c,
												 Quantity__c,
												 Invoice_Date__c
										  FROM Titan_Invoice__c 
										  WHERE Commission_Key__c IN :accountColumnKeyToInvoiceListMap.keySet()
										  ORDER BY Commission_Key__c, Invoice_Date__c];
		
		// fill in the map with relevant records
		for (String accountColumnKey : accountColumnKeyToInvoiceListMap.keySet()) {
			List<Titan_Invoice__c> mappedInvoiceList = accountColumnKeyToInvoiceListMap.get(accountColumnKey);
			
			for (Integer index = 0; index < invList.size(); i++) {
				if (invList.get(index).Commission_Key__c == accountColumnKey) {
					// will be added in order by Invoice_Date__c
					mappedInvoiceList.add(invList.remove(index));
				} 
			}
		}
		
		for (Commission_Account__c commAcct : scope){
            try{
                decimal ii=0;
                
				// loop through relevant records
                for (Titan_Invoice__c inv : accountColumnKeyToInvoiceListMap.get(commAcct.Account_Column_Key__c)){
                    Date d = date.valueof('2015-01-01');
                    
					DateTime now = System.now();
					Integer day = now.day();
                    Integer month = now.month();
                    
                    date e = date.valueof('2015-' + month + '-' + day);
                    
                    if(inv.Invoice_Date__c >= d && inv.Invoice_Date__c <= e){
						ii += inv.quantity__c;
                    } else if (inv.Invoice_Date__c > e) {
						// since in order then we will be permanantly out of the accepted range
						break;
					}
           		}
				
				commAcct.Previous_Year_Volume_YTD__c = ii;
				commAcctList.add(commAcct);
            } catch(exception e){}
        }
        database.update(commAcctList,false);
    }

    global void finish(Database.BatchableContext BC) {}
}

If your triggers allow it you can try on bigger batch sizes in execute anonomouse like so:
 
Id batchInstanceId = Database.executeBatch(new batchAccountUpdate(), 500);

Hope this helps!

AM
 
This was selected as the best answer
Chelsea LukowskiChelsea Lukowski
That helps tremendously and did the job. Thank you! I am still learning.