0

Re-factoring this below code to avoid SOQL in for loop. In this case below soql is for different object than the object passed in param. The solution I am trying needs for loop inside for loop, wonder if I am introducing more complex code and sacrificing efficiency. Below is the sample code. Any inputs appreciated.

public static void calculateTempMetod(List<demoObj1> demoObj1List) {
    for (demoObj1 demo1 : demoObj1List) {
        if (somecondition != null) {
            List<demoObj2> demoObj2List = [
                SELECT demoObj2_
                FROM demoObj2__c 
                WHERE field1 <= :demo1.field1 
                      AND field2 >= :demo1.field2
                      AND field3 = :demo1.field3 
                      AND field4 = :demo1.field4 
                LIMIT 1
            ];

            if (demoObj2List != null && !demoObj2List.isEmpty()) {
                demoObj2 tmp = demoObj2List[0];
                demo1.someValue = tmp.field1;
            }
        }
    }
}
8

This type of code is tricky to bulkify, and some of the strategies that you'll use to do so depend on your knowledge of the expected data volume that will match each of the filters you're applying in your query.

The basic principle is that you take your first object, demoObj1, and define the widest SOQL bounds you would need in order to match the right demoObj2 for each of them in a single query. Here, you have a >=, a <=, and two = filters. So what you'd want to do is find.

  • The smallest value for SWOT_Total__c among demo1Obj records in this group.
  • The largest value for SWOT_Total__c among demo1Obj records in this group.
  • The set of all Plan_Year__c values in this group.
  • The set of all Region__c values in this group.

Then you write a query that takes in all of those parameters such that it's guaranteed to find the corresponding demoObj2 record for every demoObj1, even if it might also bring back some records that fit these broadened filters but don't match any specific record. You'd end up with something like this:

SELECT Sales_Objective__c 
FROM demoObj2__c 
WHERE field1 <= :maximumSWOTTotal
      AND field2 >= :minimumSWOTTotal 
      AND field3 IN :planYearSet 
      AND field4 IN :regionSet 

That will potentially bring you back records that you don't need, but it makes sure that you do get all the records you do need in one query.

Then, you'll iterate through your demoObj1 records and find the corresponding demoObj2 record. There's a few ways to handle this, and it's going to be a bit contextual.

If you can guarantee that the numbers of records involved will be fairly small, you can use a nested for loop: iterate over demoObj1 in the outer loop, and then demoObj2 query results in the inner loop. This is an O(N*M) search, so you need to know your record counts will be on the small side or it will very rapidly scale until you hit CPU timeout errors.

If you can't guarantee this (and it's highly likely you can't), you need to reduce the search space to find corresponding demoObj2 records in some other way. One route to do so is to place the search results in a map keyed on a composite key, the combination of fields that uniquely identify this record. Here, you have a bit of a challenge because part of your comparison is not an equality but a less-than/greater than. You could still use the technique to reduce your search space, though, by building a multimap (one key, many values) keyed on the concatenation of the two equality parameters. It'd look something like this:

Map<String, List<demoObj2__c>> searchMap = new Map<String, List<demoObj2__c>>();

for (demoObj2__c d: queryResults) {
    String key = d.Plan_Year__c + d.Region__c;
    if (!searchMap.containsKey(key)) {
        searchMap.put(key, new List<demoObj2__c>());
    }

    searchMap.get(key).add(d);
}

for (demoObj1__c d1: demoObj1List) {
    String key = d1.Plan_Year__c + d1.Region__c;

    if (searchMap.containsKey(key)) {
        for (demoObj2__c d2: searchMap.get(key)) {
            // Check if d2 goes with d1.
            // If it does, update the field on d1 and break.
        }
    } else {
        // do some default behavior
    }
}

Again, the details are going to vary based on exactly what the data types and fields are here, but something along the lines of this pattern is where you'd end up in order to bulkify that query.

It's worth considering where this code is being called. If the number of inbound records is guaranteed to be small (this is not a trigger but perhaps a small-scale Visualforce page or Lightning component) and your transactions can handle the limits consumption, it may be a situation where the increased code complexity of bulkification is not worth the effort.

1
  • Thanks David for elaborate answer, let me try to figure out which strategy suits for the data volume involved. – user1902124 Mar 7 '20 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.