[Discussion] Optimizing the way to save character data

[Kyber]

Hi ragezer, i create this topic to discuss about to find another ways to saving character data in odinms's server.

As almost everyone here already know, all the public source based on odinms currently still using a function called saveToDB() in MapleCharacter.java to save the character's data after player logout. This function work as well as DELETE all old records in database, after that rewrite/ INSERT new data into database. Yes, it still work good...but it have an disadvantage.

Assume that your server have 1000 characters created, and each character have average ~200 items in inventory equal with 1000*200 = 200,000 record in database, each record/item have an id at column/field (inventoryitemid), so assume that each player login and logout atleast 1 time, so 200,000 record will be deleted out of your database, and 200,000 new records will be inserted. The column/field 'inventoryitemid' with auto increasement property will generate a new id for each new record inserted, so 200,000 old id will no longer be used. This will cause the id column will increase very fast and number become bigger cause some issue for your database and server like:
- Out of rage of data type.
- Cannot use the field `id` because of value always change.
- Data Roll-back if your saveToDB() have a bug or server corrupted. (you are on DELETE query and the server corrupted, the INSERT command will never be run, all character data gone).
I have try to find another ways to save the character's data to database, like replace DELETE/INSERT query with UPDATE/INSERT query, but it still not seem optimzed, cause it still have a pre-check what item "is new for INSERT", "is modified for UPDATE" and "is removed for DELETE"
Anyone have an new idea to change the traditional character save???

 

[Fraysa]

Practice of removing all entries just to add the current entries is bad as you already mentioned. Let's take an example using the character's inventory.

You can create a boolean to indicate if an item is "assigned". When you load the player's inventory, a loaded item is already assigned. When a player gains a new item, it's not assigned by default. When you run the "save" method on that item, check if the item is assigned or not. If it is, run the UPDATE query, if it's not, INSERT. Then you can simply run this on all of the items. As for deletion, you can make a boolean to mark for delete, or just run the DELETE query immediately when a player disposes the item.

This can work for anything in your server, honestly. From skills to pets.

 

[Arnah]

Just remember delete/updates are the slowest option as you have to update the index(primary key, unique is what creates an index in mysql)

And inserting tons of times is far from the worst way but it all depends on how you want to handle it and how you will handle grabbing the last insert since you don't want people to 'dupe' items each save.

You must be registered to see links

has some ways to speed up updates/delete when you are dealing with a table that has an index(likely all of them)

And to add onto multiple inserts, your server will have downtime, you can reset the AI value during this time, or if you never delete you can delete then reset the AI(Obviously backing up the data before you delete since you're keeping it for a reason aka tracking).

 

[Zydee]

What I did to reuse previous ids(not item) was add a function to start that takes every item in the database and pushes it back so all items column id goes from 1->x without any numbers increments missing in between. What this does is it re-opens up values to be reassigned once more during the game. I don't know if this makes sense, but theoretically, it will take a long time the first run but will take less time as time goes on. (2^3?)

I am interested in this as well so if anyone has ideas, I would like to be apart of it. Though my coding is in C++ and I will only be able to give the idea of how it works (or the code too if you have a basic understanding of object-oriented coding, you could maybe figure it out)