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

December 3, 2014
·
Like
0
·
Follow
2

Best Answer chosen by RossG

BalajiRanganathan
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 {} } } } } }

December 3, 2014
·
Like
0
·
Dislike
0

mjohnson-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;

December 3, 2014
·
Like
0
·
Dislike
0

This was selected as the best answer

RossG
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!

December 3, 2014
·
Like
0
·
Dislike
0

BalajiRanganathan
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

December 3, 2014
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

too many soql queries error

All Answers

You need to sign in to do that.