Performance Consideration in Teradata FastExport script

Tools & Utilities
Enthusiast

Performance Consideration in Teradata FastExport script

I have created a table of the following structure:

CREATE MULTISET TABLE abcd
(id INTEGER,
DateTime1 DATE FORMAT 'yyyy-mm-dd',
DateTime2 TIME(4),
DateTime3 TIMESTAMP(5),
DateTime4 TIMESTAMP(4) WITH TIME ZONE,
DateTime5 TIME(6) WITH TIME ZONE,
Offset1 INTERVAL YEAR(4) TO MONTH,
Offset2 INTERVAL YEAR(4),
Offset3 INTERVAL MONTH(4),
Offset4 INTERVAL DAY(2),
Offset5 INTERVAL DAY(2) TO HOUR,
enrolled_period PERIOD(DATE) FORMAT 'YYYY-MM-DD',
work_period PERIOD(TIMESTAMP(6)),
Numeric1 BYTEINT FORMAT 'Z9',
Numeric2 SMALLINT FORMAT '999',
Numeric3 INTEGER,
Numeric4 BIGINT,
Numeric5 DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99',
Numeric6 FLOAT,
Character1 CHAR(7),
Character2 VARCHAR(16384));

 

There are 20 million records inserted into the table and the perm space consumed by the table is around 3.08 GB.

Then I am exporting the dat using the FastExport script below:

==========================================================================================

.LOGTABLE dummy.ERR_LOG;

.LOGON serv/dummy1,dummy2;

DATABASE dummy;

.BEGIN EXPORT SESSIONS 4;

.EXPORT OUTFILE Dummy_Out_Path MODE RECORD FORMAT TEXT;

select
'"'||TRIM(CAST (ID AS VARCHAR(5)))||'"'||'|'||
'"'||TRIM(CAST (DateTime1 AS VARCHAR(10)))||'"'||'|'||
'"'||TRIM(CAST (DateTime2 AS VARCHAR(15)))||'"'||'|'||
'"'||TRIM(CAST (DateTime3 AS VARCHAR(30)))||'"'||'|'||
'"'||TRIM(CAST (DateTime4 AS VARCHAR(30)))||'"'||'|'||
'"'||TRIM(CAST (DateTime5 AS VARCHAR(30)))||'"'||'|'||
'"'||TRIM(CAST (Offset1 AS VARCHAR(10)))||'"'||'|'||
'"'||TRIM(CAST (Offset2 AS VARCHAR(10)))||'"'||'|'||
'"'||TRIM(CAST (Offset3 AS VARCHAR(10)))||'"'||'|'||
'"'||TRIM(CAST (Offset4 AS VARCHAR(15)))||'"'||'|'||
'"'||TRIM(CAST (Offset5 AS VARCHAR(15)))||'"'||'|'||
'"'||TRIM(CAST (enrolled_period AS VARCHAR(50)))||'"'||'|'||
'"'||TRIM(CAST (work_period AS VARCHAR(150)))||'"'||'|'||
'"'||TRIM(CAST ((Numeric1(FORMAT '-Z(9)')) AS VARCHAR(5)))||'"'||'|'||
'"'||TRIM(CAST ((Numeric2(FORMAT 'Z(10)')) AS VARCHAR(5)))||'"'||'|'||
'"'||TRIM(CAST (Numeric3 AS VARCHAR(5)))||'"'||'|'||
'"'||TRIM(CAST (Numeric4 AS VARCHAR(15)))||'"'||'|'||
'"'||TRIM(cast((Numeric5(FORMAT '-Z(34)9.99')) as varchar(20)))||'"'||'|'||
'"'||TRIM(cast((Numeric6(FORMAT '-Z(34)9.99')) as varchar(20)))||'"'||'|'||
'"'||TRIM(CAST (Character1 AS VARCHAR(20)))||'"'||'|'
'"'||TRIM(CAST (Character2 AS VARCHAR(20)))||'"'

from dummy.abcd
where id<10000000;

.END EXPORT;
.LOGOFF;

 

 

The system has 4 GB RAM  and 2 cores.The Teradata has 2 AMP's. Also we can't create an index on id column.There is some constraint for that.

There are 20 million records and we are exporting 10 million records.The FastExport process is taking around         30 minutes.I would like to know if there is anything we can do to improve the performance since it is taking a bit too long for 10 million records.Is there any workaround possible or any ways to treak the script to optimize the performance?

 

 

 

3 REPLIES

Re: Performance Consideration in Teradata FastExport script

In this case you can reach almost 50% performance improvement with the NOSPOOL mode, since the SQL does not contain, join, group or other "complex" operation. With NOSPOOL  you will get the locks released only after the export finished, but no need to copy all the data into a spool. I wrote an article on it:

Speed up Fastexport

 

Enthusiast

Re: Performance Consideration in Teradata FastExport script

Thanks @alevai- for the input.But the script is not able to recognize the NOSPOOL mode.The FastExport version we are using is FastExport 13.0.

Is the mode supported in this version?

 

 

 

 

Regards,

Indranil Roy

Re: Performance Consideration in Teradata FastExport script

According to the documentation this feature was introduced in V13.10

At least in V13.00 I did not find any reference for the feature, but in V13.10 I used it myself either.

 

br,

Ákos