Better Approach for 1M+ Record Updates: Appian Write Records vs Stored Procedure

We have an external event that can impact up to 1M+ Case records. For each affected Case, we need to:

• Update 3 fields in the Case table.
• Create a corresponding Event History record.

We're evaluating the following approaches:

Option 1 – Appian Write Records

Approach A

• Query Cases in batches of 5000
• Use MNI to process each Case in a single Write Records node to update the Case and create the corresponding Event History record
• Loop until all affected Cases are processed

Approach B

• Query Cases in batches of 5000
• Update all affected Cases for the batch in a Script Task
• Construct the corresponding 5,000 Event History records
• Use one Write Records node to bulk update the 5000 Case records
• Use a second Write Records node to bulk insert the 5000 Event History records
• Loop until all affected Cases are processed

Option 2 – Stored Procedure

• Use a stored procedure to bulk update Case records and bulk insert Event History records
• Persist affected Case IDs and Event History IDs in staging tables
• Query the staged IDs and performs Sync Records in batches of 1000 (due to Sync Records Smart Service limits until all ids are processed)
• Loop until all IDs are synced

Question

For workloads that can exceed 1 million records, which approach would you recommend and why?

While Option 1 benefits from automatic record synchronization, it may place significant load on Appian process execution and engines at this scale. Option 2 shifts the bulk processing to the database but requires additional ID tracking and explicit record synchronization.

Has anyone implemented a similar solution at this volume? Would you recommend Option 1A, Option 1B, Option 2, or another approach altogether for the best balance of performance, scalability, and Appian platform load?