Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 24 Feb 2003 @ 16:41:06 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Can it be any faster
 
From:   uknair

Thank you all for the replies.

Copied at the bottom is a stdout of the fasload. You could see the fastload script in there. We pipe the data through abinitio and it creates the load scripts at run time, from the ddls.

1. The table is a SET table and with a NUPI on acc_id. We tried with UPI (acc_id and itm_cd) as well. The performance is pretty much the same. The NUPI on acc_id greatly beneficial on query performance(joins).

2. There could be as much as 40 duplicates per PI value. But the data is read in a random fashion, so the hash collision is not a major issue.

3. The input data is in the same format as that of the table.

4.Total amp count is currently 416 ("13 X 32" where 13 is the number of amps per node X 32 is the current number of active nodes running Teradata in the configuration) on the NCR 5250 platform.

5.The loads happen from a Network attached UNIX system. The UNIX server is co-located with the Teradata machine and is directly coupled with no intervening routers.

6. As far as load on the system goes, we do have enough resources to run. We have tested it at different days and timings during the week and the performance stays the same.

7. Note the counts on the fasload log. The counter overflowed, hence the timings and counts you see at the end of the log are not accurate. As per some Teradata on site folks, this is a known issue.

8. How much of difference the "checkpoint" make? I have it as 5,000,000.

9. I also heard from some folks here that, this is the normal perfomance to expect. But my gut says, it could be better. I have seen an informix sps loading 800 mill in 30 min.

Unfortunately, this is all I could provide at this time.

******

.DATEFORM ansidate;
SESSIONS 24;
SLEEP 6;
.LOGON XXXX/XXXXX,;
.SET RECORD UNFORMATTED;
.BEGIN LOADING ACC_ITM_0211.ACC_itm_0211 ERRORFILES
WORK.et_ACC_itm_02111,
WORK.et_ACC_itm_02112 CHECKPOINT  5000000 INDICATORS;
DEFINE
 acct_id (DECIMAL(18)),
 itm_cd (CHAR(10)),
 itm_am (DECIMAL(15,2)),
 itm_pr (DECIMAL(8,4)),
 process_dt (SMALLINT),
 extr_date (CHAR(10))
 FILE=/tmp/abtmp_abprod/ABload-377474.pipe;
INSERT INTO ACC_ITM_0211.ACC_itm_0211
(acct_ID,ITM_CD,ITM_AM,ITM_PR,process_DT,extr_date) VALUES
(:acct_id,:itm_cd,:itm_am,:itm_pr,:process_dt,:extr_date);
.END LOADING;
.LOGOFF;
--------------------

     -------------------------------------------------------------------
     =                                                                 =
     =          FASTLOAD UTILITY     VERSION 07.04.00                  =
     =                                                                 =
     -------------------------------------------------------------------

**** 15:00:43 Processing starting at: Wed Feb 19 15:00:43 2003

0001 .DATEFORM ansidate;

**** 15:00:43 Date/Time Format set to ANSIDATE
**** 15:00:43 Command completed successfully

0002 SESSIONS 24;

**** 15:00:43 FDL4866 SESSIONS command accepted

0003 SLEEP 6;

**** 15:00:43 Sleep Minutes Set: 6 minute(s)

     -------------------------------------------------------------------
     =                                                                 =
     =          Logon/Connection                                       =
     =                                                                 =
     -------------------------------------------------------------------

0004 .LOGON prod/clprload,

**** 15:00:44 Current CLI or RDBMS allows maximum row size: 64K
**** 15:00:44 Character set for this job: ASCII
**** 15:00:59 Number of FastLoad sessions requested = 24
**** 15:00:59 Number of FastLoad sessions connected = 24
**** 15:00:59 FDL4808 LOGON successful

0005 .SET RECORD UNFORMATTED;

**** 15:00:59 Now set to read 'UNFORMATTED' records
**** 15:00:59 Command completed successfully

0006 .BEGIN LOADING ACC_ITM_0211.ACC_itm_0211 ERRORFILES WORK.e
     t_ACC_itm_02111, WORK.et_ACC_itm_02112 CHECKPOINT  500000
     0 INDICATORS;

**** 15:00:59 Indicator mode is set to ON
**** 15:02:00 Number of AMPs available: 624
**** 15:02:00 BEGIN LOADING COMPLETE

0007 DEFINE
      acct_id (DECIMAL(18)),
      itm_cd (CHAR(10)),
      itm_am (DECIMAL(15,2)),
      itm_pr (DECIMAL(8,4)),
      process_dt (SMALLINT),
      extr_date (CHAR(10))
      FILE=/tmp/abtmp_abprod/ABload-377474.pipe;

**** 15:02:00 FDL4803 DEFINE statement processed

     -------------------------------------------------------------------
     =                                                                 =
     =          Insert Phase                                           =
     =                                                                 =
     -------------------------------------------------------------------

0008 INSERT INTO ACC_ITM_0211.ACC_itm_0211 (acct_ID,ITM_CD
     ,ITM_AM,ITM_PR,process_DT,extr_date) VALUES (:acct_id,:prf
     t_itm_cd,:itm_am,:itm_pr,:process_dt,:extr_date);

**** 15:02:08 Number of recs/msg: 1367
**** 15:02:08 Starting to send to RDBMS with record 1
**** 15:03:22 Sending row 5000000
**** 15:04:41 Sending row 10000000
**** 15:06:00 Sending row 15000000
**** 15:07:26 Sending row 20000000
**** 15:08:43 Sending row 25000000
**** 15:09:58 Sending row 30000000
**** 15:11:13 Sending row 35000000
**** 15:12:30 Sending row 40000000
**** 15:13:46 Sending row 45000000
**** 15:15:01 Sending row 50000000
**** 15:16:18 Sending row 55000000
**** 15:17:37 Sending row 60000000

...(deleted to save space!!!!)
...
**** 08:49:26 Sending row 4285000000
**** 08:50:40 Sending row 4290000000
**** 08:51:56 Sending row 32704
**** 08:53:10 Sending row 5032704
**** 08:54:23 Sending row 10032704
**** 08:55:38 Sending row 15032704
**** 08:56:52 Sending row 20032704
**** 08:58:08 Sending row 25032704
**** 08:59:23 Sending row 30032704
**** 09:00:38 Sending row 35032704
**** 09:01:52 Sending row 40032704
**** 09:03:06 Sending row 45032704
**** 09:04:20 Sending row 50032704
**** 09:05:37 Sending row 55032704
**** 09:06:52 Sending row 60032704
**** 09:08:06 Sending row 65032704
**** 09:09:21 Sending row 70032704
**** 09:10:36 Sending row 75032704
**** 09:11:50 Sending row 80032704
**** 09:13:03 Sending row 85032704
**** 09:14:20 Sending row 90032704
**** 09:15:35 Sending row 95032704
**** 09:16:49 Sending row 100032704
**** 09:18:05 Sending row 105032704
**** 09:19:19 Sending row 110032704
**** 09:20:39 Sending row 115032704
**** 09:21:59 Sending row 120032704
**** 09:23:15 Sending row 125032704
**** 09:24:32 Sending row 130032704
**** 09:25:52 Sending row 135032704
**** 09:27:11 Sending row 140032704
**** 09:28:26 Sending row 145032704
**** 09:29:41 Sending row 150032704
**** 09:30:56 Sending row 155032704
**** 09:31:13 Sending row 155803074
**** 09:31:13 Finished sending rows to the RDBMS

     -------------------------------------------------------------------
     =                                                                 =
     =          End Loading Phase                                      =
     =                                                                 =
     -------------------------------------------------------------------

0009 .END LOADING;

**** 09:40:26 END LOADING COMPLETE

     Total Records Read              =  155803074
     Total Error Table 1             =  0  ---- Table has been dropped
     Total Error Table 2             =  0  ---- Table has been dropped
     Total Inserts Applied           =  4294967295
     Total Duplicate Rows            =  0

     Start:   Thu Feb 20 09:31:13 2003
     End  :   Thu Feb 20 09:40:26 2003

0010 .LOGOFF;

     -------------------------------------------------------------------
     =                                                                 =
     =          Logoff/Disconnect                                      =
     =                                                                 =
     -------------------------------------------------------------------

**** 09:40:37 Logging off all sessions
**** 09:40:40 Total processor time used = '595.82 Seconds'
Start : Wed Feb 19 15:00:43 2003
     .        End   : Thu Feb 20 09:40:40 2003
     .        Highest return code encountered = '0'.
**** 09:40:40 FDL4818 FastLoad Terminated


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016