function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Sebastian PageSebastian Page 

Governing limit Exception error on my batch class

Hi All ,

i am getting error of governing limit error is " System.LimitException: Too many query rows: 50001"

Batch apex class-

global  class SSO_User_count implements  database.Batchable<sObject> {
    
    global database.QueryLocator start(database.BatchableContext bc)
    {
        string query='Select id from account';
      return database.getQueryLocator(query);
    }
   
    global void execute(database.BatchableContext bc ,List<account> scope)
    {
        
      
            List<Account> updateacc=new List<Account>();   
       list<account> acclist=[select id,SSO_User_Count__c,ispartner ,iscustomerportal from account where Ispartner=true and IsCustomerPortal=true];
     for(Account acc:acclist){
       List<user>lstusr=[select id FROM User where isActive=true and User.Profile.UserLicense.Name='Overage High Volume Customer Portal' and accountid=:Acc.id];
        // acc.SSO_User_Count__c=lstusr.size();
        acc.SSO_User_Count__c=lstusr.size();
         
         scope.add(acc);     
     }
        update scope;
    }
    
    global void finish(database.BatchableContext bc)
    {
        
    }

}




 
Best Answer chosen by Sebastian Page
Andrew GAndrew G
Your Issue is the SELECT statement within the FOR loop.   This will cause the governor limits to be hit.  You need to have SOQL queries outside of any loop structure, so therefore must make the query into a LIST which you can then handle withn the loop.  In this case, I would look at MAPS and use of the AGGREGRATE RESULT object.

try something like : 
global void execute(database.BatchableContext bc ,List<account> scope) {
	  
		Map<Id,Account> accMap = new Map<Id,Account>();
		list<Account> updateacc=new List<Account>();   
		
		list<account> acclist = [SELECT id,SSO_User_Count__c,ispartner ,iscustomerportal FROM account WHERE Ispartner=true AND IsCustomerPortal=true];
		for (Account acc : acclist) {
			accMap.put(acc.Id, acc);
		}
		Set<Id> accIds = new Set<Id>(accMap.keySet());
		AggregateResult[] groupedResults = [SELECT AccountId accId, COUNT(Id) uCount FROM User WHERE isActive=true AND User.Profile.UserLicense.Name='Overage High Volume Customer Portal' AND accountid In :accIds GROUP BY AccountId];

		for(AggregateResult results : groupedResults ) {
			Id recId = (Id) results.get('accId');
			Account acc = accMap.get(recId);
			acc.SSO_User_Count__c = (integer)results.get('uCount');
			updateacc.add(acc);

		}
		update updateacc;
	}

Caveat: not tested with actual data

Regards
Andrew

All Answers

Andrew GAndrew G
Your Issue is the SELECT statement within the FOR loop.   This will cause the governor limits to be hit.  You need to have SOQL queries outside of any loop structure, so therefore must make the query into a LIST which you can then handle withn the loop.  In this case, I would look at MAPS and use of the AGGREGRATE RESULT object.

try something like : 
global void execute(database.BatchableContext bc ,List<account> scope) {
	  
		Map<Id,Account> accMap = new Map<Id,Account>();
		list<Account> updateacc=new List<Account>();   
		
		list<account> acclist = [SELECT id,SSO_User_Count__c,ispartner ,iscustomerportal FROM account WHERE Ispartner=true AND IsCustomerPortal=true];
		for (Account acc : acclist) {
			accMap.put(acc.Id, acc);
		}
		Set<Id> accIds = new Set<Id>(accMap.keySet());
		AggregateResult[] groupedResults = [SELECT AccountId accId, COUNT(Id) uCount FROM User WHERE isActive=true AND User.Profile.UserLicense.Name='Overage High Volume Customer Portal' AND accountid In :accIds GROUP BY AccountId];

		for(AggregateResult results : groupedResults ) {
			Id recId = (Id) results.get('accId');
			Account acc = accMap.get(recId);
			acc.SSO_User_Count__c = (integer)results.get('uCount');
			updateacc.add(acc);

		}
		update updateacc;
	}

Caveat: not tested with actual data

Regards
Andrew
This was selected as the best answer
abhishek singh 497abhishek singh 497
Instead for loop you are using soql that's why the error is coming try query outside for loop.
Thanks & Regards,
Abhishek Singh.
 
Sebastian PageSebastian Page
Hi Andrew 

Thanks for quick response.  could you explain  me one line that are you using in your  code .
SELECT AccountId accId, COUNT(Id) uCount FROM User WHERE isActive=true AND User.Profile.UserLicense.Name='Overage High Volume Customer Portal' AND accountid In :accIds GROUP BY AccountId.

In this query you are write  AccountId accId togather .  what its use.



Regards

Sebastian  
 
Andrew GAndrew G
Hi Sebastian

It is in the context of the AggregrateResult object.
I am assigning an alias (accId) to the field name AccountId.  

Whilst the AggregrateResult loop could use the fieldname, eg.
for(AggregateResult results : groupedResults ) {
            Id recId = (Id) results.get('AccountId');
}
I have found that the Aggregrate object query gives a more stable result by using an alias instead of the field name.  By more stable, I mean I have less problems with the code running - sometimes when I have used the fieldname, it is as if the compile recognises the fieldname but it fails to read it a run time.

Glad to help
Andrew
Sebastian PageSebastian Page
Thanks  a lot Andrew for given important knowlegde about Aggregrate Object . I taken good knowledge from you.

Ones again Thanks for Support .

Regards
Sebastian Page