Update in For loop.

[es kranthi]

Hi,
I just wanted to know whether writing update SQL query inside a for loop will effect the performance of the Database or not.Since everytime we iterate and update the record.Hence we are updating the Database this may increase the load on the Database hence the performance.

Ex:

String updateQuery;
for(int i=0;i<selectedEmployeesLength;i++){
updateQuery=Update emp set sal=2000 where empno=i;
execute(updateQuery);
}

Sample code please donot conside syntax

Thanks and Regards.

[Sudheer Bhat]

Check out PreparedStatement and executeBatch of Statement

[Paul Clapham]

Well, yes, if you update a table then that is going to cause the database to do some work. And obviously that means the load on the database is increased, because now it has to do some work that it wasn't doing before. But that's trivially obvious and clearly not what you were looking for.

Unfortunately it's hard to tell what you are looking for instead. Based on your post there isn't much more that can be said.

[Jeanne Boyarsky]

You should use batching. The database can be more efficient if you give it more records at the same time because it makes less trips to the database, less commit statements, etc.

[Arun Chidam]

if you get the employee id's list from the database table, then you can also look into using SP instead of update inline SQL