Java application chokes - when using MySQL

Question

If I use localhost - XAMPP, my application runs without problems. Once I try to use real MySQL databases: db4free.net, freedb.tech. Applications begins to choke and freezing, resulting it is nearly impossible to use.

If I choose to use paid MySQL database, will it solve problem?

public static void scanningPlayers ()
{
    updatePlayers.setNode( GameGroups.finalRoot); // does'nt matter node.
    updatePlayers.setDuration(Duration.millis(1000));           
    updatePlayers.play();   
    updatePlayers.setOnFinished(evt ->
    {           
        GameGroups.rootPlayers.getChildren().clear();       
        
        try 
        {
            Statement stmt      = Credentials.conn.createStatement();
            ResultSet users     = stmt.executeQuery("select id, name, avatar, Xpos, Ypos from users");  
            
            while ( users.next() )
            {
                Label playerName            = Default.Label17Light  ( "" + users.getString("name"), users.getInt("Xpos"), users.getInt("Ypos") - 25 );
                ImageView hero;

                hero            = Default.ImageView( Dir.adventure + users.getString("avatar") + ".png", users.getInt("Xpos"), users.getInt("Ypos") ); 
                
                
                GameGroups.rootPlayers.getChildren().addAll ( playerName, hero );       
                                                    
                                                        
                if ( users.getInt("id") == Variables.id ) 
                { //identify mysel, used for centering n door.
                    Variables.Xpos = users.getInt("Xpos");
                    Variables.Ypos = users.getInt("Ypos");
                    Variables.myAvatar = hero;
                    
                    GameGroups.rootUniverse.setTranslateX ( -Variables.Xpos  + 550);
                    GameGroups.rootUniverse.setTranslateY ( -Variables.Ypos  + 362);    
                }
            }                           
        } catch (SQLException e) {e.printStackTrace(); }  
                
        updatePlayers.play();               
    });     
}
                                                    
                                                        
                

Answer 1

Accessing the database is a very slow operation. In order to access a database, the following things need to happen:

1. Build an SQL query string in memory
2. Put that SQL query into a TCP/IP packet
3. Pass that packet to the network driver of the operating system
4. Send the packet to the network hardware
5. Put the packet into an ethernet frame
6. Route that ethernet frame through the network to the database server, potentially through kilometers of cable and several routers.
7. Unpack that ethernet frame into a TCP/IP packet
8. Forward that packet to the server socket of the MySQL database
9. Parse and validate the SQL string
10. Put it into the buffer of unprocessed SQL queries
11. Fetch the query from the buffer
12. Run the query on the database table(s) and assemble the result set
13. Format the result set into an SQL response string
14. Put that response into a TCP/IP packet
15. Pass that packet to the network driver of the operating system
16. Send the packet to the network hardware
17. Put the packet into an ethernet frame
18. Route that ethernet frame through the network back to the host the game runs on, potentially through kilometers of cable and several routers.
19. Unpack that ethernet frame into a TCP/IP packet
20. Forward that packet to the client socket of the gameserver
21. During all that time, the thread scheduler of your operating system will likely have put your idling game process to sleep, so you have to wait until the thread scheduler wakes it up again.
22. Parse the SQL response
23. Continue with program execution

All this stuff takes much, much longer than just reading a value from memory. Getting the best database server money can buy will only accelerate the steps 7-17. The network roundtrip alone will break your performance. So no, this is not a problem you can solve by throwing money at it.

So what should you do instead?

Do not do any database requests in your inner loop. Request data from the database once, and then keep it in memory. Only write data to the database when you need to persist it permanently.

When you do database requests, don't do them on the main thread. Do them on a background thread, so your application keeps running while it waits for a response from the database. For more information about how to do that, check out this Stackoverflow question: "Is asynchronous jdbc call possible?"