How to avoid too many soql queries error in test class

Question

I am writing test class for ignoring the weekends and Holidays from Holiday__c custom object

Test Class code:

@isTest
private class DeliveryDateUpdationtest{
static testMethod void DeliveryDateUpdation()
{
account[] testaccount1 = new List<Account>();
testAccount1.add(new Account(Name='Test',Type='Test',Status__c = 'Draft',Call_Status__c = 'called On'));
testAccount1.add(new Account(Name='Test1',Type='Test',Status__c = 'Draft',Call_Status__c = 'called On',JDE_Date_Delivery_Offset__c = 2.00));
insert testAccount1;

Product2 testProduct = new Product2(Name='Test', IsActive=true, CurrencyIsoCode = 'EUR');
insert testProduct;

Pricebook2 testPriceBook = new Pricebook2(Name='Test', IsActive=true,Shipment_Method__c = 'Internal',Market__c = 'FR');
insert testPriceBook;

PricebookEntry[] testPricebookEntries = new list<PricebookEntry>();
testPricebookEntries.add(new PricebookEntry(Pricebook2Id=Test.getStandardPricebookId(), UnitPrice=4.99, Product2Id=testProduct.Id,CurrencyIsoCode = 'EUR'));
testPricebookEntries.add(new PricebookEntry(Pricebook2Id=testPriceBook.Id, UnitPrice=4.99, UseStandardPrice=false, Product2Id=testProduct.Id,CurrencyIsoCode = 'EUR'));
insert testPricebookEntries;

order[] order1 = new list<order>();
order1.add(new Order(AccountId = testAccount1[0].id, EffectiveDate = Date.today(),Status = 'Draft',Shipping_Urgency__c = 'high',Subtype__c = 'offer',Pricebook2Id=testPriceBook.Id,OrderDeliveryDate__c = date.newinstance(2017, 3, 10)));
order1.add(new Order(AccountId = testAccount1[1].id, EffectiveDate = Date.today(),Status = 'Draft',Shipping_Urgency__c = 'high',Subtype__c = 'offer',Pricebook2Id=testPriceBook.Id,OrderDeliveryDate__c = date.newinstance(2017, 3, 10)));
order1.add(new order(AccountId = testAccount1[0].id, EffectiveDate = Date.today(),Status = 'Draft',Shipping_Urgency__c = 'high',Subtype__c = 'ASP',Pricebook2Id=testPriceBook.Id,OrderDeliveryDate__c = date.newinstance(2017, 3, 10)));
insert order1;

Holiday__c[] holiday1 = new list<Holiday__c>();
holiday1.add(new Holiday__c(Date__c = Date.today(),Country__c = 'FR'));
holiday1.add(new Holiday__c(Date__c = Date.today(),Country__c = 'IT'));
insert holiday1;

OrderItem orderItemOffer = new OrderItem();
orderItemOffer.PricebookEntryId = testPricebookEntries[1].Id;
orderItemOffer.OrderId = order1[0].Id;
orderItemOffer.Quantity = 2; 
orderItemOffer.UnitPrice = 50; 
orderItemOffer.Delivery_Date__c = date.newinstance(2017, 3, 10);
insert orderItemOffer;

orderItemOffer  = [select id, Delivery_Date__c from orderItem where Id = :orderItemOffer.Id];
System.assertEquals(date.newinstance(2017, 04, 06), orderItemOffer.Delivery_Date__c);


OrderItem orderItemOffer1 = new OrderItem();
orderItemOffer1.PricebookEntryId = testPricebookEntries[1].Id;
orderItemOffer1.OrderId = order1[1].Id;
orderItemOffer1.Quantity = 2; 
orderItemOffer1.UnitPrice = 50; 
orderItemOffer1.Delivery_Date__c = date.newinstance(2017, 3, 10);

insert orderItemOffer1;

orderItemOffer1 = [select id, Delivery_Date__c from orderItem where Id = :orderItemOffer1.Id];
System.assertEquals(date.newinstance(2017, 04, 06), orderItemOffer.Delivery_Date__c);

OrderItem orderItemASP = new OrderItem();
orderItemASP.PricebookEntryId = testPricebookEntries[1].Id;
orderItemASP.OrderId = order1[2].Id;
orderItemASP.Quantity = 2; 
orderItemASP.UnitPrice = 50; 
orderItemASP.Delivery_Date__c = date.newinstance(2017, 3, 10);
insert orderItemASP;

orderItemASP = [select id, Delivery_Date__c from orderItem where Id = :orderItemASP.Id];
System.assertEquals(date.newinstance(2017,04,04), orderItemASP.Delivery_Date__c);

}
}

But it is affecting another trigger in same edition in order object

Trigger AutoEmailsending on Order (after update) 
{
    Map<Id,Order> Orderdetail = new Map<Id,Order>();
    Set<Id> Accountid = new Set<Id>();
    User currentUser = [Select Id,Phone,name from User where Id =:System.UserInfo.getUserId()];
    for(order or1 : Trigger.new)
    {
        if(or1.Status == 'Submitted' && (or1.Subtype__c =='Offer' || or1.Subtype__c =='ASP') && (or1.CreatedById == currentUser.id))
        {

            Orderdetail.put(or1.id,or1);
            Accountid.add (or1.accountid);


        }
    }
    List<Attachment> Attachmentlst = [Select Id,
                                             ParentId, 
                                             Name,
                                             BodyLength,
                                             Body,
                                             Description 
                                        From Attachment 
                                        where ParentId=:Orderdetail.keyset()];
    Map<Id,Account> Accountmap = New Map<Id,account>([Select id,
                                                             Name,
                                                             Email_Address__c,
                                                             Market__c,
                                                             JDE_ship_to_account_no__c 
                                                        from account
                                                        where id in:accountid]);
    Map<String, Blob> resources = new Map<String, Blob>();
    for(StaticResource resource:[SELECT Name, Body FROM StaticResource where Name IN ('TC_for_France','TC_for_Italy')]) 
    {
        resources.put(resource.Name, resource.Body);
    }

But for particularly in this line it is showing error:

User currentUser = [Select Id,Phone,name from User where Id =:System.UserInfo.getUserId()];

Error like:System.LimitException: Too many SOQL queries: 101

How to rectify the error ,please guide me

It's showing the error on this line because it's the query that is the one hundred and first query to be called. That's not the problem. There must be other triggers with queries inside some loops. Or maybe too many triggers fired. — Martin Lezer, Mar 30 '17 at 07:22
Now what i have to do to rectify this error?I have changed the debug log level to profiling for finest, but i am not able to find it — , Mar 30 '17 at 07:23

score 2 · Answer 1 · answered Mar 30 '17 at 07:27

There are many best practices for writing test classes.

Based on your code, suggesting some of them:

Avoid Recursive Trigger Calls:
- Always better to avoid recursion especially on after update trigger event.
Use Test.startTest() and Test.stopTest():
- Use Test start and stop methods in test methods to restrict your utilization of governor limits in your actual test.
Use test methods:
- Write multiple test methods for covering different test scenarios.

How to avoid too many soql queries error in test class

1 Answers1