It is highly recommended that applications set the Session Mode to Teradata to achieve the most optimal performance from the provider. Refer to Connection String or ConnectionString Builder -- Session Mode for more information on setting the Session Mode. The reasons for this are explained in the following sections.
Open Access products have a requirement that all statements sent to Teradata must be Auto-Committed. Therefore, every statement that is sent to Teradata must be followed by a "COMMIT WORK". Depending upon the setting of the Session Mode, the provider will have to send the "COMMIT WORK" statement, or the Advanced SQL Engine will implicitly commit each statement that is processed.
Commands that are executed when the Session Mode is set to Teradata mode are automatically (implicitly) committed by the SQL Engine. The provider does not append a "COMMIT WORK" to the statement.
One advantage to this is that the provider does not have to append a "COMMIT WORK to each statement that is sent to Teradata. Another advantage is that the SQL Engine will use the Fast Path processing and will directly send the data from the spool to the provider when retrieving the data of a deferred LOB. In contrast, when the Session Mode is set to ANSI, an extra copy of the LOB is made.
For these reasons, specifying Teradata mode will provide the best performance.
To implement the Auto-Commit feature when the session mode is set to ANSI requires a "COMMIT WORK" to be sent after every statement that is executed. Some open access products implement this by first sending the statement to Teradata, and then sending a "COMMIT WORK".
However, the .NET Provider for Teradata optimizes the handling of Auto-Commit by appending a "COMMIT WORK" to the statement that will be submitted to Teradata when it is possible --some statements cannot be submitted to Teradata as a multi-statement request. This significantly reduces the number of round-trips made from the Data Provider to the SQL Engine.
Unfortunately, the SQL Engine does the following when a "COMMIT WORK" is appended to the statement used to retrieve the data of a deferred LOB (TdClob or TdBlob):
- Makes an extra copy of the LOB.
- Does not use the Fast Path processing.
Both of these items have a negative effect on the performance of the provider during the retrieval of the LOB's data.
Although there is a negative performance impact on LOB processing in ANSI mode, applications will be able to easily take advantage of the Fast Path processing when executing Unconstrained DELETE statements. An Unconstrained DELETE statement contains no constraints in the where clause --all the rows in the table will be deleted.
One of the following conditions must be met in order for Teradata to use the Fast Path processing on an Unconstrained Delete:
Because the provider will always append a "COMMIT WORK" to a DELETE statement, an Application can be assured that the Fast Path processing will be used without performing the additional step of appending "COMMIT WORK" to the end of the statement. The Fast Path processing cannot be taken advantage of by other open access products while in ANSI mode because the "COMMIT WORK" is sent after the Unconstrained DELETE has been processed.