Our NRGship solutions have the ability to expose OData endpoints for enhanced integration with external systems. With that, there are great benefits however there are serious performance implications that need to be considered.

Security 

We like to expose OData behind a proxy server. For that we’re using OttoFMS which allows for creation of a simple key that can be included in the URL for secure access. This key can be easily disabled without impacting user permissions in the FileMaker database being accessed.

Record Creation 

When records are created via OData POST requests, the default response is JSON for the newly created record. This JSON data includes all field data, except for containers. This differs from GET requests which do not include containers OR summary fields. Having summary fields in your table will drastically decrease performance if you have large recordsets when creating records via OData.

One solution to increase performance is to include the Prefer header in the call with return=minimal as per the Claris OData spec. When this header is included, you will receive a HTTP 204 (instead of 201) with no content in the response. This means the request was accepted and processed, but just returned without data. There will still be some overhead when summary fields exist in the table, but it is not as excessive as when they are returned in the JSON response.

The POST response does include the ROWID in the HTTP Response location header so that can be used for subsequent GET queries if data is needed. The GET query can be structured to include $select query options to return only specific field data, perhaps specifically your PrimaryKey field.

location: https://host.domain.com/fmi/odata/v4/File/Table(ROWID=621017)

Post Processing

With NRGship, often you would have a shipment created with one or more packages. Rather than sending a POST request to the shipment table, then obtaining the newly created ID, and then sending another POST to Packages, you could run a server-side script that finds new records and adds the necessary related data.

Testing OData

It’s also important to run some performance testing on your OData endpoint. Using a simple CURL, you can simulate 10 (or 100) simultaneous requests to your endpoint. Using the sample below from command line on macOS or Ubuntu, the response will be the number of seconds it took to perform the command. With our testing using the minimal header we were able to process creation of 100 records in six seconds. Previously, with summary fields present and no Prefer header it was taking over 30 seconds for a single POST.

time ( seq 10 | xargs -P 10 -I{} curl -i -X POST \
‘https://host.domain.com/otto/fmi/odata/v4/File/Table?apiKey=12345’ \
-H ‘Content-Type: application/json’ \
-H ‘Accept application/json’ \
-H ‘Prefer: return=minimal’ \
-d ‘{
“Carrier”: “UPS”,
“Service”: “UPS Ground”,
“Ref_InvoiceNumber”: “InvNum”,
“Ref_CustomerReference”: “CustRef”,
“Ref_PONumber”: “PONum”,
“UserField_OrderDate”: “2023-12-01”,
“UserField_Source”: “OData”,
“UserField_ID”: “12345”,
“Order_Notes.t”: “Some Note”,
“Order_Method.t”: “Free Shipping”,
“Special_DeliveryConfirmation”: “”,
“ShipTo_Name”: “NRG Software”,
“ShipTo_AttentionName”: “Tech Support”,
“ShipTo_AddressLine1”: “179 N Broadway”,
“ShipTo_AddressLine2”: “”,
“ShipTo_City”: “Milwaukee”,
“ShipTo_StateProvinceCode”: “WI”,
“ShipTo_PostalCode”: “53202”,
“ShipTo_Country”: “United States”,
“ShipTo_PhoneNumber”: “414-555-1212”,
“ShipTo_EmailAddress”: “user@domain.com”
}’
)

Further load testing can be performed using tools like k6. With this test file run from command line on macOS we were able to simulate over 6000 requests during a ten minute period with zero failures with a standard HTTP POST response. CPU usage on a AWS t2.large instance was at around 90%.

k6 run loadtest_sample.js