How can I make multiple Http callouts in a row?

Question

I need to build an apex class that will perform multiple callouts in a row to an external system and upsert some records.

Right now I'm sending a POST method with a query in the body and getting an amount of data greater than the heap size limit (12mb)

@future(callout=true)
public static void runBigQuerySql(){

    String jsonResponse;
    String sql = 'SELECT * FROM <table>';

    GoogleBigQuery google = new GoogleBigQuery();
    google.runQuery(sql);
    jsonResponse = google.getResponse();
    JsonResponse response = (JsonResponse) System.JSON.deserialize(jsonResponse, 
    JsonResponse.class);
    
    List<Account> listAcct = new List<Account>();
    listAcct.add(
                    new Account(
                        FirstName = item.f.get(0).v,
                        LastName = item.f.get(1).v,
                        Phone = item.f.get(2).v
                        .....
                    )
    .....
}

How can I workaround the heap size in this scenario? I thought about using the LIMIT and OFFSET statements to get small lots of data and make multiple callouts to get all records. But I'm not sure how to refresh the heap size limit for each callout.

Answer 1

Note that the limit you're going to hit first is the callout response limit. The sum of all bytes received can't exceed the heap limit (12 MB for asynchronous code).

Instead of using future, use Queueable or Batchable, which you can use to pull as much data as you need. As you've surmised, you'll need to keep each transaction's callout results to under 12 MB, but you can have essentially unlimited chains in both Queueable or Batchable classes to suit your purposes. Also remember that you can briefly exceed the heap limit if you're careful, it's kind of "squishy", you just don't want to be caught over the limit when the random check occurs.

To avoid blowing up the heap, consider discarding the results immediately after parsing:

String sql = 'SELECT * FROM <table>';
JsonResponse response;

// Use a "block" to discard intermediary data as soon as possible
{
  GoogleBigQuery google = new GoogleBigQuery();
  google.runQuery(sql);
  response = (JsonResponse) System.JSON.deserialize(google.getResponse(), 
  JsonResponse.class);
}
List<Account> listAcct = new List<Account>();
// I don't know what this looks like, just adapt to the real thing
for(JsonResponse.Record record: response.records) {
  // rest of logic
}

Using this, it would probably be safe to retrieve up to about 10 MB of data, but be sure to test it. Leave some headroom if you don't know how big the data can be.

Queueable can also use Finalizer to catch otherwise uncatchable conditions. You might use this to determine a query result was too big and try the call again with a smaller LIMIT to compensate. You might want to look into a binary search algorithm to minimize the number of failed calls. Of course, if the data is highly variable, you might settle for a simpler algorithm that just adds a small amount for each success, and decreases a small amount for each failure.