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
Writing to Oracle Table is slow
Moderators: chulett, rschirm, roy
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.kris007 wrote: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.
With any kind of 'large' volume, better to take a more Ken Blandish approach. Leverage the high-speed load capabilities of your database.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Writing to Oracle Table is slow
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
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
Re: Writing to Oracle Table is slow
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
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