Help!!! Create table performance issue

Database

Help!!! Create table performance issue

Hi, 

I create a table by following sql syntax: 

  • CREATE TABLE FA_Test2 AS (SELECT * FROM FA_Test1 WHERE CYCLE_ID = '201611') WITH DATA; 

The FA_Test1 has 50 millions rows, and the expected row number of FA_Test2 is 4 millions. 

If I put the above sql syntax in TD Studion "SQL editor" to create table, the consuming time is about 2 minutes. 

But if I put sql syntax in FastLoad script and execute by "fastload < script_name" command, the consuming time is exceed 2 hours, my script is: 

  • .LOGON 10.144.**bleep**.xxxx:1025/dbc,dbc;
    DATABASE TEST;CREATE TABLE FA_Test2 AS (SELECT * FROM FA_Test1 WHERE CYCLE_ID = '201611') WITH DATA;
  • LOGOFF;

The TD Studio Client and Fastload command client are in the same machine. Does anybody know what  I miss? 

 

Sincerely Yours

4 REPLIES 4
Senior Apprentice

Re: Help!!! Create table performance issue

Hi,

 

To be honest, I'm surprised that your Fastload even ran.

 

Fastload is intended to load data from a client system to Teradata.

The 'create table as' command does not load data from a client system. It simply transfers data from one/more tables to a target table.

 

I don't know why Fastload is so much slower, but why would you even consider Fastload for this operation?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Help!!! Create table performance issue

Chung,

     please consider to put an explicit primary index on your create table statement otherwise it defaults to the first column (and your table could be way skewed).

 

CREATE TABLE FA_Test2 AS (SELECT * FROM FA_Test1 WHERE CYCLE_ID = '201611') WITH DATA PRIMARY IDEX (yourchoice);

 

Have a nice day.

Highlighted
Enthusiast

Re: Help!!! Create table performance issue

Hi , 

 

Below syntax is being used for creating a mulitiset table with billion rows 

 

Create multiset table a (select * from b ) with data and statistics primary index (a1);

 

If we do not use statistics  does that make any difference ?

 

 

Senior Apprentice

Re: Help!!! Create table performance issue

The 'with statistics' will not make any difference to the performance of the 'create table as' command.

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com