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
ChickenOrBeefChickenOrBeef 

Nearing SOQL governor limits, Need advice

Hey everyone,

When creating an opportunity, I'm getting those "Apex governor limit warning" emails telling me that 89 SOQL queries were ran (out of the limit of 100).

For my triggers, I create one main trigger for each object which calls a bunch of classes. So the SOQL queries are made in each class. Here is my opportunity trigger:

trigger MainTriggerOpportunity on Opportunity (before insert, before update, after insert, after update) {
    
    if(trigger.isBefore){
            
        if(trigger.isInsert){
            
            if(checkRecursive.runBeforeInsertOnce()){
            
            ClassOppIndustry updater = new ClassOppIndustry();
            updater.updateOppIndustry(Trigger.new);
            
            ClassRenewalDate updater1 = new ClassRenewalDate();
            updater1.updateRenewalDate(Trigger.new);
                
            ClassLeadConvertUpdates updater2 = new ClassLeadConvertUpdates();
            updater2.updateLeadConvertFields(Trigger.new);
                
            ClassStageDateStampInsert updater3 = new ClassStageDateStampInsert();
            updater3.stampDate(Trigger.new);
                
            }
            
        }
        if(trigger.isUpdate){
            
            if(checkRecursive.runBeforeUpdateOnce()){
            
            ClassOppIndustry updater = new ClassOppIndustry();
            updater.updateOppIndustry(Trigger.new);
            
            ClassRenewalDate updater1 = new ClassRenewalDate();
            updater1.updateRenewalDate(Trigger.new);
                
            ClassBrandParent updater2 = new ClassBrandParent();
            updater2.addBrandParent(Trigger.new);
                
            ClassCallScheduledAdvance updater3 = new ClassCallScheduledAdvance();
            updater3.addCallScheduledDate(Trigger.new,Trigger.oldMap);
                
            ClassStageDateStamp updater4 = new ClassStageDateStamp();
            updater4.stampDate(Trigger.new,Trigger.oldMap);
                
            }
     }
  }
    
    
    if(trigger.isAfter){
        
        if(trigger.isInsert){
            
            if(checkRecursive.runAfterInsertOnce()){
            
            ClassRenewalProcess updater = new ClassRenewalProcess();
            updater.updateRenewalStatus(Trigger.new);
            
            ClassOppBrandCreate updater1 = new ClassOppBrandCreate();
            updater1.addBrand(Trigger.new);
                
            ClassOppTeamMember updater2 = new ClassOppTeamMember();
            updater2.insertOpp(Trigger.new);
                
            ClassPrimaryContactCopy updater4 = new ClassPrimaryContactCopy();
            updater4.copyPrimary(Trigger.new);
                
            ClassOppPicklists updater5 = new ClassOppPicklists();
            updater5.updatePicklists(Trigger.new);
                
            ClassBDSourceCreate updater6 = new ClassBDSourceCreate();
            updater6.addBDSource(Trigger.new);
                
            ClassMapPlatform updater7 = new ClassMapPlatform();
            updater7.copyPlatform(Trigger.new);
                
            ClassAnnualDealRevenue updater8 = new ClassAnnualDealRevenue();
            updater8.addAnnualRevenue(Trigger.new);
                
            ClassDemandGenOpportunity updater9 = new ClassDemandGenOpportunity();
            updater9.addContactRoles(Trigger.new);
                
            }
       }
        if(trigger.isUpdate){
            
            if(checkRecursive.runAfterUpdateOnce()){
            
            ClassRenewalProcess updater = new ClassRenewalProcess();
            updater.updateRenewalStatus(Trigger.new);
            
            ClassOppBrandCreate updater1 = new ClassOppBrandCreate();
            updater1.addBrand(Trigger.new);
                
            ClassDrawLoopUpdates updater3 = new ClassDrawLoopUpdates();
            updater3.updateDrawLoopFields(Trigger.new);
                
            ClassPrimaryContactCopy updater4 = new ClassPrimaryContactCopy();
            updater4.copyPrimary(Trigger.new);
                
            ClassOppPicklists updater5 = new ClassOppPicklists();
            updater5.updatePicklists(Trigger.new);
                
            ClassBDSourceCreate updater6 = new ClassBDSourceCreate();
            updater6.addBDSource(Trigger.new);
                
            ClassMapPlatform updater7 = new ClassMapPlatform();
            updater7.copyPlatform(Trigger.new);
                
            ClassAnnualDealRevenue updater8 = new ClassAnnualDealRevenue();
            updater8.addAnnualRevenue(Trigger.new);
                
            }
        }
    }
}

The individual classes don't have too many unneeded SOQL queries, so I'm guessing the only way to improve this situation is to make large queries in the main trigger itself, pass those lists into the classes, and then iterate on them? Does that sound right?

Thanks!
-Greg
Best Answer chosen by ChickenOrBeef
Craig WarheitCraig Warheit
There are a few ways you could implement it, but here's what I'm thinking:

trigger MainTriggerOpportunity on Opportunity (before insert, before update, after insert, after update) {
	if(trigger.isAfter){
		if(trigger.isUpdate){
			if(checkRecursive.runBeforeInsertOnce()){
				OpportunityTriggerHandler.onAfterUpdate(trigger.new);
			}
		}
	}
	....
}


public with sharing class OpportunityTriggerHandler {

	
	public void onAfterUpdate(Opportuntiy[] newOpportunities){
		Set<id> AccountIds = new Set<Id>();
		list<Account> listRenewStatus = new list<Account>();
		list<Account> listBrandUpdate = new list<Account>();
		list<Account> listDrawLoop = new list<Account>();
		for(Opportunity o:newOpportunities){
			accountIds.add(o.accountId);
		}

		for(Account a:[Select id, name, renewStatus, brandupdate, drawloop FROM Account where Id in:accountIds]){
			if(a.renewStatus = conditionForUpdate){
				listRenewStatus.add(a);
			}

			if(a.brandupdate = conditionForUpdate){
				listBrandUpdate.add(a);
			}

			if(a.drawloop = conditionForUpdate){
				listDrawLoop.add(a);
			}
		}

		if(!listRenewStatus.isEmpty()){
			 ClassRenewalProcess updater = new ClassRenewalProcess();
			 updater.updateRenewalStatus(listRenewStatus);
		}

		if(!listBrandUpdate.isEmpty()){
			 ClassRenewalProcess updater1 = new ClassOppBrandCreate();
			 updater1.addBrand(listBrandUpdate);
		}

		if(!listDrawLoop.isEmpty()){
			 ClassDrawLoopUpdates updater1 = new ClassDrawLoopUpdates();
			 updater1.updateDrawLoopFields(listDrawLoop);
		}
	}

}

You could substitute Accounts for whatever it is you are querying and there might be more than one object being queried, BUT, if those three classes each queried the Account table, here you would only query it once then use that for each of the methods.

All Answers

Sonam_SFDCSonam_SFDC
Yes, it does sound right...getting the records being updated in the trigger and pass the list/Map as an argument in the methods being called in the trigger.
Deepak Kumar ShyoranDeepak Kumar Shyoran
As it seems that you are working on multiple Class and performing DML on different Object so please make sure you are not performing and DML inside a for loop. Best practice is to take them in a list and then insert the list.
ChickenOrBeefChickenOrBeef
Thanks for the responses. Is there a way for me to check exactly which SOQL queries are being run when I test an action?

I think that's the first step for me, since while my Opportunity trigger only has 49 total SOQL queries, my Opportunity, Account, and Contact triggers have 112 SOQL queries combined. So I'm guessing all three triggers are being run, but I'd like to see specifically which queries are running.

Then I can try and put some queries within IF statements, combine some queries, and perhaps make a larger query in the main trigger which passes the list into multiple classes.

Thanks!
-Greg
Craig WarheitCraig Warheit
If you turn on your debug logs, you should be able to see each of the queries being run. 
ChickenOrBeefChickenOrBeef
Hey Craig,

I just played around with the filter and I pretty much found what I'm looking for when I filtered the logs by "SOQL_EXECUTE_BEGIN". The only issue is that I can't see which Class or Method those queries are in. So I tried filtering the logs by "METHOD_ENTRY", but that also incudes the "SYSTEM_METHOD_ENTRY" events, which clogs up the log. Any ideas?

Thanks!
-Greg
Craig WarheitCraig Warheit
Are you running out of debug statements or is there just too much noise in the debug log?

You can try opening up the Developer Console - while it's open, it will create a log for each trasnaction and then you can filter those logs more easily with the filter tools they provide.  The other thing you can do is download the log and open it up in a text editor and get rid of the all of the debug statements you don't need.  

Otherwise, look for the SOQL_EXECUTE_BEGIN, the look up to the last open METHOD_ENTRY and that should tell you from which method the SOQL query is being run.

Looking at your trigger, it's good that you are putting all the business logic in a class, but you might want to consider creating a class that handles all of that traffic and only calls the methods necessary.  I'm guessing that many of those classes is executing SOQL that is nearly redundant.  Perhaps you could create a method that makes one query and then in a for loop you populate lists or maps for each of those methods.  Then you can check to see if the list or map is populated, and if it is, call the method and pass it the appropriate data structure.
ChickenOrBeefChickenOrBeef
There's just too much noise. So I'll see if I can use your idea and download the log and then filter it in excel.

As for your last paragraph, are you saying I should make the queries in the main trigger, divy up the records into lists and maps (in the trigger), check to see if the lists and maps are populated (in the trigger), and then pass those lists and maps into the called classes? 
Craig WarheitCraig Warheit
There are a few ways you could implement it, but here's what I'm thinking:

trigger MainTriggerOpportunity on Opportunity (before insert, before update, after insert, after update) {
	if(trigger.isAfter){
		if(trigger.isUpdate){
			if(checkRecursive.runBeforeInsertOnce()){
				OpportunityTriggerHandler.onAfterUpdate(trigger.new);
			}
		}
	}
	....
}


public with sharing class OpportunityTriggerHandler {

	
	public void onAfterUpdate(Opportuntiy[] newOpportunities){
		Set<id> AccountIds = new Set<Id>();
		list<Account> listRenewStatus = new list<Account>();
		list<Account> listBrandUpdate = new list<Account>();
		list<Account> listDrawLoop = new list<Account>();
		for(Opportunity o:newOpportunities){
			accountIds.add(o.accountId);
		}

		for(Account a:[Select id, name, renewStatus, brandupdate, drawloop FROM Account where Id in:accountIds]){
			if(a.renewStatus = conditionForUpdate){
				listRenewStatus.add(a);
			}

			if(a.brandupdate = conditionForUpdate){
				listBrandUpdate.add(a);
			}

			if(a.drawloop = conditionForUpdate){
				listDrawLoop.add(a);
			}
		}

		if(!listRenewStatus.isEmpty()){
			 ClassRenewalProcess updater = new ClassRenewalProcess();
			 updater.updateRenewalStatus(listRenewStatus);
		}

		if(!listBrandUpdate.isEmpty()){
			 ClassRenewalProcess updater1 = new ClassOppBrandCreate();
			 updater1.addBrand(listBrandUpdate);
		}

		if(!listDrawLoop.isEmpty()){
			 ClassDrawLoopUpdates updater1 = new ClassDrawLoopUpdates();
			 updater1.updateDrawLoopFields(listDrawLoop);
		}
	}

}

You could substitute Accounts for whatever it is you are querying and there might be more than one object being queried, BUT, if those three classes each queried the Account table, here you would only query it once then use that for each of the methods.
This was selected as the best answer