SQL Saved My Game

With a bit of help from the super coder behind Touch Cricket I’ve managed to get SlamBall communicating with the SQLite database I’ll be using for the game’s data store. I’m going to explain a little more about how I’ll be implementing this and why I’ve made some of the design decisions.

The database serves two purposes: firstly to store support data for the game, e.g. generic fixture lists, team data etc; secondly to persist the game’s state, i.e. save the user’s game.

To do this I’ve chosen to use two types of tables. One set will store data that doesn’t change and that can be used to “seed” the save-game tables. This will include things such as the generic fixture list data, the default team data, default admin data (current season number and week number etc). The second set will store the data that is, in effect, the user’s save game data. This will be fixture lists with results for each season (so as to have a history), ever-changing team data, player and player history data, league tables and so on.

The first set of tables will allow me to easily reset the save-game tables whenever the user wants to start a new game.  It’s the save-game tables that have proved more of a challenge.  Although they are, in most cases, an almost exact copy of the base tables they are derived from, the nature of saving data has given me pause: The basic problem is one of SQL updates.

I may be worrying about nothing given the tiny amount of data my game will utilise.

A SQL Update is a potentially “expensive” operation in terms of performance, i.e. they can be slow. You may be updating records on multiple tables, using complex joins, and once the data is written there may indexes to recreate or update. All this takes time, and although SQL is optimised for this sort of thing, I don’t want my game to take an age to save.

I believe I have two options: Either I update on a row-by-row (RBR) basis whenever the uses chooses to save, or I update in a more set-friendly manner and update a whole table at a time.

RBR would involve iterating through all my game’s objects and running an update query. If I have one thousand players in the database, then that’s one thousand update operations every time I save.  And that’s just one table.

The set-friendly method would involve a third set of tables that mirror exactly the save-game tables in terms of structure. Whenever the user loads a saved game, I’d populate this third set of tables with the data from their save. This set of tables would then be updated on an RBR basis as the game progressed. The key difference between this and the RBR mentioned above, is that I would only perform updates as data changed, e.g. if a player scores a goal, I’d update the history table. In this way I’d still have many updates to do, but I’d be doing less of them than a full RBR in one hit. Then, when it came time to update the actual save-game tables, I would just update the whole table in one Update statement using a Primary Key join and a LastUpdated column to ensure a net-change, i.e. only altered records would be updated.

In theory, the one-off big update should perform better than many single updates. However, until I start to load/save real game data I can’t be sure if the set-friendly method is actually a necessity or merely overkill.

A third option is to force an auto-save onto the game and just implement the RBR of the set-friendly approach, but without the need for the big update at the end, i.e. the incremental updates will serve as the actual saved game. This is very appealing as it would simplify everything and make the game more challenging as it would prevent people from re-loading after they’ve lost a match!

I’ve nearly designed the database itself now, so I’ll be taking a closer look into the implementation of my save-game routine in due course. The next part of the development I want to talk about is the game’s economy: Income, expenditure, and the cost of everything.

Leave a Reply

%d bloggers like this: