Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Salesforce Admin 110Salesforce Admin 110 

reducing soqls - too many soql queries

i am trying to tidy my code so that i reduce the number of soqls here is a sample 
public  class updatedeletopps {

    public static void updatedelopps(List<Contact> contactIds){
        Id recId = Schema.SObjectType.Easy_Opportunity__c.getRecordTypeInfosByName().get('Residential Sales').getRecordTypeId();
        
        
//n1        
//
        contact[] activecontacts = [select id, active_contact__c from contact where id in :contactIds];
        set<id> activecontactsset = new set<id>();
        for(contact con : activecontacts) 
            if(con.active_contact__c == 'No') 
            	activecontactsset.add(con.Id);

     List<easy_opportunity__c> oppsToUpdate1 = new List<easy_opportunity__c>();
        list<easy_opportunity__c> opptys = [select id, Stage__c, recordtypeid from easy_opportunity__c where Contact_Name__c in: 
		activecontactsset and name = 'Market Appraisal' and stage__c = 'Closed Lost' and recordtypeid = :recId];
     for(easy_opportunity__c opp1 : opptys)
     { 
         opp1.Stage__c='Closed Lost';
         opp1.Opportunity_Lost_Reason__c = 'Lost Contact';
         opp1.Opportunity_Rating__c = 'Cold';
         oppsToUpdate1.add(opp1);
     }

     update oppsToUpdate1;
        
    list<easy_opportunity__c> oppstodelete2 = new list<easy_opportunity__c>();
        list<easy_opportunity__c> opptys1 = [select id, stage__c, recordtypeid from easy_opportunity__c where contact_name__c 

in:activecontactsset and (name =  'Lost MA Upsell - Gas Safety Check' or name = 'Lost MA Upsell - Home Buyer Report' or name = 'Lost MA Upsell - Conveyancing' or name = 'Lost MA Upsell - Mortgage') ];
        for(easy_opportunity__c opp2 : opptys1)
        {
        oppstodelete2.add(opp2);
        }
        delete oppstodelete2;
Blake TanonBlake Tanon
Your issue isn't in this code snippet specifically, can you post where this is running from?
Salesforce Admin 110Salesforce Admin 110
Hi Blake, that snippet is repeated 19/20 times

here's the trigger:
  
trigger iainb on Contact (before insert, after insert, before update, after update, before delete, after delete, after undelete) {

if(trigger.isupdate && trigger.isafter){


iain.sellercon (Trigger.new);
iain.buyerconconveyancy (Trigger.new);
iain.buyerconmortgage (Trigger.new);
iain.buyerconhomebuyerreport (Trigger.new);
iain.buyercongassafetycheck (Trigger.new);   
    
updatedeletopps.updatedelopps (Trigger.new);
    
   
}
    
if(Trigger.isinsert && Trigger.isafter){    
    
iain.sellercon3 (Trigger.new);  
iain.buyerconconveyancy3 (Trigger.new);  
iain.buyerconhomebuyerreport3 (Trigger.new);  
iain.buyercongassafetycheck3 (Trigger.new); 
iain.buyerconmortgage3 (Trigger.new);    
    
    
  
    
 
    
}  

if(Trigger.isinsert && Trigger.isbefore){
        
iain.contactuserfields(Trigger.new);        
        
    }
if(Trigger.isupdate && Trigger.isbefore){
        
iain.contactuserfields(Trigger.new);        
        
    }

    
    
//end    
}

 
Salesforce Admin 110Salesforce Admin 110
here is another snippet just below the first snippet i gave you: 
//n2
  /*   contact[] activecontacts1 = [select id, active_contact__c from contact where id in :contactIds]; */
        set<id> activecontactsset1 = new set<id>();
        for(contact con : activecontacts)
    
         if(con.active_contact__c == 'No')
              activecontactsset1.add(con.Id);
        
        List<easy_opportunity__c> oppsToUpdate3 = new List<easy_opportunity__c>();
		List<easy_opportunity__c> opptys2 = [select id, Stage__c, recordtypeid from easy_opportunity__c where Contact_Name__c in: 

activecontactsset1 and name = 'Market Appraisal' and stage__c = 'Closed Won' and recordtypeid = :recId];
    for(easy_opportunity__c opp3 : opptys2)
     { 
          opp3.Stage__c='Closed Won';
          opp3.Opportunity_Rating__c = 'Warm';
          oppsToUpdate3.add(opp3);
     }

     update oppsToUpdate3; 

 list<easy_opportunity__c> oppstoupdate4 = new list<easy_opportunity__c>();
        list<easy_opportunity__c> opptys3 = [select id, stage__c, recordtypeid from easy_opportunity__c where contact_name__c 
	in:activecontactsset1 and name = 'Instruction Opportunity' and stage__c = 'Closed Won' and recordtypeid = :recId];
        for(easy_opportunity__c opp4 : opptys3)
        {
         opp4.Stage__c='Closed Lost';
        opp4.Opportunity_Lost_Reason__c = 'Lost Contact';
        opp4.Opportunity_Rating__c = 'Cold';
        oppstoupdate4.add(opp4);
        }
        update oppstoupdate4;
        
        list<easy_opportunity__c> oppstodelete28 = new list<easy_opportunity__c>();
        list<easy_opportunity__c> opptys4 = [select id, stage__c, recordtypeid from easy_opportunity__c where contact_name__c 

in:activecontactsset1 and name = 'Instruction Opportunity'];
        for(easy_opportunity__c opp28 : opptys4)
        {
        oppstodelete28.add(opp28);
        }
        delete oppstodelete28;

 
Blake TanonBlake Tanon
First, why is it running that method 20 times - that is 90 queries out of the 100 limit.  I assume there are queries in the other methods that are called before it also, you should see if any of these can be combined/returned/passed along.
Salesforce Admin 110Salesforce Admin 110
the soql you see in the in the easy_opportunity__c lists are unique looking for different opp names thats why i have repeated that snippet several times