Reducing runtime for a MLOAD job

Analytics
Enthusiast

Reducing runtime for a MLOAD job

Hi All,

I have a monthly MLOAD job which loads around 26 millions of records, but it takes 16 - 20 hours to complete loading. Can someone give some tips to improve performance and reduce runtime?

PS: I don't prefer changing MLOAD to any other utilities.

Thanks a lot for your time and help
11 REPLIES
Enthusiast

Re: Reducing runtime for a MLOAD job

very little information to give useful comments...

But these would be the direction on which I would investigate

What is the number of records on the table ? is it partitioned ? on what ? how many records per partiton ? do you load across partitions ?
Is your table set/multiset ?

Do you have UPI/NUPI ?

Are there too many duplicates on the PI ?

What is the width of the records being loaded ?

Is the performance issue on the network throughput Versus DB ?

Enthusiast

Re: Reducing runtime for a MLOAD job

Hi,
Please find the details below

What is the number of records on the table ? is it partitioned ? on what ? how many records per partiton ? do you load across partitions ?
Is your table set/multiset ?

No: of records - 26million. It is not partitioned.
Table is a SET table

Do you have UPI/NUPI ?

I have UPI defined to the table. It is a combination of 4 columns.

Are there too many duplicates on the PI ?
It is a UPI . So NO duplicates

What is the width of the records being loaded ?

Record length of the file is 2500 characters

Is the performance issue on the network throughput Versus DB ?
There is no performance issue as far as i know.

Please let me know if you need more details
Enthusiast

Re: Reducing runtime for a MLOAD job

Hi,

I have noticed that the options AMPCHECK IS ALL and also 40 input files are concatenated. CHKPOINT is default 15mts. Table is not a fall back. Will this affect performance?
Enthusiast

Re: Reducing runtime for a MLOAD job

Just to experiment, can you try concatenating all the files and do a fastload to an empty table ?

Also is your 26 million the number of records you are loading or the number of records in the table ?

If the table is huge, you might find a lot of performance issues doing insert into it. (which can be fixed to certain extend by using appropriate partitioning techniques)

Do a fastload to an empty table, check it's speed, and try doing a select insert into the target table and check the time for that step.

Enthusiast

Re: Reducing runtime for a MLOAD job


26 millions of records are in the file and not in the target table.
Enthusiast

Re: Reducing runtime for a MLOAD job

Are u getting a lot of records in UV table?or check the data skew for current PI.
Also AMPCHECK option doesnt effect performance.
Enthusiast

Re: Reducing runtime for a MLOAD job

No records are going to UV table. Also it is using UPI.
Enthusiast

Re: Reducing runtime for a MLOAD job

What is the number of existing records in the table ?

Also can you load the file to an empty table and tell us if that's any faster ?
Enthusiast

Re: Reducing runtime for a MLOAD job

We are loading in to an empty table and this is a refresh load.