function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sakshi Gandhi 8sakshi Gandhi 8 

Too many Query rows More than 50001

Hello, I am inserting 60k records and trigger is showing Too many query rows exception. I am trying to achieve roll up summary through this trigger .Below is code


trigger FRB_updateExceptionField on Process_Adherence_Monitoring__c (after insert,after update,after delete) {
Set<id> accIdSet = new Set<id>();
       string FRBRecordTypeID = utility.getFRBProfileNameLabel();
       if(Trigger.isInsert){
            For(Process_Adherence_Monitoring__c pm1 : Trigger.new){
                accIdSet.add(pm1.Organization_Name__c);
            }
        }
        if(Trigger.IsUpdate || Trigger.Isdelete){
            For(Process_Adherence_Monitoring__c pm1 : Trigger.old){
                accIdSet.add(pm1.Organization_Name__c);
            }
        }
        
        List<Account> accUpdateListunderfrst = new List<Account>();
        List<Account> accUpdateListUnderScnd = new List<Account>();
        List<Account> accUpdateListUnderThird = new List<Account>();
        List<Account> accUpdateListUnderForth = new List<Account>();
        Map<id,Account> accMap = new Map<id,Account>([select id, Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id IN :accIdSet]);
       List<Account>listToUpdateFrstBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) from Account where Id IN :accIdSet];
       if(listToUpdateFrstBcktExceptions .size()>0){
           for (Account acc: listToUpdateFrstBcktExceptions ){
                accMap.get(acc.Id).Number_of_Exception__c = acc.Process_Adherence_Monitoring__r.size();
                accUpdateListunderfrst.add(accMap.get(acc.Id));
            }
        }
        
        
        List<Account>listToUpdatesecondBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' ) from Account where Id IN :accIdSet];
        if(listToUpdatesecondBcktExceptions .size()>0){
            for (Account acc:listToUpdatesecondBcktExceptions  ) {
                accMap.get(acc.Id).Number_of_exceptions_under_4_6_bucket__c= acc.Process_Adherence_Monitoring__r.size();
                accUpdateListUnderScnd.add(accMap.get(acc.Id));
            }
        }
        
        List<Account>listToUpdatethrdBcktExceptions =  [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' ) from Account where Id IN :accIdSet];
        if(listToUpdatethrdBcktExceptions.size()>0){
            for (Account acc:listToUpdatethrdBcktExceptions ) {
                accMap.get(acc.Id).Number_of_exceptions_under_7_10_bucket__c= acc.Process_Adherence_Monitoring__r.size();
                accUpdateListUnderThird.add(accMap.get(acc.Id));
            }
        }
        
        List<Account>listToUpdatefrthBcktExceptions =[select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) from Account where Id IN :accIdSet];
         if(listToUpdatefrthBcktExceptions.size()>0){
            for (Account acc: listToUpdatefrthBcktExceptions) {
                accMap.get(acc.Id).Number_of_exceptions_under_11_19_bucket__c= acc.Process_Adherence_Monitoring__r.size();
                accUpdateListUnderForth.add(accMap.get(acc.Id));
            }
        }
        if(accUpdateListunderfrst.size()>0){
             update accUpdateListunderfrst;
        }
        if(accUpdateListUnderScnd.size()>0){
             update accUpdateListUnderScnd;
        }
        if(accUpdateListUnderThird.size()>0){
             update accUpdateListUnderThird;
        }
        if(accUpdateListUnderForth.size()>0){
             update accUpdateListUnderForth;
        }
       

}

Please help
Jasper WallJasper Wall

Hi Sakshi,
50k is a salesforce Limit, Total number of records retrieved by SOQL queries can not exceed 50k as per 
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm
You will need to use Batch Apex, to insert records more than 50K, see link below to use batch apex
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch.htm

Thanks,
Balayesu
 
sakshi Gandhi 8sakshi Gandhi 8
I need to trigger this logic after insert.So is it fine to call batch through trigger?
Jasper WallJasper Wall
You can call a Batch from a trigger, but you only have 5 batch jobs can execute at once in the queue.
sakshi Gandhi 8sakshi Gandhi 8
How can we run that I never did that can you please help in above trigger how can  I call batch 
Jasper WallJasper Wall
I am writing code for 'accUpdateListunderfrst' , and you need call your batch class from the other if conditons.
 
Call the Batch class from your trigger like this,

    
if(accUpdateListunderfrst.size()>0){
             Database.executeBatch(new YourBatchClass(accUpdateListunderfrst));
   }

Write your Batch class like this,

  
global class YourBatchClass implements Database.Batchable<sObject> {
//map of userid - user
List<Account> myaccs=new List<Account>();
Set<Id> accIdSet=new Set<Id>();
//Constructor initialization
global YourBatchClass(List<Account> accs) {
myaccs= accs;
for(Account a:myaccs){
   accIdSet.add(a.Id);
}
}
//Quuery method.
global Database.QueryLocator start(Database.BatchableContext BC) {
return DataBase.getQueryLocator([select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) from Account where Id IN :accIdSet]);
}

//Execute Method.
global void execute(Database.BatchableContext BC,List<Account> scopeAcc) {
   update scopeAcc;
}

//Finish method to execute at last.
global void finish(Database.BatchableContext BC) {
//Send an email to the User after your batch completes
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {'youremail@gmail.com'};
mail.setToAddresses(toAddresses);
mail.setSubject('Apex Batch Job is done');
mail.setPlainTextBody('The batch Apex Job Processed Successfully');
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
}
}

Mark it as the best answer if it helps,

Thanks,
Balayesu
sakshi Gandhi 8sakshi Gandhi 8
While callling batch through trigger it is giving below error

"You've exceeded the limit of 100 jobs in the flex queu"
sakshi Gandhi 8sakshi Gandhi 8
It is giving below error 


You've exceeded the limit of 100 jobs in the flex queu
Jasper WallJasper Wall
To avoid hitting the salesforce limit of 100 jobs, write a query before calling your batch class like this, 
Integer flexQueueSize = [SELECT COUNT() FROM AsyncApexJob WHERE Status = 'Holding' FOR UPDATE];

if(flexQueueSize<100 && accUpdateListunderfrst.size()>0){
 
    Database.executeBatch(new YourBatchClass(accUpdateListunderfrst));

}

call the batch class in your trigger only once by combining all the four lists as one and pass this one as a parameter to your batch class.

Thanks,
Balayesu
sakshi Gandhi 8sakshi Gandhi 8
Thank you so much Balayesu for your help

Now it is allowing me to insert 60k records successfully but again giving "First error: Too many query rows: 50001" even in batch class below is the code:



Trigger Code:

trigger FRB_updateExceptionField on Process_Adherence_Monitoring__c (after insert,after update,after delete) {
Set<id> accIdSet = new Set<id>();
       string FRBRecordTypeID = utility.getFRBProfileNameLabel();
       if(Trigger.isInsert){
            For(Process_Adherence_Monitoring__c pm1 : Trigger.new){
                accIdSet.add(pm1.Organization_Name__c);
                
            }
            if(accIdSet.size()>0){
                   Integer flexQueueSize = [SELECT COUNT() FROM AsyncApexJob WHERE Status = 'Holding'];
                   if(flexQueueSize < 100){
                    Database.executeBatch(new FRB_BatchToUpdateExceptionFields ());
                    system.debug('*********'+accIdset);
                    }
            }
        }
        if(Trigger.IsUpdate || Trigger.Isdelete){
            For(Process_Adherence_Monitoring__c pm1 : Trigger.old){
                accIdSet.add(pm1.Organization_Name__c);
               
            }
             if(accIdSet.size()>0){
                    Integer flexQueueSize = [SELECT COUNT() FROM AsyncApexJob WHERE Status = 'Holding'];
                   if(flexQueueSize < 100){
                    Database.executeBatch(new FRB_BatchToUpdateExceptionFields ());
                    }
                }
        }
        
      

}


Batch Class code:

global class FRB_BatchToUpdateExceptionFields implements Database.Batchable<SObject>{
    global database.querylocator start(Database.BatchableContext bc)
    {  
       string FRBRecordTypeID = utility.getFRBProfileNameLabel();                 
       string query= 'Select id,Status__c,LastModifiedDate,Account_Resolution_Date__c,Organization_Name__c,C1_TOTAL_BAL_THRESHOLD__c,C1_Resolution_Date__c,C2_RCVRABLE_COST_COST_SPENT__c,C2_Resolution_Date__c,C3_ATTY_OTHER_FEES_CNTRCT_AMT__c,C3_Resolution_Date__c,C4_ATTY_FEES_STAT_AMT__c,C4_Resolution_Date__c,C5_INTEREST_RATE__c,C5_Resolution_Date__c,C6_INTEREST_THRESHOLD__c,C6_Resolution_Date__c,C7_CALCULATED_BALANCE_FIRM_BAL__c,C7_Resolution_Date__c,C7A_PRIN_AMT_PLACED_PRIN__c,C7A_Resolution_Date__c,C7B_REC30_PMTS_ADJS_REC42_PMTS_ADJS__c,C7B_Resolution_Date__c,C7C_REC30_RCVR_COST_REC42_RCVR_COST__c,C7C_Resolution_Date__c,C7D_CURRENT_BALANCE_0__c,C7D_Resolution_Date__c  from Process_Adherence_Monitoring__c where RecordtypeId =: FRBRecordTypeID  AND Status__c != \'Closed\'';
       return Database.getQueryLocator(query);
    }
    global void execute(Database.BatchableContext bc, Process_Adherence_Monitoring__c[] FRBList)
    {
       string FRBRecordTypeID = utility.getFRBProfileNameLabel();
     system.debug('=====FRBList'+FRBList);
     List<Account> accUpdateListunderfrst = new List<Account>();
     List<Account> accUpdateListUnderScnd = new List<Account>();
     List<Account> accUpdateListUnderThird = new List<Account>();
     List<Account> accUpdateListUnderForth = new List<Account>();
     set<id>accidSet = new set<id>();  
        if(FRBList.size()>0){
           for(Process_Adherence_Monitoring__c p:FRBLIST){
               accidSet.add(p.Organization_Name__c);
           }
        }
       
     Map<id,Account> accMap = new Map<id,Account>([select id, Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id IN :accIdSet]);
     if(accidSet.size()>0){
       List<Account>listToUpdateFrstBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) from Account where Id IN :accIdSet];
       if(listToUpdateFrstBcktExceptions .size()>0){
            for (Account acc: listToUpdateFrstBcktExceptions ){
                accMap.get(acc.Id).Number_of_Exception__c = acc.Process_Adherence_Monitoring__r.size();
                accUpdateListunderfrst.add(accMap.get(acc.Id));
            }
        }        
        
        List<Account>listToUpdatesecondBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' ) from Account where Id IN :accIdSet];
        if(listToUpdatesecondBcktExceptions .size()>0){
            for (Account acc:listToUpdatesecondBcktExceptions  ) {
                accMap.get(acc.Id).Number_of_exceptions_under_4_6_bucket__c= acc.Process_Adherence_Monitoring__r.size();
                accUpdateListUnderScnd.add(accMap.get(acc.Id));
            }
        }
        
        List<Account>listToUpdatethrdBcktExceptions =  [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' ) from Account where Id IN :accIdSet];
        if(listToUpdatethrdBcktExceptions.size()>0){
            for (Account acc:listToUpdatethrdBcktExceptions ) {
                accMap.get(acc.Id).Number_of_exceptions_under_7_10_bucket__c= acc.Process_Adherence_Monitoring__r.size();
                accUpdateListUnderThird.add(accMap.get(acc.Id));
            }
        }
        
        List<Account>listToUpdatefrthBcktExceptions =[select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) from Account where Id IN :accIdSet];
         if(listToUpdatefrthBcktExceptions.size()>0){
            for (Account acc: listToUpdatefrthBcktExceptions) {
                accMap.get(acc.Id).Number_of_exceptions_under_11_19_bucket__c= acc.Process_Adherence_Monitoring__r.size();
                accUpdateListUnderForth.add(accMap.get(acc.Id));
            }
        }
      }
        if(accUpdateListunderfrst.size()>0){
             update accUpdateListunderfrst;
        }
        if(accUpdateListUnderScnd.size()>0){
             update accUpdateListUnderScnd;
        }
        if(accUpdateListUnderThird.size()>0){
             update accUpdateListUnderThird;
        }
        if(accUpdateListUnderForth.size()>0){
             update accUpdateListUnderForth;
        }
       
    }
    global void finish(Database.BatchableContext bc)
    {
    
    }
}
Jasper WallJasper Wall
Limit the query records in your batch by 200 ( this size can be upto 2000 )  in your executebatch() and also use scheduleBatch().
try the if condition in your trigger like this,
  
if(accIdSet.size()>0){
                   Integer flexQueueSize = [SELECT COUNT() FROM AsyncApexJob WHERE Status = 'Holding'];
                   if(flexQueueSize < 100){
                    Database.executeBatch(new FRB_BatchToUpdateExceptionFields (),200);
                    system.debug('*********'+accIdset);
                    }
                    else{
                     System.scheduleBatch(new FRB_BatchToUpdateExceptionFields (),'FRB_BatchToUpdateExceptionFields',60);
                    }
            }

 
sakshi Gandhi 8sakshi Gandhi 8
hello Balayesu,

I have changes my code as per your suggestion and again load records and it is saying 

that Job has already scheduled for execution:

The Apex job named "FRB_BatchToUpdateExceptionFields" is already scheduled for execution.

As apex jobs are more than 100 so it is trying to execute it again .Not sure how to achieve this
Jasper WallJasper Wall
Give a unique name to your job like this, 
System.scheduleBatch(new FRB_BatchToUpdateExceptionFields (),'FRB_Flds'+System.currentTimeMillis(),60);
sakshi Gandhi 8sakshi Gandhi 8
So it means when apex job will be more than 100 after that it will schedule batch and when that batch runs it will update rest of records?
Jasper WallJasper Wall
yes
sakshi Gandhi 8sakshi Gandhi 8
Unfortunately it is not working even if I am not triggering batch scheduling it seprately then also its giving too many query rows error
Jasper WallJasper Wall
limit records in this method also,
  
System.scheduleBatch(new FRB_BatchToUpdateExceptionFields(),'FRB_Flds'+System.currentTimeMillis(),60,200);