Improving performance

[pradkumar]

Hi

I am having a job which loads a table. For initial load there were around 35 million records.
I loaded them and it tooka round 9 hours. Like this I am having 7 tables

NOw I am doing an incremental load for three months and it is taking more time than the initial load.
My option int he target DRS is Insert New Rows or Update Existing.

I am having indexes also on this table..

Let me know your ideas on how to improve performance of incremntal load

[Krazykoolrohit]

For a start you could tell us what your database is.

even 35 million rows should not take 9 hours. my guess is that the tables has lots of referential dependencies.

Have you checkd for any warnings?

[DSguru2B]

My option int he target DRS is Insert New Rows or Update Existing.

For a start, your problem is right there. Split your inserts and updates. And for millions of records, use the bulk loader.

[paddu]

Pradeep,

Give more details on the stages used in the job?
rows per transactions size????

when you say incremental load , is it weekly data or monthly data ? give me more details


Thanks
Paddu

[pradkumar]

There were no warnings. I am working on Oracle Database.(Source and Target).. there are no referential conditions

By the way this job is runing in development. It is not yet moved to production. Hopefully it should take less time in PRD...

Correct me if I am wrong and let me know if this performance can be improved too

Incremental===> 3months data

[DSguru2B]

Read my reply above.

[pradkumar]

Hi DSGuru

There is no key generation process here.. If I split Insert and Update, I will be needing Lookup right!...Then a full scan of 35 million records will take more time..(I think)

Correct me if I am worng

[paddu]

is your incremental load also 35 million???

[DSguru2B]

But not as much as doint UPSERTS.
Alternatively, load your file into a temp table. Do a sql join and get the inserts. Similarly you can get updates as well. Do bulk inserts. For updates, well you can pass it through DRS stage or OCI stage and do updates only. Will be much faster.

[pradkumar]

My Incremental load is for only 1 million records..But i think it is doing full table scan as My update action is "Insert New or Update Existing"

[pradkumar]

Hi DSGuru

I am unable to get what you are saying..My source is coming form Oracle table and not file. Plz elaborate a little more

[DSguru2B]

You are correct and hence its taking a lot of time. Also it forces your array size to be set to 1 and thats another downfall.

[pradkumar]

You are correct and hence its taking a lot of time. Also it forces your array size to be set to 1 and thats another downfall.

So is it better to use only Insert New Rows only..in Update Action

Moreover downt he line we will be doing incremental load for every two days..Even then using Inser and Update will pose a problem

[paddu]

Partitioning of database tables should help incase of Large volume of data .Contact your DBA .

please read this article http://www.oracle.com/technology/produc ... oning.html

paddu

[DSguru2B]

So is it better to use only Insert New Rows only..in Update Action
Moreover downt he line we will be doing incremental load for every two days..Even then using Inser and Update will pose a problem

Yes, 110% better.
Yes.
And if your DBA is anything like mine, he will laugh as the fact that you are asking him/her to make a database partitioned for 30 million records. For them this is considered an average amount. They consider partitioning for billions of records. But maybe thats just my DBA.