function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Brad Nordling 9Brad Nordling 9 

Batch Apex too many queries

I have a batch apex job that builds a select statement for contact records for the start method.  When I am in the execute method, I loop through the account records and for each one, I have a select to go get something from a custom object for locations.  An account can have more than one location.

In the execute method the job fails because of that select within the for loop of accounts.  Too many account, to many loops with a select in it.

I'm thinking the original select in the start method should be modified to select the account and the locations.  But how do I do that?  I want the execute method to be handed a list of accounts with each one having a list of locations.

Does anyone know how to pass a list to the execute method where each record has one or more items in a list from another object?
 
Best Answer chosen by Brad Nordling 9
AnkaiahAnkaiah (Salesforce Developers) 
Hi Brand,

You use map or another set of string.
  
public void execute (Database batchableContext bc, List<Account> lstAccounts){

Map<id, string> accmap = new map<id, string>();

Set<string> accnames = new set<string>();

set<id> setAccId = new set<id>();

    for(Account objAcc : lstAccounts){
        setAccId.add(objAcc.Id);
        accnames.add(objAcc.Name);
       accmap.put(objAcc.id,objAcc.Name);
    }
    
    for(Location__c objLocation : [SELECT AccountId, Name from Location__c WHERE AccountId IN: setAccId AND Name IN:accnames]){
        System.debug('objLocation :'+objLocation);
    }
}

If this helps, please mark it as best answer.

Thanks!!

 

All Answers

Brad Nordling 9Brad Nordling 9
Account records, not contact record
Neha Arora 50Neha Arora 50
Use Database.queryLocator in the start method to return the list of accounts to the execute method.
Link to see how Database.Querylocator work - https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm
And in execute method, iterate on the list of accounts, store the Ids in a separate set and then fetch the list of locations based on Account Ids in the set.

As a best practice, we should never query in the for loop that can cause limit exceptions same as you are getting error for too many accounts.

sample code -
public void execute (Database batchableContext bc, List<Account> lstAccounts){

    for(Account objAcc : lstAccounts){
        setAccId.add(objAcc.Id);
    }
    
    for(Location__c objLocation : [SELECT AccountId, Name from Location__c WHERE AccountId IN: setAccId]){
        System.debug('objLocation :'+objLocation);
    }
}

Let me know if it helps.
Brad Nordling 9Brad Nordling 9
Thanks for the response!  I have a question about this - what if I need two fields from the account object?  Not just id nut another one as well.
AnkaiahAnkaiah (Salesforce Developers) 
Hi Brand,

You use map or another set of string.
  
public void execute (Database batchableContext bc, List<Account> lstAccounts){

Map<id, string> accmap = new map<id, string>();

Set<string> accnames = new set<string>();

set<id> setAccId = new set<id>();

    for(Account objAcc : lstAccounts){
        setAccId.add(objAcc.Id);
        accnames.add(objAcc.Name);
       accmap.put(objAcc.id,objAcc.Name);
    }
    
    for(Location__c objLocation : [SELECT AccountId, Name from Location__c WHERE AccountId IN: setAccId AND Name IN:accnames]){
        System.debug('objLocation :'+objLocation);
    }
}

If this helps, please mark it as best answer.

Thanks!!

 
This was selected as the best answer
Brad Nordling 9Brad Nordling 9
I appreciate the responses!  I need to do this another way - going to brute force it by splitting into smaller jobs. 

BTW the second field I need is a Type field.  I need the Id and the Type.  I am not going to use the Type field in a select, just the id field in the select to the location object.  But once I am in the for loop of the location objects (for each account), I need the Type field that corresponds to the Id field of the account I have in hand (in the loop).  It seems your example was looking for any occurance of the id field and any occurance of the Name field, not the name field that corresponds to any particular Id.  Is that right?  Seems I need to iterate through (I don't know much about Maps) the Map and use each Id and its associated Type (or Name, in your example).

Also, I don't see how this model is any different as far as reducing the number of queries.  We're still querying the locations for each account we have, it's just done with a different code model and doesn't seems to reduce the total number of queries.  Like, if I have 1000 accounts, then for each one I do a Select statement to get the locations.  I only get 200 and I'm at 1000.  I was looking for a way to pass into the execute method ALL of the data by doing the "select in a select" (kinda like a join) in the start method and then create and pass into the execute method a custom list or set or map (Iterable) that has the accounts and their locations so no lookup is needed in the execute method.  Never got that to work. (yet)