Using PeopleCode in Loops

You can insert PeopleCode inside of a Do loop, but be careful when using PeopleCode inside of high-volume Do loops (While, Select, Until). Minimize the number of distinct programs inside the loop. You should avoid having PeopleCode perform the actual work of the program and instead use it primarily to control the flow (If, Then logic), build dynamic SQL, or interact with external systems.

Using bind variables instead of literals to pass values to SQL statements is essential in PeopleCode loops or if the PeopleCode is called in a loop. If the PeopleCode loops, Application Engine probably will use a dedicated cursor, which saves the overhead of recompiling the SQL for all iterations. If the PeopleCode is called from within a loop, Application Engine does not reduce the number of compiles, but it avoids flooding the SQL cache (for those database servers that support SQL cache) when it uses bind variables. Do not use bind variables for values in a Select list or for SQL identifiers, such as table and column names, as some databases do not support them.

Note:

Null bind values of type DateTime, Date, or Time are always resolved into literals.

On database platforms for which this feature is implemented, setting BulkMode to True often results in significant performance gains when inserting rows into a table within a loop.

In general, avoid PeopleCode calls within a loop. If you can call the PeopleCode outside of the loop, use that approach to increase overall performance.