Optimizations

Execution time can be an important factor when running large data processing statements. OmniFi supports a set of optimization options that can improve the overall execution time. Optimization is optional to use as it introduces certain semantic differences from un-optimized execution.

Optimization options are specified in the service header Optimize field.

Supported options are:

• OFF
• QUERY
• AUTOAPPLY
• FILTER
• ALL

To specify more than one option, use comma separated syntax, e.g. AUTOAPPLY, FILTER.
Optimizations are only in effect when the main key word is *Query or *QueryOrCreate.

Query optimization is required for other optimizations to function. When using AUTOAPPLY or FILTER optimization, QUERY is implied and doesn’t have to be explicitly specified.

• AUTOAPPLY = “AUTOAPPLY + QUERY”
• FILTER = “FILTER + QUERY”
• ALL = “AUTOAPPLY + FILTER + QUERY”

Query optimization

Query optimization instructs data processing to retain any business objects from a *Query or *QueryOrCreate statement across data rows, for as long as the query criteria remains unchanged.

Consider the following case:

The same transaction is queried repeatedly on each data row. With query optimization, the business object for transaction #10607 is saved, and re-used with each data row, saving a round trip to ComKIT for each row.

• Query optimization processes rows in the order they are found in the import sheet. For this reason it is important that the import sheet is sorted according to the query criteria columns to make use of the full potential of optimization.

• Data processing by default works with a batch size of 500 rows before the import is reset and continued on newly created ComKIT connection. With query optimization, this threshold can occur inconveniently within an optimized block, with significant performance impact. When using optimization it is recommended to set BatchSize to -1 to turn off batch recovery:

Auto-Apply optimization

With AUTOAPPLY optimization, data processing applies the business objects from a *Query or *QueryOrCreate –statement when they are cleared from the cache, i.e. when the query criteria changes or after the last row in the set.

The cached business objects are applied after the two highlighted rows have been processed; for #10607 because the criteria has changed to 10608, and for #10608 because it is the last row in the set.

The manual *Apply/ command is still respected. To make full use of the AUTOAPPLY optimization, any calls to Apply need to be removed from the header.

Consider the following example:

The Apply command has been commented out to allow full effect of the AUTOAPPLY optimization.

• AUTOAPPLY is very strongly linked to the QUERY optimization. The QUERY optimization creates the cache of business objects that AUTOAPPLY relies on. Therefore specifying AUTOAPPLY optimization implies QUERY optimization, which doesn’t have to be specified explicitly.

• The use of AUTOAPPLY defers most ComKIT errors to the last row where the entire set is committed. It can therefore be difficult to distinguish exactly which row causes the actual error. In such a case the import should be re-executed with optimization turned off to identify the faulty row.

• AUTOAPPLY has relatively small impact on over-all performance. It is however in some cases required in order to make use of the QUERY and FILTER optimizations which have much larger benefits.
  In the example below, the Cashflow business objects are actually re-generated when the transaction is applied. This means that the business object cache created by QUERY is stale, and any subsequent changes to the cash flow level are lost. The example below would yield unexpected results; only the first cash flow (on 2017-05-01) is changed.

Filter optimization

Business objects are allocated in the ComKIT service and for each value read from an object a call is made across the network.

The FILTER optimization instructs Data Processing to cache any field values fetched from the business object, so that each required field is only fetched once, minimizing the over-head from network calls. This is particularly useful when used with the *Filter keyword, which in nature checks many values on all available business objects.

Consider a transaction #10607 which has N cash flows and we want to update each flow individually.

Un-optimized, the *Filter command in the import sheet below checks all N cash flows of the transaction against each data row. When updating all cash flows on the transaction this yields a workload of NN comparisons. The example uses three filter criteria, so the number of calls to check, making 3N*N calls across the network to fetch field values.

For a small N like N5 this gives 355=75 calls. For a larger N, like N=100 this gives 3100*100 = 30 000 calls.

With FILTER optimization, each value is only fetched at most once when needed. For a small N=5 this gives 35=15 calls, or for a larger N=100, 3100=300 calls.

• FILTER is very strongly linked to the QUERY optimization. The QUERY optimization creates the cache of business objects that FILTER relies on. Therefore specifying FILTER optimization implies QUERY optimization, which doesn’t have to be specified explicitly.

• When a field is set on a business object, the cache for that field on the affected business object is cleared.

• In some cases fields on a business object are re-calculated based on the value of another field. In this case, any cache for a dependent field is not cleared. Consider the following sequence:

Limitations

FILTER Optimization when updating Cashflows

When applying changes to a Transaction, the Cashflow business objects are re-generated in the background of ComKIT, and any cached Cashflow objects are dead.

For this reason it is not possible to use only FILTER optimization with manual applies when modifying Cashflows; instead ALL optimization must be used.

Creation of sub-entities cannot be optimized

Query optimization causes sub-entities to be cached the first time that they are fetched. If a command creates a new sub-entity (e.g. adding a charge cash flow to a transaction) the new sub-entity is not known.

Static-Data optimization

Updating Static-Data sub-entities requires apply on both the sub-entity and main entity levels. AUTOAPPLY optimization will only automatically apply the main entity level at the end of each import block. An explicit Apply on the sub-entity level is required, as shown in the below example.

QueryOrCreate, GetValues and AUTOAPPLY optimization

When creating a transaction and updating cashflows within a *QueryOrCreate block, and AUTOAPPLY optimization is used, commit of the transaction is deferred to after the last cashflow has been updated. This means that when the *GetValues operation is executed the transaction has not been committed yet, and has a Transaction Number of ‘NULL’, as shown by the blow example.