Performance Issue

xch2005 · Post by **xch2005** » Fri May 06, 2011 11:23 am

Basically we are loading from Sybase to Oracle tables, it takes close to 30 mins. for loading 2 million records. It is direct mapping and no validation.

For the first run it will be full referesh but then onwards it would be delta records. Hence we are using a upsert process (Update then insert)

SYB -> TRN -> ORA

Apart from this when we add a column to the existing record and load the overall load time for 2 million records is 1 hour.

Any suggestions on how to bring the load time down please.

Thanks

DSguru2B · Post by **DSguru2B** » Fri May 06, 2011 12:18 pm

Ask your oracle dba to drop indexes and turn off logging for the first run. once your run is complete, ask him to recreate the indexes and turn logging back on and perform a reorg. That should speed up your process.

greggknight · Post by **greggknight** » Fri May 06, 2011 6:17 pm

I would do Insert then Update as opposed to Update then insert.
Less scanning.

greggknight · Post by **greggknight** » Fri May 06, 2011 6:18 pm

And actually
We have two seperate jobs Inserts and Updates
We don't use upsert
but if you .do the before post would be the better way

xch2005 · Post by **xch2005** » Mon May 09, 2011 7:29 am

Thanks.
I have now split the load into 2 jobs.. Now the time taken for load is better.

Thank you.