Serialized values in the database

[Graveen]

serialized or not, honestly, just another data structure, it worths a lot. Where it is not coherent is regarding the database engine performances, but life is full of things like this

The real question is effectively 'are we able to put support into this ?'

[Dunnerholl]

serialized attributes ftw!

what does it really matter in the places we use them? its neither a performance issue nor would we gain anything else from putting it to yet another and another and another table.

forget about total normalization, welcome to the real world.

hell, most of dol's tables could be even totally serialized textfiles, cause the server doesnt interact with them at runtime and uses is purely for persistance purposes, saving periodically. just some are really used a lot, for example the merchant lists ^^

[tryagain]

Oh, believe me when I tell you I live in the real world. Every day.

Mainly I am learning my way around the code base, saw the serialized columns, wished they were regular tables and started hacking on that. It's given me a chance to learn about DOL's database layer and the mapping of classes to database elements. In the end I hope to have a patch and updater that makes the serialized data into regular tables. Will it be "better"? Perhaps not. Will it be more friendly to new developers? I hope. Either way, the keepers can make that decision and there's no harm.

[Dunnerholl]

surely, its educational and you can do anything you like

many years ago i wanted to do the same, and it took a long time for me to understand that it doesnt matter and makes it even harder instead of easier in those specific cases, in my opinion.

[Tolakram]

It's given me a chance to learn about DOL's database layer and the mapping of classes to database elements.

That is an excellent way to learn!

[tobz]

The real problem here is that data is handled all inclusively and you're always doing full DB updates, etc. Everything that does DB stuff really just needs a save method that makes all the appropriate calls. One place to update when there are new requirements. Add a new table to track RAs for a player? No problem, just add a save line for the DB object in GamePlayer.Save(), etc.

[Blue]

Just saw this discussion and want to give my reason why I used serialized fields on dolcharacter.

It's just on thing => DB performance.

If the data is not stored in the serialized fields, the database will have the task to join the tables. If you imagine that every player has lets say 15-20 skills on average the table size of the join table will be 20x character count. So if a player loads from DB it will join this table everytime. And that table can be quite huge. If you have even more of these huge tables to join in for every char you will propably stress the DB.

A typical example of such a performance hit can be seen on the inventorytable where the visible equipment has to be joined in when a client requests a character overview. On Uthgard this can easily consume up to 1000ms.

I know its not the nice "school-database-design" and its against database normalization but the win in performance was the point to do it this way, and in old times it was quite important with weak cheap servers.

Having working factions will face the same problem as you have to store relations of a player to every faction which can be a huge amount. Imagine there are 400 factions. This results in 400x character count datasets in the table which is to join whenever a player enters the game. Given Uthgards character count of 150,000 the DB would face 60.000.000 table entries on every player load to join in (in the worst case if every relation is stored). That on top of the inventory load.

[Dunnerholl]

i think that as long as you dont want to do a search (where) with stuff in a serialized field, there is no real downside of using it, as far as dol is concerned, that used db mainly as a pure persistance layer. of course if the splitting consumes more than db operations that would be necessary, it could be a reason to change it.

[tryagain]

What about a document oriented database for values like these? Of course it adds some additional complexity especially with setup but it's all experimental at this point anyway (or rather the things I am doing are). I've played with mongoDB just a tad but there are several others.

[Graveen]

definitively no. Replacing mysql with another db is not in the scope of future need. I agree to replace mysql layer with linq/ef layer, thus we could easily change the db type (even a nosql one), but going directly to a nosql or using 2 concurrent layers is infernal with the ressources we have

[tryagain]

Infernal eh? That's pretty serious. At any rate like I said, I'm interested in experiments at this point. So, maybe for me it's OK.

[Graveen]

if you are interested, go, but i fear the bugfixes/benefits ratio, and this 'll be determinant if it must be included in the core

[Dunnerholl]

grav, you have good experience with linq/ef? all my trys were insane slow performing