C# Aurora Database

[Steve Walmsley (OP)]

While the current debate on the nature of the Aether is being resolved, I have taken a break from coding functionality to look at the database situation. VB6 Aurora uses an Access database and writes to that DB frequently (One of the reasons for the slow performance).

C# Aurora (at the moment) also uses Access, although everything is loaded into memory at game start (about 10-12 seconds on my PC). I have, however, run into a problem.

My intention is to save at user request, rather than the continual save in VB6 Aurora. One option is to flag every object that is changed/deleted at any point and then update the DB based on those flags. However, that is open to errors (if I forget to flag something) and I have to remember to ignore any 'deleted' records. So I developed a cunning plan. I would backup the DB first, then simply remove all non-static data relating to the current game and replace with it whatever was currently held in memory. Even if this took a few minutes, it would still be far superior to the current situation of slow increments and I am not going to miss anything.

Yesterday I finally got around to implementing this plan and discovered you can't use Bulk Insert on an Access database. Aaagh!

So faced with going through all the existing code and flagging every change or simply changing the database, I have decided on the latter. Fortunately the program was set up with the intention of being flexible on the DB and all data access is handled within a single class. For example, one function accepts SQL and returns a data table so I can change the code in that function without affecting any of the code that calls it.

So now the question is, which database? Or do I even need a database? Another option is to save to XML or basic files.

I use SQL Server at work and there are a few different versions, including SQL Server Express, SQL Server Local DB and Compact Edition. For deployment purposes, the last of those options seems best but Microsoft have discontinued the product with no replacement apparent. The former two require SQL Server installs on the target PC, which is not ideal.

So I have been looking at SQLite. This seems to fulfil what I need and apparently I can write a series of records before committing the transaction, which should make things much faster. So, for those who know much more about this than me, a couple of questions.

1) Does SQlite seem like a good idea in this situation. If not, can you suggest an alternative?

2) If SQLite is suitable, which GUI should I choose? So far I have downloaded trial versions of:
   a) DB Browser for SQLite (basic but free)
   b) SQLiteManager (not keen as it flickered a LOT)
   c) SQLite Expert ($100 but my favourite so far)

Any comments appreciated.

[AbuDhabi]

This might be of use.

IME, MySQL is the best if you like copying solutions from Stack Overflow. Incredible amounts of help are available on the internet. SQLite appears to be suitable to your needs (fast, embedded); provided you don't want to tinker with performance (I infer you do not), this is a solid choice. I wouldn't recommend PostreSQL, because it's, well, overfeatured for relatively simple applications and slow.

I have looked at the free SQLite software and did not like it. I don't know how the purchasable software handles.

[Erik L]

I use SQLite for the Astra Imperia aid. You don't necessarily need a GUI, but I use Valentina (http://www.valentina-db.com/en/studio-for-sqlite)

[rosteroster]

After using both postgres and mysql, I'd advise using postgres as mysql is poorly optimized for large datasets or subviews.  This is largely because MySQL is barely at all supported nowadays and recent developments for SQL have been more hacked into MySQL than supported.

[Bluebreaker]

Why not just use a serialiser? For example SharpSerializer, just give it a object/s to save and its done almost without doing anything else.

[wedgebert]

I agree, if you're loading everything into memory, there's no reason to use a database if you're not using it for set based operations.
I can think of some other benefits to storing as files instead of in a database

• You can separate data into separate files, so things like themes, initial planet configurations, etc can be saved apart from the same itself. So adding Planet 9 when it's discovered just means releasing a new Sol.txt file instead of a whole database.
• No more fussing with adding/removing columns, making sure your C# data types match SQL, foreign keys, indexes, etc.
• Save games are separate from each other, so I can delete/backup/share as necessary. On a related note, it's easier to submit a save with a bug if it's a standalone file and not a full database.
• For better or worse, it makes it much easier for players to open their saves up and 'tweak' things if necessary (or for the lulz)

From an end-user perspective, I would go with an XML serialization method. While I deal with JSON more often (I'm a web developer), I find XML easier to read and search for larger data sets. Then again, from a developer viewpoint, it's hard to argue against how easy Json.NET is to use.

[Steve Walmsley (OP)]

A quick update: I've played around with a few different options, including XML and some DB options - even CSV

Finally I have decided to go with SQLite, despite abandoning that idea twice in the process due to problems making it work.

Everything is up and running and the Aurora DB is no longer Access, but SQLite. It loads a little more quickly but the real time saver is writing to the DB. I can delete the entire system body table (60,000 rows with 48 fields) and then re-insert all the records from the program in about 15 seconds. While that isn't exactly super-fast it is much better than Access. It probably means an actual save game will take a few minutes (maybe 3-5 mins once I code all the tables) but as some turns take that long now, this should be a huge improvement, especially as it means no disk access during play.

I'll need to check if updating every system body record (due to orbital movement) is faster than delete and replace but I doubt it. For some tables thought I can also be a little more imaginative than simply deleting everything and updating it (history tables for example).

I'm also auto-backing up the DB before saving so I can make that more useful too and create separate save files.

[Ynglaur]

Updating a couple columns should be a bit faster in an RDBMS than delete and insert on the entire table.  RDBMS use column widths to make shortcuts with pointer references, so updating elements pointed to with x pointers vs. y pointers should be faster if x < y.

I'm not a SQLlite expert, though, so I could be mistaken with this particular platform.

[sloanjh]

It probably means an actual save game will take a few minutes (maybe 3-5 mins once I code all the tables) but as some turns take that long now, this should be a huge improvement, especially as it means no disk access during play.

Hey Steve,

  If this (3-5 mins for a save) is true, that sounds like a significant regression in people's ability to execute "undo"/"backup" strategies.  Due to e.g. the bugs in detection, I like to save several times while exploring a new system where I suspect there might be aliens (so that I can use reasonable impulses while out of contact while still knowing that I can go back and re-run using smaller impulses if Aurora messes up).  In the old days, this consisted of a simple copy of the database file (a few seconds).  If it really does take minutes to do a save, then this strategy won't be available.

  One suggestion:  It seems like a lot of the information in the system body table shouldn't change of the course of the game.  Would it be possible to break big tables in two (a "read-only" and "non-const" table) so that you don't spend time re-saving big configuration tables?

John

[alex_brunius]

I don't understand why it would take 3-5 minutes to save the database 

And how dependent is this on game size? Will a fresh game still save fast but a huge one could take even longer upwards to 10min+?
Is it influenced by having SSD/HDD?

As sloanjh wrote 3-5min is a big step backwards as currently Windows does a copy + paste of the database in a few seconds max, and add a few more seconds and you get it compressed too...


Would it be possible to have the game save without user having to wait for it? ( like in the background from a memory snapshot or something ). As long as you can look around and do other stuff while saving it wouldn't be so bad, and I have to admit that I never actually played a game further then until each economy turn is taking more then 5-10 seconds. So for me 3-5min would feel really bad.

[Bughunter]

Hmm, if you are updating 60000 rows in a loop and the index is updated on each update I could easily see it taking a few minutes. One simple workaround could be that you run a DROP INDEX first and then recreate it after the updates: https://sqlite.org/lang_dropindex.html

Possibly some db restructuring to move the orbit data out of the main table could also be helpful. That could allow for a complete rewrite of the orbit table on each save so you do inserts instead of updates and save yourself the seek time for the records to update as well.

[alex_brunius]

If a normal savegame serializer can save game states from normal games from memory in 0.3-5 seconds for most games, it seems odd to cling to a database with 60-600 times worse save performance.

Unless it would be an insurmountable effort to rewrite it to a more normal serialization that is. Or there are other reasons the game must use a database.

Was the option to skip a database and write to XML or basic files evaluated from a performance standpoint?

[lennson]

Just wondering, how long does it take to load the table that you mentioned taking 15 seconds to write?

What is your estimate for how long it would take to load everything into memory (i.e. start the game)?

I would expect that load and write times should be similar unless there is large amount of unnecessary overhead involved in writing.

[Steve Walmsley (OP)]

I don't understand why it would take 3-5 minutes to save the database 

And how dependent is this on game size? Will a fresh game still save fast but a huge one could take even longer upwards to 10min+?
Is it influenced by having SSD/HDD?

As sloanjh wrote 3-5min is a big step backwards as currently Windows does a copy + paste of the database in a few seconds max, and add a few more seconds and you get it compressed too...


Would it be possible to have the game save without user having to wait for it? ( like in the background from a memory snapshot or something ). As long as you can look around and do other stuff while saving it wouldn't be so bad, and I have to admit that I never actually played a game further then until each economy turn is taking more then 5-10 seconds. So for me 3-5min would feel really bad.

It is a trade-off. in VB6 Aurora, the game saves as you play but this means the turns take a while. In C# Aurora, the turns are very fast because I am not saving anything as you play.

The 15 second save I quoted for the system body table is in my game with 23 players and 500 generated systems. It will obviously be a lot faster for a 'normal' size game. The 3-5 minute timescale is an estimate for this very large game. Until I write all the saving code, I don't know the actual number. Currently, it takes less than 10 seconds to load the above game entirely into memory.

[Steve Walmsley (OP)]

Hmm, if you are updating 60000 rows in a loop and the index is updated on each update I could easily see it taking a few minutes. One simple workaround could be that you run a DROP INDEX first and then recreate it after the updates: https://sqlite.org/lang_dropindex.html

Possibly some db restructuring to move the orbit data out of the main table could also be helpful. That could allow for a complete rewrite of the orbit table on each save so you do inserts instead of updates and save yourself the seek time for the records to update as well.

The 60,000 rows I mentioned only needs 15 seconds. The 3-5 minutes is an estimate for the whole database, which has 203 tables (not all of which would be saved as they consist of static data). I'll have a more accurate number once I have done more work.