Solved More efficient MySQL code?

Hello, so I'm new to MySQL and I pretty much everything works which is the first step. However, I want to make it as efficient as possible because, why wouldn't you want that? So, I have a way to save a list to a MySQL database. It adds the strings from one list, and removes the ones from the second list. Although, when I run the command to save the list to the database, the server lags for about a second. Which, could increase if there's up to 200 people on the server.

Here is my code:

I believe the inefficiency comes from how I check if every string exists in the database. Maybe if there's a way to get everything from the database and compare it with the current list? Again, I'm fairly new to MySQL. So, I was wondering if anyone could help me. Any help is deeply appreciated.

 

InfamousSheep
Run all these MySQL queries and such asych.

 

InfamousSheep executeBatch() should be outside the for loop. Also run it asynchronously, if not already done so.

 

If I'm following your logic correctly, you could get rid of check() entirely (as well as use batch correctly):

Code:java

try {
 
    PreparedStatement ps = Main.c.prepareStatement("INSERT IGNORE INTO `player_data` VALUES (?)");
    for(String s : Main.BannedPhrases) {
        ps.setString(1,s);
        ps.addBatch();
    }
    ps.executeBatch();
    ps.close();
 
    ps = Main.c.prepareStatement("DELETE FROM `player_data` WHERE `words` = ?");
    for(String s : Main.DeletedPhrases) {
        ps.setString(1,s);
        ps.addBatch();
    }
    ps.executeBatch();
    ps.close();
 
    Main.DeletedPhrases.clear();
 
} catch (SQLException e) {
    e.printStackTrace();
}

 

Thanks dude, it still creates the same amount of lag but after looking at it, it's much more efficient. Thanks again.

Thanks zreed, the issue with executeBatch() outside the loop, that was stupid. But how would I run it asynchronously? I'm sure it's something simple but I'm incredibly tired so I apologise for my idiocy in advance. Thanks

 

InfamousSheep http://wiki.bukkit.org/Scheduler_Programming

 

Keep those methods there and just call them from a runnable as fireblast709 posted of.

 

Ahh, I already have an update timer that I implemented. Thanks a lot guys for your help.