Hi,
I have been using the Mload stage to update 1 million records in a table.
The rows/sec or rather the performance of the update is low.
I am doing a migration project from Oracle to Teradata.
The performance when compared to the oracle job is poor
The stats are
Oracle job updates the target at the rate of 400+ rows/sec
Teradata job is at a rate of 70+ rows/sec
Can you Please advice on the various options which would help me match the performance of the oracle version.
Thanks and Regards,
Alan
Poor Performance using MLoad Stage
Moderators: chulett, rschirm, roy
-
shershahkhan
- Participant
- Posts: 64
- Joined: Fri Jan 25, 2008 4:41 am
Can you please provide some more details like what is the table stucture, columns in Primary Index and what values you are updating. Seems like you are updating some column which are part of Primary Index, and it cause the row to go to another AMP which is why its taking time. Give us details about the above and the job archecture and we will then be able to identify the cause
I am adopting this way because my where condition does not have every key column of the table.
Table1---->updating--->Temporary_Table1
API MLOAD
Table1
=====
Actually the table has 7 key columns.
My job has brought in just 2 of the key columns from the source.
The steps I am following are :
=====================
Source(before)-->create a temp table based on the the primary keys required(ie 2 key columns)
Run the ds job to update the temp_table1
target(After)
=====
1.delete from Table1 the records pertaining to get updated.
2.insert into Table1 from data that is updated in temp_table1
3.drop temp_table1
Please lemme know if any other details are required like settings of the MLoad(in Fastload mode)
Thanks and Regards,
Alan
Table1---->updating--->Temporary_Table1
API MLOAD
Table1
=====
Actually the table has 7 key columns.
My job has brought in just 2 of the key columns from the source.
The steps I am following are :
=====================
Source(before)-->create a temp table based on the the primary keys required(ie 2 key columns)
Run the ds job to update the temp_table1
target(After)
=====
1.delete from Table1 the records pertaining to get updated.
2.insert into Table1 from data that is updated in temp_table1
3.drop temp_table1
Please lemme know if any other details are required like settings of the MLoad(in Fastload mode)
Thanks and Regards,
Alan
-
shershahkhan
- Participant
- Posts: 64
- Joined: Fri Jan 25, 2008 4:41 am
I think the following is taking more time in your job.
1) You create the temp table and then copy all the data to this temp table, the data seems to be large and redistribution take time.
2) delete from Table1 the records pertaining to get updated.
If the two keys are giving Equal disturbution then i would recommand to change the Primary index of the main table then these two steps will be removed. If the distrubtion is not good on 2 keys or the business required is such that you cannot use this. Then try to use API stage and use simple update on the main table, maybe that give you better response. Let us know if you have more constaints.
1) You create the temp table and then copy all the data to this temp table, the data seems to be large and redistribution take time.
2) delete from Table1 the records pertaining to get updated.
If the two keys are giving Equal disturbution then i would recommand to change the Primary index of the main table then these two steps will be removed. If the distrubtion is not good on 2 keys or the business required is such that you cannot use this. Then try to use API stage and use simple update on the main table, maybe that give you better response. Let us know if you have more constaints.