Why TPT mload is taking long time than bteq for huge volume of data

Tools
N/A

Why TPT mload is taking long time than bteq for huge volume of data

Hi ,

I am deleting 80 millions of records from a table , for this tpt is taking 12 hours to complete where as bteq deleting data in just 2 hours. Actually tpt should be more faster than the bteq.  Please suggest me how can i avoid this long run thrugh tpt.

16 REPLIES
Teradata Employee

Re: Why TPT mload is taking long time than bteq for huge volume of data

Are you using the Delete Task to delete the records?

Or the generic DELETE FROM . . . ?

-- SteveF
N/A

Re: Why TPT mload is taking long time than bteq for huge volume of data

Hi Steve, I am physically deleting the data from the table.

Teradata Employee

Re: Why TPT mload is taking long time than bteq for huge volume of data

For that many rows, maybe try the DELETE Task.

-- SteveF
N/A

Re: Why TPT mload is taking long time than bteq for huge volume of data

Hi Steve, pelase let me know what exactly is delete task.My TPT script includes apply which deletes data, exported by export operator. Is this delete is different to your delte task

Teradata Employee

Re: Why TPT mload is taking long time than bteq for huge volume of data

Please send me the script you are using currently to delete those 80 million rows.

-- SteveF
N/A

Re: Why TPT mload is taking long time than bteq for huge volume of data

Hi Steve, I couldnt send the exact script due to some privacy problems. Please find below structure of my tpt script. 

Update operator detials

Define Operator Export

Type Export

Schema {tbshort}_Schema

Attributes

(logon details

,varchar SelectStmt = 'Select *

                       From

                         tableA

where conditions

                      ;'

Step Apply_Delta

( DELETE FROM tableA

where key_columns= :key_columns

             ;

            )

   End

 To Operator (Update[1])

 Select * From Operator Export[1]);

Teradata Employee

Re: Why TPT mload is taking long time than bteq for huge volume of data

The only privacy issues probably involve any password and you can always just remove that text.

I need to see all of the other attributes you have set up in the script.

What is this script attempting to do?

You are using the Export operator to read data from a file, in order to provde values for the WHERE clause in the DELETE?

The "Delete Tak" option in the Update operator is probably more what you are looking for. Please look at the "Delete Task Option" in the TPT Reference Manual (Chapter 16) for information.

-- SteveF
N/A

Re: Why TPT mload is taking long time than bteq for huge volume of data

Hi Steve,

I am not using using the parameter Deletetask='Y' as I am also inserting few records in to table with the same script. But number of records that are being inserted are minimal compared to the delete records. Pls look below script parameters I am using. 

Define Operator Update_Operator

Description 'TPT  Update Operator'

Type Update

Schema Schema

Attributes

(integer maxsessions = 12

,integer minsessions = 2

,integer maxdecimaldigits = 38

,varchar targettable = 'TABLEA'

,varchar tdpid = '{tdp}'

,varchar username = 'username'

,varchar userpassword = 'password'

,varchar errortable1 = 'ETTABLE'

,varchar errortable2 = 'UVTABLE'

,varchar logtable = 'LTTABLE'

,varchar worktable = 'WTTABLE'

,varchar workingdatabase = 'LOGDATABASE'

);

Define Operator Export_Operator

Description 'TPT  Export Operator'

Type Export

Schema Schema

Attributes

(integer maxSessions = 12

,integer minSessions = 2

,integer maxdecimaldigits = 38

,varchar tdpid = '{tdp}'

,varchar username = 'username'

,varchar userpassword = 'password' 

,varchar workingdatabase = 'LOGDATABASE'

,varchar SelectStmt = 'Select  *  From  TABLEB Where condtions

                      ;'

);

Step Apply_Delta

(Apply

   Case

     When (   Delta_Action_Code = 'D'

          )

       Then ('Delete from TABLEA

 where Key=:Key

 and From_Date = :From_Date

              ;'

            )

   End

 To Operator (Update_Operator [1])

 Select * From Operator (Export_Operator [1]);

);

);

Teradata Employee

Re: Why TPT mload is taking long time than bteq for huge volume of data

I do not see where you specify that rows are being inserted.

The only DML statements I see is a DELETE.

-- SteveF