 |
 |
Archives of the TeradataForum
Message Posted: Mon, 24 Feb 2003 @ 16:41:06 GMT
| 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
| |
