Most efficient way to retrieve specific records by ID?

[FullCircleFoods]

I have a table of products with ~5000 records. I’d like to grab a subset of these records (about 1000 of them) using the API, and I already have the list of Record IDs that I want.

I’ve figured out two ways to get this data using filterByFormula, but both methods take a long time to fetch 1000 records.

First method: takes about 20 seconds

OR(
    RECORD_ID = "a1b2c3",
    RECORD_ID = "a1b2c4",
    RECORD_ID = "a1b2c5",
    RECORD_ID = "a1b2c6"
)

Second method: takes about 10 seconds

SEARCH(RECORD_ID(), "a1b2c3,a1b2c4,a1b2c5,a1b2c6") != ""

Are there any more efficient formulas I could be using here?

[Chester_McLaugh]

I hadn’t thought of using SEARCH before! I gave it a shot on 100 record ID’s and compared it to the OR method but didn’t see any difference (I still got results in ~500ms). I was limiting the returned fields to a single one for simplicity.

I’m curious what code (language/library?) you’re using and how you handle the paging (100 record limit and offset) from the API?

My hunch is that if you’re providing ~1000 record ID’s in your query and then stepping through the paged results, you’d be better off chunking your record ID’s into batches of 100 and making 10 individual requests. I don’t have time to test it now, but essentially that is how I handle large result sets in Airpress

[FullCircleFoods]

Good question. I’m using this PHP client: https://github.com/sleiman/airtable-php

And I wrote my own helper function for stepping through all of the paged results:

// Recursive version of Airtable PHP client's getContent() method
// Including some built-in friendly debugging
function getContentRecursive($db_inventory, $table_name, $filters = []) {

  // Fetch the first response
  $response = $db_inventory->getContent($table_name, $filters)->getResponse();

  // If there's an error, show it and return an empty array.
  if ($response->error) {
    var_dump($response->error->type.': '.$response->error->message);
    return [];
  }
  
  $content = [];
  $content = array_merge($content, $response['records']);
  while ($response->next()) {
    $response = $response->next()->getResponse();
    $content = array_merge($content, $response['records']);
  }
  return $content;
}

I will try chunking up the record IDs as you suggested – thanks for the tip!

[FullCircleFoods]

Chunking the IDs into batches of 100 is indeed a second or two faster for my query. Here is the code snippet I’m using with the airtbale-php library:

    $product_records = [];
    foreach (array_chunk(array_keys($products), 100) as $chunk) {
      $response = $db_inventory->getContent('Products', [
        'filterByFormula' => 'SEARCH(RECORD_ID(), "'.implode($chunk, ',').'") != ""'
      ])->getResponse()['records'];
      $product_records = array_merge($product_records, $response);
    }