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
Steve Berley [Left Propeller]Steve Berley [Left Propeller] 

Iterable Batch giving error -- Too many query rows: 50001

I'm running into the dreaded too many query rows error on an iterable batch.  No matter the batch size I use, it immediately errors out; even if I set the batch size to 1.  Looking at the logs, it seems to happen while still in the start() method.

This is driving me nuts - I look forward to your insights...

Thanks!

Here's the batch... 
global with sharing class update_MBI implements Database.Batchable<AggregateResult> {
	global update_MBI() { }
    global string mbiQuery = 'select account__c, sum(spend__c) spendLast12 from spend__c where age__c < 0 and age__c >= -12 group by account__c ';

	global Iterable<AggregateResult> start(Database.BatchableContext BC) {  
		return new mbiIterable(mbiQuery);
	}

   	global void execute(Database.BatchableContext BC, List<AggregateResult> scope) {
		list<account> u = new list<account>();
		for (AggregateResult ar : scope){
			id acctID = id.valueOf(string.valueOf(ar.get('account__c')));
			decimal newMBI = decimal.valueOf(string.valueOf(ar.get('spendLast12'))) / 12;
			u.add(new account(id=acctID, MBI_Monthly_Spend_Current__c = newMBI));
		}
		update u;
	} 

	global void finish(Database.BatchableContext BC) {	}
}

The Iterable  
public with sharing class mbiIterable implements Iterable<AggregateResult> {
    private String query;

    public mbiIterable(String soql){
		query = soql;
    }

    public Iterator<AggregateResult> Iterator(){
		return new mbiIterator(query);
    }
}

The iterator 
public class mbiIterator implements Iterator<AggregateResult> {

  AggregateResult[] results { get;set; }
  Integer index { get;set; }

  public mbiIterator(String query) {
	  index = 0;
	  results = Database.query(query);
  }

  public Boolean hasNext(){
	  return results != null && !results.isEmpty() && index < results.size();
  }

  public AggregateResult next() {
	  return results[index++];
  }
}

 
Amit Chaudhary 8Amit Chaudhary 8
total number of records retrieved by SOQL queries = 50,000

Add limit 50000 in your query like below
select account__c, sum(spend__c) spendLast12 from spend__c where age__c < 0 and age__c >= -12 group by account__c limit 50000
 
Alain CabonAlain Cabon

Query Editor (Developer console): select count() from spend__c where age__c < 0 and age__c >= -12

Result: > 50.000 ?

global string mbiQuery = 'select account__c, sum(spend__c) spendLast12 from spend__c where age__c < 0 and age__c >= -12 group by account__c ';

It is one of the most-dread limitation of Salesforce in Apex.

You cannot process more than 50.000 records in Apex (including for counting with id or grouping them) with one call of a SOQL query but that works by using a query editor and Rest queries (if the timeout limit is not reached).

Query editor (Developer console): select account__c, sum(spend__c) from spend__c where age__c < 0 and age__c >= -12 group by account__c    Does that work? (rest query)? 

This limitation in Apex has this workaround  
Integer intCount = 0;
for(AggregateResult result :[SELECT COUNT(Id) intVal FROM Custom_Object__c])
{
    intCount+=(Integer)result.get('intVal');
}   
System.debug('No of records are: '+intCount);

You summarize the global result by chuncks of 10.000 records.

for(AggregateResult result :[SELECT COUNT(Id) intVal FROM Custom_Object__c]) : has an implicit limitation of 10.000 records.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_loops_for_SOQL.htm

Idea: Count the SOQL count() query as a single row query
https://success.salesforce.com/ideaView?id=08730000000Br7TAAS
 
Steve Berley [Left Propeller]Steve Berley [Left Propeller]
@Alain to the rescue, as always!  

I'm surprised you find that this works because for loops page includes the following passage

Note that queries including an aggregate function don't support queryMore. A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop

So I'm not sure how that would work using the average function.

However, since I'm averaging by account - my latest thinking is to break batch to do accounts with names starting with A-L then M-Z.  I would have the finish method of an A-L dispatch the batch for M-Z.  Yeah, it's a bit hacky, but it should work.
Alain CabonAlain Cabon
@Steve: Do not confuse the result of the aggregation and the read records to get this result.

The maximum number of records for the result is 2,000 rows but the read records is also limited in Apex to 50,000 records in APEX.

I don't know your data;

Query Editor (Developer console): select count() from spend__c where age__c < 0 and age__c >= -12   ( WITHOUT GROUPING only COUNT() = read records )

Result: > 50.000 ?  (  I don't know, only you can give us the result)

I feel that you confuse the result of the aggregation and the read records to get this result (and the query in Rest or in Apex)/
 
Steve Berley [Left Propeller]Steve Berley [Left Propeller]
@Alain - I get it - thanks.

I'm in the unfortunate position of both an excess of 50k records to read and more than 2000 records in the aggregated result.  So, I need to work my way out of two holes.

Since I'm aggregating within accounts the idea of managing scope using the first letter of account names becomes workable, though not terribly appealing.
Alain CabonAlain Cabon
@Steve: did you solve your problem?

I finally created a test with a scratch org ( 200 mo instead 5 mo for a developer org ) with more than 100.000 rows.

You can query large object with the query editor of the developer console directly but that will not work with Apex code:

User-added image

But the simple Apex code below generates the Too many query rows: 50001
  
AggregateResult result =[select sum(number__c) mysum from test__c];
decimal mysum =(decimal)result.get('mysum');
system.debug('sum:' + mysum);
I tested this code: 
Map<String,Decimal> totalAccounts = new Map<String,Decimal>(); 
for(List<AggregateResult> result :[select account__r.name myname, sum(number__c) mynumber from test__c group by account__r.name]) { 
       for (AggregateResult ar : result) { 
             String myname= (String) ar.get('myname');
             decimal mynumber =(Decimal)ar.get('mynumber');
             decimal tmp = 0;
             if (totalAccounts.containsKey(myname)) {        
                 tmp = totalAccounts.get(myname);
             } 
             totalAccounts.put(myname,tmp + mynumber); 
      }
}
for (String key:totalAccounts.keySet()) {
    system.debug('account:' + key + ' total=' + totalAccounts.get(key));
}

User-added image

I often use the tested results of questions in this forum for my own work but all the questions are not interesting.
Steve Berley [Left Propeller]Steve Berley [Left Propeller]
@Alain - I did solve it. 

I take advantage of the fact I'm aggregating within accounts so limit each run to accounts beginning with a single letter.  For example all accounts starting with A,

The genQuery() method dynamically generates the soql query and I cycle through the alphabet with each batch kicking off the next one in the finish() method.

Yeah, it's inelegant, but it gets the job done and I won't face 50k+ accounts starting witih a given letter anytime soon.

Thanks for asking and Happy Holidays...

Steve  
global with sharing class update_MBI implements Database.Batchable<AggregateResult> {

    global string letter;
    private map<string, string> letters = new map<string, string>{'a' => 'b', 'b' => 'c', 'c' => 'd', 'd' => 'e', 'e' => 'f', 'f' => 'g', 'g' => 'h', 'h' => 'i', 'i' => 'j', 'j' => 'k', 'k' => 'l', 'l' => 'm', 'm' => 'n', 'n' => 'o', 'o' => 'p', 'p' => 'q', 'q' => 'r', 'r' => 's', 's' => 't', 't' => 'u', 'u' => 'v', 'v' => 'w', 'w' => 'x', 'x' => 'y', 'y' => 'z', 'z' => '0', '0' => '1', '1' => '2', '2' => '3', '3' => '4', '4' => '5', '5' => '6', '6' => '7', '7' => '8', '8' => '9' };


    global update_MBI(string startsWith) {
        letter = startsWith;
    }

    global Iterable<AggregateResult> start(Database.BatchableContext BC) {  
        return new genericIterable( genQuery(letter) );
    }

    global void execute(Database.BatchableContext BC, List<AggregateResult> scope) {
        processData(scope);
    } 

    global void finish(Database.BatchableContext BC) {
        if (letters.containsKey(letter)){ 
            string nextLetter = letters.get(letter);
            database.executebatch(new update_MBI(nextLetter));
        }
    }

    global static string genQuery(string startsWith){
        string q = 'select account__c, sum(spend__c) spendLast12 from spend__c ';

        if(startsWith != null && startsWith != '') {
            q += ' where account__r.name like \'' + startsWith + '%\'' ;
        }
        q += ' group by account__c ';
        return q;
    }
}

 
    
 
Alain CabonAlain Cabon
@Steve : Good news. You need to rely on the distribution of the groupings but there is no better solution at first glance.

All the "tricks" are valuable and we get the results surely (that is the most important).

The inner loop above will block at 2,000 accounts differents by bunch of 50,000 (that is always possible) so that is not perfect either (that is a  partial workaround sufficent in some cases).

This most-dread limitation of Salesforce should have a "best practice/solution" provided by the engineering of Salesforce directly because it is a fundamental need but I didn't find it.

Wait and see if someone can provide a more "elegant" solution but that will be difficult according to me.
You need to sample the data with sub-grouping values indeed (trusting that the governor limits will be never reached either with a sufficient margin).

Happy Holidays.

Alain
Steve Berley [Left Propeller]Steve Berley [Left Propeller]
@Alain,

I was worried about the 2000 record limit in the AggregateResult.  Fortunately my worst letter has barely 500 in the AR set so it looks like I'll be set until they find a better solution to the problem.

Steve 
Alain CabonAlain Cabon
"The inner loop above will block at 2,000 accounts differents by bunch of 50,000 (that is always possible) so that is not perfect either (that is a  partial workaround sufficent in some cases)." .. that is for my code above yours. 

If you want to be "sure" (wide margin 500 / 2000 ), the subgrouping (your solution) is surer (verified wider margin).