Apex Code Development You need to sign in to do that
Don't have an account?
Jina Chetia Too many query rows 10001 in Batch Apex class
Hi ,
I have written a batch apex to create/update custom object records. I have two custom objects Purchase_Sales_Report__c and Portfolio_Report__c. Portfolio_Report__c is basically a summary of Purchase_Sales_Report__c records based on a Unique Key. They are both child of the Account object.
Every day I get a feed of Purchase_Sales_Report__c objects and based on those records I either need to create a new Portfolio_Report__c record or update the existing record based on a Unique key.
In the Database.queryLocator I send all the Purchase_Sales_Report__c records but inorder to update the existing Portfolio_Report__c report, I also require a list of all the existing Portfolio_Report__c records where I face an issue of 'Too many query rows 100001' as the number of records returned is more than 10 k.
here is my piece of code. The one in red is where I get an error,
global class CreateSummaryReport implements Database.Batchable<sObject>, Database.Stateful {
global String query;
global Map<String, Portfolio_Report__c> existingPortMap = new Map<String, Portfolio_Report__c>();
global Map<String, Double> purchaseAmountMap = new Map<String, Double>();
global Map<String, Double> productMap = new Map<String, Double>();
global Map<String, Double> unitsMap = new Map<String, Double>();
global Map<String, Purchase_Sales_Report__c> otherDetailsMap = new Map<String, Purchase_Sales_Report__c>();
global String email;
global CreateSummaryReport(){
for(Portfolio_Report__c por: [Select Gain__c,
Units_Quanitity__c,
Scheme_Scrip_Name__c,
Purchase_Amount__c,
Portfolio_Type__c,
Folio_Number__c,
Current_Value__c,
Absolute_Ret__c,
Annualized_Ret__c,
UniqueName__c,
Dividend_Reivestment__c,
Transaction_Type__c,
Nav_p__c
From Portfolio_Report__c] ){
existingPortMap.put(por.UniqueName__c, por);
}
//email = email;
}
global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext BC, List<sObject> scope){
for(sObject s: scope){
Purchase_Sales_Report__c ps = (Purchase_Sales_Report__c)s;
if(purchaseAmountMap.containsKey(ps.Unique_Name__c)){
if(ps.Amount__c != null){
Double d = ps.Amount__c + purchaseAmountMap.get(ps.Unique_Name__c);
purchaseAmountMap.put(ps.Unique_Name__c, d);
}
}
else {
if(ps.Amount__c == null)
{
ps.Amount__c = 0;
}
purchaseAmountMap.put(ps.Unique_Name__c, ps.Amount__c);
}
if(productMap.containsKey(ps.Unique_Name__c)){
if(ps.Product__c != null) {
Double d = ps.Product__c + productMap.get(ps.Unique_Name__c);
productMap.put(ps.Unique_Name__c, d);
}
}
else {
if(ps.Product__c == null)
{
ps.Product__c = 0;
}
productMap.put(ps.Unique_Name__c, ps.Product__c);
}
if(unitsMap.containsKey(ps.Unique_Name__c)){
if(ps.Units__c != null){
Double d = ps.Units__c + unitsMap.get(ps.Unique_Name__c);
unitsMap.put(ps.Unique_Name__c, d);
}
}
else {
if(ps.Units__c == null)
{
ps.Units__c = 0;
}
unitsMap.put(ps.Unique_Name__c, ps.Units__c);
}
if(!otherDetailsMap.containsKey(ps.Unique_Name__c)){
otherDetailsMap.put(ps.Unique_Name__c, ps);
}
}
}
global void finish(Database.BatchableContext BC){
List<Portfolio_Report__c> updateList = new List<Portfolio_Report__c>();
List<Portfolio_Report__c> newList = new List<Portfolio_Report__c>();
for(String s: purchaseAmountMap.keySet()){
if(existingPortMap.containsKey(s)){
Portfolio_Report__c pr = existingPortMap.get(s);
pr.Purchase_Amount__c = purchaseAmountMap.get(s);
pr.Product__c = productMap.get(s);
pr.Units_Quanitity__c = unitsMap.get(s);
updateList.add(pr);
}
else {
Portfolio_Report__c pr = new Portfolio_Report__c(UniqueName__c=s,
Scheme_Scrip_Name__c=otherDetailsMap.get(s).Scheme_Name__c,
Folio_Number__c = otherDetailsMap.get(s).Folio_Number__c,
Entity__c = otherDetailsMap.get(s).Entity__c,
Product__c = productMap.get(s),
Units_Quanitity__c = unitsMap.get(s),
Purchase_Amount__c = purchaseAmountMap.get(s));
newList.add(pr);
}
}
insert newList;
update updateList;
AsyncApexJob a = [Select Id,
Status,
NumberOfErrors,
JobItemsProcessed,
TotalJobItems,
CreatedBy.Email
from AsyncApexJob
where Id =:BC.getJobId()];
// Create and send an email with the results of the batch.
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
mail.setToAddresses(new String[] {email});
mail.setReplyTo('tech@ffreedom.in');
mail.setSenderDisplayName('Batch Processing');
mail.setSubject('Detailed Portfolio Report Update ' + a.Status);
mail.setPlainTextBody('The batch apex job processed ' + a.TotalJobItems + ' batches with ' + a.NumberofErrors + ' failures.');
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
}
}
Can someone please give me a workaround for this?
Many Thanks,
Jina
Unfortunately you are up against a hard limit there. I'd suggest that rather than carrying out this large query at the beginning of your batch apex, see if you can refactor it to work inside the execute method and limit the extracted results to the sobjects in scope for the execute method.
hey Jina,
Whatever you are querying in batch class constructor
Query it before from where you are calling the batch class..and pass it as a parameter to the batch class
hope this works