All Forums Database
TrackingDaily 9 posts Joined 09/12
17 Dec 2013
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 ))
;

 

Kawish_Siddiqui 37 posts Joined 03/07
17 Dec 2013

What utility you are using?

Kawish Siddiqui -

TrackingDaily 9 posts Joined 09/12
17 Dec 2013

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.

dnoeth 4628 posts Joined 11/04
19 Dec 2013

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.

Dieter

Raja_KT 1246 posts Joined 07/09
19 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.