Update 20m rows table

prasad v · Post by **prasad v** » Mon Jul 13, 2015 7:24 am

Hi

I have table which has over 20million rows in it. In Source System, many rows (over 30%) are updated every day. I am using Upsert method in datastage. It is taking more time for upsert.

Is there any other method we can use to get the performance?

Thanks

chulett · Post by **chulett** » Mon Jul 13, 2015 7:32 am

More time than what?

20 million is not that large. Let's start with this. How are you determining which records have been updated? Do you also need to insert new records or are we literally just talking just inserts? Are the columns in your update's where clause indexed?

priyadarshikunal · Post by **priyadarshikunal** » Mon Jul 13, 2015 7:57 am

Also do check if the table and indexes are analyzed.

Have you given complete reload a thought?

prasad v · Post by **prasad v** » Mon Jul 13, 2015 9:22 am

In My Current Process, I have written custom insert and update scripts and pasted in ODBC Stage and Selected the Key field.

rkashyap · Post by **rkashyap** » Mon Jul 13, 2015 9:50 am

What is your target database?

ray.wurlod · Post by **ray.wurlod** » Mon Jul 13, 2015 4:05 pm

Columns named in the WHERE clause of the UPDATE statement should be indexed, and the table statistics must be current.

chulett · Post by **chulett** » Tue Jul 14, 2015 6:59 am

There's an echo in this thread, need to get that looked into.