How to avoid update query

kaushal · Post by **kaushal** » Mon Sep 01, 2008 5:05 am

I have to update aprox 20000 records.Since i am using same the same table as input and ouput,so i need time delay .to update the records of volume aprox 20000,its take too much time,is it any alternate to avoid writing update query at output.

DS_SUPPORT · Post by **DS_SUPPORT** » Mon Sep 01, 2008 5:28 am

Introduce a Sequential file in your process, it will actually stop the process until all the rows are read. So your update process will start only after you read your entire input.

There might be some other method also, wait for MASTERS replies.

kaushal · Post by **kaushal** » Mon Sep 01, 2008 5:39 am

Ya i have taken sequential file between process,So i am able to update a records,but the problem is its taking too much time to update 20 thousands records.So i need to know is any alternate method imstead of update query in job.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 01, 2008 6:27 am

No. UPDATE is, by its very nature, slow. Think about all the things UPDATE has to do.

chulett · Post by **chulett** » Mon Sep 01, 2008 7:14 am

How long is "too much time"? How many indexes on your target table and is there one over the key column(s) you are using for the update? What about foreign keys? Check constraints? Triggers? As noted, there are many factors that effect update speed.

Can you avoid an update? You tell us. One method would be to use the 'Replace completely' Update Action (if that's available for your database, you haven't mentioned) which does a delete and then an insert but I doubt it will be faster.

kaushal · Post by **kaushal** » Mon Sep 01, 2008 7:25 am

Ya ,in table i have index,key column,no foreign key.If I use replacement before updation(may be insert into table),I have concurn in that??...If suppose insertion get faild due to some cause,it will become a big problem bcz the existing records get deleted before data,which become a business problem???

chulett · Post by **chulett** » Mon Sep 01, 2008 7:30 am

That would entirely depend on how you handled these errors. Me, I would use a transaction size of 0, then abort the job and rollback all the work if there were any problems. That and design the job such that errors would be a remote possibility.

kaushal · Post by **kaushal** » Mon Sep 01, 2008 7:34 am

I am using transaction size as 100.The update query is working,Problem is its like 100 record updation/min which is too slow when i need to update 20 thousand record,this speed is not acceptable......

chulett · Post by **chulett** » Mon Sep 01, 2008 8:41 am

What database? What stage? What array size?

Work with your DBA, see what suggestions they have to improve the speed.

major · Post by **major** » Mon Sep 01, 2008 10:06 pm

kaushal wrote:I am using transaction size as 100.The update query is working,Problem is its like 100 record updation/min which is too slow when i need to update 20 thousand record,this speed is not acceptable......

Try user defined query instead of generated query or else you can write a store proc for update action

chulett · Post by **chulett** » Mon Sep 01, 2008 11:10 pm

How would that help? An update is an update.