+ Start a Discussion
Kelly KKelly K 

invalid query locator + Aggregate query has too many rows for direct assignment, use FOR

I think I'm stuck between a rock and a hard place and I'm hoping someone here with a bit more experience in batch classes can give me some direction.

My batch class is relatively simple: determine the max lead score across contacts within an account and update the account.

The start method is as followed:

global Database.QueryLocator start(Database.BatchableContext BC) {
		return Database.getQueryLocator([SELECT Id, Max_Lead_Score__c , (SELECT Id, mkto2__Lead_Score__c, AccountId FROM Contacts WHERE IsDeleted = false) FROM Account WHERE IsDeleted = false]);
}

And the execute method of my code like this:
global void execute(Database.BatchableContext BC, List<sObject> scope) {
        List<Account> accounts = (List<Account>) scope;        
	    Map<Id, Account> accountsForUpdate = new Map<Id, Account>();
        Id[] accountIds = new Id[]{};
	    Integer maxLeadScore;        
        
	for(Account account : accounts) {
		maxLeadScore = null;

		//Iterate through all contacts and find the max lead score
	       	if(account.Contacts.size > 0) {
	                for(Contact contact : account.Contacts) {				
        	            if(contact.mkto2__Lead_Score__c > maxLeadScore || maxLeadScore == null)
                	        maxLeadScore = (Integer)contact.mkto2__Lead_Score__c;					
                	}
    		}

		//Some contacts have a null lead score, in the event that's the only option available, populate with a 0 value on the account.
		if(maxLeadScore == null)
			maxLeadScore = 0;

		account.Max_Lead_Score__c = maxLeadScore;
		accountsForUpdate.put(account.Id, account);
	}
        
        //This is used for debug tracking - the save results array should line up
        for(Id id : accountsForUpdate.keySet())
            accountIds.add(id);

	List<Database.SaveResult> saveResults = Database.update(accountsForUpdate.values(), false);
        List<Apex_Error__c> apexErrors = new List<Apex_Error__c>();
        
        for(Integer i = 0; i < saveResults.size(); i++) {
            if(!saveResults.get(i).isSuccess()) {
                failedUpdates++;
                
                for(Database.Error error : saveResults.get(i).getErrors()) 
                	apexErrors.add(new Apex_Error__c(Apex_Class__c = 'AccountMaxContactLeadScore', Error_Record__c = accountIds.get(i), Error_Details__c = error.getMessage()));                
            }
        }
		if(apexErrors.size() > 0)
        	insert apexErrors;
}
 

The first issue I ran into was First error: Aggregate query has too many rows for direct assignment, use FOR loop. Fine. So I restructured my contact loop to not do the size check:

//Iterate through all contacts and find the max lead score
            for(Contact contact : account.Contacts) {				
       	        if(contact.mkto2__Lead_Score__c > maxLeadScore || maxLeadScore == null)
            	        maxLeadScore = (Integer)contact.mkto2__Lead_Score__c;					
    		}
 

Then I run into this error: First error: invalid query locator. Did some research, found this, restructured my code based on his advice. http://www.interactiveties.com/b_batch_apex_invalid_query_locator.php#.VNPK953F-4J
 

for(SObject s : scope) {
    Account account = (Account) s;
    maxLeadScore = null;

	//Iterate through all contacts and find the max lead score
	Contact[] contacts = account.getSObjects('Contacts');
        	
        if(contacts != null) {
                for(Contact contact : contacts) {				
                    if(contact.mkto2__Lead_Score__c > maxLeadScore || maxLeadScore == null)
                        maxLeadScore = (Integer)contact.mkto2__Lead_Score__c;					
                }
    	}
 

Then I got First error: Aggregate query has too many rows for direct assignment, use FOR loop. So similar to the first method, I remove the check for nulls. Then I get null pointer errors.

So I tried removing the subquery in the start method and then doing a direct query in the excute method and I get First error: Too many query rows: 50001 - which means I have to use the subquery method.

Any advice on how to get this optimized to hit zero errors?
 

AshwaniAshwani
It seems that you have too many child records of a parent. The only solution ca be is limit your inner query to return less number of records.

"(SELECT Id,mkto2__Lead_Score__c, AccountId FROM Contacts WHERE IsDeleted = false)" is returning too many record together with parent records. If therer are 50000 accounts each has 200 contacts then it will exceed the 100000 rows. 
This problem is realted to large number of child rows returning for parents.
Kelly KKelly K

Thank you for your response Ashwani - your response is accurate. I recieved some direction from our coding consultants and his suggestion was to restructure my query and loop to be an aggregate result instead of going through the account.Contacts loop. Also, it was pointed out to me that using the .containsKey() map method is more efficient to use than doing a .get() != null, so that solves my null pointer errors that I was receving after stripping that out.

Here's what I settled for on my final code and it's running without hitting any limits while performing the expected functionality.

global Database.QueryLocator start(Database.BatchableContext BC) {
		return Database.getQueryLocator([SELECT Id, Max_Lead_Score__c FROM Account]);
	}

	//Query all of the contacts within the scope of accounts. Determine the max lead score for the account and then update the accounts with the max lead score.
	global void execute(Database.BatchableContext BC, List<Account> scope) {    
		Map<Id, Account> accountsForUpdate = new Map<Id, Account>();
        Id[] accountIds = new Id[]{};
        
        //Place the current accounts of the scope into a map
        for(Account account : scope)
            accountsForUpdate.put(account.Id, account);        
        
        //Iterate through a max aggregate result for current accounts
        for(AggregateResult aggRes : [SELECT AccountId, MAX(Mkto2__Lead_Score__c) MaxLeadScore FROM Contact WHERE AccountId IN : accountsForUpdate.keySet() GROUP BY AccountId]) {
            Account account = accountsForUpdate.get(String.ValueOf(aggRes.get('AccountId')));
            
            //If there were contacts in the aggregate result and the results were not null, update the account max lead score
            if(accountsForUpdate.containsKey(String.ValueOf(aggRes.get('AccountId')))) {
                if(aggRes.get('MaxLeadScore') != null)
                    account.Max_Lead_Score__c = (Decimal)aggRes.get('MaxLeadScore'); 
                //Otherwise, if there were not any contacts, update the max lead score to 0
                else
                    account.Max_Lead_Score__c = 0;
            }
            
            accountsForUpdate.put(account.Id, account);
        }
        
        //This is used for debug tracking - the save results array should line up so we can pull the Id if there's an error
        for(Id id : accountsForUpdate.keySet())
            accountIds.add(id);

		List<Database.SaveResult> saveResults = Database.update(accountsForUpdate.values(), false);
        List<Apex_Error__c> apexErrors = new List<Apex_Error__c>();
        
        for(Integer i = 0; i < saveResults.size(); i++) {
            if(!saveResults.get(i).isSuccess()) {
                failedUpdates++;
                
                for(Database.Error error : saveResults.get(i).getErrors()) 
                	apexErrors.add(new Apex_Error__c(Apex_Class__c = 'AccountMaxContactLeadScore', Error_Record__c = accountIds.get(i), Error_Details__c = error.getMessage()));                
            }
        }
        
		if(apexErrors.size() > 0)
        	insert apexErrors;
	}