Access Module Factors
In addition to database performance factors that might improve performance, consider
the following issues that are specific to the Teradata OLE DB Access Module:
If multiple OLE DB providers can access a particular data source, choose the fastest
Look for bottlenecks in the load operation. Possible locations might be the OLE DB
provider on the source side or on the destination (Teradata) side of the transfer.
When not in batch mode, observe the statistics in the dialog box that is displayed
during the load job execution. The Teradata OLE DB Access Module displays how many
rows it retrieves from the source OLE DB provider and how many rows it returns to
the Teradata load software:
If the “a” statistic is constantly a good deal higher that the “b” statistic, the
data source is supplying the data faster than the load software is requesting it,
and thus the speed of the load job is limited by the destination (Teradata Database).
If the “a” statistic stays close to the “b” statistic then the data source is not
able to provide the data as fast as it can be loaded and thus the speed of the load
job is limited by the data source side of the transfer.
It might also be possible to improve performance by using batch mode to disable the
dialog box that displays the status and progress status of the transfer. This frees
the processing resources used to display and updates dialog box.
If the data sources support SQL commands, use a SQL SELECT command that returns the
minimum amount of data to be loaded, that is, only the required columns and rows.
In load jobs that transfer fixed‑length text data [as in CHAR(n) data], if the text
data only contains characters that can be properly transferred using the ASCII session
character set, use the ASCII session character set instead of the UTF‑8 session character
set. When the UTF‑8 session character set is used, extra padding characters are added
to the end of these fields during the load transfer. These padding characters consume
some of the bandwidth available between the Teradata Database and the system running
the Teradata client utility. Another option for avoiding this padding is to CAST the
columns to a variable‑length test data type [such as VARCHAR(n)].
Run load jobs on a system with at least two CPUs to allow for the retrieval of the
data from the data source and the returning of the data to the Teradata Database.
For best performance, do these operations in parallel.
If a value is entered in the Checkpoint interval field, performance might be enhanced if values are also added to the Rows per fetch and Buffer size boxes, and the Enable scroll backwards option is selected.
If Rows per fetch is blank, the default is 10 jobs. Increasing the number too much might prevent rows
from being retrieved in parallel, which could decrease performance. Consider limiting
the number to one‑third or less of the number of rows than can fit into the internal
buffer (Buffer Size) after deducting from the buffer whatever space is consumed by holding rows between
checkpoints when the checkpoint/restart feature is enabled.
If Buffer size is blank, the default is 128 KB. No less than the default is accepted. It is recommended
that the number be three times the rows specified in Rows per fetch; however, if the
number is too large, performance might drop because the chances increase that data
in the buffer will get paged out to disk, especially on systems with low memory or
when running other load task.
If Enable scroll backwards is selected, restarts begin at the most recent checkpoint, but the internal buffer
(Buffer size) does not store data for restarts. Selecting this option can cause problems because
some data sources can consume all available memory, which can result in job failure.
Therefore, caution is recommended when in using this option. If Checkpoint interval
is selected while this option is blank, restarts can still succeed if Buffer size is large enough to store the rows loaded between checkpoints.