Performance issue

bhaskarjha · Post by **bhaskarjha** » Wed May 31, 2006 11:00 pm

Hi,
I am loading data to a table from a view which contains 13 lacs record. It took 13 hours to load the table. My design is very simple.
Source View----> Transformer------> Target table

Can you please let me help in increasing performance? The loading option for target is "clear the table then insert rows".
Thanks,
Bhaskar

ray.wurlod · Post by **ray.wurlod** » Wed May 31, 2006 11:24 pm

If you select from a View, then the SELECT statement that defines that view must be executed first. One approach would be to pre-execute that original SELECT statement (perhaps in a DataStage job) capturing the result into a text file. You may well find that this is taking the bulk of the time - if you can "time shift" it to an earlier time, then the load itself may be able to proceed much faster.

How much work occurs in the Transformer stage? Have you used optimally efficient practices in the expressions in this stage?

Have you considered replacing your target table with a text file, and using that text file as the data file in a bulk loader?

Finally, if the source and target are in the same database, have you considered bypassing DataStage entirely and doing the whole thing with SQL?

Code: Select all

INSERT INTO target (column_list) SELECT ... FROM source...

bhaskarjha · Post by **bhaskarjha** » Thu Jun 01, 2006 12:36 am

How can I preexecute the select statement in Data Stage Job? I have executed the query in TOAD & getting the result.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 01, 2006 1:44 am

Two separate jobs.

bhaskarjha · Post by **bhaskarjha** » Thu Jun 01, 2006 2:14 am

Hello Ray,
That means I have to design two similar job. Then execute the first one & finally execute the second one.

chulett · Post by **chulett** » Thu Jun 01, 2006 6:49 am

One job to extract and another job (or jobs) to load.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 01, 2006 4:20 pm

One job sequence to rule them all.

manteena · Post by **manteena** » Thu Jun 01, 2006 4:25 pm

If you think loading (not extraction) is the problem, try to tune the table in the database increase commit level or something like that ..13lac recs are no biggy for DS should not take the time you specified.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 01, 2006 4:43 pm

It might, if there are lots of indexes and constraints to be checked on the target table.

But that's not a DataStage problem.