0
\$\begingroup\$

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();               
    });     
}
                                                    
                                                        
                
\$\endgroup\$
7
  • \$\begingroup\$ public static Connection conn = DriverManager.getConnection( "jdbc:mysql://freedb.tech/"+ db, login, pw ); \$\endgroup\$ Aug 26, 2020 at 8:05
  • \$\begingroup\$ What does your profiler say? \$\endgroup\$
    – user35344
    Aug 26, 2020 at 8:22
  • \$\begingroup\$ How often do you run the method you posted here? \$\endgroup\$
    – Philipp
    Aug 26, 2020 at 8:56
  • \$\begingroup\$ Every 1 second. Even If I changed to every 6 seconds, application still chokes during mysql query execution. \$\endgroup\$ Aug 26, 2020 at 8:57
  • 1
    \$\begingroup\$ Wait, you are not trying to use a database as a makeshift game server to synchronize clients in a realtime multiplayer game, do you? \$\endgroup\$
    – Philipp
    Aug 26, 2020 at 13:03

1 Answer 1

1
\$\begingroup\$

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?"

\$\endgroup\$
3
  • \$\begingroup\$ I downvoted this because most of the things in the list are not the reason it's slow. \$\endgroup\$
    – user253751
    Aug 26, 2020 at 11:49
  • 1
    \$\begingroup\$ @user253751 I don't feel that your criticism is fair. My goal with that overly verbose list was to illustrate just how much invisible work happens in the background whenever you do a database request to an external server. Yes, a couple of these are negligible compared to the network roundtrip. But they are still not negligible compared to just fetching data which is already in RAM or even in the CPU cache. \$\endgroup\$
    – Philipp
    Aug 26, 2020 at 12:03
  • \$\begingroup\$ I'd bet that in this case, accessing the database is a very slow operation, because of the network latency and literally nothing else. \$\endgroup\$
    – user253751
    Aug 26, 2020 at 12:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .