Tune the Number of Columns in Update Statements

Tune the number of columns for which full UPDATE statements are issued.

Background

The Number of Columns in Updates property specifies what the UPDATE statements generated by the Rules Engine will look like. For tables with less columns than the Number of Columns in Updates value the Rules Engine will generate UPDATE statements that update all columns of the table. In a full UPDATE statement, even if only one column value has been updated, all columns are mentioned in the SET clause of the RDBMS SQL statement. This has the advantage that RDBMS cursors created for these statements can be reused, which will generally increase performance. If the number of columns of a table is equal to or higher than this value, UPDATE statements on this table will only update the columns actually updated. The default value is 10.

When to use

Raise the value of Number of Columns in Updates if your application includes tables that have more than 10 columns and in which frequent UPDATE operations occur on a variety of columns. The new value of Number of Columns in Updates should equal the number of columns in the "largest" frequently updated table (the table with the greatest column count).

For tables with a large number of columns that are NOT frequently updated, especially if these include columns with long character fields or LONG or CLOB fields, try to keep the value of Number of Columns in Updates below the number of columns of these tables. Otherwise, performance may suffer because of the size of the SQL statements sent over the network.

To change the number of columns in UPDATE statements:

1.	Choose Edit, Select, Application from the Designer menu bar.

The Property Inspector for the application is opened.

2.	On the Behavior tab page, change the Number of Columns in Updates property to a preferred value.

3.	Choose File, Save All from the menu bar.