Performance problem
Moderators: chulett, rschirm, roy
Performance problem
Hi all. I have to load a table with a file of 3.5 Gb corrisponding to 9.000.000 record. Initially DataStage loads record at 1.500 rows/second but, after 2.000.000 record, it decrease to 500 r/s ad finish at 250 r/s. Is there an answer to this behaviour ? In which way can i increase the performace ? Thanks in advance.
Re: Performance problem
Can you give some more information about your job structure, like it source stages, intermediate processing stages and finally where actually you're inserting/writing data ?
Saad
Saad
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
It does, to some extent, with a conventional (DIRECT=FALSE) load. But you're correct about the performance over the duration of the load. Any of those 'combo' Update Actions are a crutch, IMHO, that enable poor or lazy job design, and picking the correct one is critical to minimizing the damage.senthilmp wrote:if its doing update and insert mean it will take more time to process, Oracle bulk load doesnt support this.
Think about it. If you say 'Insert else Update' and what you end up doing are primarily updates, then you are burning two database operations to get one record into the table. Same goes for the opposite situation. Some portion of your loads will take twice as long as they should. Plus there's the old standby of updating with non-indexed Key coumns, each update is a full table scan so the more you do the slower it can get. If both are going on - double whammy.
Best solution? Pre-qualify your inserts and updates using a reference hashed file, the Heart & Soul of the Server product. Bulk load the inserts with a direct path load. Then the updates can follow, either as normal OCI updates or perhaps as a conventional bulk load or even as a MERGE performed from a bulk-loaded work table. This becomes a teensy bit more complicated if records inside the dataset you are loading fall into both the insert and update camp, but it's not that hard to handle.
Bottom line, anything noted above - heck, even pre-qualified OCI insert and updates links (plural) - will perform better than your current design.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers