Apex Code Development You need to sign in to do that
Don't have an account?
prady System.LimitException: Too many SOQL queries: 101
Hi,
I am trying to copy some set of data into an custom object. I am hitting the governor limits. Can anyone suggest better means of not exceeding governor limits.
Changing the for loops to use SOQL query for loops might help, but i am not sure how to go about doing it. Any pointers would be of great help.
public with sharing class move_records_to_reports { List<Reporting__c> Rep = new List<Reporting__c>(); List<Timecard_base__c> TB = new List<Timecard_base__c>(); List<TimeCard_Line_Items__c> TL = new List<TimeCard_Line_Items__c>(); public List<Assigned_Consultant__c> AC= new List<Assigned_Consultant__c>(); public void move_records_to_reports() { } public void transfer() { TL=[select Id,name,Timecard__r.consultant__r.Employment_Notes__c,assigned_consultant__c,Timecard__r.WeekStartdate__c,project__c,consultantId__c, Monday_Hours__c,Tuesday_Hours__c,Wednesday_Hours__c,Thursday_Hours__c,Friday_Hours__c,Saturday_Hours__c,Sunday_Hours__c from TimeCard_Line_Items__c]; for(integer j=0;j<TL.size();j++) { if(TL[j].Assigned_Consultant__c==null) { AC=[select id from Assigned_Consultant__c where Project__c =: TL[j].Project__c and Contact__c=:TL[j].consultantId__c and (Start_Date__c <=: (TL[j].Timecard__r.WeekStartdate__c.addDays(6)) or Start_Date__c = null) and (End_Date__c >=: TL[j].Timecard__r.WeekStartdate__c or End_Date__c = null) limit 1]; if (ac.size()>0) { TL[j].Assigned_Consultant__c=AC[0].id; } } upsert TL; } for(integer i=0;i<TL.size();i++) /*for(List<TimeCard_Line_Items__c> TL :[select Id,name,Timecard__c,Timecard__r.WeekStartdate__c, Monday_Hours__c,Tuesday_Hours__c,Wednesday_Hours__c,Thursday_Hours__c,Friday_Hours__c,Saturday_Hours__c,Sunday_Hours__c from TimeCard_Line_Items__c]) */{ //update assigned consultant field in timecard line items Reporting__c r = new Reporting__c(); r.TimeCard_Line_Item__c=TL[i].Id; r.name='From Moved Class'; r.Employment_Notes__c=TL[i].Timecard__r.consultant__r.Employment_Notes__c; r.timelineitemDate__c=TL[i].Timecard__r.WeekStartdate__c; r.hours__c=TL[i].Monday_Hours__c; Rep.add(r); Reporting__c r1 = new Reporting__c(); r1.TimeCard_Line_Item__c=TL[i].Id; r1.name='From Moved Class'; r1.Employment_Notes__c=TL[i].Timecard__r.consultant__r.Employment_Notes__c; r1.timelineitemDate__c=TL[i].Timecard__r.WeekStartdate__c.AddDays(1); r1.hours__c=TL[i].Tuesday_Hours__c; Rep.add(r1); Reporting__c r2 = new Reporting__c(); r2.TimeCard_Line_Item__c=TL[i].Id; r2.name='From Moved Class'; r2.Employment_Notes__c=TL[i].Timecard__r.consultant__r.Employment_Notes__c; r2.timelineitemDate__c=TL[i].Timecard__r.WeekStartdate__c.AddDays(2); r2.hours__c=TL[i].Wednesday_Hours__c; Rep.add(r2); Reporting__c r3 = new Reporting__c(); r3.TimeCard_Line_Item__c=TL[i].Id; r3.name='From Moved Class'; r3.Employment_Notes__c=TL[i].Timecard__r.consultant__r.Employment_Notes__c; r3.timelineitemDate__c=TL[i].Timecard__r.WeekStartdate__c.AddDays(3); r3.hours__c=TL[i].Thursday_Hours__c; Rep.add(r3); Reporting__c r4 = new Reporting__c(); r4.TimeCard_Line_Item__c=TL[i].Id; r4.name='From Moved Class'; r4.Employment_Notes__c=TL[i].Timecard__r.consultant__r.Employment_Notes__c; r4.timelineitemDate__c=TL[i].Timecard__r.WeekStartdate__c.AddDays(4); r4.hours__c=TL[i].Friday_Hours__c; Rep.add(r4); Reporting__c r5 = new Reporting__c(); r5.TimeCard_Line_Item__c=TL[i].Id; r5.name='From Moved Class'; r5.Employment_Notes__c=TL[i].Timecard__r.consultant__r.Employment_Notes__c; r5.timelineitemDate__c=TL[i].Timecard__r.WeekStartdate__c.AddDays(5); r5.hours__c=TL[i].Saturday_Hours__c; Rep.add(r5); Reporting__c r6 = new Reporting__c(); r6.TimeCard_Line_Item__c=TL[i].Id; r6.name='From Moved Class'; r6.Employment_Notes__c=TL[i].Timecard__r.consultant__r.Employment_Notes__c; r6.timelineitemDate__c=TL[i].Timecard__r.WeekStartdate__c.AddDays(6); r6.hours__c=TL[i].Sunday_Hours__c; Rep.add(r6); } try{ upsert Rep; // upsert TL; System.debug('completed insertion'); } catch (Exception e) { ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. '); ApexPages.addMessage(errormsg); } } }
This is a really common problem, and is almost always caused by having SOQL queries inside loops. A trigger, could potentially process up to 200 records, and if you loop over all those records, and there's just one SOQL query within that loop, the query is going to run 200 times (well, actually it will throw an exception and the trigger will just fail as you've encountered).
There is a well defined best practice of figuring out what records your going to need inside a loop, and querying all of those records before entering the loop. It's a trade off between SOQL queries and statements, but you have easily far more statements at your disposal than SOQL queries.
Here's the wiki page with examples of this problem and how to resolve it.
http://wiki.developerforce.com/index.php/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops
I also just noticed that you have no where clause on your first query, so it's going to be returning every record in that object! This SOQL query is then the base of the for loop, in which the second SOQL query resides. We're talking n+1 queries, where n is the number of records returned in TL. That doesn't sound too bad but when you consider that a SOQL query can return thousands of records, it's easy to see why your hitting the SOQL query limit.
The solution still requires pulling that second SOQL query outside of the loop, and designing it to catch all of the records that you need to alter. Your second query has alot of where conditions though, so it's not going to be straight forward. This may require a bit of reengineering. Without knowing how your org is desgined I can suggest two extra possible solutions, other than just modifying your code to run that query once for all records.
Relationship queries
These can reduce the amount of queries you have to make by getting lists of related objects in the primary query. When you iterate over the primary query, the list of child queries is stored in there as a __r variable. e.g.
for (Parent__c p : [SELECT Id, (SELECT Id FROM Childs__r) FROM Parent__c]) { System.debug('Listing child ids for parent: ' + p.Id); for (Child c : p.Childs__r) { // perform some operation on the child records; System.debug('- ' + c.Id); } }see http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm
Batch Apex
If you need to work on a large number of records, and it would require too many SOQL queries to do them all at once, you can use batch apex to reduce the complexity by breaking the job into smaller byte size jobs.
see http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
Yes, you are right...
getting the second loop with lot of where conditions is the problem.
What i am trying to do is one time operation where all the records of the custom object timecard_line_items__c are read and copied to another custom object.
I have a trigger which does the same thing when new records are inserted. so all any new reocrds entered are automaticaly getting copied into custom object.
But issue is with already existing records, i need to move those data too. Is there any other way other than the code written to copy the values?
Hi. Sorry, I was just editing my last post to include more detail and a couple of potential avenues that 'might' help. Have a read and see what you think.