Archives of the TeradataForum

Message Posted: Fri, 18 Apr 2003 @ 15:03:18 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Use ".set session respbuflen max64;"  and
    ".set session tworespbufs on; "

The data return format can affect throughput significantly. ".set recordmode on;" is the fastest. (assuming TTU7.0)

Here's how this works. BTEQ issues a request that builds a spool file on Teradata. Then Teradata returns the first buffer of rows-- ideally this is the max of 64K. If double buffering, CLI immediately initiates a continue request to overlap the return of the next buffer with application processing of the first.

The other typical bottlenecks are the I/O (e.g. if writing to a slow disk file) and the network connection to Teradata (e.g. 10 Mbit or a routed and congested path will limit the throughput).

I achieved over 12MB/second in the return phase of a BTEQ export. This was with 650 byte rows comprised of an integer and a varchar column. This ran standalone on a 5350 non-TPA node (2-way 2.8 GHZ) against a 4 node 5350. In the return phase the cpu utilization both on the Teradata nodes and the non-TPA was quite low. The data was written to /dev/null.

So large buffers maximize the rows/buffer. Double buffering overlaps processing with return. Recordmode minimizes the per column and per row processing.

I don't think that parallel CLI would help here--that becomes interesting when client cpu is a bottleneck as it does the TCP/IP communication under a separate process.

	<Prev	Next>		<<First	<Prev	Next>	Last>>