I am getting
Too many query rows: 50001
when i run the batch apex.
Below code is part of batch apex (FInish Method).
Also there is a soql inside for loop .can someone help me fixing this.
list<Id> terrIdList = new list<Id>();
Map<Id,Id> groupIdAndTerrIdMap = new Map<Id,Id>();
Map<Id,Set<Id>> terrIdAndSetOfNonTargetPractionerAccIdMap = new Map<Id,Set<Id>>();
Map<Id,Set<Id>> terrIdAndSetOfTargetPractionerAccIdMap = new Map<Id,Set<Id>>();
Map<Id,Set<Id>> terrIdAndSetOfPharmacyAccIdMap = new Map<Id,Set<Id>>();
for(Account_Statistic__c accStatic : districtAccList){
Id tID = Id.valueOf(accStatic.Territory_Id__c);
terrIdList.add(tId);
}
list<Territory> tlist = new list<Territory>();
tlist = [Select t.ParentTerritoryId, t.Id From Territory t where t.ParentTerritoryId in:terrIdList];
map<Id,list<Territory>> tmap = new map<Id,list<Territory>>{};
for(Territory t:tlist){
if(tmap.containsKey(t.ParentTerritoryId)){
tmap.get(t.ParentTerritoryId).add(t);
}else{
list<Territory> plist = new list<Territory>();
plist.add(t);
tmap.put(t.ParentTerritoryId,plist);
}
}
list<Territory>relatedlist= new list<Territory>();
for(Account_Statistic__c accStaticRec:districtAccList)
{
Set<Id> finalaccIdSet = new Set<Id>();
Set<Id> totalaccIdSet = new Set<id>();
relatedlist = tmap.get(accStaticRec.Territory_Id__c);
for(Territory terr:relatedlist){
for(Group groupRec : [SELECT Id,RelatedId FROM Group WHERE RelatedId =:terr.Id] ){
groupIdAndTerrIdMap.put(groupRec.Id,groupRec.RelatedId );
}
if(groupIdAndTerrIdMap != NULL && groupIdAndTerrIdMap.size() > 0){
for(AccountShare accShare : [SELECT Id,AccountId,Account.RecordTypeId,Account.RecordType.Name,Account.Target__c, UserOrGroupId FROM AccountShare WHERE (RowCause = 'Territory' OR RowCause = 'TerritoryManual') AND UserOrGroupId IN :groupIdAndTerrIdMap.keySet() AND (Account.RecordType.Name = 'Practitioner' OR Account.RecordType.Name = 'Pharmacy')] ){
if(accShare.Account.RecordType.Name == 'Practitioner'){
if(accShare.Account.Target__c){
Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );
if(!terrIdAndSetOfTargetPractionerAccIdMap.containsKey(RelatedId) )
terrIdAndSetOfTargetPractionerAccIdMap.put(RelatedId,new Set<Id>());
terrIdAndSetOfTargetPractionerAccIdMap.get(RelatedId).add(accShare.AccountId);
}else{
Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );
if(!terrIdAndSetOfNonTargetPractionerAccIdMap.containsKey(RelatedId) )
terrIdAndSetOfNonTargetPractionerAccIdMap.put(RelatedId,new Set<Id>());
terrIdAndSetOfNonTargetPractionerAccIdMap.get(RelatedId).add(accShare.AccountId);
}
}else if(accShare.Account.RecordType.Name == 'Pharmacy'){
Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );
if(!terrIdAndSetOfPharmacyAccIdMap.containsKey(RelatedId) )
terrIdAndSetOfPharmacyAccIdMap.put(RelatedId,new Set<Id>());
terrIdAndSetOfPharmacyAccIdMap.get(RelatedId).add(accShare.AccountId);
}
}
}
Set<Id> accIdSet = new Set<Id>();
Set<Id> accSet = new Set<Id>();
if(accStaticRec.Account_Type__c == 'Target'){
accIdSet = terrIdAndSetOfTargetPractionerAccIdMap.get(accStaticRec.Territory_Id__c);
}else if(accStaticRec.Account_Type__c == 'Non-Target'){
accIdSet = terrIdAndSetOfNonTargetPractionerAccIdMap.get(accStaticRec.Territory_Id__c);
}else if(accStaticRec.Account_Type__c == 'Pharmacy'){
accIdSet = terrIdAndSetOfPharmacyAccIdMap.get(accStaticRec.Territory_Id__c);
}
if(accIdSet != NULL && accIdSet.size() > 0){
AggregateResult[] groupedResultCall = [SELECT Account_vod__c,count(Id) FROM Call2_vod__c WHERE Call_Date_vod__c >= :MonthStartDate AND Call_Date_vod__c <= :MonthEndDate AND Account_Type__c =:accStaticRec.Account_Type__c
AND Territory_vod__c =:terr.Id AND Status_vod__c = 'Submitted_vod' AND (Call_Type_vod__c != 'Call Only' OR Call_Type_vod__c != 'Event Only' OR Call_Type_vod__c != 'Event Detail')
AND Account_vod__c IN :accIdSet
//PS added 7.11.2013
AND Activity_Type__c <> 'Staff'
GROUP BY Account_vod__c ];
System.debug('::::groupedResultCall:::::'+groupedResultCall);
Map<Id,Integer> AccIdAndCountOfCallMap = new Map<Id,Integer>();
Integer totalCallsCount = 0;
for (AggregateResult ar : groupedResultCall) {
String accId = String.ValueOf(ar.get('Account_vod__c'));
accSet.add(Id.valueOf(accId));
finalaccIdSet.addAll(accSet);
}
totalaccIdSet.addAll(accIdSet);
}
System.debug('::::finalaccIdSet:::::'+finalaccIdSet.size());
System.debug('::::totalaccIdSet:::::'+totalaccIdSet.size());
}
System.debug('::::finalaccIdSet:::::'+finalaccIdSet.size());
System.debug('::::totalaccIdSet:::::'+totalaccIdSet.size());
if(accStaticRec.Reach_Status__c == 'Reached'){
accStaticRec.Accounts__c = finalaccIdSet.size();
}else if(accStaticRec.Reach_Status__c == 'Not Reached'){
if(finalaccIdSet.size() == totalaccIdSet.size()){
accStaticRec.Accounts__c = 0;
}else{
accStaticRec.Accounts__c = totalaccIdSet.size() - finalaccIdSet.size();
}
}
accStaticListUpdate.add(accStaticRec);
String mapKey = accStaticRec.Territory_Id__c+accStaticRec.Account_Type__c+accStaticRec.Reach_Status__c;
ProcessedDistrictMap.put(mapKey,new Map<String,Decimal>());
ProcessedDistrictMap.get(mapKey).put('#Accounts',accStaticRec.Accounts__c);
}
Best Answer
Try cutting your batch size down. Make sure you process records in the execute method.
------- EDIT -------
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
One has to be very careful when using a SOQL query in a loop to make sure you don't run into the TOO MANY SOQL QUERIES error -- you need a guarantee that the outer loop has a sufficiently small number of records to iterate over. And using some clever scheme with maps can get the SOQL query out of the loop. But you're not running into this problem (yet). The issue is the current batch size (default = 200) requires you to query too many records. Two solutions:
-------- EDIT #2 ------
Here's an example of a method I use to send a nice email describing a batch process to be used in its
finish
methodTest code:
With test_Batch_code: