CODEWITHSUNDEEP
javamysql
JavaCake
JavaCake

Reputation: 4115

Updating multiple rows efficiently

How do i update multiple rows efficiently?

  1. One statement
  2. Multiple statements

Can a single statement string become too large for SQL to handle (10000+ entries/rows)?

I have one single variable to modify, which is status:

| id | status |

My data is stored in a List (ArrayList).

Upvotes: 1

Views: 1232

Answers (5)

berni
berni

Reputation: 1975

If you want to use JDBC and do it efficient you should definitely check out this blog post about batch inserts performance (applies to updates too).

Generally speaking you need to add rewriteBatchedStatements=true to your connection string, for example:

Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/database_name?rewriteBatchedStatements=true","login", "password");

This will allow driver to take prepared statements and re-write them to more efficient form.

Upvotes: 1

Yuriy Nakonechnyy
Yuriy Nakonechnyy

Reputation: 3842

Also you may try batch updates like this, though I'm not sure whether they are efficient:

update `id_status_table` `row`
set `status` = (
    select case `row`.`id` when 1 then 'one'
                           when 2 then 'two'
                           else 'three or more' end
);

While the query string for 10000 rows may get too big, you may apply such query to each 1000 rows.

Upvotes: 0

ejb_guy
ejb_guy

Reputation: 1125

If your status is limited set of values, than I will break the list in subsets based on status and than update the set of rows per single statement. Even if there are 10K rows for specific staus, you can update multiple rows (using in operator) in single call. This will decrease the roundtrip that your application need to made for updation.

Upvotes: 0

D-Rock
D-Rock

Reputation: 2676

It could be more efficient to use the Load Data command in MySQL. Provided you can structure your input into CSV format. Utilize the REPLACE and/or IGNORE keywords appropriately. This will be much faster than 1000's of individual statements to MySQL.

Upvotes: 1

Fabien
Fabien

Reputation: 975

See prepared statements : http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Create your prepared statement out of the loop, then inside the loop, just execute prepared statement with updated parameters.

Upvotes: 0

Related Questions