4

I am trying to syc data email from contact to account for 20 million records.Here is the batch i have written to do that.when i run this batch i am getting an error "First error: Aggregate query has too many rows for direct assignment, use FOR loop".Moreover, I am not sure whether this code can handle 20 Million records.Your review and help is much appreciated.

global class EmailSycBatch implements Database.Batchable<SObject> {
    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(SELECT Name,Id,Primary_Email__c,(SELECT Email,Id,Primary_Indicator__c  FROM Contacts where Primary_Indicator__c=true) FROM Account ); 
    }
    global void execute(Database.BatchableContext BC, List<SObject> scope) {
        for(Account a : scope){
           List<Contact> cc = a.getSObjects('Contacts'); //grab all the related contact records
                if (cc != null) { //if there are related contact records
                for (Contact con: cc) { 
                  if(a.Primary_Email__c != con.Email)
                  a.Primary_Email__c = con.Email;
                }  
            }
         }
     try
        {
            update scope;
        }
        catch(Exception ex)
        {
            throw new TriggerException('Error in batch UpdateAccountBatch on method Batchable execute with the following error message'+ex.getMessage() + 'Stack Trace = ' +ex.getStackTraceString() );
        }   
    }
    global void finish(Database.BatchableContext info) {
        AsyncApexJob a = [SELECT CreatedBy.Email, CreatedBy.Name, CreatedDate, CompletedDate, Id, JobItemsProcessed, NumberOfErrors, Status, TotalJobItems FROM AsyncApexJob WHERE Id =: info.getJobId()];  
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); 
        String[] toAddresses = new String[] {'XXXXXXXX'}; 
        mail.setToAddresses(toAddresses); //to
        mail.setSubject('Apex batch_field_aggregation:'+a.Status); 
        mail.setPlainTextBody('The batch Apex job created by '+a.CreatedBy.Name+' ('+a.CreatedBy.Email+') processed '+a.TotalJobItems+' batches with '+a.NumberOfErrors+' failures. The process began at '+a.CreatedDate+' and finished at '+a.CompletedDate+'.'); 
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail }); //send
    }
}
1

1 Answer 1

5

Query just the parent records in the initial querylocator, then select additional fields as you need in your execute function:

global Database.QueryLocator start(Database.BatchableContext BC) {
    return Database.getQueryLocator([SELECT Id FROM Account]);
}
global void execute(Database.BatchableContext BC, List<SObject> scope) {
    Map<Id, Account> accounts = new Map<Id, Account>(scope);
    for(Account a :[SELECT Primary_Email__c, (SELECT Email FROM Contacts WHERE Primary_Indicator__c=true) WHERE Id IN :scope]){
        for(Contact con: a.Contacts) {
            if(a.Primary_Email__c != con.Email) {
                accounts.get(record.Id).Primary_Email__c = con.Email;
            }  
        }
    }

A few other notes: a sub-query from a query is never null. Also, don't query fields that you don't need. You'll see how I reduced the complexity to just two fields, since that's all I need to know to perform the update logic. Also, you get Id "for free", so you never need to include it in a query.

Additional notes: You're okay with the query as long as it's under 50,000,000 rows. After that, you'll have to figure out how to break up your batches.

12
  • I'm curious, does the 50,000,000 rows issue have something to do with governor limits? if so, isn't the query rows limit 50000? Also, according to SFDC docs "If you use a QueryLocator object, the governor limit for the total number of records retrieved by SOQL queries is bypassed". Again, is this a problem with governor limits?
    – Joca
    Commented Nov 13, 2015 at 21:22
  • 1
    @JoseCarlos The 50 million row limit applies to QueryLocator objects, which is internally represented with a database cursor. The QueryLocator may be further constrained in some contexts. For example, an ApexPages.StandardSetController allows only 10,000 rows, and the QueryLocator's limits are also affected by transaction limits. This means it's generally 50,000 in trigger and full-access Visualforce, and 1,000,000 in read-only Visualforce.
    – sfdcfox
    Commented Nov 13, 2015 at 22:09
  • @Sfdcfox.Thanks alot for your inputs.It is taking long time to process 40,000 recods.How do i improvise the performance of the bacth? How do I pervent the accounts that are already processed from getting reprocessed? Commented Nov 13, 2015 at 22:18
  • You should have commented on my answer; this post will eventually be flagged for deletion. There's not much you can do to improve the performance of the batch, as it's already about as efficient as it's going to get. Keep in mind that this largely depends on how many triggers, validation rules, workflow rules, etc, that you have in play. I have one org where I can process 40,000 records in a couple of minutes, and another that'd take over an hour. As far as preventing re-processing, try modifying your query to exclude accounts already last modified by your user account By SFDCFOX Commented Nov 13, 2015 at 22:20
  • 1
    @JoseCarlos - Here is where it is documented: developer.salesforce.com/docs/… - search page for "50 million"
    – Eric
    Commented Nov 14, 2015 at 5:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .