Solved More efficient MySQL code?

Discussion in 'Plugin Development' started by InfamousSheep, Jul 13, 2014.

Thread Status:
Not open for further replies.
  1. Offline

    InfamousSheep

    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:
    [​IMG]
    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.
     
  2. Offline

    mazentheamazin

  3. Offline

    fireblast709

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

    zreed

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

    Code:java
    1. try {
    2.  
    3. PreparedStatement ps = Main.c.prepareStatement("INSERT IGNORE INTO `player_data` VALUES (?)");
    4. for(String s : Main.BannedPhrases) {
    5. ps.setString(1,s);
    6. ps.addBatch();
    7. }
    8. ps.executeBatch();
    9. ps.close();
    10.  
    11. ps = Main.c.prepareStatement("DELETE FROM `player_data` WHERE `words` = ?");
    12. for(String s : Main.DeletedPhrases) {
    13. ps.setString(1,s);
    14. ps.addBatch();
    15. }
    16. ps.executeBatch();
    17. ps.close();
    18.  
    19. Main.DeletedPhrases.clear();
    20.  
    21. } catch (SQLException e) {
    22. e.printStackTrace();
    23. }
     
  5. Offline

    InfamousSheep

    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
     
  6. Offline

    fireblast709

  7. Offline

    1Rogue

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

    InfamousSheep

    Ahh, I already have an update timer that I implemented. Thanks a lot guys for your help.
     
Thread Status:
Not open for further replies.

Share This Page