2

Thought I had this nailed until I deployed into production. My loop is generating too many SOQL queries: 101. How can I rewrite my For Loop to reduce the SOQL queries.

Thanks in advance!

Kevin

global with sharing class ADV_MajorGiftProspect implements Schedulable
{

    global void execute(SchedulableContext ctx)
    {

    // Create Map to hold Contact records to update
    map< id, contact > contacts = new map< id, contact >();

    // Find Contacts to update:
    for(OpportunityContactRole ocr: [Select contact.id from OpportunityContactRole 
                                            WHERE Opportunity.DEPARTMENT_TYPE__C = 'Advancement' 
                                            and Opportunity.DEPARTMENT_Sub_Type__C = 'Major Gifts' 
                                            and Contact.Major_Gift_Prospect_Contact__c = false ])
        {
            // Add contacts to Contact map
            contacts.put(ocr.contact.id, new contact(id=ocr.contact.id, Major_Gift_Prospect_Contact__c=true));

        }

        // Update Contact records
        update contacts.values(); 

    }


}
3
  • are you sure that the problem is the SOQL queries and not the DML statements? the update contacts.values() should be outside of the for loop. Jan 7, 2015 at 17:14
  • the DML statement is already outside the loop Jan 7, 2015 at 17:20
  • yep, noticed the indentation was off now. Jan 7, 2015 at 17:24

3 Answers 3

4

This code isn't your problem. To see which query might be causing the problem, start by profiling your database queries. Open the Developer Console, go to Debug > Change Log Levels, and specify "FINEST" for the Database and Profiling settings.

Then, run your code again so it crashes. In the Developer Console, you'll see a new log file. Open the log file and look at the end for the profiling information. You'll see lines like:

XXXXXXXXX|CUMULATIVE_PROFILING|SOQL operations|
Class.________: line ___, column ___: [SELECT ...]: executed 2 times in 745 ms
Class.________: line ___, column ___: [SELECT ...]: executed 5 times in 220 ms
Class.________: line ___, column ___: [SELECT ...]: executed 7 times in 173 ms
Class.________: line ___, column ___: [SELECT ...]: executed 6 times in 63 ms

(Information redacted to protect the innocent). You'll see in this log example here (this came from a sandbox org just a few minutes ago), that in four different places, a total of 20 queries were executed, how long they took, and where they were physically located in the source code. Look for the highest valued query and start your focus there. That's most likely where your problem is.

The code presented in the question only uses one query invocation, so that couldn't possibly be the problem. I'd have to assume that your DML operation is calling several triggers that are calling queries and ultimately causing the problem. The only probable suggestion for this code would be to LIMIT the query to some reasonable value, such as 10,000 rows. If you need more processing than that, make the class batchable.

1

I don't see anything wrong with this loop. Your problem must be coming from inefficient queries in other classes/triggers someplace downstream during the transaction

4
  • Hmmm..... I ran the query in the Developer Console. It pulled 134 records. Could it be that the way I wrote the test for this class is what is causing this to fail? The initial error came from the Inbound Change Set verification: eventsControllerTests.myPage_Test(), Details: System.LimitException: awduplicate2:Too many SOQL queries: 101 (awduplicate2) Jan 7, 2015 at 18:38
  • I created some records in my test. I can't display the entire test here, but this is the heart of it: // Start the test test.startTest(); ADV_MajorGiftProspect adv = new ADV_MajorGiftProspect(); adv.execute(null); // Stop test test.stopTest(); Jan 7, 2015 at 18:42
  • 1
    Or... could it be that I can't update more than x amount of Contacts at one time due to triggers kicked off on the Contact object? If that is the case I need to figure how to make the class batchable. Jan 7, 2015 at 19:07
  • Something along those lines would be my guess. You might want to try the profiling trick mentioned by sfdcfox to see where the excess queries are actually happening, and see what you can do about making those more bulk-safe Jan 7, 2015 at 19:29
0

In addition to what everyone else said .. I had this same issue, and I created utility class for logic that I was doing quite often such as getting RecordTypes and User Profiles. The code below you can easily edit it to use it for Profiles, User types. I don't know how many queries this freed up, but it certainly fixed my SOQL Limits. I know there is a easier way to get RecordTypes and you can explore that, but this is what worked for me.

You should be focusing on how well the other triggers and classes were written in your org, because I think we all agree here that your snippet isn't the issue.

public class RecordTypeUtils {
   public static List<RecordType> RECORD_TYPES {
    get {
        if (RECORD_TYPES == null) {
            RECORD_TYPES = [
                select Name,Id,
                    DeveloperName
                from RecordType
            ];
        }

        return RECORD_TYPES;
    }
    private set;
}

public static Map<String, Id> RECORD_TYPE_BY_DEVNAME {
    get {
        if (RECORD_TYPE_BY_DEVNAME == null) {
            RECORD_TYPE_BY_DEVNAME = new Map<String, Id>();

            for (RecordType rt: RECORD_TYPES) {
                RECORD_TYPE_BY_DEVNAME.put(rt.DeveloperName, rt.Id);
            }
        }

        return RECORD_TYPE_BY_DEVNAME;
    }

    private set;
}

public static Map<String, Id> RECORD_TYPE_BY_NAME {
    get {
        if (RECORD_TYPE_BY_NAME == null) {
            RECORD_TYPE_BY_NAME = new Map<String, Id>();

            for (RecordType rt: RECORD_TYPES) {
                RECORD_TYPE_BY_NAME.put(rt.Name, rt.Id);
            }
        }

        return RECORD_TYPE_BY_NAME;
    }
    private set;
    }
}

Then call it

Id recordTypeId= RecordTypeUtils.RECORD_TYPE_BY_NAME.get('Your Record Type');

If you see some other fishy queries, or loops open a new topic and I'm sure someone can help.

2
  • Thanks all. I've got an APEX workbook and I'm working my way through the Apex Batch Processing chapter. I'm going to see if I can pass my query results to a scheduled batch job for processing. - In our org we do have a number of Contact triggers that kick off, from a purchased Duplicate Check and HubSpot package and one of our own. Batch processing may be the only way for me to go! Jan 8, 2015 at 18:28
  • You should always get your record types like this: Id RecordTypeId = Schema.SObjectType.Account.getRecordTypeInfosByName('Your Account Record Type').getRecordTypeId();
    – arucker
    Oct 30, 2015 at 14:43

You must log in to answer this question.

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