How to do/use DML Query Multiple Times in a Batch Apex Salesforce?

In Salesforce, Batch Apex is a robust mechanism for processing large datasets asynchronously while adhering to Salesforce's governor limits. When working with Batch Apex, you may encounter scenarios where you need to perform multiple DML (Data Manipulation Language) operations within the same batch. However, managing DML operations efficiently in this context requires careful planning to avoid hitting governor limits.

This article explores how to safely and effectively use multiple DML operations in Batch Apex, provides best practices, and includes detailed examples to help intermediate and advanced developers.s.

Understanding Batch Apex and DML in Salesforce

Batch Apex is designed to process large volumes of records by dividing them into smaller chunks or batches. Each batch executes independently, allowing developers to bypass the limits that apply to synchronous processing.

DML Limits in Salesforce:

• Salesforce enforces a limit of 150 DML statements per transaction.
• Each DML statement can process up to 10,000 records.

Batch Execution Flow:

• A batch job consists of three methods:
• start(): Gathers the records to process.
• execute(): Processes the records in chunks.
• finish(): Performs post-processing after all batches have been completed.

Governor Limits in Batch Apex:

• Each batch execution is treated as an independent transaction.
• Limits, such as the number of queries or DML operations, reset for each batch execution.

When Do You Need Multiple DML Statements in Batch Apex?

Common Scenarios:

1. Processing Different Object Types: When a single batch needs to insert, update, or delete records from multiple objects.
2. Conditional DML Operations: When records require different actions based on their attributes (e.g., some need updating, others need deleting).
3. Complex Relationships: When updates must cascade through multiple related objects.

Strategies for Managing Multiple DML Statements

1. Batching DML Operations

Group similar DML operations together to minimize the number of DML statements. Instead of performing multiple single-record DML operations, batch them into a list and execute them in one statement.

Example:

public class BatchProcessAccounts implements Database.Batchable<SObject> {
    public Database.QueryLocator start(Database.BatchableContext context) {
        return Database.getQueryLocator('SELECT Id, Name, Status__c FROM Account WHERE Status__c = \'Pending\'');
    }
    
    public void execute(Database.BatchableContext context, List<SObject> scope) {
        List<Account> accountsToUpdate = new List<Account>();
        for (Account acc : (List<Account>)scope) {
            if (acc.Status__c == 'Pending') {
                acc.Status__c = 'Active';
                accountsToUpdate.add(acc);
            }
        }
        // Single DML operation for all updates
        if (!accountsToUpdate.isEmpty()) {
            update accountsToUpdate;
        }
    }

    public void finish(Database.BatchableContext context) {
        System.debug('Batch Job Completed');
    }
}

2. Chunking Records in Subgroups

Divide records into smaller groups based on criteria, and perform separate DML operations for each group.

Example:

public class BatchProcessMixedUpdates implements Database.Batchable<SObject> {
    public Database.QueryLocator start(Database.BatchableContext context) {
        return Database.getQueryLocator('SELECT Id, Type__c FROM CustomObject__c');
    }

    public void execute(Database.BatchableContext context, List<SObject> scope) {
        List<CustomObject__c> typeAUpdates = new List<CustomObject__c>();
        List<CustomObject__c> typeBUpdates = new List<CustomObject__c>();

        for (CustomObject__c record : (List<CustomObject__c>)scope) {
            if (record.Type__c == 'TypeA') {
                record.Status__c = 'Processed';
                typeAUpdates.add(record);
            } else if (record.Type__c == 'TypeB') {
                record.Status__c = 'Reviewed';
                typeBUpdates.add(record);
            }
        }

        if (!typeAUpdates.isEmpty()) update typeAUpdates;
        if (!typeBUpdates.isEmpty()) update typeBUpdates;
    }

    public void finish(Database.BatchableContext context) {
        System.debug('Batch Process Completed');
    }
}

3. Using Queued Apex for Sequential DML Operations

If your processing involves multiple dependent DML operations, you can chain batch jobs using Queueable Apex.

Example:

public class BatchInitialProcess implements Database.Batchable<SObject> {
    public Database.QueryLocator start(Database.BatchableContext context) {
        return Database.getQueryLocator('SELECT Id FROM Account');
    }

    public void execute(Database.BatchableContext context, List<SObject> scope) {
        // Initial processing
        List<Account> accountsToUpdate = new List<Account>();
        for (Account acc : (List<Account>)scope) {
            acc.Status__c = 'Step1Completed';
            accountsToUpdate.add(acc);
        }
        if (!accountsToUpdate.isEmpty()) update accountsToUpdate;

        // Queue next batch for further processing
        System.enqueueJob(new SecondProcessingJob());
    }

    public void finish(Database.BatchableContext context) {
        System.debug('Initial Batch Completed');
    }
}

public class SecondProcessingJob implements Queueable {
    public void execute(QueueableContext context) {
        List<Account> accountsToProcess = [SELECT Id FROM Account WHERE Status__c = 'Step1Completed'];
        for (Account acc : accountsToProcess) {
            acc.Status__c = 'Finalized';
        }
        update accountsToProcess;
    }
}

4. Leverage Database.Stateful

Use Database.Stateful to maintain state across batch executions, allowing you to aggregate results or conditionally trigger additional DML operations.

Example:

public class BatchWithState implements Database.Batchable<SObject>, Database.Stateful {
    public Integer recordsProcessed = 0;

    public Database.QueryLocator start(Database.BatchableContext context) {
        return Database.getQueryLocator('SELECT Id, Name FROM Account');
    }

    public void execute(Database.BatchableContext context, List<SObject> scope) {
        List<Account> accountsToUpdate = new List<Account>();
        for (Account acc : (List<Account>)scope) {
            acc.Name = acc.Name + ' - Processed';
            accountsToUpdate.add(acc);
        }
        update accountsToUpdate;

        recordsProcessed += scope.size();
    }

    public void finish(Database.BatchableContext context) {
        System.debug('Total Records Processed: ' + recordsProcessed);
    }
}

Best Practices for Using Multiple DML Operations in Batch Apex

1. Minimize DML Statements:

• Batch records into collections to reduce the number of DML statements.
• Avoid performing DML operations inside loops.

2. Optimize Queries:

• Retrieve only the fields you need for processing.
• Avoid unnecessary queries inside the execute() method.

3. Error Handling:

• Use Database.SaveResult to capture and handle DML exceptions without interrupting the entire batch process.

Example:

Database.SaveResult[] results = Database.update(accountsToUpdate, false);
for (Database.SaveResult result : results) {
    if (!result.isSuccess()) {
        System.debug('Error updating record: ' + result.getErrors()[0].getMessage());
    }
}

4. Consider Future Methods or Queueable Apex:

• When the logic becomes too complex for a single batch job, break it into smaller units using Queueable or Future Apex.

5. Monitor Batch Jobs:

• Use debug logs or the Apex Jobs page in Salesforce Setup to monitor and troubleshoot batch jobs.

Conclusion

Using multiple DML operations in Batch Apex can be a powerful technique for processing large datasets with complex requirements. By following best practices such as batching records, avoiding DML in loops, and leveraging stateful processing, you can ensure efficient execution without hitting governor limits.

With careful planning and proper implementation, Batch Apex can handle even the most demanding use cases, making it an essential tool for developers working in Salesforce.