How to avoid update query
Moderators: chulett, rschirm, roy
How to avoid update query
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.
Regards
Kaushal Kumar
Kaushal Kumar
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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???
Regards
Kaushal Kumar
Kaushal Kumar
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Try user defined query instead of generated query or else you can write a store proc for update actionkaushal 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......