function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Daniel BallingerDaniel Ballinger 

testMethods causing LimitException: "Too many SOQL queries: 101" in production but not sandbox

Hi,

 

A number of my test methods need to setup records for the required test scenarios. I’ll typically need to create Account, Contact, Opportunity, Product2, PricebookEntry, OpportunityLineItem and custom sObject records before the test scenario can be executed. Some of these records are configuration and typically wouldn’t change much in production.

 

The setup process can be expensive in terms of the Apex governor limits on SOQL queries as I have to first check the existing production configuration data to determine if the test records need to be inserted or not.

 

I tried updating the test case classes to API version 24.0 so that the test methods wouldn’t run with live production data (http://developer.force.com/releases/release/Spring12/test+data+access). However, I ran into a STANDARD_PRICE_NOT_DEFINED Exception when setting up the OpportunityLineItems (http://stackoverflow.com/questions/9164986/how-do-i-avoid-standard-price-not-defined-when-unit-testing-an-opportunitylineit).

 

I’ve also looked at wrapping the actual test case code within a Test.startTest() and Test.stopTest() but is has no effect on the governor limits (I’m not doing any future or batch jobs).

 

Is there a way I can perform the expensive test setup without pushing the SOQL query governor limit up before the actual test has started?

 

In the ideal world I'd be able to break the test scenario down into some smaller units but I'm not sure how that would work considering the number of prerequisites and no mocking framework.

 

I've added an idea for test setup functionality to be added to apex: Test method setup code with separate governor limits to the core test itself

Best Answer chosen by Admin (Salesforce Developers) 
Starz26Starz26

Set up a static variable / class to indicate a test is running that should not allow the trigger to fire...

 

set the variable to true test classes that disbale triggers, then set back to false when you want triggers to run, and check it in the triggers....

 

As an alternativ, if you do not want to explicitly allow/denyg triggers running but want to always disable triggers during all tests, wrap each trigger in a :

 

if(!system.testisRunning()){

 

}

 

 

All Answers

steve456steve456

please post the code so that v can look into the code
Daniel BallingerDaniel Ballinger

The test class is large with a number of test cases and setup/configuration methods. I'll try and edit it down so you can see the core structure.

 

The issue I'm facing is that by the time I get to calling Test.startTest() there have already been 70 SOQL queries. Partially this is being caused by triggers pushing the query count up as the test data is setup.

 

@isTest
public class Test_XYZController {

    static {
        system.debug('Test_XYZController() - static block started');
        // Several static methods are called here to create configuration sObjects before any test case runs.
        checkConfigurationSetup();
        system.debug('Test_XYZController() - static block complete - Current SOQL Queries:' + Limits.getQueries() + '/' + Limits.getLimitQueries());
    }

    static testMethod void updateOrder() {
        
        system.debug('updateOrder() - starting test case setup - Current SOQL Queries:' + Limits.getQueries() + '/' + Limits.getLimitQueries());
        
        TestOrderData td = createTestingOrder();
        Opportunity opp = td.testOpportunity;
        
        system.debug('clientAccountCreateOrder() - testing data created - Current SOQL Queries:' + Limits.getQueries() + '/' + Limits.getLimitQueries());
        
        system.currentPageReference().getParameters().put('oppId', opp.Id);
        system.currentPageReference().getParameters().put('oliId', td.testOpportunityLineItem.Id);
        
        System.Test.startTest();
        
        system.debug('updateOrder() - test started - Current SOQL Queries:' + Limits.getQueries() + '/' + Limits.getLimitQueries());
        
        XYZController controller = new XYZController();
        
        system.debug('updateOrder() - creating order');
        Pagereference pageReference = controller.createOrder();
        System.assert(pageReference != null);
        System.assertEquals('/' + opp.Id, pageReference.getUrl());
        
        String orderId = system.currentPageReference().getParameters().get('orderId');
        system.debug('updateOrder() - orderId:' + orderId);

        system.currentPageReference().getParameters().put('oppId', opp.Id);
        system.currentPageReference().getParameters().put('orderId', opp.ABOrderID__c);
        
        system.debug('updateOrder() - updating order');
        controller.updateOrder();
        
        System.Test.stopTest();
    }

}

 
Daniel BallingerDaniel Ballinger

I've narrowed the cause of the high SOQL query count to the triggers that fire as I'm inserting the testing data.

 

I'll need to find a way to disable the triggers when the test cases are inserting records to act as test data.

 

At this stage I'll look at using a mixture of System.runAs() in the test case with Test.isRunningTest() and UserInfo.getName() in the triggers to bypass the functionality.
Starz26Starz26

Set up a static variable / class to indicate a test is running that should not allow the trigger to fire...

 

set the variable to true test classes that disbale triggers, then set back to false when you want triggers to run, and check it in the triggers....

 

As an alternativ, if you do not want to explicitly allow/denyg triggers running but want to always disable triggers during all tests, wrap each trigger in a :

 

if(!system.testisRunning()){

 

}

 

 

This was selected as the best answer
craigmhcraigmh

You shouldn't "disable" a trigger for certain data, the trigger should be able to handle any data passed to it.
Starz26Starz26
craigmh wrote:

You shouldn't "disable" a trigger for certain data, the trigger should be able to handle any data passed to it.

While I agree with you on this point, it is not the case here

 

Bulkified triggers are designed to handle bulk records of the same object. They are not designed to handle the cumulative effects of setting up and inserting multiple rows for multiple different objects at the same time as you do in test methods.

 

Due to the nature of parent triggers firing after insert of children etc, the cumulative effects of adding records for testing across multiple objects can add up...

 

For example: Inserting Account (1000 records) 15 soql - Inserting Opportunities - 20 soql, then runs parenent trigger etc.... Not inserting a custom object unrelated to the two, 15 soql, keep going and you add up to 100 quickly in a test method.

 

This DOES NOT happen outside of test methods as you typicall do not insert BULK data across several objects at the same time

 

So creating test records for test methods can be expensive. If you know that the trigger is irrelevant to the records being inserted, setting the flag can save you soql statements. This will allow you to utilize additional soql as needed within the test methods outside of the test.startTest and stopTest Methods.

 

In woul be even better if SFDC would allow us to split up transactions when inserting multiple objects to mimic what would really occur....
craigmhcraigmh

From my testing, a trigger will never apply the limits to more than 200 records at a time. From what I've done in testing, that's how many records a trigger processes at once. Although I'd like to see that for sure using the size() method on either trigger.old or trigger.new...
Starz26Starz26
craigmh wrote:

From my testing, a trigger will never apply the limits to more than 200 records at a time. From what I've done in testing, that's how many records a trigger processes at once. Although I'd like to see that for sure using the size() method on either trigger.old or trigger.new...

You are correct, but I think you are missing the point:

 

In a test methods

 

test{

 

Account a = New Account(......);

insert a;

Opportunity o = New Opportunity(......);

insert o;

Product2 = New........

 

and on until you create the entire set of base records

 

 

}

 

In this case the SOQL statements add up as the test method is a single transaction. This would not occur in real bulk data uploads as you are working with one object at a time.

 

 
craigmhcraigmh

Ahhh, ok, got it now. Hitting 101 queries would take some effort, I usually divide up what I'm doing to avoid that.
Starz26Starz26

Agreed...

 

In my case though, I have a custom object that is 5 relationships away from the Account. It updates the account info as well as info on some of the middle objects. So to test that I have to create all the records and children in between before I even get to the object I am testing....

 

It adds up to a bunch of SOQLs as many of the middle objects have before and after update triggers....ugh...very expensive to do in terms of soql statements.

 

 

SF, if you are reading, please allow us to indicate an individual transactions in test methods to accuratly reflect and test ... That way I could wrap the insert account within a transaction, then do the opportunity, etc....
Daniel BallingerDaniel Ballinger

Thanks everyone for the assistance.

 

By using Test.isRunningTest() and a combination of either UserInfo.getName() with System.runAs() or a static variable I can disable the triggers during the SOQL expensive phase of the test setup. That way I won't run into the SOQL limit when I setup several Account, Contact, Opportunity, Product2, PricebookEntry, OpportunityLineItem and custom sObject records.

 

I did raise the Idea test method setup code with separate governor limits to the core test itself to raise the issue with Salesforce.

 

Thanks,

Daniel
amritamrit

Hi,

 

Can you send me a sample code on this.how did u disable the trigger?im also facing same issue .

 

Thanks
Daniel BallingerDaniel Ballinger

I went with a specific user rather than static variable soltuion so that I could also disable validation rules if required.

 

So in your testMethod setup code:

 

Profile p = [SELECT Id FROM Profile WHERE Name='System Administrator']; //Standard User
    	string label = 'UserIdentifier';
    	
    	string rand = String.valueOf(Math.rint(Math.random() * 10000)); 
    	
    	User u = new User(Alias='testUser', Email='test@example.com', 
    		EmailEncodingKey='UTF-8', LastName=label, LanguageLocaleKey='en_US', 
      		LocaleSidKey='en_US', ProfileId = p.Id, 
      		TimeZoneSidKey='America/Los_Angeles', UserName='unitTest' + rand + '@example.com');
      		
    	System.runAs(u) {
             // Setup code here that bypasses triggers
        }

 Then at the start of your trigger you want to bypass:

	if(Test.isRunningTest() && UserInfo.getName() == 'UserIdentifier') {
		return;
	}

 You could also consider using an existing user rather than creating one or storing the test users name in a custom label.