Reducing the Number of Updates to Data Tables

If your most frequently updated data sets have a significant number of items with OCCURS clauses that are not flattened, you may want to set the DSOPT_Use_bi_ai bit in the ds_options column of the corresponding DATASETS entries. The configuration file parameter optimize_updates causes the define command to set this bit for all data sets that have active items with unflattened OCCURS clauses.

Note: You can set this parameter from the Client Console by selecting Enable optimized SQL updates in the Processing parameters of the Client Configuration dialog box.

If the ratio of SQL rows to DMSII records is five or more during update processing, setting this parameter to True will likely improve performance. Note that this increases the TCP/IP and CPU overhead. If the ratio of SQL rows to DMSII records is low, you won't see any significant reduction in SQL overhead, which can hinder performance.

For best results, set the DSOPT_Use_bi_ai bit only for data sets that have a high ratio of SQL rows to DMSII records. For example, a data set that has only one item with an OCCURS 2 TIMES clause is a poor candidate for the DSOPT_Use_bi_ai bit (SQL row/DMSII records = 3). Conversely, a data set that has 3 items with OCCURS 12 TIMES clauses is a good candidate to use the DSOPT_Use_bi_ai bit (SQL row/DMSII records = 37).

For details, see optimize_updates.