FWD » Core Development » Database

From Onno:

Hi guys,
In OpenEdge it's possible to specify temp-table parameters as "by-reference".
See: https://docs.progress.com/bundle/openedge-develop-abl-applications/page/Pass-a-temp-table-by-reference.html

Using this option avoids copying of temp-tables when making calls to an internal procedure.

How does FWD handle temp-table parameters? In the same way as OpenEdge? Does it make sense to add "by-reference" in the ABL, to speed up FWD?

If so: is it a big difference? After how many records (ballpark estimate) is it worthwhile? I can imagine that a temp-table of 50-100 small records won't be impacted much.

Hope you guys can shed some light on this. I browsed the documentation/wiki-page but couldn't find much; so perhaps you guys can add the answer to the wiki as wel. Thanks in advance!

In OpenEdge it's possible to specify temp-table parameters as "by-reference".
See: https://docs.progress.com/bundle/openedge-develop-abl-applications/page/Pass-a-temp-table-by-reference.html
Using this option avoids copying of temp-tables when making calls to an internal procedure.

Evidently, the internal data representation is different in FWD from 4GL. We aim to fully mimic both the 4GL syntax and behaviour (semantic), but the internals are different. From the metadata (table definition structures stored in FWD schema dictionary) to actual data (stored in H2 in-memory database).

How does FWD handle temp-table parameters? In the same way as OpenEdge?

This is a bit complex. Too keep it simple, when the conversion encounters one of these cases, it uses TableParameter objects (and derived classes) to wrap the reference to actual parameter. Depending on the options specified by the ABL programmer (see ParameterOption enum which contains BY_REFERENCE item), some operations are performed at the moment the method is invoked (make a copy of the data or make slight adjustments of DMO interface) but also on method return, when the result needs to be copied back to expected location.

Does it make sense to add "by-reference" in the ABL, to speed up FWD?

The quick answer is yes. FWD recognise the flag and will avoid unnecessary data movements. However, since the implementation pursued the correct (functional) execution, we did not perform specific profiling. Since ABL allows the schema of the 'source' and 'destination' to be slightly different, FWD needed some kind of mapping to make it work. This is a bit costly and this might make the benefit of adding the by-reference option hard to notice.

If so: is it a big difference? After how many records (ballpark estimate) is it worthwhile? I can imagine that a temp-table of 50-100 small records won't be impacted much.

I cannot give you a straight answer. The low-level accesses to H2 database for moving the data back and forth is optimised (SQL), where possible. But in case of by-reference this should not be the case. I will create a couple of testcases and time the execution for a few dimensions of a temp-table parameter. The collected information will be used for:

• having a better response to this question. I'll look for a wiki page best-suited to store it.
• creating a new task for improving the performance when this option is used, if the case.

Hope you guys can shed some light on this. I browsed the documentation/wiki-page but couldn't find much; so perhaps you guys can add the answer to the wiki as wel. Thanks in advance!

I will update the wiki as soon as I have more data. Indeed, https://proj.goldencode.com/projects/p2j/wiki/Parameter_Passing_(Database)_Tests seems the only wiki page which refers this option. It only mention functionality, not performance. There are also some tasks, but they are holding technical details.