Overview

FOLIO clients often have the need to call an API to look up a record by UUID, one at a time for thousands if not hundred of thousands of records.  The obvious disadvantage of retrieving one record at a time is the overhead associates with it. Currently querying for one record incurs a SELECT count_estimate() call, which equals the actual SELECT query time. Additionally, each API call out to a storage module incurs a mod-authtoken call, which is needed to verify the token and permission of the caller. Therefore, this testing effort explores whether or not concatenating UUIDs in the CQL query string would be more efficient in retrieving records, and if there was any downside to doing this.

This test will 

Summary

• Concatenating UUIDs in a batch can be done up to 50 records. 
• Retrieving one record at a time at a rate of 40 requests/second uses up about 700MB of database RAM in 15 minutes. Retrieving more records at a time or more users doing multi-records lookups concurrently will use up more DB memory.
• The optimal range in terms of time and resource is about 10-20 records
• It's up to the application developer to make a decision of concatenating UUIDs, and if so, how many UUIDs. Many applications have different needs, to look up a handful of records or hundreds of thousands of records. This report will lay out the facts for the developer to be informed about the pros and cons of concatenating UUIDs.

Test Results

The test that was run mimic the data-export workflow (PERF-98), making two calls: GET /inventory-storage/item-storage and GET /inventory-storage/holdings-storage but is not designed to behave the same way as the actual data-exporting workflow (the real workflow would pause to process the retrieved records after each API call, for instance).  More importantly UUIDs are concatenate in these calls which deviates from the real use case.