What to do in case of too many DML Statements?
I am attempting to write a Quick Action that is a LWC that will validate if a quote can be submitted for approval. If there is a validation it will return a string and display it on the screen, with no validation it should submit a record into approval flow and return a string and display. When kicking off the approval flow I get the error of To Many DML Statements 1. I have read that it is because I am either using @wire or (cacheable=true). If I take (cacheable=true) away the string will not be returned and nothing will display. How should the below be handled?
HTML
{somethingReturned.data}
Javascript
import { LightningElement, api, wire } from 'lwc'; import ValidateQuoteForApproval from '@salesforce/apex/clsSubmitToOrderLWC.ValidateQuoteForApproval'; export default class TestQuickAction extends LightningElement { @api recordId; //@wire must be used to call the method. @wire(ValidateQuoteForApproval, {idQuote:'$recordId' }) somethingReturned; //API Invoke is used to display the Quick action @api invoke() { somethingReturned } }
Apex
public with sharing class clsSubmitToOrderLWC { @AuraEnabled(cacheable=true) public static String ValidateQuoteForApproval(Id idQuote) { boolean blnError = False; String strErrorMessage = ''; system.debug(logginglevel.debug, 'Start ValidateQuoteForApproval ' + idQuote); SBQQ__Quote__c soQuote = [Select Id, SBQQ__LineItemCount__c, Special_Lines_Roll_UP__c From SBQQ__Quote__c Where Id = : idQuote]; system.debug(logginglevel.debug, 'ValidateQuoteForApproval' + soQuote); If(soQuote.SBQQ__LineItemCount__c == 0){ strErrorMessage = strErrorMessage + 'Wait a Second! This Particular Quote has no Quote Line items n'; blnError = True; } If(soQuote.Special_Lines_Roll_UP__c >= 0){ blnError = True; strErrorMessage = strErrorMessage + 'Wait a Second! This Particular Quote has some Special line items which are rejected.'; } If(blnError == True){ Return strErrorMessage; }Else{ Approval.ProcessSubmitRequest req1 = new Approval.ProcessSubmitRequest(); req1.setComments(''); req1.setObjectId(soQuote.id); Approval.ProcessResult result = Approval.process(req1); Return 'Quote has been submitted for Approval'; } } }
In case of too many dml statements - you cannot perform a DML statement within cacheable=true as you noted. If you added a debug log, you'd notice the following under LIMIT_USAGENumber of DML statements: 1 out of 0 * CLOSE TO LIMIT
This is by design.
In your case, and when working with LWC, you should always take a look to see if there's any provided wire adapters that can handle the action before jumping into apex.
It seems you're doing two things:
Querying the record you're on to do some validation checks
The approval process.
getRecord is the perfect adapter for the first step so you can quickly validate data. I'll also throw out that the createRecord is also very helpful, although won't work with your use case of processing an approval so you'll have to set @cacheable=false and call the apex method imperatively.
const FIELDS = ['SBQQ__Quote__c.SBQQ__LineItemCount__c','SBQQ__Quote__c.Special_Lines_Roll_UP__c']; export default class TestQuickAction extends LightningElement { quote; @wire(getRecord, { recordId: '$recordId', fields: FIELDS }) wiredRecord({ error, data }) { if(data) { this.quote = data; if(this.quote.fields.SBQQ__LineItemCount__c == 0){ //handle displaying error toast message } // after all validations pass, I'd suggest renaming method to do //what it now does without validations CreateQuoteApproval, ({idQuote:'$recordId' }) .then(result => { //process success toast }) } } }
Answer (1)
When you encounter a situation with too many Data Manipulation Language (DML) statements in a database, it can lead to performance bottlenecks, contention, and even system crashes. Here are several strategies to address and mitigate these issues:
1. Batch Processing
Batch DML Statements: Group multiple DML operations into a single batch to reduce the overhead associated with each individual statement. This can be done using transaction management to ensure atomicity and consistency.
Bulk Inserts/Updates: Utilize bulk operations to handle large datasets more efficiently. Many database systems provide specific commands or APIs for bulk operations.
2. Optimize DML Statements
Indexing: Ensure proper indexing to speed up the execution of DML statements. Be careful with over-indexing, as it can slow down inserts, updates, and deletes.
Query Optimization: Analyze and optimize the SQL queries involved in DML operations. Use tools like the query planner/explainer to understand and improve query performance.
Stored Procedures: Use stored procedures to encapsulate DML logic on the database server side, reducing network overhead and improving performance.
3. Transaction Management
Transaction Size: Keep transactions small to minimize lock contention and reduce the risk of transaction conflicts.
Isolation Levels: Adjust the isolation levels based on the requirement. Lower isolation levels like READ COMMITTED can reduce locking but might lead to dirty reads.
Commit Frequency: Commit transactions frequently to avoid long-running transactions which can hold locks for extended periods and affect concurrency.
4. Load Balancing
Distribute Workload: Distribute DML operations across multiple database servers if possible, using techniques like sharding or replication.
Queue Management: Implement job queues to manage the rate at which DML operations are executed, ensuring the database isn’t overwhelmed.
5. Monitoring and Alerts
Monitoring Tools: Use database monitoring tools to track the performance and identify bottlenecks. Tools like PostgreSQL's pg_stat_activity, MySQL's Performance Schema, or third-party tools can be invaluable.
Alerts: Set up alerts to notify when the number of DML statements exceeds a threshold, allowing for proactive management.
6. Database Configuration
Connection Pooling: Use connection pooling to manage database connections efficiently and reduce overhead.
Concurrency Controls: Adjust settings like max connections, lock timeouts, and deadlock detection to handle high volumes of DML operations.
7. Archiving and Cleanup
Archiving: Regularly archive old data to reduce the size of the active dataset, making DML operations faster.
Cleanup: Periodically clean up obsolete or temporary data to maintain database performance.
8. Application-Level Strategies
Data Caching: Implement caching strategies at the application level to reduce the need for frequent DML operations.
Retry Logic: Implement robust retry logic for handling transient failures in DML operations.
Example Implementation: Batch Processing
BEGIN TRANSACTION;
-- Example of bulk insert
INSERT INTO my_table (column1, column2)
VALUES
(value1, value2),
(value3, value4),
(value5, value6);
-- Example of bulk update
UPDATE my_table
SET column1 = new_value
WHERE column2 IN (value2, value4, value6);
COMMIT TRANSACTION;Conclusion
Addressing the issue of too many DML statements involves a combination of optimizing queries, managing transactions, balancing loads, and monitoring performance. By implementing these strategies, you can significantly improve database performance and reduce the impact of heavy DML operations.