Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
HamptonHampton 

Need Help with Too Many SOQL Queries

Hello:

 

Please see my code below. What I am doing is creating child records (Assigned_Address__c) on custom object Turf__c when Turf__c is created. The number of child records can be in the multiple hundreds at times. How can I tweak this Apex to avoid hitting the SOQL Limit (which is currently happening)?

 

trigger NewAddressAssignment on Turf__c (after insert) {

Set<String> releaseName = new Set<String>();
for (Turf__c newDoor: Trigger.new) {
     releaseName.add(newDoor.Name);

Map<String, Address__c> addressMap = new Map<String, Address__c>();
for(Address__c address1 : [Select ID, Name, Unparsed_Address__c, Release__c, Video_Qualification__c from Address__c where Release__c in : releaseName]){
    addressMap.put(address1.Release__c, address1);

List<Assigned_Address__c> newAssignment = new List<Assigned_Address__c>();
    for(Turf__c newRelease : trigger.new) {
      if(addressMap.containsKey(newRelease.Name)){  
      		if(newRelease.New_Resweep__c == 'New'){
             newAssignment.add(new Assigned_Address__c(
                    Address__c = addressMap.get(newRelease.Name).Unparsed_Address__c,
                    Turf__c = newRelease.ID,
                    Address_ID__c = addressMap.get(newRelease.Name).ID));
      		}
      }
insert newAssignment;       
    }
}
}
}

 Thanks!

 

Hampton

Best Answer chosen by Admin (Salesforce Developers) 
vbsvbs
@Hampton - So change this from
Map<String, Id> releaseName = new Map<String, Turf__c>(); to
Map<String, Id> releaseName = new Map<String, Id>();

I have not reviewed the code for correctness so apologies for the oversight. Do mark this as a solution if this now works and add kudos.

All Answers

souvik9086souvik9086

Hi,

 

You have to try avoiding this soql query within for loop. If there are more than 100 records of child object which is fetched then you try to do that using BatchApex setting 50 records or so at a time so that Too Many SOQL Queries can be avoided.

 

If this post solves your problem kindly mark it as solution. if this post is helpful please throw Kudos.

Thanks
sushant sussushant sus
trigger UpdateContactType on Account (after update)
{
Set<Id> SetAccountId = new Set<Id>();
for(Account objAccount: [Select Id from Account where Id in : Trigger.new])
{
SetAccountId.add(objAccount.Id);
}
if(!SetAccountId.isEmpty())
{
string strquery='SELECT Name ,Id ,UpdateContact_Type__c FROM Account Where Id IN :SetAccountId';
BatchToUpdateContactType objBatchToUpdateContactType=new BatchToUpdateContactType(strquery);
Database.executeBatch(objBatchToUpdateContactType);
}
}

Batch:



global class BatchToUpdateContactType implements Database.Batchable<sObject>, Database.stateful
{
global string strqry='';
global BatchToUpdateContactType(String q)
{
strqry=q;
}
global Database.Querylocator start (Database.Batchablecontext BC)
{
return Database.getQueryLocator(strqry);
}
global void execute (Database.Batchablecontext BC, list<sObject> scope)
{
// Perform your Action here
system.debug('All your Accounts are here'+scope);
}

global void finish(Database.Batchablecontext BC)
{

}
}
HamptonHampton

I'm still kind of at a loss on what to do here...using the code I originally placed, how would I integrate that into the batch apex class provided? I am getting errors left and right on it.

 

Thanks!

 

Hampton
HamptonHampton

Here is what I have. I was able to save the class and the trigger but the creation of the new Assigned_Address__c records is not happening. I have a feeling it is a simple fix but I cannot seem to find it:

 

global class BatchToCreateAssignedAddress implements Database.Batchable<sObject>, Database.stateful
{
global string strqry='';
global BatchToCreateAssignedAddress(String q)
{
strqry=q;
}
global Database.Querylocator start (Database.Batchablecontext BC)
{
return Database.getQueryLocator(strqry);
}
global void execute (Database.Batchablecontext BC, list<sObject> scope)
{
     Set<String> releaseName = new Set<String>();
     for (Turf__c newDoor: [Select Name from Turf__c]) {
          releaseName.add(newDoor.Name);

     Map<String, Address__c> addressMap = new Map<String, Address__c>();
     for(Address__c address1 : [Select ID, Name, Unparsed_Address__c, Release__c, Video_Qualification__c from Address__c where Release__c in : releaseName]){
     addressMap.put(address1.Release__c, address1);

     List<Assigned_Address__c> newAssignment = new List<Assigned_Address__c>();
         for(Turf__c newRelease : [Select ID, Name, New_Resweep__c from Turf__c where Name = : newDoor.Name] ) {
           if(addressMap.containsKey(newRelease.Name)){  
        		if(newRelease.New_Resweep__c == 'New'){
                    newAssignment.add(new Assigned_Address__c(
                    Address__c = addressMap.get(newRelease.Name).Unparsed_Address__c,
                    Turf__c = newRelease.ID,
                    Address_ID__c = addressMap.get(newRelease.Name).ID));
        		}
           }
     insert newAssignment;       
    }
     }
     }
}
global void finish(Database.Batchablecontext BC)
{
}
}

 

rigger NewAddressAssignment on Turf__c (after insert)
{
Set<Id> SetTurfId = new Set<Id>();
for(Turf__c objTurf: [Select Id from Turf__c where Id in : Trigger.new])
{
SetTurfId.add(objTurf.Id);
}
if(!SetTurfId.isEmpty())
{
string strquery='Select Name ,Id from Turf__c Where Id in :SetTurfId';
BatchToCreateAssignedAddress objBatchToCreateAssignedAddress=new BatchToCreateAssignedAddress(strquery);
Database.executeBatch(objBatchToCreateAssignedAddress);
}
}

 
souvik9086souvik9086

It seems to be correct. While inserting you can check that through Debug Log exactly which is causing the problem. Before that give System.debug in the class and trigger just to make sure that where the error actually coming.

 

If this post solves your problem kindly mark it as solution. if this post is helpful please throw Kudos.

Thanks
HamptonHampton

Here is what I am seeing. It queried the correct amount of rows (it should have created 81 records) but they aren't there so I am thinking there is a problem with the add/insert portion of my class or code?

 

Any thoughts?

 

Thanks!

 

Hampton

 

15:44:37.157 (157673000)|CODE_UNIT_FINISHED|NewAddressAssignment on Turf trigger event AfterInsert for [a1Vc0000000FFMm]
15:44:37.158 (158355000)|DML_END|[24]
15:44:37.426 (158415000)|CUMULATIVE_LIMIT_USAGE
15:44:37.426|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 2 out of 100
  Number of query rows: 81 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 1 out of 150
  Number of DML rows: 1 out of 10000
  Number of code statements: 329 out of 200000
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 10
  Number of Email Invocations: 0 out of 10
  Number of fields describes: 0 out of 100
  Number of record type describes: 0 out of 100
  Number of child relationships describes: 0 out of 100
  Number of picklist describes: 0 out of 100
  Number of future calls: 0 out of 10
souvik9086souvik9086

Yes, please give System.debug inside your code. That gives better idea exactly where the problem is addressing. First of all check upto which line of the controller the code is executing. Then print in System.debug "newAssignment" before and after the insert statement to check what value is coming.

 

If this post solves your problem kindly mark it as solution. if this post is helpful please throw Kudos.

Thanks
HamptonHampton

I have added system.debug at all places that could be causing the problem, the problem I have is I have no idea how to make sense of a debug log, where to find the results of the system.debug statements, etc. I know how to enable debug logs but am just lost and frustrated at this point.

 

global class BatchToCreateAssignedAddress implements Database.Batchable<sObject>, Database.stateful
{
global string strqry='';
global BatchToCreateAssignedAddress(String q)
{
strqry=q;
}
global Database.Querylocator start (Database.Batchablecontext BC)
{
return Database.getQueryLocator(strqry);
}
global void execute (Database.Batchablecontext BC, list<sObject> scope)
{
 
     Set<String> releaseName = new Set<String>();
     for (Turf__c newDoor: [Select Name from Turf__c ]) {
          releaseName.add(newDoor.Name); }
     system.debug('*************releaseName :'+ releaseName);     
          
     Map<String, Address__c> addressMap = new Map<String, Address__c>();
     for(Address__c address1 : [Select ID, Name, Unparsed_Address__c, Release__c, Video_Qualification__c from Address__c where Release__c in : releaseName]){
     addressMap.put(address1.Release__c, address1);}
     system.debug('*************addressMap size :'+ addressMap.size());

     List<Assigned_Address__c> newAssignment = new List<Assigned_Address__c>();
         for(Turf__c newRelease : [Select ID, Name, New_Resweep__c from Turf__c where Name = : releaseName] ) {
         system.debug('*************newRelease Name :'+ newRelease.Name);  
           if(addressMap.containsKey(newRelease.Name)){  
        		if(newRelease.New_Resweep__c == 'New'){
        			newAssignment.add(new Assigned_Address__c(
                     Address__c = addressMap.get(newRelease.Name).Unparsed_Address__c,
                     Turf__c = newRelease.ID,
                     Address_ID__c = addressMap.get(newRelease.Name).ID));
        		}
           }
     insert newAssignment;
         }
         }  
global void finish(Database.Batchablecontext BC)
{
}
}

 The issues has to be somewhere in the for(Release__c) statement as the logs have shown the map is working, there is just an issue creating the records. I don't know what to do.

 

Hampton
vbsvbs

@Hampton - The reply from souvik is spot on but partly. The cause of your problem is the embedded SOQL listed here:

for(Address__c address1 : [Select ID, Name, Unparsed_Address__c, Release__c, Video_Qualification__c from Address__c where Release__c in : releaseName]){

 

A simple change to your code to get the list of release names and then use then use this separately in the child object SOQL should fix the issue 

trigger NewAddressAssignment on Turf__c (after insert) {

    Set<String> releaseName = new Set<String>();
    for (Turf__c newDoor: Trigger.new) {
        releaseName.add(newDoor.Name);
    }
	
    Map<String, Address__c> addressMap = new Map<String, Address__c>();
    for(Address__c address1 : [Select ID, Name, Unparsed_Address__c, Release__c,              Video_Qualification__c from Address__c where Release__c in : releaseName]){
        addressMap.put(address1.Release__c, address1);
    }
	
    List<Assigned_Address__c> newAssignment = new List<Assigned_Address__c>();
    for(Turf__c newRelease : trigger.new) {
        if(addressMap.containsKey(newRelease.Name)){  
      	    if(newRelease.New_Resweep__c == 'New'){
                newAssignment.add(new Assigned_Address__c(
                Address__c = addressMap.get(newRelease.Name).Unparsed_Address__c,
                Turf__c = newRelease.ID,
                Address_ID__c = addressMap.get(newRelease.Name).ID));
            }
        }
    }
    insert newAssignment;       		
}

 

I have moved the DML statement out of the for loop as well as this would be the next problem you would hit with regards to DML governor limits. Do try this and mark this as a solution is this fixes your problem and provide KUDOS as this seems to be the way on the forum ;-)

 

Regards

vbs 

 
HamptonHampton

VBS:

 

The code you have provided worked, however it only created one child record. In the unit test I did in the Sandbox, it should have created 81 records (that is how many Address__c records there are with the Release__c Name). That's the problem I am running into is how to bulk create anywhere from 10 - 500 child records with each Release__c creation. I will only be creating 2-3 new Release__c records per week.

 

Thanks!

 

Hampton
vbsvbs

Hi Hampton,

 

The data model is not quite clear. But I have now modified the code based on the following understanding:

1. For every 'New' Turf__c record, determine relevant Address__c records

2. Create Assigned_Address__c record for each Address__c record.

If the above is correct then the code should look even more simpler. Here goes another stab at your solution:

trigger NewAddressAssignment on Turf__c (after insert) {

    Map<String, Id> releaseName = new Map<String, Turf__c>();
    for (Turf__c newDoor: Trigger.new) {
    	if (newDoor.New_Resweep__c == 'New')
            releaseName.put(newDoor.Name, newDoor.Id);
    }
	
    List<Assigned_Address__c> newAssignment = new List<Assigned_Address__c>();
    for(Address__c address1 : [Select ID, Name, Unparsed_Address__c, Release__c, Video_Qualification__c from Address__c where Release__c in : releaseName]){
        newAssignment.add(new Assigned_Address__c(
	                Address__c = address1.Unparsed_Address__c,
	                Turf__c = releaseName.get(Release__c).Id,
                	Address_ID__c = address1.ID));
    }
	
    insert newAssignment;       		
}

 

Let me know if this helps and mark this as a solution along with Kudos on the way.

 

Regards

VBS 

 

 
HamptonHampton

VBS:

 

Your assumption on the data model is correct. I appreciate your help. I am getting an error at Line 14 that variable Release__c does not exists.

 

Not sure exactly how that line should be written but Assigned_Address__c.Turf__c should be equal to the ID in releaseName. I don't know that there is a field on Assigned_Address__c to link back to the map. Does that make sense?

 

Thanks,

 

Hampton
vbsvbs
Hampton - My apologies. Just change
Turf__c = releaseName.get(Release__c).Id, to
Turf__c = releaseName.get(address1.Release__c).Id,

This was was an oversight as Release__c is a field on the SObject so needs to be prefixed with the instance variable. Let me know if this finally fixes the issue.
souvik9086souvik9086
Turf__c = releaseName.get(address1.Release__c).Id,

If this post solves your problem kindly mark it as solution. if this post is helpful please throw Kudos.

Thanks
HamptonHampton

I am now getting : Compile Error: Illegal assignment from MAP<String,Turf__c> to MAP<String,Id> at line 3 column 5
vbsvbs
@Hampton - So change this from
Map<String, Id> releaseName = new Map<String, Turf__c>(); to
Map<String, Id> releaseName = new Map<String, Id>();

I have not reviewed the code for correctness so apologies for the oversight. Do mark this as a solution if this now works and add kudos.
This was selected as the best answer
HamptonHampton

I cannot thank you enough for your help. I had to make one more minor change to the code, but here is the final output. I did a couple of manual tests and it worked wonderfully. Thank you so much, again.

 

trigger NewAddressAssignment on Turf__c (after insert) {

    Map<String, Id> releaseName = new Map<String, Id>();
    for (Turf__c newDoor: Trigger.new) {
      if (newDoor.New_Resweep__c == 'New')
            releaseName.put(newDoor.Name, newDoor.Id);
    }
  
    List<Assigned_Address__c> newAssignment = new List<Assigned_Address__c>();
    for(Address__c address1 : [Select ID, Name, Unparsed_Address__c, Release__c, Video_Qualification__c from Address__c where Release__c in : releaseName.keyset()]){
        newAssignment.add(new Assigned_Address__c(
                  Address__c = address1.Unparsed_Address__c,
                        Turf__c = releaseName.get(address1.Release__c),
                  Address_ID__c = address1.ID));
    }
  
    insert newAssignment;           
}