You need to sign in to do that
Don't have an account?

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
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
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
{
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)
{
}
}
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
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:
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
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
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
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.
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
@Hampton - The reply from souvik is spot on but partly. The cause of your problem is the embedded SOQL listed here:
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
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
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
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:
Let me know if this helps and mark this as a solution along with Kudos on the way.
Regards
VBS
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
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.
If this post solves your problem kindly mark it as solution. if this post is helpful please throw Kudos.
Thanks
I am now getting : Compile Error: Illegal assignment from MAP<String,Turf__c> to MAP<String,Id> at line 3 column 5
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.
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.