How to increase performance?

kalpanam · Post by **kalpanam** » Sun Sep 19, 2010 11:49 pm

Hi,

My job design is like:

DB2---------->Modify----------->DB2

I have 5,925,155 records in source, an I am doing direct load into target using write/append option. In modify I'm using NOWARN specification. Other than this I'm not doing any kind of conversion. In source I'm converting decimal to char using char(bigint(internet_order_nbr) function. This is taking 1 hour to get complete. Is there any option to increase the performance?

Please let me know..

Thanks& Regards,
Kalpana.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 20, 2010 1:15 am

That's about 1600 rows per second. The bottleneck is almost certainly the "writing" stage. But you need to establish the timings of each - the select with and without the functions, writing to a text file versus loading into a database via bulk load versus loading into a database via SQL. What indexes and constraints exist on the target?

Or maybe just lower your expectations.

kalpanam · Post by **kalpanam** » Mon Sep 20, 2010 3:29 am

ray.wurlod wrote:That's about 1600 rows per second. The bottleneck is almost certainly the "writing" stage. But you need to establish the timings of each - the select with and without the functions, writing to a tex ...

As I'm not premier member,I'm not able to view the full answer of yours.Ray could you please provide me the full details?

Thanks & Regards,
Kalpana.

ArndW · Post by **ArndW** » Mon Sep 20, 2010 3:36 am

The only way to see Ray's complete post is become a premier member. But he has already given you the approach in the visible part of his response. You need to determine which step is the blocking one. Replace your output DB2 with a peek - has the speed changed?