Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I am new here and have no coding experience whatsoever. I am a Salesforce admin with 3 years experience.

Before I started at my current company, they hired a consultant to create some APEX triggers. They were working fine until a couple weeks ago. I created a workflow rule to send out a welcome email when an Opportunity is closed won and this workflow updates the End User Contact field on the Opportunity. Ever since I activated it, we have been randomly getting this too many SOQL query error when a Lead is attempting to convert to an Account, Contact and Opportunity.

It's completely random in the fact that it doesn't happen for every Lead and if I were to create a Lead with all the exact same information as the one giving me the problem, the new Lead will convert fine.

If I convert a Lead to just an Account and Contact, no error is received.

I've spoke with several different people at Salesforce Dev support and they keep telling me the trigger is running in a for loop but can't explain why it is happening now (trigger is about 6 months old) and only specific records which I can't figure out the differences in the ones that give the error and the ones that don't. So they keep telling me to edit the code to remove the for loop (which I don't know how to do).

Also, the workflow rule I mentioned previously? If I deactivate the Workflow rule, the problem goes away.

Can anyone help me?

trigger LeadSetTerritory on Lead (before insert, before update, after insert, after update) 
{  
    Set<String> CountryLookup = new Set<String>();
    Map<Id, String> LeadToCountry = new Map<Id, String>();

    for(Lead lead : trigger.new)
    {
        // Not if we are doing a conversion
        if(! lead.IsConverted )
        {            
            String lookup = lead.Country__c;

            // Use the State and Country to find the appropriate Territory Object
            if(lead.Country__c == 'United States')
            {                               
                // This is a US Territory so we need to 
                // append to the Territory Name ' - ' + State                
                //lookup  += ' - ' + lead.State_Territory__c;

                String stateString = '';
                //cbarisic - made a mod here to build it from End user/Reseller/Distributor
                if(lead.End_User_State_Territory2__c != null && lead.End_User_State_Territory2__c != '') {
                  stateString = lead.End_User_State_Territory2__c;
                }
                else if(lead.Reseller_State_Territory2__c != null && lead.Reseller_State_Territory2__c != '') {
                  stateString = lead.Reseller_State_Territory2__c;
                }
                else if(lead.Distributor_State_Territory2__c != null && lead.Distributor_State_Territory2__c != '') {
                  stateString = lead.Distributor_State_Territory2__c;
                }
                else if(lead.State_Territory__c != null && lead.State_Territory__c != '') {
                  stateString = lead.State_Territory__c;
                }
                lookup  += ' - ' + stateString;
            }

            CountryLookup.add(lookup);
            LeadToCountry.put(lead.Id, lookup);          
        }
    }


    //Populate OWNERSHIP Section
    if(trigger.isBefore){

    Map<string,Territory__c> TerritoryByCountryMap=TerritoryRecord.BuildTerritoryByCountryMap(CountryLookup);

        for(Lead l : trigger.new){
            String key = LeadToCountry.get(l.Id);
            if (TerritoryByCountryMap.containsKey(key)){                       
              l.Territory__c=TerritoryByCountryMap.get(key).Id;              
                l.Sales_Engineer_T__c = TerritoryByCountryMap.get(key).Global_Acct_Mgr__r.name;                
                l.Inside_Sales_Mgr_T__c = TerritoryByCountryMap.get(key).Inside_Sales_Mgr__r.name;
                l.ITAM_T__c = TerritoryByCountryMap.get(key).ITAM__r.name;
                l.ITAM_2_T__c = TerritoryByCountryMap.get(key).ITAM_2__r.name;
                l.ITAM_3_T__c = TerritoryByCountryMap.get(key).ITAM_3__r.name;
                l.Sales_Director_T__c = TerritoryByCountryMap.get(key).Sales_Director__r.name;
                l.TAM_Agent_T__c = TerritoryByCountryMap.get(key).TAM_Agent__r.name;
                l.TAM_Agent_2_T__c = TerritoryByCountryMap.get(key).TAM_Agent_2__r.name;        
                l.TAM_Agent_3_T__c = TerritoryByCountryMap.get(key).TAM_Agent_3__r.name;
                l.Sales_Territory_Manager_T__c = TerritoryByCountryMap.get(key).Sales_Territory_Manager__r.name;
                l.Sales_Vice_President_T__c = TerritoryByCountryMap.get(key).Sales_Vice_President__r.name;
                l.Global_Sales_Vice_President_T__c = TerritoryByCountryMap.get(key).Global_Sales_Vice_President__r.name;
              //email fields
              l.Sales_Engineer_Email__c = TerritoryByCountryMap.get(key).Global_Acct_Mgr__r.email;
              l.Inside_Sales_Mgr_Email__c = TerritoryByCountryMap.get(key).Inside_Sales_Mgr__r.Email;
                l.ITAM_Email__c = TerritoryByCountryMap.get(key).ITAM__r.Email;
                l.ITAM_2_Email__c = TerritoryByCountryMap.get(key).ITAM_2__r.Email;
                l.ITAM_3_Email__c = TerritoryByCountryMap.get(key).ITAM_3__r.Email;
                l.Sales_Director_Email__c = TerritoryByCountryMap.get(key).Sales_Director__r.Email;
                l.TAM_Agent_Email__c = TerritoryByCountryMap.get(key).TAM_Agent__r.Email;
                l.TAM_Agent_2_Email__c = TerritoryByCountryMap.get(key).TAM_Agent_2__r.Email;        
                l.TAM_Agent_3_Email__c = TerritoryByCountryMap.get(key).TAM_Agent_3__r.Email;
                l.Sales_Territory_Manager_Email__c = TerritoryByCountryMap.get(key).Sales_Territory_Manager__r.Email;
                l.Sales_Vice_President_Email__c = TerritoryByCountryMap.get(key).Sales_Vice_President__r.Email;
                l.Global_Sales_Vice_President_Email__c = TerritoryByCountryMap.get(key).Global_Sales_Vice_President__r.Email;
            }
        }
    }

    // Set the Sharing for Lead
    if(trigger.isAfter){

        //set of Active User for checkups
        Map<id,User> ActiveUserIds = new Map<id,User>([select id from User where Isactive=true]);

        // List of share objects to delete
        List<Id> sharesToDelete = new List<Id>();

        //Deleting Existing Manual Shares
        for(Lead l : trigger.new)
      sharesToDelete.add(l.Id);               

        delete [SELECT Id FROM LeadShare WHERE LeadId IN :sharesToDelete and RowCause = 'Manual'];

      //Recreate Share
      Map<string,Territory__c> TerritoryByCountryMap=TerritoryRecord.BuildTerritoryByCountryMap(CountryLookup);

      List<LeadShare> listOfSharing = new List<LeadShare>();

        for(Lead l : trigger.new) {        


            String key = LeadToCountry.get(l.Id);
            if (TerritoryByCountryMap.containsKey(key)){         

              // Added a check to see if the share is being added to an owner
              if (TerritoryByCountryMap.get(key).Global_Acct_Mgr__c!=null&&TerritoryByCountryMap.get(key).Global_Acct_Mgr__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Global_Acct_Mgr__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Global_Acct_Mgr__c));

              if (TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c!=null&&TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c));

              if (TerritoryByCountryMap.get(key).ITAM__c!=null&&TerritoryByCountryMap.get(key).ITAM__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).ITAM__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).ITAM__c));

              if (TerritoryByCountryMap.get(key).ITAM_2__c!=null&&TerritoryByCountryMap.get(key).ITAM_2__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).ITAM_2__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).ITAM_2__c));

              if (TerritoryByCountryMap.get(key).ITAM_3__c!=null&&TerritoryByCountryMap.get(key).ITAM_3__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).ITAM_3__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).ITAM_3__c));

              if (TerritoryByCountryMap.get(key).Sales_Director__c!=null&&TerritoryByCountryMap.get(key).Sales_Director__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Sales_Director__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Sales_Director__c));

              if (TerritoryByCountryMap.get(key).TAM_Agent__c!=null&&TerritoryByCountryMap.get(key).TAM_Agent__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).TAM_Agent__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).TAM_Agent__c));

              if (TerritoryByCountryMap.get(key).TAM_Agent_2__c!=null&&TerritoryByCountryMap.get(key).TAM_Agent_2__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).TAM_Agent_2__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).TAM_Agent_2__c));

              if (TerritoryByCountryMap.get(key).TAM_Agent_3__c!=null&&TerritoryByCountryMap.get(key).TAM_Agent_3__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).TAM_Agent_3__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).TAM_Agent_3__c));

              if (TerritoryByCountryMap.get(key).Sales_Territory_Manager__c!=null&&TerritoryByCountryMap.get(key).Sales_Territory_Manager__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Sales_Territory_Manager__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Sales_Territory_Manager__c));

              if (TerritoryByCountryMap.get(key).Sales_Vice_President__c!=null&&TerritoryByCountryMap.get(key).Sales_Vice_President__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Sales_Vice_President__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Sales_Vice_President__c));

              if (TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c!=null&&TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c));

            }    
        }

        if (listofsharing.size()>0)
          insert listOfSharing;        
   }

And the TerritoryRecord Class:

public with sharing class TerritoryRecord {

  public static Map<string,Territory__c> BuildTerritoryByCountryMap(set<string> CountryLookups){

    // Get a list of Country Id's 
      List<Country__c> countries = [SELECT Id FROM Country__c WHERE Name IN : CountryLookups];

  // make a map of the Territories by Country lookup    
  Map<string,Id> MapCountriesTerritory = new Map<string,Id>();


  for (Territory_By_Country__c tc:[SELECT Territory__c, Country__r.Name FROM Territory_By_Country__c WHERE Country__c IN : countries]){      
    MapCountriesTerritory.put(tc.Country__r.Name,tc.Territory__c);        
  }

  //Map<String, Id> mapOfTerritories = new Map<String, Id>();    

  //for(Territory_By_Country__c tbc : listOfCountries){
      //Id tId = tbc.Territory__c;
      //String c = tbc.Country__r.Name;        
      //mapOfTerritories.put(c, tId);
  //}        

  Map<Id, Territory__c> mapTerritory=new Map<Id,Territory__c>();

  for (Territory__c t:[SELECT Id,name,  US_EMEA__c,
             Global_Acct_Mgr__c,Global_Acct_Mgr__r.name, Global_Acct_Mgr__r.email,
                         Inside_Sales_Mgr__c,Inside_Sales_Mgr__r.name,Inside_Sales_Mgr__r.email, 
                         ITAM__c,ITAM__r.name,ITAM__r.email,
                         ITAM_2__c,ITAM_2__r.name,ITAM_2__r.email,
                         ITAM_3__c,ITAM_3__r.name,ITAM_3__r.email, 
                         Sales_Director__c,Sales_Director__r.name,Sales_Director__r.email,
                         Sales_Territory_Manager__c,Sales_Territory_Manager__r.name,Sales_Territory_Manager__r.email,
                         Sales_Vice_President__c,Sales_Vice_President__r.name,Sales_Vice_President__r.email,
                         Global_Sales_Vice_President__c,Global_Sales_Vice_President__r.name, Global_Sales_Vice_President__r.email,
                         TAM_Agent__c,TAM_Agent_2__c,TAM_Agent_3__c,
                         TAM_Agent__r.name,TAM_Agent_2__r.name,TAM_Agent_3__r.name,
                         TAM_Agent__r.email,TAM_Agent_2__r.email,TAM_Agent_3__r.email
                                                FROM Territory__c
                                                WHERE Id IN : MapCountriesTerritory.values()]){
      mapTerritory.put(t.Id,t) ;                                             
    }


//Result
Map<string,Territory__c> result= new Map<string,Territory__c>();

for (string s:MapCountriesTerritory.keyset()){

  //What's the parent territory
  Id TerritoryId=MapCountriesTerritory.get(s);
  result.put(s,mapTerritory.get(TerritoryId));        
}

   return result;
      }
    }
share|improve this question
    
the workflow rule - does it do a field update on the Opportunity? –  crop1645 Aug 6 at 18:46
    
Yes, it updates the email address on the End User Contact field. I use that field to send the welcome email to the End User –  Trisha M Aug 6 at 18:48
    
you are going to have to post the trigger (which hopefully is short/concise) –  crop1645 Aug 6 at 18:49
    
It's not... It is super long and complicated and to be honest, I can't see what it has to do with Opportunities. It is 145 lines –  Trisha M Aug 6 at 18:50
1  
Just because the error gets thrown in the code you posted does not mean that is where the problem is (Lets say the opportunity code uses 99 SQL and this code uses 2, this code will throw the error but the opportunity code is the real issue). I do not see anything obvious that would cause the issue. I suspect that the code with the actual problem is elsewhere. –  Eric Aug 6 at 19:11

1 Answer 1

up vote 6 down vote accepted

It appears you'd benefit from a caching algorithm. I use this technique in cases where data that's not expected to change in the course of a single transaction is "cached" in static memory for future use. Here's how I design that type of code:

public class TerritoryRecord {
    static Map<String, Territory__c> cache = new Map<String, Territory__c>();

    public static Map<String, Territory__c> BuildTerritoryByCountryMap(Set<String> CountryLookup) {
        Set<String> cacheMiss = CountryLookup.clone();
        cacheMiss.removeAll(cache.keySet());
        if(!cacheMiss.isEmpty()) {
            // Do query, add results into cache; don't worry about the result map yet
        }
        // Build a new map
        Map<String, Territory__c> results = cache.clone();
        // And return only the values the code calls for
        results.keySet().retainAll(CountryCodes);
        return results;
    }
}

This code reduces queries by only requiring a database call when new values are requested.

Caches have three benefits. First, when a workflow rule causes recursion, the cache will be called the second time around, saving the associated database call from the recursion. Secondly, large batches processed at once (e.g. a batch class that updates 2,000 records per execution) won't invoke each database call multiple times due to the 200 per batch processing that occurs behind the scenes, potentially reducing such queries by ten times. Thirdly, other triggers can benefit if they're called recursively from a trigger and also use the cache, producing even further savings.

Caches do have a penalty, however. They can grow stale when the data is modified and the cache isn't updated within the same transaction. This means that caching users and Territory__c records are probably fine, but caching account queries would probably be a bad idea, especially if you expect them to be altered during the course of a transaction.

You also have to sacrifice memory for those caches, so if they grow too large, you'll run into heap errors, even without having much data used in the local stack. Keep caches to only as large as they need to be (e.g. avoid querying all fields on the record if not necessary). Avoid clearing the cache early though, as you have to presume that recursion will probably need those same records again.

Also note that salesforce.com specifically mentions that you shouldn't try to "pre-warm" a cache, for example, by preemptively querying every single Territory__c record ahead of time. It'll simply have a worse performance than if you stuck to querying exactly what you need.

I would highly recommend caching only the most frequently used queries. Dial "profiling" up to the "finest" setting to see the frequency of queries, and cache those first.

share|improve this answer
    
I really appreciate you helping with this. Unfortunately, I am not a developer so I wouldn't know how to edit the code to do this. Sounds like it is exactly what I need though. –  Trisha M Aug 6 at 20:00
    
@TrishaM Did you figure it out, or are you going to need additional help? I'd be glad to try and assist you offline if you're short on developer resources. –  sfdcfox Aug 6 at 23:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.