We have a scenario where we need to query 2 large text area fields and add them as a response to an external system.
The source object on which these fields are present, we have used SOQL for loop to iterate through them and get required data, but even with SOQL for loop just querying data for 200 records results in us hitting more than 90% heap which fails the transactions afterwards. In total we may need to send these fields for 1000-2000 records of that object.
As a workaround what we have implemented is to loop through the records in limits of 40 and check for heap and do the concatenation. if we cross 80% heap we ask the external system to call again.
Basically custom SOQL for loop implementation. Is there a better way to do this and how should this be approached?