Increasing datastage job performnace

Abhilasha · Post by **Abhilasha** » Tue Jun 19, 2007 1:12 pm

Hi,

I have developed a job, which select records from 1 table and inserts into other table.

I have used Informix enterprise stage for source table and target table. I have transformer stage for mapping the columns.

The source table has huge amount of data. Currently its more than 10,000.

I want to increase the performance of this job. So that it loads data with greater speed.

Please suggest me some tips to increase the performance of this job.

Thanks,
Abhilasha

samsuf2002 · Post by **samsuf2002** » Tue Jun 19, 2007 1:15 pm

i dont think 10000 is huge by the way how much does it take now to run the job?

Abhilasha · Post by **Abhilasha** » Tue Jun 19, 2007 1:17 pm

Right now for 10000 its taking 4-5 mins. And later the size of table is expected to be more, so it will go upto 100,000 records or even more.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 19, 2007 3:17 pm

Manage expectations better.
Get faster hardware and more memory.
Drop indexes and constraints from the target table. If that's not acceptable, use bulk-loader technology for the inserts.

Abhilasha · Post by **Abhilasha** » Tue Jun 19, 2007 3:30 pm

can u suggest some approach with existing resources and technology.

ArndW · Post by **ArndW** » Tue Jun 19, 2007 3:53 pm

Abhilasha - have you identified your bottleneck? Is it CPU, I/O, Memory? Have you tested with different number of nodes in your configuration file to get the optimum for that job? Are DS and Informix on different machines (if yes, is the network bandwidth sufficient)?

These are just the first questions whose answers you need before deciding what to do in order to increase performance.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 19, 2007 7:15 pm

Abhilasha wrote:can u suggest some approach with existing resources and technology.

Manage expectations better.
Drop indexes and constraints from the target table. If that's not acceptable, use bulk-loader technology for the inserts.

vmcburney · Post by **vmcburney** » Tue Jun 19, 2007 9:26 pm

You really should be loading those 10,000 records in a much shorter time. Your job will have array size in the database stage that you can increase. You can use the bulk insert stage for faster inserts. You should have a DBA monitor the load to see if there is any type of table locking that is slowing it down. You may be able to add an index to the table to make updates run a lot faster (if there are any). You should check the connectivity between DataStage and the database server for bottlenecks.