1.17.x loop optimization

Discussion in 'Spigot Plugin Development' started by PandaSlayer, Jun 23, 2021.

  1. I have a limited knowledge of SQL and I have some questions.
    What you are looking at is a SQL statement that grabs everything from the table and it looks through the column with all the group names, gets a JSON text from it that contains a list of the groups members and their roles. It checks to see if that player is in the text, then it moves onto the next group.

    So I have these loops and it seems problematic to me. I forgot how I had it set up before this but it used to lag the server for a couple of seconds (it doesn't do that anymore). I just wanted to know if this code could be problematic when there are lots of entries in the SQLite database with big json texts. And if so how I could make it more efficient.

    Code (Java):
        public String getPlayerGroup(Player player) {
            try {
                PreparedStatement ps = connection.prepareStatement("SELECT * FROM " + table);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    String name = rs.getString("name");
                    for (OfflinePlayer p : getMembers(name)) {
                        if (p.getUniqueId() == player.getUniqueId()) {
                            return name;
                        }
                    }
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;
        }
     
    • Funny Funny x 2
  2. You should always run SQL queries asynchronously. Is that what you changed to prevent your server lag (I don't know if maybe you are calling this method async somewhere)? If so, be wary of calling spigot-related functions on another thread as the majority of the Spigot-API is not thread-safe.

    On another note, I'm having trouble visualizing your DB design though. Could you possibly post it?
     
    • Like Like x 1
  3. You should properly model the 'member' relation as a table with foreign key constraints in your database, that way you can do the entire filter operation using SQL JOINs.
     
  4. Save the uuid in the database and use WHERE clause to filter by uuid.

    What youre doing is highly inefficient.
     
    • Agree Agree x 2
  5. What you are doing is basically like this:

    Code (Java):

    Collection<File> files = new HashSet<>();
    Files.walk(Paths.get("/"))
            .filter(Files::isRegularFile)
            .forEach((path) -> files.add(path.toFile()));

    File theFileIWant = null;

    for(File file : files) {
        if(file.equals(new File("/my/file"))) {
            theFileIWant = file;
        }
    }
     
    Where you should actually be doing just this:

    Code (Java):

    File theFileIWant = new File("/my/File");
     
    @Janmm14 already explained how you should do it.
     
    • Like Like x 1
  6. Thanks for the feedback,
    What I did is instead of looping through the whole thing like I did before I just did this
    Code (Java):
        public String getPlayerGroup(Player player) {
            try {
                PreparedStatement ps = connection.prepareStatement("SELECT * FROM " + table + " WHERE MEMBERS LIKE '%' || ? || '%'");
                ps.setString(1, player.getUniqueId().toString());
                ResultSet results = ps.executeQuery();
                if (results.next()) {
                    return results.getString("NAME");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;
        }
    Basically, I'm just checking if the table contains the player's UUID in the member's column. Seems to be working fine so far...
    The only thing I'm still having trouble figuring out is how to run a SQL query Async. Never written Async code before.
     
  7. You should make sure to also close the PreparedStatement. The cleanest way to do this would be to use the try block as a try-with-resources block instead. Note that the ResultSet should also be closed, but closing the PreparedStatement will do this automatically.
    Code (Java):
    public String getPlayerGroup(Player player) {
        try (PreparedStatement ps = connection.prepareStatement("SELECT * FROM " + table + " WHERE MEMBERS LIKE '%' || ? || '%'")) {
            ps.setString(1, player.getUniqueId().toString());
            ResultSet results = ps.executeQuery();
            if (results.next()) {
                return results.getString("NAME");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
     
    • Like Like x 2
    • Informative Informative x 1
  8. Thank you!