Logical doubt LOAD/WRITE on DB

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

italianRichy
Posts: 14
Joined: 09 May 2019, 17:24

Logical doubt LOAD/WRITE on DB

Postby italianRichy » 09 May 2019, 17:35

Hi, I'm creating a video game with SFS2X, I have a logical doubt about server programming.

Is it convenient to load all user data into RAM when the server is started, or to use database queries to access each user?

When is it convenient to write user data to the database?
- On OnLogout User; (It will happen very often)
- At server shutdown (will rarely occur);
- To the destruction of the user's clan room? (Will often)
- Every 10 minutes?

Thanks for your attention
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Logical doubt LOAD/WRITE on DB

Postby Lapo » 10 May 2019, 07:35

Hello,
Is it convenient to load all user data into RAM when the server is started, or to use database queries to access each user?

Generally speaking no, it isn't.
The main reasons are... wasting too much memory and loosing the ability to perform queries.

When is it convenient to write user data to the database?

Normally it is whenever you need it.
I am not sure what you're afraid of... maybe database performance?
If you run the database on a dedicated server and in the local network you shouldn't be too concerned. Databases such as MySQL can handle hundreds of operations per second without problems.

On the other hand if you find yourself writing to the database too often for a single user session, then you may need to optimize the amount of calls. It really depends on the specific use case and there is no general solution for everything.

Hope it helps
Lapo
--
gotoAndPlay()
...addicted to flash games
italianRichy
Posts: 14
Joined: 09 May 2019, 17:24

Re: Logical doubt LOAD/WRITE on DB

Postby italianRichy » 10 May 2019, 09:49

Thank you
italianRichy
Posts: 14
Joined: 09 May 2019, 17:24

Re: Logical doubt LOAD/WRITE on DB

Postby italianRichy » 10 May 2019, 13:40

Hi, but for a chat, it's the same?
If a user sends so many messages, do they do too many queries?
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Logical doubt LOAD/WRITE on DB

Postby Lapo » 10 May 2019, 14:13

A chat could be different, yes.
If you want to store every message to a database it may be best to use a "batching" technique, where you don't hit the DB for every new message being sent. Instead you accumulate a number of messages in memory and then store them in the DB with one call.

A similar approach could be to use a time interval where, for example, you accumulate all messages from a user in a queue and flush the queue to the database every 1 or 2 minutes or something like that.

Hope it helps
Lapo

--

gotoAndPlay()

...addicted to flash games
italianRichy
Posts: 14
Joined: 09 May 2019, 17:24

Re: Logical doubt LOAD/WRITE on DB

Postby italianRichy » 10 May 2019, 14:36

Thank you
italianRichy
Posts: 14
Joined: 09 May 2019, 17:24

Re: Logical doubt LOAD/WRITE on DB

Postby italianRichy » 10 May 2019, 15:21

What is the difference between:

Code: Select all

IDBManager dbmanager = getParentExtension().getParentZone().getDBManager();
      Connection connection = null;
      try
      {
         connection = dbmanager.getConnection();
         ResultSet result_query = connection.prepareStatement("SELECT * FROM table).executeQuery();
      }
       catch (Exception e)
      {
            trace("sql error" + e);
      }
      finally
      {
          try
          {
             if(connection != null) connection.close();
          }
          catch(SQLException e)
          {
             trace("Connection error " + e);
          }
      }

or

Code: Select all

IDBManager dbmanager = getParentExtension().getParentZone().getDBManager();
      try {         
         ISFSArray array= (ISFSArray)dbmanager.executeQuery("SELECT * FROM table", null);         
      }
       catch (Exception e) {
         trace("sql error" + e);
      }

It seems to me that they are the same thing
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Logical doubt LOAD/WRITE on DB

Postby Lapo » 10 May 2019, 16:45

The first one returns a native JDBC ResultSet and it is generally more flexible, as it allows to do all kinds of advanced operations with the DB.

The second one is more high-level, it wraps JDBC and it returns the results in SFSObject/SFSArray format, which is easy to use for sending to clients etc...

They are both valid and useful in different situations.
Lapo

--

gotoAndPlay()

...addicted to flash games
italianRichy
Posts: 14
Joined: 09 May 2019, 17:24

Re: Logical doubt LOAD/WRITE on DB

Postby italianRichy » 05 Jun 2019, 23:13

Hello world again,

Another doubt arose, I am currently working on the user statistics of my project, this involves taking a large amount of data from the database, if there is a small possibility that I have this data in RAM, it is worth checking if the user is Online, and in case I take them from him?
Or do I take them directly from the database?
To get this data I use 2 queries with various LEFT JOINs.
What is the most efficient way?
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Logical doubt LOAD/WRITE on DB

Postby Lapo » 06 Jun 2019, 09:24

Yes, if the stats data is available in RAM when a User is logged in, it is certainly better to check if the User is connected.

To do that you can simply call

Code: Select all

getApi().getUserByName(...)

or

Code: Select all

getApi().getUserById(...)

in your Extension code.

Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: Google [Bot] and 68 guests