Apex Code Development You need to sign in to do that
Don't have an account?
Arash Teimoupoor too many querry rows:50001 in batch
Hi,
I have a batch that when I run I get the too many querry rows:50001 error. the reason I'm using this batch is because som of my parent records has more than 200 thousand childs adn I need to update the. please advise:
global class ProjectSettings_ChartsTriggerBatchClass implements Database.Batchable<sObject>{
// Start Method
global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator([Select Id,Name,Parent_Project_Name__c,Healthplan_Contact__c,PR_Number__c,
Line_Of_Business__c,ChartSecure_Account_Manager__r.Name,Covering_Account_Manager__r.Name,Requestor__c,
Project_End_Date__c,Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,
Copy_Cost__c,Onsite_Threshold__c,Reporting_Active__c from Project_Setting__c]);
}
global void execute(Database.BatchableContext BC, List<Project_Setting__c> scope){
Set<id> ProjectIds = new Set<id>();
for(Project_Setting__c PS : scope){
ProjectIds.add(PS.id);
}
List<charts__c> ChartListFirst = [select id,Project_Name__c,Parent_Project_Name__c,Healthplan_Contact__c,Client_Number__c,Line_of_Business__c,
Healthplan_Account_Manager__c,Healthplan_Requestor__c,Covering_Account_Manager__c,Project_End_Date__c,
Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,Copy_Cost__c,Onsite_Threshold__c,
Reporting_Active__c from charts__c where Project_Name__c In : ProjectIds limit 50000];
set<id> FirstIds = new set<id>();
for(charts__c c : ChartListFirst){
FirstIds.add(c.id);
}
List<charts__c> ChartListSecond = [select id,Project_Name__c,Parent_Project_Name__c,Healthplan_Contact__c,Client_Number__c,Line_of_Business__c,
Healthplan_Account_Manager__c,Healthplan_Requestor__c,Covering_Account_Manager__c,Project_End_Date__c,
Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,Copy_Cost__c,Onsite_Threshold__c,
Reporting_Active__c from charts__c where Project_Name__c In : ProjectIds and id Not In : FirstIds limit 50000];
set<id> SecondIds = new set<id>();
for(charts__c c : ChartListFirst){
SecondIds.add(c.id);
}
for(charts__c c : ChartListSecond){
SecondIds.add(c.id);
}
List<charts__c> ChartListThird = [select id,Project_Name__c,Parent_Project_Name__c,Healthplan_Contact__c,Client_Number__c,Line_of_Business__c,
Healthplan_Account_Manager__c,Healthplan_Requestor__c,Covering_Account_Manager__c,Project_End_Date__c,
Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,Copy_Cost__c,Onsite_Threshold__c,
Reporting_Active__c from charts__c where Project_Name__c In : ProjectIds and id Not In : SecondIds limit 50000];
List<charts__c> UpdateChartList = new List<charts__c>();
for(Project_Setting__c Ps : scope){
for(charts__c ch : ChartListFirst){
if(ch.Project_Name__c == Ps.id){
ch.Parent_Project_Name__c = Ps.Parent_Project_Name__c;
Ch.Healthplan_Contact__c = Ps.Healthplan_Contact__c;
Ch.Client_Number__c = Ps.PR_Number__c;
Ch.Line_of_Business__c = Ps.Line_Of_Business__c;
Ch.Healthplan_Account_Manager__c = Ps.ChartSecure_Account_Manager__r.Name;
Ch.Healthplan_Requestor__c = Ps.Requestor__c;
Ch.Covering_Account_Manager__c = Ps.Covering_Account_Manager__r.Name;
Ch.Project_End_Date__c = Ps.Project_End_Date__c;
Ch.Project_Start_Date__c = Ps.Project_Start_Date__c;
Ch.Extended_Project_End_Date__c = Ps.Extended_Project_End_Date__c;
Ch.Project_Closed__c = Ps.Project_Closed__c;
Ch.Date_Project_Closed__c= Ps.Date_Project_Closed__c;
Ch.Copy_Cost__c = Ps.Copy_Cost__c;
Ch.Onsite_Threshold__c = Ps.Onsite_Threshold__c;
Ch.Reporting_Active__c = Ps.Reporting_Active__c;
UpdateChartList.Add(ch);
}
}
}
for(Project_Setting__c Ps : scope){
for(charts__c ch : ChartListSecond){
if(ch.Project_Name__c == Ps.id){
ch.Parent_Project_Name__c = Ps.Parent_Project_Name__c;
Ch.Healthplan_Contact__c = Ps.Healthplan_Contact__c;
Ch.Client_Number__c = Ps.PR_Number__c;
Ch.Line_of_Business__c = Ps.Line_Of_Business__c;
Ch.Healthplan_Account_Manager__c = Ps.ChartSecure_Account_Manager__r.Name;
Ch.Healthplan_Requestor__c = Ps.Requestor__c;
Ch.Covering_Account_Manager__c = Ps.Covering_Account_Manager__r.Name;
Ch.Project_End_Date__c = Ps.Project_End_Date__c;
Ch.Project_Start_Date__c = Ps.Project_Start_Date__c;
Ch.Extended_Project_End_Date__c = Ps.Extended_Project_End_Date__c;
Ch.Project_Closed__c = Ps.Project_Closed__c;
Ch.Date_Project_Closed__c= Ps.Date_Project_Closed__c;
Ch.Copy_Cost__c = Ps.Copy_Cost__c;
Ch.Onsite_Threshold__c = Ps.Onsite_Threshold__c;
Ch.Reporting_Active__c = Ps.Reporting_Active__c;
UpdateChartList.Add(ch);
}
}
}
for(Project_Setting__c Ps : scope){
for(charts__c ch : ChartListThird){
if(ch.Project_Name__c == Ps.id){
ch.Parent_Project_Name__c = Ps.Parent_Project_Name__c;
Ch.Healthplan_Contact__c = Ps.Healthplan_Contact__c;
Ch.Client_Number__c = Ps.PR_Number__c;
Ch.Line_of_Business__c = Ps.Line_Of_Business__c;
Ch.Healthplan_Account_Manager__c = Ps.ChartSecure_Account_Manager__r.Name;
Ch.Healthplan_Requestor__c = Ps.Requestor__c;
Ch.Covering_Account_Manager__c = Ps.Covering_Account_Manager__r.Name;
Ch.Project_End_Date__c = Ps.Project_End_Date__c;
Ch.Project_Start_Date__c = Ps.Project_Start_Date__c;
Ch.Extended_Project_End_Date__c = Ps.Extended_Project_End_Date__c;
Ch.Project_Closed__c = Ps.Project_Closed__c;
Ch.Date_Project_Closed__c= Ps.Date_Project_Closed__c;
Ch.Copy_Cost__c = Ps.Copy_Cost__c;
Ch.Onsite_Threshold__c = Ps.Onsite_Threshold__c;
Ch.Reporting_Active__c = Ps.Reporting_Active__c;
UpdateChartList.Add(ch);
}
}
}
if(UpdateChartList.size() > 0 ){
Update UpdateChartList;
}
}
global void finish(Database.BatchableContext BC){
}
}
I have a batch that when I run I get the too many querry rows:50001 error. the reason I'm using this batch is because som of my parent records has more than 200 thousand childs adn I need to update the. please advise:
global class ProjectSettings_ChartsTriggerBatchClass implements Database.Batchable<sObject>{
// Start Method
global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator([Select Id,Name,Parent_Project_Name__c,Healthplan_Contact__c,PR_Number__c,
Line_Of_Business__c,ChartSecure_Account_Manager__r.Name,Covering_Account_Manager__r.Name,Requestor__c,
Project_End_Date__c,Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,
Copy_Cost__c,Onsite_Threshold__c,Reporting_Active__c from Project_Setting__c]);
}
global void execute(Database.BatchableContext BC, List<Project_Setting__c> scope){
Set<id> ProjectIds = new Set<id>();
for(Project_Setting__c PS : scope){
ProjectIds.add(PS.id);
}
List<charts__c> ChartListFirst = [select id,Project_Name__c,Parent_Project_Name__c,Healthplan_Contact__c,Client_Number__c,Line_of_Business__c,
Healthplan_Account_Manager__c,Healthplan_Requestor__c,Covering_Account_Manager__c,Project_End_Date__c,
Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,Copy_Cost__c,Onsite_Threshold__c,
Reporting_Active__c from charts__c where Project_Name__c In : ProjectIds limit 50000];
set<id> FirstIds = new set<id>();
for(charts__c c : ChartListFirst){
FirstIds.add(c.id);
}
List<charts__c> ChartListSecond = [select id,Project_Name__c,Parent_Project_Name__c,Healthplan_Contact__c,Client_Number__c,Line_of_Business__c,
Healthplan_Account_Manager__c,Healthplan_Requestor__c,Covering_Account_Manager__c,Project_End_Date__c,
Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,Copy_Cost__c,Onsite_Threshold__c,
Reporting_Active__c from charts__c where Project_Name__c In : ProjectIds and id Not In : FirstIds limit 50000];
set<id> SecondIds = new set<id>();
for(charts__c c : ChartListFirst){
SecondIds.add(c.id);
}
for(charts__c c : ChartListSecond){
SecondIds.add(c.id);
}
List<charts__c> ChartListThird = [select id,Project_Name__c,Parent_Project_Name__c,Healthplan_Contact__c,Client_Number__c,Line_of_Business__c,
Healthplan_Account_Manager__c,Healthplan_Requestor__c,Covering_Account_Manager__c,Project_End_Date__c,
Project_Start_Date__c,Extended_Project_End_Date__c,Project_Closed__c,Date_Project_Closed__c,Copy_Cost__c,Onsite_Threshold__c,
Reporting_Active__c from charts__c where Project_Name__c In : ProjectIds and id Not In : SecondIds limit 50000];
List<charts__c> UpdateChartList = new List<charts__c>();
for(Project_Setting__c Ps : scope){
for(charts__c ch : ChartListFirst){
if(ch.Project_Name__c == Ps.id){
ch.Parent_Project_Name__c = Ps.Parent_Project_Name__c;
Ch.Healthplan_Contact__c = Ps.Healthplan_Contact__c;
Ch.Client_Number__c = Ps.PR_Number__c;
Ch.Line_of_Business__c = Ps.Line_Of_Business__c;
Ch.Healthplan_Account_Manager__c = Ps.ChartSecure_Account_Manager__r.Name;
Ch.Healthplan_Requestor__c = Ps.Requestor__c;
Ch.Covering_Account_Manager__c = Ps.Covering_Account_Manager__r.Name;
Ch.Project_End_Date__c = Ps.Project_End_Date__c;
Ch.Project_Start_Date__c = Ps.Project_Start_Date__c;
Ch.Extended_Project_End_Date__c = Ps.Extended_Project_End_Date__c;
Ch.Project_Closed__c = Ps.Project_Closed__c;
Ch.Date_Project_Closed__c= Ps.Date_Project_Closed__c;
Ch.Copy_Cost__c = Ps.Copy_Cost__c;
Ch.Onsite_Threshold__c = Ps.Onsite_Threshold__c;
Ch.Reporting_Active__c = Ps.Reporting_Active__c;
UpdateChartList.Add(ch);
}
}
}
for(Project_Setting__c Ps : scope){
for(charts__c ch : ChartListSecond){
if(ch.Project_Name__c == Ps.id){
ch.Parent_Project_Name__c = Ps.Parent_Project_Name__c;
Ch.Healthplan_Contact__c = Ps.Healthplan_Contact__c;
Ch.Client_Number__c = Ps.PR_Number__c;
Ch.Line_of_Business__c = Ps.Line_Of_Business__c;
Ch.Healthplan_Account_Manager__c = Ps.ChartSecure_Account_Manager__r.Name;
Ch.Healthplan_Requestor__c = Ps.Requestor__c;
Ch.Covering_Account_Manager__c = Ps.Covering_Account_Manager__r.Name;
Ch.Project_End_Date__c = Ps.Project_End_Date__c;
Ch.Project_Start_Date__c = Ps.Project_Start_Date__c;
Ch.Extended_Project_End_Date__c = Ps.Extended_Project_End_Date__c;
Ch.Project_Closed__c = Ps.Project_Closed__c;
Ch.Date_Project_Closed__c= Ps.Date_Project_Closed__c;
Ch.Copy_Cost__c = Ps.Copy_Cost__c;
Ch.Onsite_Threshold__c = Ps.Onsite_Threshold__c;
Ch.Reporting_Active__c = Ps.Reporting_Active__c;
UpdateChartList.Add(ch);
}
}
}
for(Project_Setting__c Ps : scope){
for(charts__c ch : ChartListThird){
if(ch.Project_Name__c == Ps.id){
ch.Parent_Project_Name__c = Ps.Parent_Project_Name__c;
Ch.Healthplan_Contact__c = Ps.Healthplan_Contact__c;
Ch.Client_Number__c = Ps.PR_Number__c;
Ch.Line_of_Business__c = Ps.Line_Of_Business__c;
Ch.Healthplan_Account_Manager__c = Ps.ChartSecure_Account_Manager__r.Name;
Ch.Healthplan_Requestor__c = Ps.Requestor__c;
Ch.Covering_Account_Manager__c = Ps.Covering_Account_Manager__r.Name;
Ch.Project_End_Date__c = Ps.Project_End_Date__c;
Ch.Project_Start_Date__c = Ps.Project_Start_Date__c;
Ch.Extended_Project_End_Date__c = Ps.Extended_Project_End_Date__c;
Ch.Project_Closed__c = Ps.Project_Closed__c;
Ch.Date_Project_Closed__c= Ps.Date_Project_Closed__c;
Ch.Copy_Cost__c = Ps.Copy_Cost__c;
Ch.Onsite_Threshold__c = Ps.Onsite_Threshold__c;
Ch.Reporting_Active__c = Ps.Reporting_Active__c;
UpdateChartList.Add(ch);
}
}
}
if(UpdateChartList.size() > 0 ){
Update UpdateChartList;
}
}
global void finish(Database.BatchableContext BC){
}
}
One query from the start method will do, you doesnt need to query any thing else., as i said query all the chart__c records with all the fields you need in the start method . In the same query include the parent fields that you need to update the child information with, using '__r' as you queried ChartSecure_Account_Manager__r.Name from Project_Setting__c in the code you are using now.
In the execute block, loop through chart__c records, assign the information from parent fields to the child fields, at the end of execute block update the chart__c records.
Thanks,
balaji
All Answers
I may not have proper answer for your question but have couple of suggestions:
If i understand the code correctly, you are try to fetch all Project_Setting__c records and its child records of charts__c, update all its child records using the information from parent.
I have couple of questions in this regard:
1) If you trying to update all the records with out any condtions on them, why dont you create formula fields on child records which always have the right information pulled from parent, and justify the concept of one information - one place. :)
2) If not, why dont you query all the child records directly and get its parent information using '__r' and update the child records with same information, where you will not encounter this issue and will be much more simple to handle. for example
in start method query for
select id,Project_Name__r.Healthplan_Contact__c,..... from charts__c where Project_Name__c!=Null
and loop through the chart__c records in execute and update them accordingly!
Hope this might help.
Thanks,
Balaji
can you explain more about the second solution? should I change all the querries in my batch?
Thanks
One query from the start method will do, you doesnt need to query any thing else., as i said query all the chart__c records with all the fields you need in the start method . In the same query include the parent fields that you need to update the child information with, using '__r' as you queried ChartSecure_Account_Manager__r.Name from Project_Setting__c in the code you are using now.
In the execute block, loop through chart__c records, assign the information from parent fields to the child fields, at the end of execute block update the chart__c records.
Thanks,
balaji
Use database.stateful in your batch class .
global class ProjectSettings_ChartsTriggerBatchClass implements Database.Batchable<SObject>, Database.Stateful{And also use limit 1000 records. and run your batch class every hour using the following expression.
Let me know if it solved your problem.
Thanks Regards
Vickey