When using the TdDataAdapter and TdCommandBuilder there are several ways that overall performance of pushing the changes made to the DataTable to the Advanced SQL Engine can be improved. The following sections discuss possible areas where performance can be affected. Some areas will affect performance more than others, and different combinations can be used to improve performance. It is up the the application developer to determine what settings will be optimal.

Modify the batch size -- TdDataAdapter.UpdateBatchSize Property

By default the UpdateBatchSize property is set to 1. To take advantage of the Multi-Statement and Iterated request capabilities of the provider, this property should be set to either 0 or a value greater than 1. Refer to Batch Update -- UpdateBatchSize section for more information on this property.

Setting UpdateBatchSize to 0 is NOT recommended when a DataTable contains a large number of rows, and many of those rows have been modified. The reason for this recommendation is that an SQL statement will be generated for each row that has been modified, and each statement is kept in memory. Depending upon what properties have been set in the TdCommandBuilder and TdDataAdapter, the text for these statements can require a large number of bytes.

As mentioned in the section Batch Update, the TdDataAdapter will consider sending SQL statements to Teradata using Iterated requests. Iterated requests performs better than Multi-Statement requests. In order to use Iterated requests, the following conditions must be meet:

• UpdateBatchSize must be set to greater than 5.
• KeepCommandBatchSequence must be set to false.
• The number of SQL statements that have been generated must be greater then 5.

Refer to the SetAllValues section for more information on how the TdDataAdapter uses Iterated requests.

Do not keep the batch sequence -- TdDataAdapter.KeepCommandBatchSequence Property

When possible set the KeepBatchSequence property to false. This will allow the TdDataAdapter to consider using Iterated requests when sending the SQL statements to Teradata. The performance of Iterated request is better than sending SQL statements as a Multi-Statement request. Refer to Batch Update -- KeepCommandBatchSequence for more information on this property.

UseEnhancedSchemaTable Connection String Attribute

The UseEnhancedSchemaTable attribute has been deprecated. This attribute will no longer have any effect on the SQL commands created by the TdCommandBuilder.

TdCommandBuilder.ConflictOption property

If ConflictOption is set to the default value of CompareAllSearchableValues then all the columns of the row will appear as parameters in the WHERE clause of an UPDATE or DELETE statement. When the SQL statements are generated, the TdCommandBuilder must consider the situation where data of columns that are nullable are null. To be able to handle both the condition where the data could be a null or not null value, the comparison of a nullable column will appear in the WHERE clause as:

      (? = 1 AND NullableColumn IS NULL) OR (NullableColumn = ?)

One extra parameter appears in the WHERE clause to handle the null condition for each column that is nullable.

To reduce the number of parameters that appear in the SQL statements that are generated by the TdCommandBuilder either set ConflictOption to OverwriteChanges, or keep the number of nullable columns contained in the SQL table to a minimum.

Another strategy that can be used to reduce the number of parameters is to specify the commands that will be used for the delete, insert, and update actions. By specifying the commands that will be used for each of the actions, an application can control the SQL statement that is executed and the parameters that are passed to Teradata. Refer to the Specifying the DELETE, INSERT, UPDATE Commands page for more information.

TdCommandBuilder.SetAllValues

The SetAllValues property affects how the TdCommandBuilder will generate the UPDATE statement. A description of the behavior of TdCommandBuilder for each value is described in the following:

• true A row in the DataTable has been modified. All the columns contained in the row will be included in the SET clause of the UPDATE statement.
• false Only those columns that have been updated will be included in the SET clause of the UPDATE statement.

The setting of this property can affect how the TdDataAdapter uses Iterated requests.

In order to use Iterated requests, the provider must perform the following tasks:

1. Group similar SQL statements together by sorting the list of statements to be processed. Only similar statements can be sent as an iterated request. That is, the statement must be the same type (UPDATE, INSERT, DELETE) and have the same parameters.
2. For each group, the data for the parameters are packaged in a format defined by the SQL Engine. The SQL statement that represents this group is included in the package.
3. The package is sent to the SQL Engine as an Iterated request.

By sorting the list of statements, DELETE, INSERT, and UPDATE statements will be grouped together. All the DELETE statements can be packaged and set as an Iterated request, as well as all the INSERT statements. However, UPDATE statements can differ by the columns that are to be updated.

If SetAllValues property is set to true, ALL the UPDATE statements contained in the list will be the same, and the provider will package these statements as an Iterated request. Therefore, if different columns of several rows are updated, setting SetAllValues to true would provide optimal performance.

See Also

DbCommandBuilder.ConflictOption
DbCommandBulder.SetAllValues
TdDataAdapter.UpdateBatchSize
TdDataAdapter.KeepCommandBatchSequence
UseEnhancedSchemaTable Attribute
Batch Update