function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Tyler DarnellTyler Darnell 

DataLoad System.LimitException: Too many SOQL queries: 101

Hello,

I wrote a trigger which is working fine in development. I am able to update accounts and peform account update dataloads without any issues. I deployed to production where I'm able to mannualy update accounts however I'm receiving the below error when I attempt an account update dataload.

System.LimitException: Too many SOQL queries: 101

I belive my trigger is offending the following trigger best pratice. Please suggest to me how to fix this issue.
5) Avoid SOQL Queries or DML statements inside FOR Loops
An individual Apex request gets a maximum of 100 SOQL queries before exceeding that governor limit. So if this trigger is invoked by a batch of more than 100 Account records, the governor limit will throw a runtime exception
 
trigger trgAccount_CustomNAICS on Account (before insert, before update) {

    for(Account acc:trigger.new)
    {        
        IF (String.isBlank(acc.NAICS_Code__c) && String.IsNotBlank(acc.NaicsCode))
        {
            try {
            System.debug('IF###AccountID: ' + acc.id);
            
            list <NAICS__c> listNaics=[select id from NAICS__c where name =:acc.NaicsCode LIMIT 1];
            
            System.debug('NAICS__c Name: ' + listNaics[0].id);
            
            acc.NAICS_Code__c = listNaics[0].id;
            }
        catch(System.Exception e){ System.debug('Exception occured: ' + e); }     
        }
        Else
        {
        System.debug('ELSE###AccountID: ' + acc.id);
        System.debug('NAICS_Code__c: ' + acc.NAICS_Code__c);
        System.debug('NaicsCode: ' + acc.NaicsCode);
        }
    }
    
}

 
Best Answer chosen by Tyler Darnell
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Tyler, 

As you told, the problem really is the SOQL query inside the for loop. So you have to solve it and working with maps would be a gread idea in this case but if I may, I would like to suggest you another approach:

I would suggest you to create a text field in the NAICS__c sObject setting it to be a External Field and Unique. The value of this field would be the same contained in the Name field, which means, the NAICS code. This allows you to link a NAICS__c record to an Account without querying the NAICS__c sObject and it will save time when you have to update or insert NAICS__c records, especially if this data is loaded at once with Dataloader or with a webservice. 

Furthermore, if you are using the Name field in the NAICS__c sObejct only because of the query, you then can change it to be an Autonumber field so you won't have to fill the name field with the NAICS code, only the external field.

And your code would be like:
 
trigger trgAccount_CustomNAICS on Account (before insert, before update) {

    for (Account acc : Trigger.new) {        
		
		if (String.isBlank(acc.NAICS_Code__c) && String.IsNotBlank(acc.NaicsCode)) {
            acc.NAICS_Code__r = new NAIC__c ( MyExternalField__c =  acc.NaicsCode );
		}

    }
}

 

But if you decide not to change it as suggested, then you can try this code instead that avoid the limit:

 

trigger trgAccount_CustomNAICS on Account (before insert, before update) {

	Map<String, Id> naicIdByNaicCode = new Map<String, Id> ();
	
    for (Account acc : Trigger.new) {        
        
		if (String.isBlank(acc.NAICS_Code__c) && String.IsNotBlank(acc.NaicsCode)) {
			
			naicIdByNaicCode.put(acc.NaicsCode, null);

        }

    }
	
	for (NAICS__c naic : [SELECT Id, Name FROM NAICS__c WHERE Name =: naicIdByNaicCode.keySet()]) {
		naicIdByNaicCode.put(naic.Name, naic.Id);
	}
    
    for (Account acc : Trigger.new) {        
        
		if (String.isBlank(acc.NAICS_Code__c) && String.IsNotBlank(acc.NaicsCode)) {
            acc.NAICS_Code__c = naicIdByNaicCode.get(acc.NaicsCode);
        }

    }	
	
}
 


Hope to have helped!

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.

All Answers

Rahul KumarRahul Kumar (Salesforce Developers) 
Hi Tyler Darnell,

May I request you to please refer the below link for reference. Hope it will be helpful.

Regards
Rahul Kumar
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Tyler, 

As you told, the problem really is the SOQL query inside the for loop. So you have to solve it and working with maps would be a gread idea in this case but if I may, I would like to suggest you another approach:

I would suggest you to create a text field in the NAICS__c sObject setting it to be a External Field and Unique. The value of this field would be the same contained in the Name field, which means, the NAICS code. This allows you to link a NAICS__c record to an Account without querying the NAICS__c sObject and it will save time when you have to update or insert NAICS__c records, especially if this data is loaded at once with Dataloader or with a webservice. 

Furthermore, if you are using the Name field in the NAICS__c sObejct only because of the query, you then can change it to be an Autonumber field so you won't have to fill the name field with the NAICS code, only the external field.

And your code would be like:
 
trigger trgAccount_CustomNAICS on Account (before insert, before update) {

    for (Account acc : Trigger.new) {        
		
		if (String.isBlank(acc.NAICS_Code__c) && String.IsNotBlank(acc.NaicsCode)) {
            acc.NAICS_Code__r = new NAIC__c ( MyExternalField__c =  acc.NaicsCode );
		}

    }
}

 

But if you decide not to change it as suggested, then you can try this code instead that avoid the limit:

 

trigger trgAccount_CustomNAICS on Account (before insert, before update) {

	Map<String, Id> naicIdByNaicCode = new Map<String, Id> ();
	
    for (Account acc : Trigger.new) {        
        
		if (String.isBlank(acc.NAICS_Code__c) && String.IsNotBlank(acc.NaicsCode)) {
			
			naicIdByNaicCode.put(acc.NaicsCode, null);

        }

    }
	
	for (NAICS__c naic : [SELECT Id, Name FROM NAICS__c WHERE Name =: naicIdByNaicCode.keySet()]) {
		naicIdByNaicCode.put(naic.Name, naic.Id);
	}
    
    for (Account acc : Trigger.new) {        
        
		if (String.isBlank(acc.NAICS_Code__c) && String.IsNotBlank(acc.NaicsCode)) {
            acc.NAICS_Code__c = naicIdByNaicCode.get(acc.NaicsCode);
        }

    }	
	
}
 


Hope to have helped!

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.

This was selected as the best answer