Efficient way of storing and retrieving complex objects in C++?

Question

I'm currently working on a small dungeon simulation game. The game is quite detailed and i'm planning to have, over time, +200k instances of a class that represents a "monster". They contain perks, skills and history of that monster. Things like how many potions he has used, where he lives, what are his patrol routes, etc.

I started implementing this with SQLite and using a simple table called "monsters", which contained all the data. This allowed me to use SQL queries to find the monsters needed for simulation calculation on each frame. For example: finding all monsters who patrolled point A, or finding all monsters who used Potion X, etc. Unfortunately, querying SQLite several times on every frame quickly slowed down the game. Even though it's a 2D game, i need the precious milliseconds for simulation calculations.

Also, i was going to need JOIN's in the future to do graphs: i need to know if a monster has attacked another monster or if a monster is part of the team of another monster. That would slow things even further.

Does anyone have any suggestion on how to approach this?

My data resembles something like this:

Answer 1

Based on the former answers/comments and your answers for them, I assume that you decided to continue using SQL but you want a better way for reading/processing it. Therefore, my answer focuses that aspect only, and is related solely to design, rather than other suggestions (like using different SQL engines).

The main issue in your DB, as it reflects in your original question, is that all the aspects of a given monster lay in a single record, all in the same table. That makes the table huge over time. Besides that, this design makes your DB and code hard to maintain and evolve.

While it might sound "right" to hold all the details of a monster in a single record (and, maybe, a single object that reflects it in the code itself), this is rarely a good decision. You should have a clear separation between an object attributes as modeled in your "world" to those attributes modeled in software. For example, the location of a monster is probably being changed very frequently, while its name is not. Since you hold all of the data in a single table, any change to the location is done on the very same table that holds all the other attributes, which makes it a very heavy action.

What you should do, instead, is to:

• Separate the data into different tables
• Choose good indices to each table
• Use joins as necessary (based, for example, on the monster's ID)

That way, any read and change is done only in the relevant context; for example, changing the location of a monster will only change the locations table, without affecting tables of more persistent details. Joins should not take lots of time, as long as you have good index and you filter only those data that interest you. In addition to speeding up your queries, this design is much more flexible. For example, if you want to add a new type of attribute to monsters, you can just add a new table, or use an existing table with similar data (e.g. monster's equipment) to add hold it.

If your queries rely a lot on "geographic" locations, and you still want to handle it using a relational DB, you might consider other types of DBs that have better support in spatial queries.

HTH!