+ Start a Discussion
Vijaya kavuruVijaya kavuru 

Too many soql queries:50001 batch job

I have more than 50000 records in production after applying all required filters to soql query. I have Used LIMIT, it is not helping. batch size, i have passed as 200 while execute. Still no go. I do not prefer to use Visualforce page with readonly= true. any better solution please..?

global class abc implements Database.Batchable<sObject>{
String strQuery='';     
global List<Opportunity> ListOfDeals = new List<Opportunity>();     
global List<Policy__c> listtobeUpdated = new List<Policy__c>();     
global map<string,Opportunity> Oppmap = new map<string,opportunity>();     
global Abc(){         strQuery ='SELECT Former_Policy__c,id,Opp__c FROM Policy__c WHERE Form_Policy__c != null AND opp__c = null AND Not_Belong_to_ABC__c=False AND Not_To_Be_Linked_To_Opp__c=False AND Stage__c !=Null AND Status__c != Null LIMIT 49000';     }              
global Database.QueryLocator start(Database.BatchableContext context) {                   
 return Database.getQueryLocator(strQuery);              }         global void execute(Database.BatchableContext context, List<Policy__c> policyBatch){             


          global void finish(Database.BatchableContext context){     }      }
Charisse de BelenCharisse de Belen
Hello Vijaya,

Can you post your code for your autoMatchRenewedPolicy method?
Vijaya kavuruVijaya kavuru
Hello Charlsse,
Sure, Please find below code:

public class AutoMatchRenewedPolicyHelper{
    public static void autoMatchRenewedPolicy(List<Policy__c> policies){
        Map<String,Opportunity> oppMap = new Map<String,Opportunity>();
        List<Policy__c> updPolicies = new List<Policy__c>();
      set<string> Polvals = new set<String>();

for(Policy__c pol:policies)

 List<Opportunity> oppList = new List<Opportunity>([Select Id,Former_Policy__c,OrPolicy_Number__c from Opportunity where OrPolicy_Number__c IN:Polvals]);

        for(Opportunity opp:oppList){
        for(Policy__c pol:policies){
                pol.Opp__c = oppMap.get(pol.Former_Policy__c).Id;
            update updPolicies ;
Charisse de BelenCharisse de Belen
Oh I see, you are actually getting a "Too many query rows" error instead of a "Too many SOQL queries" error.

I think you can remove the LIMIT from the query in your start() method, because that query is probably not causing the error. The Apex Developer Guide says this:
"If you use a QueryLocator object, the governor limit for the total number of records retrieved by SOQL queries is bypassed. For example, a batch Apex job for the Account object can return a QueryLocator for all account records (up to 50 million records) in an org." (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm)

So I think your problem is coming from the SOQL query in autoMatchRenewedPolicy(). I think each Policy record has many related Opportunity records, so the query is returning more than 50000 records. To fix this, try passing in a smaller batch size when you execute the batch. I would start with a small number like 50. You can add this line after the Opportunity query so you can debug how close you are to the limit:
System.debug('>>>>>TOTAL RECORDS RETURNED: ' + Limits.getQueryRows());
You can then adjust the batch size to get as close to the limit without going over.

By the way, you don't need to construct a new list for that Opportunity query. SOQL queries will automatically cast to a list, so you can just do this:
List<Opportunity> oppList = [SELECT Id, Former_Policy__c, OrPolicy_Number__c FROM Opportunity WHERE OrPolicy_Number__c IN :Polvals];
Vijaya kavuruVijaya kavuru
We tried passing batch size as 1. even then we have this issue. I am not sure if this is because we have many other queries happening on update of policies through trigger. It is just not policy trigger but also opportunity trigger that gets triggered on Update Policies.

Thank for the response...! the query for Opportunity really helps. So, i can avoid one for Loop their.  You are correct in saying LIMIT wouldn't work.I will try system.debug. thanks