two similar tables - very different import speed

Database
Enthusiast

two similar tables - very different import speed

I have two very similar tables, see below.  On a regular basis, I import about 300,000 records.  The original table required about 90 min to 2 hours to complete the import.  The newer table can do it in under 30 minutes.  What makes the newer table so much faster?  Is it having so many fewer NOT NULL fields in the new table?  The new table has one additional column, so it actually imports about 5% more data.  I can't really figure out any other possible differences.  Thank you.

/* original table -- very slow import */

CREATE SET TABLE mydb.Table_One ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
a DATE FORMAT 'YY/MM/DD' NOT NULL,
b INTEGER NOT NULL,
c VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
d DATE FORMAT 'YY/MM/DD' NOT NULL,
e FLOAT NOT NULL,
f FLOAT NOT NULL,
g FLOAT NOT NULL,
h VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
i VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
j VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
k VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
l VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
m VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
n VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
o VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
p INTEGER NOT NULL,
q INTEGER NOT NULL,
r INTEGER NOT NULL,
s SMALLINT DEFAULT 0 ,
PRIMARY KEY ( a , b ))
;

/* newer table, much faster -- but why? */

CREATE SET TABLE mydb.Table_Two ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
a DATE FORMAT 'YY/MM/DD' NOT NULL,
b INTEGER NOT NULL,
c VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
d DATE FORMAT 'YY/MM/DD' NOT NULL,
e FLOAT,
f FLOAT,
g FLOAT,
h VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
i VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
j VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
k VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
l VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
m VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
n VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
o VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
p VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
q INTEGER NOT NULL,
r INTEGER NOT NULL,
s INTEGER NOT NULL,
t SMALLINT DEFAULT 0 ,
PRIMARY KEY ( a , b ))
;
4 REPLIES
Enthusiast

Re: two similar tables - very different import speed

What utility you are using?

Enthusiast

Re: two similar tables - very different import speed

It is not a Teradata utility -- my database IDE has an "import table data" function that reads CSV files (my source is a text file -- should have said that in the original post).  But the process is the same for both tables, just the speed is 3x faster with the new table.  Optimizing is always good, I just can't figure out what the optimization is here.

Senior Apprentice

Re: two similar tables - very different import speed

Each record is less than 500 bytes and adding 10 bytes shouldn't cause any difference, strange. Without more info it's hard to say what caused this. Is this repeatable?

300.000 rows in 30 minutes is still quite slow, even when it's a single session with single row inserts. You should definitely think about switching to BTEQ (using a high PACK factor) or a TD load utility, they can easily handle CSV.

Enthusiast

Re: two similar tables - very different import speed

Even if you say "regular basis" before, maybe you can check the workload history then and compare with the present one. It is just a thought.

Cheers,

Raja