insert takes more time than select in Teradata

Database
Enthusiast

insert takes more time than select in Teradata

select query takes only few seconds, whereas insert takes 4 to 5 hrs. index in both source and target are same. what might cause this delay?

stats collected on target tables. no where conditions/joins in select statement.

Please help me on this.
5 REPLIES
WAQ
Enthusiast

Re: insert takes more time than select in Teradata

need a bit more information.
> what are the types of indexes?
> stats are collected on which columns?
> how many records are there?
Enthusiast

Re: insert takes more time than select in Teradata

Stats collected on index column.

SOURCE_TABLE_1 has 4 billion rows which takes 2 hrs to insert and SOURCE_TABLE_1 has 45 million rows takes 11 hrs to insert.

The same index is defined on many other tables which is having similar set of data. It works fine with other tables.

CREATE SET TABLE table_temp ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
T_ID VARCHAR(22) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
DES CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
T_DT DATE FORMAT 'YYYY-MM-DD' ,
)
PRIMARY INDEX ( T_ID );

CREATE SET TABLE SOURCE_TABLE_1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
S_ID VARCHAR(22) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
S_DT DATE FORMAT 'YYYY-MM-DD' ,
)
PRIMARY INDEX ( S_ID );

CREATE SET TABLE SOURCE_TABLE_2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
R_ID VARCHAR(22) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
R_DT DATE FORMAT 'YYYY-MM-DD' ,
)
PRIMARY INDEX ( R_ID );

INSERT INTO TABLE_TEMP
SEL
S_ID
,'A' AS DES
,max(S_DT)
FROM SOURCE_TABLE_1
group by 1;

INSERT INTO TABLE_TEMP
SEL
R_ID
,'B' AS DES
,R_DT
FROM SOURCE_TABLE_2;
Enthusiast

Re: insert takes more time than select in Teradata

i changed my targettable as MULTISET, its executing fast.
WAQ
Enthusiast

Re: insert takes more time than select in Teradata

Yes because you have a set table and no unique index. If you want to make it SET then there should have a unique index, doing so divert the check on unique index from SET.

Re: insert takes more time than select in Teradata

Hello from 5 years later.  Thanks - this resolved an issue I was having as well.  Switched to multiset table and the data inserted rapidito!