Click here to Skip to main content
15,878,814 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table which contains around 2 crore plus records. The table contains 150+ columns as well. Among these 150+ columns i need to update few columns with a value. The new value will be generated from a function by passing the column value as input. Inside the function there will be few lines of code which will return a value based on the input supplied.

Now my question is regarding the approach that needs to be followed to update records with good performance in production.

Option 1: Is it better to write an update query for each column to update at a time( Means if I have 10 columns to update, 10 separate update statements will be written)?

Option 2: In a single update statement all the 10 columns are updated.

Which approach is better, Please give your inputs.
Thanks in Advance.
Posted
Comments
Bernhard Hiller 15-Sep-14 2:18am    
In case of failure: do you want to rollback all previously updated values, or just leave it in a state where a few but not all rows have been updated (keyword: transaction)?
dinesh42 15-Sep-14 5:14am    
I need to roll back in case if the update statement fails
_Asif_ 15-Sep-14 2:45am    
What is your search criteria for Update? I mean is it PK based search?
dinesh42 15-Sep-14 5:15am    
The table have client numbers. For a particular client number i need to update the records.

1 solution

The better performance is to update all 10 fields in one single UPDATE call, and from the SQL command execution speed should be almost 10 time faster then each individual update for each field.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900