When do you need a Dynamically allocated Array in RPGLE?

[ckannapp]

I have a stored proc returning a result set to webservice program. The result set array datastructure maximum DIM is 15000. The Array subfields make up 1004 character in each element. Array size is around 15 MB. But we do not always send 15000 rows, it is usually around 200 rows. But in certain cases, it could get multiplied by 50. 50 X 200 makes 10000 rows returned in those cases
will making this a Dynamically allaocted array improve the performance? We are looking to improve the performace
We are on V7R2
We have conflicting opinions in the team.
We have identified some other SQL changes to improve the performance, but wanted to get expert opinion if this will make any significant impact.

[tomliotta]

It's possible that performance could be improved over your existing program; but it's not clear what a dynamically allocated array would improve nor why. And if it does improve performance, it's not clear if it'd be significant. Without seeing relevant code and knowing more about run-time environment and creation options, it can be difficult to make meaningful guesses.

[CRinger400]

Can you write rows to a temporary file, close it, open a cursor on it and return the cursor?

[Barbara Morris]

Depending on the nature of the subfields, initializing a large data structure array (done during program activation or on a subsequent call after setting on LR) can take a significant time. If you remove any INZ keywords from the data structure (the subfields and the DS itself), the whole data structure will get initialized to blanks, which will usually be faster, could be much faster. You'd have to remember to set the initialization values by using CLEAR each time you start using an element, and explicitly set any subfield values that you previously set using INZ. And if you were using RESET to refresh values when reusing them, you'd have to switch to using CLEAR+set-subfields instead.

[mlopez01]

Can you CREATE GLOBAL TEMPORARY TABLE xxx for each table in the result set, then SET RESULTS CURSOR C1, C2, etc. to return the results?