Inter-Level Experience Reset - A Possible Solution

So I know this will probably be lost, but as a data engineer, I saw the most recent notice and thought that there has to be a better way.

I don’t know what SE’s backend looks like and I don’t know what the data storage for FFXIV looks like. I can only assume it’s a relational database. Given that, it seems like there’s an easy solution to this problem that wouldn’t reset people’s experience. The post made it seem like they’d need to do a mathematical operation for every row representing accumulated experience, with a specific constant to multiply the value by for each level.

This is expensive from a performance standpoint, but also incredibly inefficient, because math operations are costly when you’re trying to do a data operation, especially across what’s probably several hundred million data points.

Instead, just make a table ahead of time that indexes the existing values and maps them to a new one. The table only needs three columns: level, old_exp, and new_exp. Create a compound index on level and old_exp, and then add indices and FK references to wherever those values are stored in the character data. Then, when you do the squish, rather than doing a math operation, you’re just doing an update. The math operation I can imagine taking 2-3 days to calculate every single column, but doing the in advance and storing every possible solution is both relatively cheap and the most efficient way to do it.

I understand if the team doesn’t have bandwidth for it, but this would be a good solution to a complex data problem.

Quote:

---

Originally Posted by Setesh45

How do you do it in advance? The game is up and running until the very end, the maintenance in case of the SHB expansion lasted only one day: https://na.finalfantasyxiv.com/lodes...e2e18da8fd6962

They have to do everything in one day, I'm pretty sure they considered all possible solutions, and reset to 0 xp was simply the most feasible one.
Imo it's not a big deal, we know it in advance, just stop leveling after reaching a full level.

---

The calculation is the part that takes a long time, and if they’re doing it for every data point, there’s bound to be repeat calculations in there. So instead, make a solution lookup table.

You calculate every possible value and what it maps to in advance and store them in a separate table. Going by this chart, the table would only need ~325M rows (not nothing, but also not unreasonable, especially if you partition it right), and would be fully static. This would mean that it could be indexed extremely easily, and the indices would have zero chance of going stale. Rather than calculating everything, you’re just joining another table, which is extremely simple in terms of design, implementation, operational complexity, and execution.

Doing a full table update over ~2B rows without any math would take maybe 15 minutes.

And perhaps they did consider it, but perhaps not. I just saw the operational complexity and know there’s an easier way to do it (because I’ve done it).

Just throw a hash map at the problem, ya? CompSci 101.

I gotta say, I'm with you on this one. It doesn't seem like their explanation really tracks, so either it's not the real problem they are facing or they had already decided this was how they were handling it no matter what. Either way, we're losing the xp.

IMO, resetting the experience bar back to 0% of the current level is an extremely small price to pay.

Even if I had a job at 90% though level 79 and it got reset to 0% I wouldn't care. It takes no time at all to get that level anyways. Hell, you can get little over half of that level being afk 70% of the time in an MSQ Roulette.

If people care that much then push all your jobs just over the line now and your loss will be almost nothing.

They're likely recalculating the entire exp value scaling through the whole chart along with the amounts earned from the game's activities. Individually scaling partial progress shouldnt be too much of an issue. But when it's an inconsistent value that has to be done seperately for every job, on every character, on every datacenter, for every account it adds up.

In the end I don't expect any change in speed to how fast we lvl up.

Quote:

---

Originally Posted by Setesh45

You realize they are still updating the entire database, right? They may no longer be updating the character experience, but they are still updating the HP and damage dealt, Recipe progress and quality, Required and earned EXP. The math is still happening. And this needs to be done for monsters too, not just players, their damage, the experience rewarded upon kill etc.

---

Wow okay, there’s a lot to unpack here. Fortunately for you, I feel like educating a complete stranger. HP and other stats are calculated realtime based on a collection of constant values by class/job and level, and then combined together to get the number that is displayed. The values for, e.g. HP, how much damage an attack will do, or how much a spell will heal for are calculated in realtime, and are not stored anywhere. All of this—every example you cite here—is updated with a client update. There’s nothing they need to do on the backend to accomplish this stat squish. I promise you this work is basically already done.

I think you have a fundamental misunderstanding about what data is actually stored about characters, and what’s involved in the stat squish. It’s very likely that, in terms of stats, the only values that are stored are things like:

• The level of each class and job.
• What gear each character has equipped at the moment, what it’s glamoured to, and what its condition is.
• What items each character has in their inventory.
• How much EXP each character has past each level for each job.

There are likely many more data points, but the only things they store are things that need to persist between play sessions. Everything else can be calculated by the client based on that data.

Quote:

---

Originally Posted by Setesh45

You cannot separate these operations.

---

Actually, you’d be really foolish not to.

Quote:

---

Originally Posted by Setesh45

Doing it your way would imo require:

1. To download the database numbers for every player and their all experience points across all affected jobs. There's over 24 million registered players, how many characters are there? 100m? More?
   
2. Doing what you are suggesting separately in another database.
   
3. Running the downscaling update to the game database.
   
4. Uploading the xp numbers from the separate database into the newly downscaled game database, replacing the original untouched xp values.

You cannot cross run these two different operations simultaneously, that would be too risky imo.

---

So first, let’s clarify some terms here:

• Database: An application designed to store, retrieve, and serve data. These fall into two categories: relational and non-relational (or SQL and NoSQL), the main difference between them being that relational databases tend to segregate like data into separate tables (see below), while non-relational databases tend to keep data together as much as possible (I haven’t worked a lot with the latter, so I fully expect that someone may correct me).
• Schema: A collection of linked tables within a relational database, usually representing all of the data for a unique system.
• Table: A spreadsheet containing similar data points within a relational database, but with added features like indexing (to speed up lookups on big tables), foreign keys (to show how different tables relate to one another), and column constraints (e.g. this column can only be up to this value, this other column can only contain strings, etc.).

So for example, if you wanted to map data about all the inhabitants of all the houses in Milwaukee, you’d have a database for Milwaukee, a schema for housing, and tables for buildings, neighborhood, inhabitants, people, pets, and vehicles respectively. If you wanted to find out how many houses in Milwaukee housed people with at least one vehicle and at least one pet, you’d run a query that looked at the buildings, filtered by which ones are houses, and joined to inhabitants and pets.

When you say “database,” I’m assuming you mean “table.”

Second, with databases, you don’t have to download the data in order to modify it. All databases have support for full CRUD operations: Create, Read, Update, and Delete. Some administrators may disable some of these operations for some profiles, but these are core to every database in existence. All applications that interact with databases need to be able to perform these operations. Because of this, there is no need for them to download the data and re-upload it. They can just do an update statement into a non-active schema, validate that it’s correct, and then promote it. This is probably their plan, they’re just going about it in a silly fashion.

They are on record as saying there’s approximately 2 billion data points that they’d need to update, representing about 10 GB of data. Speaking in terms of relational databases, 2 billion really isn’t that big of a number unless you’re doing computations. This is where I say they’re being silly: based on that update, it seems like their plan was to do 2 billion computations in three steps: (1) look up the scaling coefficient by level, (2) scale the EXP value according to the coefficient, and (3) round the resulting value to an integer. This plan is somewhat straightforward to a layperson, but has a computational complexity of potentially n * (79 + 2), or in our terms, 2B * (79 + 2) = 160 billion (160,000,000,000). That is absurdly complex, and would take days to accomplish, which is likely why they opted to simply not.

However, the total amount of possible values for how much EXP a person can have is fixed. With the current scaling, there is a total of just 325,868,400 values. They can computationally determine what those values will become after the scaling in advance of the maintenance window. It would take one developer maybe 3-4 hours of work to set up the program to do the math and store the results, and then maybe a day to validate that the math was done correctly by the computer. At that point, it then becomes a simple lookup, where you can take the ~2B data points and compare them to the table where the math was done to determine what the value should be. During the maintenance window, the operational complexity goes down to just 2 Billion (2,000,000,000). A transaction like that will not take that long—again, 2B records and 10GB of data are not big numbers when we’re talking databases. What’s more, it’s very likely that these databases are broken up by data center, so they can likely run the update in parallel, in addition to everything else they’re doing, so the actual numbers involved will actually be smaller than the whole thing.

So really, the way I’m suggesting—effectively a hashmap—would be much faster than what I can only presume their plan was. I’m not sure if they considered this route or not, but it could be accomplished during the maintenance window without too much difficulty.

I expect the reasons for the reset are not likely anything more than time and money. We're talking about a team that is stretched to the breaking point that is already dealing with what is likely a laundry list of last minute issues (2 week delay?). We've got hardware shortages affecting server roll outs on top of all that. I'm not educated nor experienced in the field but I have some experience managing very large projects and sometimes things just don't make the cut. Must do. Should do. Nice to do. It just didn't make the cut.

Quote:

---

Originally Posted by Arrhin

I expect the reasons for the reset are not likely anything more than time and money. We're talking about a team that is stretched to the breaking point that is already dealing with what is likely a laundry list of last minute issues (2 week delay?). We've got hardware shortages affecting server roll outs on top of all that. I'm not educated nor experienced in the field but I have some experience managing very large projects and sometimes things just don't make the cut. Must do. Should do. Nice to do. It just didn't make the cut.

---

That’s true, and I hadn’t thought of that perspective. Depending on what issues they’re hammering out, it’s entirely possible they may not have the 3-4 hours it would take to implement something like this.

Quote:

---

Originally Posted by Jpec07

That’s true, and I hadn’t thought of that perspective. Depending on what issues they’re hammering out, it’s entirely possible they may not have the 3-4 hours it would take to implement something like this.

---

3-4 hours only if you skip testing. For something like this it would require thorough testing. Updating the character database is something that just isn't allowed to go wrong. So add a few days (at least) for testing.

Solution: Just make sure you have as close to 0 exp on a level as possible by the 1st. Problem solved with no issue on ether end.

Spending days to try to fix about 1-2 hours of grinding otherwise is a colossal waste of time. Sure, it's fun to think how it could be addressed, but the above solution is just the easiest possible method.

I mean, yea anyone with minimal programming background knows that in theory should be easy enough. That includes SE, if they are not taking the route of recrawling all the new values its obvious that is something else that we don't know about. And its better we don't know, the little they advertise their infrastructure the better. Now based on wild speculation I think that they will need multiple read and write actions per character ( as any character could have up to 17 battle jobs + all DoH and DoL jobs). Probably its calculated in relation to an exponential curve and that curse will most likely change per expansion (by level interval). My guess is that you would need multiple different calculations per level interval/per job/per character/per something else. SO maybe having to look up some many things would lead to combinatorial explosion. Thinking that solving the problem with hashes/3-4 hours of programming and SE haven't done that because it didn't know better its really naive. They know how to do it its just not worth it.

Yar, clearly all they need to do is take a meat cleaver to the end of all the numbers in this game and chop off the last 2 digits from everything. That would be far faster than your lolly-gagging. I'm a butcher by trade so clearly I know the best way to code an MMORPG. Yar!

Quote:

---

Originally Posted by Ryvick

Yar, clearly all they need to do is take a meat cleaver to the end of all the numbers in this game and chop off the last 2 digits from everything. That would be far faster than your lolly-gagging. I'm a butcher by trade so clearly I know the best way to code an MMORPG. Yar!

---

You, I like you

the solution most likely exists, but is ultimately not worth the doing. easier to take the path of least resistance for something of this nature.

Quote:

---

Originally Posted by Jpec07

The calculation is the part that takes a long time, and if they’re doing it for every data point, there’s bound to be repeat calculations in there. So instead, make a solution lookup table.

You calculate every possible value and what it maps to in advance and store them in a separate table. Going by this chart, the table would only need ~325M rows (not nothing, but also not unreasonable, especially if you partition it right), and would be fully static. This would mean that it could be indexed extremely easily, and the indices would have zero chance of going stale. Rather than calculating everything, you’re just joining another table, which is extremely simple in terms of design, implementation, operational complexity, and execution.

Doing a full table update over ~2B rows without any math would take maybe 15 minutes.

And perhaps they did consider it, but perhaps not. I just saw the operational complexity and know there’s an easier way to do it (because I’ve done it).

---

You're not suggesting making a table row for for literally every experience point are you?.... then trying to join it... with other tables are you....

If there’s anything I’ve learned in over a decade working in professional software development, it’s that someone else’s backend is never as simple or clean to worth with as you assume it is from the outside.

Quote:

---

Originally Posted by Jpec07

--snip--

---

First. HP isn't all calculated on the client side. The items are being stat squished so a calculation is being ran against those stats for sure. And HP is calculated from items, levels etc. Levels arent being stat squished per say.

They are just taking out the calculation in the loop. If im honest a gut feeling tells me what you are suggesting may be more time expensive. It'd be easier to run through and just normalize everything.

Say level 79 with 37,650,000. if you have 16,500,000 EXP out of 37,650,000 , just divide and multiple. 16.5/37.65 = .438. Take that value and multiply it too the max value of the new exp value table would only need 90 btw. Just a tad smaller than 325 Million rows. This calculation is easily performed in an UPDATE SQL statement im sure, or an SQL script for that matter.

^ That would be easier.

But now take away that last part, and its just Number of Characters times the # of datasets/points they have to affect with it, and just set to 0 that is no calculation, and its faster.
If you start adding in joins, you will start incurring huge time penalties.

Also No-SQL is Document Based .... IMAGINE like your character info and everything is stored in document in JSON format would be nice. You can add or remove as you see needed. But that comes at a cost (performance and potentially bug prone if not handled correctly.) Honestly it would be more beneficial to use in MMO's, but not completely. Having to join tables over and over. If its not cached just takes too much time. Also caching takes too much memory. Alot of companies go the route of SQL, which is relational which is fine for the most part... but i don't think all my character data should be stored across multiple tables that seems costly as hell to query, and takes up precious computing time on the servers. I would actually use No-SQL for certain parts of the MMO backend and SQL for other parts (say the data analytics they gather.). I don't know why tbh they don't having to deal with all the joins and stuff is annoying programming, and im almost certain things could be made alot faster if programmers made NoSQL and SQL work together.

Like for example assuming that character data is all relational and items etc.
You have to join the character table on the items table. (Again performance cost.)
Now however that is assuming their is one table for items, and 1 for character. but say they have 10 columns or whatever.. for the individual items they have. They could just pull it from one table. Which would be alright. But if its seperated into rows, oh buddy...

In a NOSQL - SQL hybrid you could be like CharacterItemsEquipped = 1,2,3,4,5,6,7,8,9 (being item indexes) do an SQL QUery where ItemIndex = CharacterItemsEquipped and return the items pretty fast, then it simpler to just update the table of items when a stat squish is needed.

Maybe it's not a problem worth going out of their way to solve.

dude just level up lmao

Quote:

---

Originally Posted by Anonicat

You're not suggesting making a table row for for literally every experience point are you?.... then trying to join it... with other tables are you....

---

That’s precisely what I’m suggesting. A nonvolatile table with 325.8M rows, fully indexed and partitioned, will be incredibly performant for a join like this.

Quote:

---

Originally Posted by Krojack

IMO, resetting the experience bar back to 0% of the current level is an extremely small price to pay.

Even if I had a job at 90% though level 79 and it got reset to 0% I wouldn't care. It takes no time at all to get that level anyways. Hell, you can get little over half of that level being afk 70% of the time in an MSQ Roulette.

If people care that much then push all your jobs just over the line now and your loss will be almost nothing.

---

This is the actual answer, but this kind of reasonable response has no place on the internet.

Quote:

---

Originally Posted by Jpec07

That’s precisely what I’m suggesting. A nonvolatile table with 325.8M rows, fully indexed and partitioned, will be incredibly performant for a join like this.

---

No read my other response. That's too much. Why join a table that big.... there is still a performance hit. Just cause the algorithm for joins is optimal doesn't mean it won't take some time.

See my other response.

Quote:

---

Originally Posted by Equitable_Remedy

This is the actual answer, but this kind of reasonable response has no place on the internet.

---

Yes this is the actual responswe. But I felt like delving into CS stuff.

Pointless armchair developer suggestions.
Do you think any of this didn't occur to them? They've decided to do it this way for reasons you do not know.

You should know that it's unwise to propose a solution when you do not know the details of the problem.

Quote:

---

Originally Posted by LariaKirin

Pointless armchair developer suggestions.
Do you think any of this didn't occur to them? They've decided to do it this way for reasons you do not know.

You should know that it's unwise to propose a solution when you do not know the details of the problem.

---

I agree with you to some extent. And I'm just replying to OP's thoughts on it. Second stop with "Armchair X". First we've both worked in software development as a career, so we do have some knowledge on it.

Second you must be of the belief cause some one is a "Senior Software Engineer" as a job 40+ hours a week that they are an expert on said subject. Let me assure you job titles mean jack diddly in the real world. You couldn't be further from the truth my friend. Job titles are just titles to make oneself feel better very rarely do they mean what they should. Now im not saying the Devs at Square Enix are like this at all.. But some people do have actual knowledge in that field and have some insight into.

Also just a side teaser, you know those career professionals you speak so highly about they get hacked all the time by people who aren't employed by said companies. Check out Cyberpunk 2077, or any other. Kinda odd, didn't think those software professionals were able to be touched.. *shrugs*

I am experienced with database development, but I think no matter what your experience is, there is no way to tell what can be done without knowing the database architectures, the technical constraints that they have, the qa process they have, etc etc.

Just a guess, but they probably are trying to keep it simple to avoid having too long a downtime before EW release with too many things to check and verify post-release when XYZ player reports having only 20% of their progress now when they are certain they had 80% beforehand, etc. This is a safe way of doing things with a very, very considerable amount of advance notice.

Quote:

---

Originally Posted by Garnix

I am experienced with database development, but I think no matter what your experience is, there is no way to tell what can be done without knowing the database architectures, the technical constraints that they have, the qa process they have, etc etc.

---

True. Yes. But speaking in a more general sense. But do keep in mind Developers do make things more complicated than things need to be.