CODEWITHSUNDEEP
javamysql
Thomas Simonsen Strømmen
Thomas Simonsen Strømmen

Reputation: 3

Java MySQL Update Optimization

I have a code that are uploading player data from an entry to MySQL database. The query looks like this:

            uploadPlayers = getManager().getMysqlManager().getConnection().prepareStatement("UPDATE players SET name=?, ips=?, gamemode=?, lastlogin=?, timeplayed=?, rank=?, badges=?, level=? WHERE uuid=?");

And the method to upload the players looks like this:

    @Override
public void saveDataToDatabase() {
    try{
        for (final Entry<String, IPlayerData> entry : playerdatalist.entrySet()) {
            final IPlayerData pd = entry.getValue();

            SH.uploadPlayers.setString(1, pd.getName());
            SH.uploadPlayers.setString(2, pd.getIp());
            SH.uploadPlayers.setInt(3, pd.getGamemode());
            SH.uploadPlayers.setString(4, SH.getManager().getMysqlManager().getDate(pd.getLastlogin()));
            SH.uploadPlayers.setLong(5, pd.getTimeplayed());
            SH.uploadPlayers.setInt(6, pd.getRank());
            SH.uploadPlayers.setString(7, pd.getBadgesAsString());
            SH.uploadPlayers.setInt(8, pd.getLevel());
            SH.uploadPlayers.setString(9, pd.getUUID());
            SH.uploadPlayers.addBatch();


        }   
        SH.uploadPlayers.executeBatch();
    }catch(SQLException e){
        e.printStackTrace();
    }

There is approximately 1500 players in the entry and in the database. But it uses 15-20 seconds to update all the players every time i use the code. How can i make it go faster and optimize it?

Upvotes: 0

Views: 333

Answers (2)

e4c5
e4c5

Reputation: 53734

There are two problems here. As @JiriTousek has suggested not having an index on the uuid field is slowing you down (+1 for that answer). The second is you are updating all the fields is it strictly needed?

According to the manual:

An update statement is optimized like a SELECT query with the additional overhead of a write. The speed of the write depends on the amount of data being updated and the number of indexes that are updated. Indexes that are not changed do not get updated

The fact that the UUID field is a charfield and 9 characters in length means an index here would give a big boost. Even a partial index might work.

The manual also says:

If you set a column to the value it currently has, MySQL notices this and does not update it.

Despite this you shouldn't update all the fields (if they are not strictly needed) because you are passing a lot of data back and forth and binding parameters has a very small overhead too.

Finally if speed is really important @GordonLinoff suggestion of creating a temp table and then updating with a single statement is the way to go. If you find even that to be slow @GordonLinoff other suggestion of LOAD DATA INFILE is the best option of them all.

Upvotes: 1

Jiri Tousek
Jiri Tousek

Reputation: 12440

You need to create an index on uuid column.

Without the index, for every entry in the batch the DB engine will have to go through all rows of the table to determine which row to update, giving you time complexity in the scale of (number of rows updated) * (number of all rows in the table). With an index, the time complexity will drop to scale of number of rows updated only.

Adding the index will incur additional (time) cost to the updates and inserts, but it will be negligible compared to the improvement if you're always updating rows by their uuid (which you probably are).

Upvotes: 1

Related Questions