DSXchange

Hi All,

One of my job is production taking more than 3 Hrs 50 min to load the data into the data set. The design is very simple

1. Incremental source file extract the records having 21 million records from sequential file to load the data into data set.

2. The above loaded data set having look up with reference data set (LOOK UP STAGE) using left outer join then load the data into final data set.

I need to reduce the timing to load the data into data set. Kindly share your input what are all the performance tuning need to be taken in order to increase the performance in my job.

First of all you need to know what your limiting factor is and also what your potential gains are.

Make a copy of your job with just the source file writing to a dataset, 1:1 with nothing in between. Run the job and see how long it takes; this result is your current theoretical maximum speed.

If the speed is close to your current 3:50 speed then modifying the lookup or dataset is not going to bring much. If your source file is fixed-width then you might be able to speed up reading with a parallel read.

What APT_CONFIG_FILE configuration are you using? Also, in the original job, if you monitor your system load is it maxing out at close to 100% or is the CPU relatively idle during the 3:50 runtime?

That sounds like really bad performance. I would be curious if you tried any of the above suggestions and what you found out from those.