Query more than 50000 records

Question

Inner query(b object in this case) is contributing to 50k issue. I need to count the number of records based on type and update the parent object.

A - parent B - child Select id, (select id, type from b) from A; Is returning more than 50k. Parent records are less than hundred. Child records are sometimes more than 50k

We can go with a batch class but I’m confused with the no. of records it processes since i need all the records in the inner query at once. I know that execute method executes 200 records so is it 1 a and 199 b records ?

Since we’re just querying we can use for loop but it’s same case as batch apex.

Please let me know how to achieve this or how the batch apex or for loop query functions in case when using inner queries?

Answer 1

Lucky for you, Salesforce changed the way query aggregation counts against your limits recently, so you can drop the child query altogether. Just use aggregate queries instead. Before, every record which went into the aggregation counted against you, but now only every aggregation counts.

public with sharing class MyRollupBatch implements Database.Batchable<SObject>
{
    public Database.QueryLocator start(Database.BatchableContext context)
    {
        return Database.getQueryLocator([
            SELECT Id FROM Parent__c WHERE ...
        ]);
    }
    public void execute(Database.BatchableContext context, List<Parent__c> records)
    {
        List<Parent__c> rollups = new List<Parent__c>();
        for (AggregateResult result : [
            SELECT Parent__c, count(Id) rollup FROM WHERE Parent__c IN :records
        ]) rollups.add(new Parent__c(
            Id=(Id)result.get('Parent'),
            Rollup__c=(Decimal)result.get('rollup')
        );
        update rollups;
    }
    public void execute(Database.BatchableContext context) { }
}

Answer 2

I would recommend you to use Batch but without querying sub-records, this could lead to unpredictable errors, especially if you have 200+ of them. Try to rewrite your query in the next way:

public Database.QueryLocator start(Database.BatchableContext context) {
  return Database.getQueryLocator([
    SELECT Id FROM Child__c WHERE Parent__c IN :(SELECT Id FROM Parent__c ...)]);
}

Later you can collect some data from childs using Database.Stateful interface and update parent records with collected results in the finish method. Or you could map parents to children in stateful variable. But be aware of the Heap limit.