I know this is a database question really, but I'm guessing a few of you may have faced the same problem and found a good working solution. Anyway, I thought I'd ask just in case...
The game I'm currently working on has a large database, larger than I'd intended, but I'm working towards a PC game rather than phablet so it doesn't really matter. However I'm having some problems getting the data into the game without visual hicups.
The player is in control of 5 objects, one is controlled directly while others are given movement orders. There is a huge game play map split into many many sections, in every section there are a load of mini objects (just over 640,000 in total), any one player object can pass from one section to any other adjoining section at any time, during this change there is a subset of data to save and load.
I've done a lot of testing and it turns out to be loading data that's causing the hicks, the actual SQL.Command, the parsing after the load is almost instantaneous.
I had hoped it was going to be the other way around so I could load a table then parse a line at a time after xx updates, but as it's the load statement that won't make a jot of difference.
SQLite isn't served, so there's no server to take any load, and as far as I can work out I'm working fully async, so I'm already cutting as much overhead as possible. And of course, this is a Load, this data isn't being passed, it's being received.
Surely there has to be an answer, I'm just not sure what it is. I've thought of holding more data in memory, but to cut out the lag spike I'd be storing too much and I'd end up constantly disk buffering which I need to avoid.
I keep thinking I can work this naturally into the update loop, there is an arbitrary amount of time lost when an object passes from one section to another, a few seconds, so there is time to load this data record by record, but I can't think of a way to get around the Command("select * from ... where")
I have thought of writing a second app which could sit behind the game being a server, only the commands go back and forth, the other app could build tables on a different thread while the game loads and saves record by record, but this is a lot of work to get right, and there are timing issues to be considered.
Has anyone else faced and defeated this kind of thing?