Access Module Factors - Access Module

Teradata Tools and Utilities Access Modules User Guide
Product
Access Module
Release Number
15.10
Language
English (United States)
Last Update
2018-06-06
Product Category
Teradata Tools and Utilities

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 provider.
  • 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.