Best way to investigate "System.LimitException: Too many SOQL queries: 101"?

Question

I am not a developer by trade; however, I've inherited some code from a consultant that was contracted several years ago.

We recently noticed that one of the test classes is now failing, with an error message of "System.LimitException: Too many SOQL queries: 101". This is not doubt due to changes made to our configuration (on the declarative side).

After reading up on best practices, I've noticed several things:

• There are no SELECT statements inside for loops (as far as I can tell)
• Most of the SOQL queries are being conducted by a single "before insert, before update" trigger (two queries at a time, over and over)

Given that I have little to no developer experience, what would be the best approach?

The test class is testing several things. Should I first look into splitting up the test scenarios into multiple tests to avoid the hitting the SOQL limit? Or, should I look into streamlining the trigger? Or, should I look into the workflow rules? (There appears to be a series of Apex Trigger, Validation Rule, and Workflow Rule that occurs several times.)

Any suggestions would be welcome!

EDIT

Here is the code for the trigger:

trigger CustomObject_beforeInsertbeforeUpdate on CustomObject__c(before insert, before update) {

List < Id > AssetIds = new List < Id > ();
List < Id > OppIds = new List < Id > ();

for (CustomObject__c l: Trigger.new) {
    AssetIds.add(l.Asset__c);
    OppIds.add(l.Opportunity__c);
}

MAP < Id, Asset > AssetMAP = new MAP < Id, Asset > ([SELECT ID, AccountId, Contract__c, ContactId FROM Asset WHERE ID IN: AssetIds]);
MAP < Id, Opportunity > OppMAP = new MAP < Id, Opportunity > ([SELECT ID, AccountId FROM Opportunity WHERE ID IN: OppIds]);

system.debug('UDBG::: AssetMAP: ' + AssetMAP);
system.debug('UDBG::: OppMAP: ' + OppMAP);

for (CustomObject__c l: Trigger.new) {

    if (l.RecordTypeName__c == 'Trial') {
        if (l.TrialExpiryDate__c == null) {
            l.TrialExpiryDate__c = date.today() + (Integer) l.TrialDays__c;
        }

        if (OppMAP.containsKey(l.Opportunity__c)) {
            l.Account__c = OppMAP.get(l.Opportunity__c).AccountId;
        }

    } else if (l.RecordTypeName__c == 'Perpetual') {
        if (AssetMAP.containsKey(l.Asset__c)) {
            l.Account__c = AssetMAP.get(l.Asset__c).AccountId;
            l.Contract__c = AssetMAP.get(l.Asset__c).Contract__c;

        }
    }
}

Answer 1

I find a good starting point when this happens is to capture the Debug Log with the developer console. Ensure you have the DB logging category is set to INFO or FINEST when you do so. Then run just one of the test methods that is failing due to the limit exception.

Every SOQL query that is made will be captured in the log. You can then start the investigation by looking for the SOQL queries that occur most frequently.

There are additional perspectives on each log that can be helpful, such as the Stack Trace Execution Tree and Execution Overview Limits tab (requires Finest Profiling logging level.)

Answer 2

This is an article about hitting governor limits: https://developer.salesforce.com/page/Best_Practice:_Use_of_the_Limits_Apex_Methods_to_avoid_Hitting_Governor_Limits .

Personally, I find this mechanism is good for debugging about governor limits as well. You can put these code in your code:

System.debug('1.Number of Queries used in this apex code so far: ' + Limits.getQueries());
System.debug('2.Number of rows queried in this apex code so far: ' + Limits.getDmlRows());

And you can find out whether the query numbers jumped abruptly.

Daniel's method is a good one. However, if you set the debug level to be finest, sometimes the debug log will exceed the length limit. I personally would recommend using the limits class.