Writing to Oracle Table is slow

[ketfos]

Hi,

I am reading from seq file and writing to Oracle table.

The number of input records is 12 million.

The action is set to Update/Insert.
Array size is 400, transcation size = 0, transaction handling = 10000

I am getting like 60 rows/sec.

Seqfile - Transformer - Oracle table.

Any clues/suggestions for improving the performance.

Thks

Ketfos

[kris007]

Writing to a DB with an Update action of Update/Insert or Insert/Update is always going to be slow..in fact very very slow. Always split your Updates and Inserts. Load them through two seperate links and then it will very much faster.

HTH

[kcbland]

Are you monitoring the resources on the database server and the DataStage server? Create a work table and DIRECT path bulk load into the table, then execute a MERGE statement.

[kommven]

Try DB Tuning by placing some indexes with Keys columns in place...

Thats the only solution available....

One another Option is to capture your logic in ETL and use insert strategy....

[sb_akarmarkar]

Indexing is better idea................



Thanks,
Anupam

[DSguru2B]

Even with indexing, you cannot get a enormous boost in the performance if the target stage is set to Insert and update or update and insert. For 12M records, it would be a better to go with Ken's recommendation. A logged activity for this size of data set will be slow.

[chulett]

Writing to a DB with an Update action of Update/Insert or Insert/Update is always going to be slow..in fact very very slow. Always split your Updates and Inserts. Load them through two seperate links and then it will very much faster.

I totally agree. Not just slow but typically the slowest way you can work with a database. When you are going to stick with a DB stage (like OCI) the use of either of the 'dual action' actions - Insert/Update or Update/Insert - is a crutch and a weak design. IMHO.

With any kind of 'large' volume, better to take a more Ken Blandish approach. Leverage the high-speed load capabilities of your database.

[vjeran]

Hi,

there is cute explanation about bulk load and inser/update problem. Probably you get good idea what to do.

viewtopic.php?t=95526&highlight=bulk+lo ... oracle+oci

BR Vjeran

[Umbix62]

Hi

can you describe me the layout of the table and can you tell me how do you update or insert the rows (update fields keys)?

Thank you

Umberto