0

I have issue with the below code when executing gives the error "System.LimitException: Too many SOQL queries: 101". Is there a way to optimize the below code?

As per the error log, it happens when executing "Select Id, Country_Key__c, Description__c,....."

for (Samples__c s: Trigger.new) {    

        String countryKey = s.Country__c;
        String regionKey = s.State_Province__c;         
        countryKey = countryKey.toUpperCase();
        Map<String, String> countryCodeMap = new Map<String, String>();


        Map < String, Map < String, Validation_Region__c >> validRegions = new Map < String, Map < String, Validation_Region__c >> ();

        for (Validation_Region__c objR: [Select Id, Country_Key__c, Description__c, Name FROM Validation_Region__c WHERE Country_Key__c = :countryKey ]) {
            String countryKeyInner = objR.Country_Key__c;
            Map < String, Validation_Region__c > regionMap = validRegions.get(countryKey);

            if (regionMap == null) {
                regionMap = new Map < String, Validation_Region__c > ();
                validRegions.put(countryKeyInner, regionMap);
            }
            string regionKeys = objR.Name;
            regionMap.put(regionKeys, objR);
        }
        if ((countryKey == 'HK'  || countryKey == 'IE') && (s.Zip_Postal_Code__c.length() > 0)) { //Condition for length of  0!
            s.addError('There is an error. ' + countryKey + ' do not have postal codes.');               
        }
}

1 Answer 1

2

Before your loop over Trigger.new, do this single query for the Validation_Region__c objects for all the country keys:

Map<String, List<Validation_Region__c>> m = new Map<String, List<Validation_Region__c>>();
for (Samples__c s: Trigger.new) {
    m.put(s.Country__c.toUpperCase(), new List<Validation_Region__c>());
}
for (Validation_Region__c r : [
        Select Id, Country_Key__c, Description__c, Name
        FROM Validation_Region__c
        WHERE Country_Key__c in :m.keySet()
        ]) {
    m.get(r.Country_Key__c.toUpperCase()).add(r);
}

and then inside your Trigger.new loop you can get the list of Validation_Region__c objects without any further queries from the map:

for (Validation_Region__c objR: m.get(countryKey)) {
    ...
}
2
  • Thank you Keith!! <br/> I also need to get another field "Zip_Postal_Code__c" from the Trigger.new to be used along with Country__c. How can I add that to the Map?
    – Gopi
    Feb 10, 2016 at 23:46
  • @Gopi Not entirely sure what logic you are looking for. But if its Validation_Region__c keyed by a zip code, have a second map created and used much like the m map except using Validation_Region__c as the key.
    – Keith C
    Feb 11, 2016 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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