How to speed up Teradata Fastexport

About Teradata Fastexport

Teradata Fastexport is a nice, powerful tool to export mass volume of data to file. Its default execution flow is the following:

1.   Apply locks on the affected tables

2.   Execute the SELECT statement

3.   Places the result in a SPOOL

4.   Releases locks

5.   Exports data from the SPOOL

It has an advantage: the release of locks will happen immediately after the result data is copied into the SPOOL.

Therefore need not to wait until the data is written to the export file outside the database – it is typically a single process operation, can last for a long time.

The price to be payed for this advantage: all the exported data must be copied first to the SPOOL. When the SELECT is a simple operation (no join, sort or other extras), the copy phase can be saved, which can approach 50% of the total resource and time consumption.

The NOSPOOL trick

For these simple cases one can use the NOSPOOL/NOSPOOLONLY mode.

While NOSPOOL is a soft switch, which is ignored if not applicable, NOSPOOLONLY will make statement failed if the nospool option is not available for the given query.

In NOSPOOL mode the database will not make a copy of result data. Instead it directly passes the data to the client, which has another benefit: the execution of the SELECT runs dominantly parallel with the file write operation, instead of successively like in SPOOL (default) case.

Usage:

.BEGIN EXPORT

SESSIONS …

[SPOOLMODE SPOOL|NOSPOOL|NOSPOOLONLY];

The tradeoff

The NOSPOOL operation only supports the “simple” SELECT statements. The not supported operations:

·        SORT (ORDER BY, HAVING)

·        JOIN

·        Aggregations (GROUP BY, SUM())

·        OLAP functions

·        Non-table data (date, session, user)

·        Multiple statements

But allows:

·        Sampling

·        Column selection

·        Scalar functions (like col1 + col2, substr())

·        “Where” clause (incl. partition elimination)

The disadvatages of NOSPOOL mode:

·        No ActivityCount is available

·        Locks maintained till the end of the export session

·        Data conversion errors can happen during the exporting phase

·        (Only available from V13.10)

What is it good for indeed?

It can almost double the throughput in case of:

·        DataMart export (to another database)

·        Archive dumps

·        Copy data to test environment

·        Migration to another database systems

Measures

We’ve executed a Teradata to Netezza migration, where got to transport ~12TBytes of data through file interface. After we activated the NOSPOOL option, the average export speed grew up by 84%.

Regards,

Pankaj Chahar

Pankajchahar052@gmail.com

+91-8802350184