DSXchange

All,
I have 2 tables located in same database in different schema and we are merging(insert and update based on keys)one table with other.Merge is getting slower day by day as volume increases in DataMart.
This merge is the replacement of requirement..
1.Extract from Source store in Staging area and
2.Load(insert/update) extracted data into DataMart.
We started with normal DS job with insert and update from staging to DataMart but due to millions of records coming from source on daily basis it was very slow so we opted approach of writing daily extracted data into some temporary table and have one merge query to merge this daily data with actual datamart table.Which was faster than previous one but as DataMart reaches to 200Million and merging 1Million record daily is very slow.
I was wondering if anyone has come across this issue or have any suggestion on same.Your help is highly appreciated.
Thanks

Welcome aboard. The safest answer is "your mileage may vary".

As a general rule best throughput is achieved by separating the data stream into insert-only and update-only streams, even though they're both into the same table.

Consider making separate load modules for each, so that you can run the inserts ahead of the updates (just in case the same key comes in more than once).