Optimizing SQL code.

Discussion in 'Performance Tweaking' started by hcherndon, Mar 29, 2013.

  1. So I have recently been having problem with bad timings on my SQL plugins, in specific, keeping track of players online in a database. It is accurate, but inefficient. The timings come up around 20 to 30%. More than anything, I probably need to rewrite my SQL methods, but I will include my current SQL code blocks, anyway someone could help me improve timings please? :3

    I am also keeping player kills in SQL. It is having some trouble, but not as bad. Would still be nice to tidy it up.

    DB Type: MySQL
    Engine: InnoDB
    Char set: UTF-8

    Recent Timings:
    (This involves INFBlood)
    Timings

    Codes:
    Online Players SQL plugin
    Player Kill Logging
     
  2. md_5

    Administrator Developer

    Make them async, simple as that.
     
  3. I know how to do async -> sync, but what do I use to make something async? Wait, more editing, is it .scheduleAsyncDelayedTask();? Edit number 6 or 7. I have no clue what I am doing. o.o
     
    #3 hcherndon, Mar 30, 2013
    Last edited: Mar 30, 2013
  4. md_5

    Administrator Developer

    Schedule a task with that and put all your SQL code in the runnable
     
  5. Ahhhhh. Ok. Though I am confused... What is the difference between async and sync?
     
  6. md_5

    Administrator Developer

    Async runs in a new thread, parallel to sync stuff
     
  7. Ahhh ok.

    http://prntscr.com/yglxf

    So that is a recent shot of it. It is down tremendously, but is there another way to get just a slight more of an edge off of that?
     
  8. Here's my 2 cents
    Instead of
    Code (Text):

     
      if(killer != null){
      st.execute("SELECT * FROM ass_kills WHERE player='" + killer.getName() + "'");
      ResultSet rs = st.getResultSet();
      if(rs.next()){
      st.execute("UPDATE ass_kills SET kills='" + (rs.getInt("kills") + 1) + "' WHERE player='" + killer.getName() + "'");
      } else {
      st.execute("INSERT INTO ass_kills (player, kills, deaths)VALUES('" + killer.getName() + "', 1, 0)");
      }
     
     

    try (same for deaths)

    Code (Text):

     
      if(killer != null){
      st.execute("UPDATE ass_kills SET kills=(kills+1) WHERE player='" + killer.getName() + "'");
      }
     
     
    And have a check when they first log in to make sure they have a place in your table id suggest loading everything when the plugin loads and storing the players in an array of sorts eg HashSet<String> or ArrayList<String> etc. Something like this on startup
    Code (Text):

    HashSet<String> players = new HashSet<String>();//store this in your main plugin class imo
    st.execute("SELECT player FROM ass_kills);
    ResultSet rs = st.getResultSet();
    while(rs.next(){
    players.add(rs.getString("player"))
    }
     
    Then when they first login to the server in a listener
    Code (Text):

    if(players.contains(event.getPlayer().getName())return;
        st.execute("INSERT INTO ass_kills (player, kills, deaths)VALUES('" + killer.getName() + "', 0, 0)");
     
    This should atleast cut down the calls to the database and this post reminded me to async all my db calls in BungeeSuite lol fun...
     
  9. Oh sweet, didn't know you could do math in an SQL statement! Thanks for the help, I'll be doing that a long now. xD
     
  10. An SQL table index on 'player' might help, if you don't have one. It slows down inserts (as it has to recalculate the index) but should speed up updates and lookups. It looks like your load is update-heavy (an update is usually just search/change).

    IANA SQL guru though.
     
  11. Ill look into that. Thanks!