When designing a Process Model that needs to iterate over a list of records and write updates back to the database, Appian offers several looping and execution patterns to accomplish this.

According to Appian's official documentation, how do the following three performance dimensions differ across the available methods?

1. Execution time — how quickly does the full update complete?
2. Process instance memory usage — how much memory is consumed during execution?
3. Process execution engine load balancing — how well is the workload distributed across Appian's execution engines?

What is the correct ranking of available methods from best to worst, and what is the technical reason behind each ranking?

Ranked from best to worst on execution time, memory and engine load balancing:

1. Script task with a!forEach()

The whole update runs inside one expression evaluation. No process instances are created and there is no engine round trip per record, so memory use is small and the process engines are barely touched. a!forEach() works on the full list in memory, which makes it the right choice when the complete list of records is already available. This is the approach Appian recommends for in-memory data transformation.

2. Start Process smart service with MNI

Multiple Node Instances start one asynchronous process per record. The instances run in parallel, which shortens execution time, and because they are separate processes Appian distributes them across its execution engines. The cost is memory: every record now has its own process instance.

3. Subprocess node with MNI

Also parallel, but every subprocess instance runs on the same execution engine as the parent. Under heavy load that engine saturates and execution slows as volume grows. It scales worse than the Start Process approach because the work never spreads to other engines.

4. Synchronous subprocess in a loop

Records are processed one at a time and the parent waits for each subprocess to finish, holding its memory and process context for the whole run. No parallelism, high memory use, the longest execution time, and everything on a single engine.

Official documentation

1. Looping in Appian: https://docs.appian.com/suite/help/latest/looping.html
2. Subprocess Activity: https://docs.appian.com/suite/help/latest/Sub-Process_Activity.html
3. Start Process Smart Service: https://docs.appian.com/suite/help/latest/Start_Process_Smart_Service.html
4. Ways to Start a Process: https://docs.appian.com/suite/help/latest/Ways_to_Start_a_Process_From_a_Process.html