+ Start a Discussion
RossGRossG 

too many soql queries error

Hi, I've got a lead trigger bulkification issue.  The following piece of code throws a too many soql queries error when more than 200 leads are inserted:
 
if(Trigger.isInsert || Trigger.isUpdate){  
        
            if(HelperClass.firstRun2){
                
            HelperClass.firstRun2=false;          
        
            for(Lead l1: trigger.new){               
                if((UserInfo.getUserId() == Label.MarketoIntegrationUser || UserInfo.getUserId() == Label.WebsiteIntegrationUser) &&
                   (l1.RecordTypeId == leadRecordTypeId) && (l1.Country == 'United States' || 
                   l1.Country == 'United States of America' ||
                   l1.Country == 'USA' ||
                   l1.Country == 'U.S.' ||
                   l1.Country == 'U.S.A'
                   ) ){     
               
                    Set<String> postalCodes = new Set<String>();

                    for( Lead l : trigger.new ) {
                            if(l.PostalCode != null && l.PostalCode.length() >=5)
                                try{                          
                                    String zipStringx = l.PostalCode.substring(0,5);
                                    postalCodes.add( zipStringx );  
                                   // postalCodes.add( l.PostalCode );     
                                } catch(DMLException e){
                                     ApexPages.addMessages(e);
                                     Messaging.SingleEmailMessage mail=new Messaging.SingleEmailMessage();
                                     String[] toAddresses = new String[] {'sfdc-admins@pingidentity.com'};
                                     mail.setToAddresses(toAddresses);
                                     mail.setReplyTo('sfdc-admins@pingidentity.com');
                                     mail.setSenderDisplayName('Apex error message');
                                     mail.setSubject('Error from Org : ' + UserInfo.getOrganizationName());
                                     mail.setPlainTextBody(e.getMessage());
                                     Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
                                
                                }        
                                                                                         
                    }

                    Map<String, Zip_State_Table__c > validZips = new Map<String, Zip_State_Table__c >();

                    for( Zip_State_Table__c obj : [SELECT  Id, 
                                        Name,
                                        ZipCode__c,
                                        Territory__c                                            
                                        FROM Zip_State_Table__c 
                                            WHERE ZipCode__c IN :postalCodes] ) {
                        validZips.put( obj.ZipCode__c, obj );
                    }

                    for( Lead l : trigger.new ) {
                        if( l.PostalCode != null && l.PostalCode.length() >=5 ) { 
                            if( validZips.containsKey(l.PostalCode.substring(0,5)) ) { 
                                l.State = validZips.get(l.PostalCode.substring(0,5)).Name;
                                l.RegionTerritoryHidden__c = validZips.get(l.PostalCode.substring(0,5)).Territory__c;
                            } else {}

                        } 
                    }
                }
                }
            }
        }

I know the error in the above code is with this part of the code:  
 
for( Zip_State_Table__c obj : [SELECT  Id, 
                                        Name,
                                        ZipCode__c,
                                        Territory__c                                            
                                        FROM Zip_State_Table__c 
                                            WHERE ZipCode__c IN :postalCodes] ) {
                        validZips.put( obj.ZipCode__c, obj );
                    }


I'm unable to re-write that code to avoid the error.  Does anyone see how I can re-write that code with the soql query for loop to avoid the too many soql queries error?  Thanks 
 
Best Answer chosen by RossG
BalajiRanganathanBalajiRanganathan
Below code should work and it does the same
if(Trigger.isInsert || Trigger.isUpdate){  
        
            if(HelperClass.firstRun2){
                
            HelperClass.firstRun2=false;          

                    Set<String> postalCodes = new Set<String>();

                    for( Lead l : trigger.new ) {
                            if(l.PostalCode != null && l.PostalCode.length() >=5)
                                try{                          
                                    String zipStringx = l.PostalCode.substring(0,5);
                                    postalCodes.add( zipStringx );  
                                   // postalCodes.add( l.PostalCode );     
                                } catch(DMLException e){
                                     ApexPages.addMessages(e);
                                     Messaging.SingleEmailMessage mail=new Messaging.SingleEmailMessage();
                                     String[] toAddresses = new String[] {'sfdc-admins@pingidentity.com'};
                                     mail.setToAddresses(toAddresses);
                                     mail.setReplyTo('sfdc-admins@pingidentity.com');
                                     mail.setSenderDisplayName('Apex error message');
                                     mail.setSubject('Error from Org : ' + UserInfo.getOrganizationName());
                                     mail.setPlainTextBody(e.getMessage());
                                     Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
                                
                                }        
                                                                                         
                    }

                    Map<String, Zip_State_Table__c > validZips = new Map<String, Zip_State_Table__c >();

                    for( Zip_State_Table__c obj : [SELECT  Id, 
                                        Name,
                                        ZipCode__c,
                                        Territory__c                                            
                                        FROM Zip_State_Table__c 
                                            WHERE ZipCode__c IN :postalCodes] ) {
                        validZips.put( obj.ZipCode__c, obj );
                    }
  
            for(Lead l1: trigger.new){               
                if((UserInfo.getUserId() == Label.MarketoIntegrationUser || UserInfo.getUserId() == Label.WebsiteIntegrationUser) &&
                   (l1.RecordTypeId == leadRecordTypeId) && (l1.Country == 'United States' || 
                   l1.Country == 'United States of America' ||
                   l1.Country == 'USA' ||
                   l1.Country == 'U.S.' ||
                   l1.Country == 'U.S.A'
                   ) ){     
               
                        if( l1.PostalCode != null && l1.PostalCode.length() >=5 ) { 
                            if( validZips.containsKey(l1.PostalCode.substring(0,5)) ) { 
                                l1.State = validZips.get(l1.PostalCode.substring(0,5)).Name;
                                l1.RegionTerritoryHidden__c = validZips.get(l1.PostalCode.substring(0,5)).Territory__c;
                            } else {}
                    }
                }
                }
            }
        }

 

All Answers

mjohnson-TICmjohnson-TIC

How are you running this update of leads, through a for loop? I can't think of any scenario that would require you to update 200+ individual lead records one at a time in a single execution - this is the only way you would run into that exception.

Below causes 300 SQL queries with your trigger because every lead is individually updated in one execution and the trigger is run 300 times.

for(Lead l: [Select Company from Lead LIMIT 300]){
l.Company = 'New Company';
update l;
}

Below would only cause 1 SQL query to be run, because you are updating a batch of 300 and the trigger is only run once through the batch.

list<Lead> ll = new list<Lead>();
for(Lead l: [Select Company from Lead LIMIT 300]){
l.Company = 'New Company';
ll.add(l);
}
update ll;

BalajiRanganathanBalajiRanganathan
Below code should work and it does the same
if(Trigger.isInsert || Trigger.isUpdate){  
        
            if(HelperClass.firstRun2){
                
            HelperClass.firstRun2=false;          

                    Set<String> postalCodes = new Set<String>();

                    for( Lead l : trigger.new ) {
                            if(l.PostalCode != null && l.PostalCode.length() >=5)
                                try{                          
                                    String zipStringx = l.PostalCode.substring(0,5);
                                    postalCodes.add( zipStringx );  
                                   // postalCodes.add( l.PostalCode );     
                                } catch(DMLException e){
                                     ApexPages.addMessages(e);
                                     Messaging.SingleEmailMessage mail=new Messaging.SingleEmailMessage();
                                     String[] toAddresses = new String[] {'sfdc-admins@pingidentity.com'};
                                     mail.setToAddresses(toAddresses);
                                     mail.setReplyTo('sfdc-admins@pingidentity.com');
                                     mail.setSenderDisplayName('Apex error message');
                                     mail.setSubject('Error from Org : ' + UserInfo.getOrganizationName());
                                     mail.setPlainTextBody(e.getMessage());
                                     Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
                                
                                }        
                                                                                         
                    }

                    Map<String, Zip_State_Table__c > validZips = new Map<String, Zip_State_Table__c >();

                    for( Zip_State_Table__c obj : [SELECT  Id, 
                                        Name,
                                        ZipCode__c,
                                        Territory__c                                            
                                        FROM Zip_State_Table__c 
                                            WHERE ZipCode__c IN :postalCodes] ) {
                        validZips.put( obj.ZipCode__c, obj );
                    }
  
            for(Lead l1: trigger.new){               
                if((UserInfo.getUserId() == Label.MarketoIntegrationUser || UserInfo.getUserId() == Label.WebsiteIntegrationUser) &&
                   (l1.RecordTypeId == leadRecordTypeId) && (l1.Country == 'United States' || 
                   l1.Country == 'United States of America' ||
                   l1.Country == 'USA' ||
                   l1.Country == 'U.S.' ||
                   l1.Country == 'U.S.A'
                   ) ){     
               
                        if( l1.PostalCode != null && l1.PostalCode.length() >=5 ) { 
                            if( validZips.containsKey(l1.PostalCode.substring(0,5)) ) { 
                                l1.State = validZips.get(l1.PostalCode.substring(0,5)).Name;
                                l1.RegionTerritoryHidden__c = validZips.get(l1.PostalCode.substring(0,5)).Territory__c;
                            } else {}
                    }
                }
                }
            }
        }

 
This was selected as the best answer
RossGRossG
Yeah that works.  I'm using Eclipse to compare my code to yours to see exactly what you did but if you get a chance to explain what you changed so I can learn from it I would really appreciate it.  Thanks a lot Balaji!
BalajiRanganathanBalajiRanganathan
there is a unnecessary double iteration leads with in the lead (for(Lead l: trigger.new){ for(Lead l1: trigger.new){
1) Moved the zip code logic out of the loop
2) removed another loop which is not required