All Forums Tools
DarrenN 2 posts Joined 03/12
23 Mar 2012
Fastload Import very slow

Hi

I been testing the FastExport and Fastload Teradata tools and I am finding that data import using fastload is very slow for a particular table.

The process is as follows:

I have a table that I have exported from using a FastExport Script. I have then attempted to re-import that same data into another table using Fastload, but this import has taken 5 days to import around 85,000 records, at the rate of 500 rows every 40 minutes or so.

Here an example of the data contained in the original table:

Here is the fastload Log so far:

FastLoad - Enter your command:

SET RECORD FORMATTED;
0006 SET RECORD FORMATTED;

**** 15:02:30 Now set to read 'FORMATTED' records
**** 15:02:30 Command completed successfully

FastLoad - Enter your command:

DEFINE
        Item_Name(VARCHAR(100)),
        Item_SKU_Num(VARCHAR(50)),
        Shopping_Cart_Event_Cnt(INTEGER),
        Shopping_Cart_Event_Amt(DECIMAL(18,4)),
        Shopping_Cart_Event_Id(INTEGER),
        Shopping_Cart_Event_Type_Cd(VARCHAR(50)),
        Shopping_Cart_Event_Dttm(CHAR(19)),
        Web_Session_Id(BIGINT),
        Page_View_Sequence_Num(INTEGER)
FILE = G:\Files\fastexport\ExportData\Test\ shopping_cart_event_export.txt;
0007 DEFINE
        Item_Name(VARCHAR(100)),
        Item_SKU_Num(VARCHAR(50)),
        Shopping_Cart_Event_Cnt(INTEGER),
        Shopping_Cart_Event_Amt(DECIMAL(18,4)),
        Shopping_Cart_Event_Id(INTEGER),
        Shopping_Cart_Event_Type_Cd(VARCHAR(50)),
        Shopping_Cart_Event_Dttm(CHAR(19)),
        Web_Session_Id(BIGINT),
        Page_View_Sequence_Num(INTEGER)
     FILE = G:\Files\fastexport\ExportData\Test\ shopping_cart_event_export.txt;

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

FastLoad - Enter your command:

BEGIN LOADING DEL_SHOPPING_CART_EVENT
ERRORFILES TRANFLERR1, TRANFLERR2
CHECKPOINT 500;
0008 BEGIN LOADING DEL_SHOPPING_CART_EVENT
     ERRORFILES TRANFLERR1, TRANFLERR2
     CHECKPOINT 500;

**** 15:02:31 Number of AMPs available: 2
**** 15:02:31 BEGIN LOADING COMPLETE

FastLoad - Enter your command:

SHOW;
0009 SHOW;

     FILE = G:\Files\fastexport\ExportData\Test\ shopping_cart_event_export.txt
     ITEM_NAME                        OFFSET =      0 LEN =   100 VARCHAR
     ITEM_SKU_NUM                     OFFSET =    102 LEN =    50 VARCHAR
     SHOPPING_CART_EVENT_CNT          OFFSET =    154 LEN =     4 INTEGER
     SHOPPING_CART_EVENT_AMT          OFFSET =    158 LEN =     8 DECIMAL
     SHOPPING_CART_EVENT_ID           OFFSET =    166 LEN =     4 INTEGER
     SHOPPING_CART_EVENT_TYPE_CD      OFFSET =    170 LEN =    50 VARCHAR
     SHOPPING_CART_EVENT_DTTM         OFFSET =    222 LEN =    19 CHAR
     WEB_SESSION_ID                   OFFSET =    241 LEN =     8 BIGINT
     PAGE_VIEW_SEQUENCE_NUM           OFFSET =    249 LEN =     4 INTEGER
     TOTAL RECORD LENGTH = 253

FastLoad - Enter your command:

INSERT INTO DEL_SHOPPING_CART_EVENT
(
        :Item_Name,
        :Item_SKU_Num,
        :Shopping_Cart_Event_Cnt,
        :Shopping_Cart_Event_Amt,
        :Shopping_Cart_Event_Id,
        :Shopping_Cart_Event_Type_Cd,
        :Shopping_Cart_Event_Dttm,
        :Web_Session_Id,
        :Page_View_Sequence_Num);
     ====================================================== =============
     =                                                                 =
     =          Insert Phase                                           =
     =                                                                 =
     ====================================================== =============

0010 INSERT INTO DEL_SHOPPING_CART_EVENT
     (
        :Item_Name,
        :Item_SKU_Num,
        :Shopping_Cart_Event_Cnt,
        :Shopping_Cart_Event_Amt,
        :Shopping_Cart_Event_Id,
        :Shopping_Cart_Event_Type_Cd,
        :Shopping_Cart_Event_Dttm,
        :Web_Session_Id,
        :Page_View_Sequence_Num);

**** 15:02:31 Number of recs/msg: 253 (Monday)
**** 15:02:31 Starting to send to RDBMS with record 1
**** 15:02:31 Sending row 500
**** 15:36:41 Sending row 1000
**** 16:18:41 Sending row 1500
.
. (Intervening days)
.
**** 11:15:03 Sending row 85000
**** 11:55:05 Sending row 85500 (Friday)

 

Can someone please tell me what I am doing wrong?

DarrenN 2 posts Joined 03/12
23 Mar 2012

I forgot to mention that there are 7,189,995 rows in the original table...
 

feinholz 1234 posts Joined 05/08
08 May 2012

This is highly unsual, even with a checkpoint every 500 rows, and only 2 sessions (I mean, it would be slow with only 2 sessions but not that slow).

Have you tried removing the checkpoint?

With only 7 million rows, the load should not take that long.

Otherwise, open an incident with the GSC.

 

--SteveF

You must sign in to leave a comment.