Increasing the Query Performance in a Salesforce Batch Class

Recently, I came across a performance issue in my project in which a Batch class was running for a minimum of 24 hours to process 10 million records.

Introduction 

Recently, I came across a performance issue in my project in which a Batch class was running for a minimum of 24 hours to process 10 million records.

Existing Flow 

We have a batch class that will query the records from an object based on certain conditions and then process those records based on the business requirement.

In our execute method, we are sending the scope to handler and querying child records based on several conditions.

Sample Query: 

SELECT field1, field2, field3 FROM Sobject WHERE cond1 AND (cond2 OR cond3) AND (cond4 or Cond 5 OR cond6) AND (cond7 OR cond 8) AND (cond9 OR cond 10) AND (cond 11 OR cond 12) 

The above query alone was taking minimum of 3 seconds for a single record and the overall time for the single record was taking about 4 seconds. So, the batch was running for more than 24 hours to process 10 million records.

Solution 

Each record is taking more time because they were going under multiple checks while running the batch.  Instead of checking the conditions while the batch is running, we created a formula field directly in the child object based on the above conditions. The field will return true or false based on the conditions.

Now, we are using that formula field in our queries after removing all the conditions.

Updated Query

SELECT field1, field2, field3 FROM Sobject WHERE formulaField = True 

After the change, each record is taking less than a second to complete.

Conclusion: 

From the above solution, whenever the record is created or updated, the formula field will have the calculated value. So, on running the batch, we can refer the formula field value rather executing multiple conditions during the batch execution. This approach will reduce the process time and increase the performance.

About MST

At MST Solutions our cornerstone is to adapt, engage and create solutions which guarantee the success of our clients. The talent of our team and experiences in varied business verticals gives us an advantage over other competitors.

Recent Articles

Work with us.

Our people aren’t just employees, they are key to the success of our business. We recognize the strengths of each individual and allow them time and resources to further develop those skills, crafting a culture of leaders who are passionate about where they are going within our organization.