Loading data. (SQLite)

1

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?

Thanks folks.

2

I am not sure what type of question you are asking.

Are you saying that SQLite is not server based and you would prefer a server-based RDBMS? If so you should consider MySQL Community Edition, which is one of the fastest Open Source databases available. You could also consider Firebird, which is also relatively fast.

If you go to my site at http://www.blackfalconsoftware.com you will find freely available data access layer for both databases.

However, if you are going to be doing save and loads of data ever time an entity makes a move and crosses an internal section you are going to experience some decline in performance with a disk-based database.

Have you considered trying an in-memory database?

Also using a remote server-based database engine may lighten the load on the application but will deteriorate your performance even further since your application has to make a remote call to the database, retrieve the data and then send it back to your application making your user experience a longer lag.

From the quick research I just did on your issue, MySQL is a very popular database engine for game databases. Oracle and EnterpriseDB are also used; the latter being an advanced fork of the powerful PostgreSQL database engine.

However, your problem is the lag issue. If you have too much data to consider an in-memory database and the data is too complex for an object database since you require being able to save and load sets of data you may want to consider creating an enterprise scale type of database. This entails designing two types of databases that work with each other; a standard OLTP database where you add, update, and delete data and an OLAP database which is used to query the data. When an OLTP database is updated, using triggers it will immediately update the OLAP database keeping both databases in sync. In this fashion you can increase your data access by up to 50% to 100% depending on what you are doing.

Dependent on your design, this can be quite a bit of work and often requires the use of database development teams to accomplish this level of complexity.

I hope these notes help… :slight_smile:

3

The answer will be to put the SQL operations onto another thread. This will prevent the render thread from being stalled during the operation. You will then need to synchronize data between the render thread and the worker thread.

4

@KonajuGames

I am not sure if your suggestion would work in that Munty is asking about data loading at a specific time in his game process. Even if you split the data loads and saves into separate threads, the load will still require the time it takes to get the data into the game application.

To me, your suggestion would work if the issue was the saving of data, which could be put into a background thread as the load of new data takes place, which if I understand correctly, occurs when a game object crosses a section.

The only way I know that Munty’s process can be sped up considerably is by removing the disk access component of the data load, which would entail an in-memory database or a very sophisticated memory caching component.

I believe the ExtremeDB database engine may be a potential fit for Munty’s issue as it is supposed to be a very high-speed in-memory database. Munty can review the specs for this engine at the following link… http://www.mcobject.com/

Addendum…

Another option you may want to look into is the use of SQL Server with optimized in-memory database tables. Here is a link that will explain this feature within SQL Server versions 2014, 2016, and 2017…

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/in-memory-oltp-in-standard-and-express-editions-with-sql-server-2016-sp1/

5

No, server or no server isn’t the issue and frankly, I don’t care in this instance as it will make no difference what is used.

Saving us not an issue, a save ‘throws’ data away and doesn’t watch it disappear.

Load is the issue. The LOAD command. It cannot be fully async as is asks for data and HAS to wait for it to arrive.

The second thread is the exactly the kind of solution I’d hoped to hear about. As I’ve stated getting the data into the game (from datatable to game objects) is not a concern, I can handle this.

Are there any additional things to consider when generating another thread while running the game loop, or is it just business as usual?

Thanks.

6

If the thread doesn’t touch any data currently used by the game loop, it is quite simple. It just has to notify the game loop when it is finished. When the game loop gets this notification, it can then use that data to show the objects or whatever it needs to do. Make sure the processing of the data by the game loop after receiving the notification doesn’t take too long per frame, or you will end up with the same frame stalls as you had before.

7

That’s exactly the idea. Although j have just tried putting the Load call on another thread, and if anything it’s slower. So either I need to look at my database connection, or SQLite demands high system priority, which I would find strange.

I’d really like to stick to Lite as there’s no install required, just another support overhead dealt with.

8

"that’s causing the hicks, the actual SQL.Command, the parsing after the load is almost instantaneous."

Because by nature SQLite is slow on large records if not handle with care : - D This link provides overview of how the query planner and optimizer for SQLite works. https://sqlite.org/optoverview.html

But I will never do disk I/O or database request on “hot game update routine” other than loading the game content and saving the game requested by the user.

“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.”

I believed this is the best solution on your situation if your dealing with a large database, while your client will only do a request from the server ( Thru SOCKET NOT from the Database itself and continue the normal game update and rendering until it received the data from server, let the Game Server do database request and other disk I/O , you will need a good Network implementation on this case tho ^ _ ^ y

“Has anyone else faced and defeated this kind of thing?”

Yes we have before I was assigned to write our own Network Library and our own Database library for the project not open yet source tho :smiley:

We did something like this ::

SERVERS CLUSTER TOPOLOGY.jpg811×550 122 KB

9

@Dexter_Z_Gamer

A very impressive description. And your diagram appears to be a classic n-tiered topology for highly efficient, database access.

The only thing I would add is the following. If MuntyScruntFundle is going to build a separate server component to act as a service, I would recommend the use of binary-based remoting, which is a subset to Windows Communication Foundation or WCF.

Most such services are now being built as REST APIs for broader spectrums of OS and language support but if his implementation will be Windows based than he can use the binary remoting protocol, which is the fastest that WCF has to offer.

To add to the database information MuntyScruntFundle would want to consider, is the size of his record definitions. As you mentioned, SQLite appears to have some issues with large or what are also called wide-record definitions. This is true also of Microsoft’s SQL Server; however, probably to a lesser degree now with the increasingly refined versions released.

As a result, for the efficient handling of wide-width record definitions, Oracle is the best database for such situations as it is designed to handle these types of records and its Express Edition is still free as far as I know. However, if you have to go beyond the Express edition, Oracle is not inexpensive.

For small or narrow-width record definitions SQL Server has been the best database for such data access as it was originally designed to handle, small, normalized record widths.

It appears that SQLite may have the same issue so if he has not already done so, MuntyScruntFundle should consider redesigning his database schema to define narrow-width record definitions.

To my knowledge, MySQL is the fastest freely available Open Source database system out there but PostgreSQL is the most powerful of them all being able to support Oracle-like speed and efficiency with its EnterpriseDB Advanced Server.

Hope these notes add some helpful information to MuntyScruntFundle’s issue… :relaxed:

10

"I would recommend the use of binary-based remoting, which is a subset to Windows Communication Foundation or WCF.

_Most such services are now being built as REST APIs for broader spectrums of OS and language support but if _
_his implementation will be Windows based than he can use the binary remoting protocol, _
which is the fastest that WCF has to offer."

Hi SNaidamast,

Sorry, but I beg to differ that MS WCF is fast : - D anyone who has experience in doing multiplayer game using SOCKET VS WCF knows WCF and .Net Remoting is 50% relatively slower than any SOCKET network implementation particularly using UDP. Who would need WCF if you already have a rock solid TCP/UPD Socket implementation for games.

Note that SOCKET is a low level and WCF is higher level messaging implementation of MS that sit on top of TCP which is good on service oriented applications and has lot service features that make it slow and I would not recommend that model on any network game.

That’s the reason of most .NET game engine like MonoGame is using Lidgren which is a popular UDP SOCKET Api implementation, I wrote my own Network library to support both TCP & UDP SOCKET.

Imagine World of Warcraft or any MMO is using WCF service on their multiplayer game, if will be disastrous :smile: that’s why they implemented their own TCP NETWORK Library for their game ^ _ ^ y

But most of your database posts are mostly accurate and you know your stuff in terms of database management ^ _ ^ y

11

@Dexter_Z_Gamer

I was under the impression that the service MuntyScruntFundle was considering would be on the same machine as the application. This would then use a local remoting service, which should be quite fast.

I have written such a service for a large scale enterprise Internet application to handle hundreds if not thousands of objects at at a time and it seemed to work out quite nicely based upon the testing my team performed.

I am not suggesting that a socket based service should not be considered but we are talking about database access here and no matter how you slice and dice it, data access always has a some level of performance degradation.

If one were to develop a remoting service accessing an in-memory database this could speed up MuntyScruntFundle’s requests substantially… :relaxed:

Doing this he could also take advantage of SQL Server’s in-memory table processing, which was implemented starting with SQL Server 2014. Though not free for the standard edition of SQL Server, this edition should be available with a hosted service.

I would also look at MySQL and PostgreSQL as I previously mentioned but I am not knowledgeable of their in-memory processing capabilities…

1 Like
12

Was it left at the default thread priority (Normal) or changed to another priority? Despite some thoughts that thread priorities are evil, they can have an impact on performance especially when set to BelowNormal. See the MSDN doc for Thread.Priority for an example.

Even so, if it does run a bit slower but there is no more frame glitch or hiccup, is it still a win?

p.s. Check out the following blog post to that Thread Priorities Are Evil post from 2006. He mentions “Microsoft’s intriguing XNA Game Studio, which is now available in beta”. :slight_smile:

13

Hi folks! :slight_smile:

Just some ideas: You might want to double check the indices you’ve set on your tables. This is quite crucial, as too much or too few indices can affect your performance quite negatively.

Also, if you do a lot of i/o on your database, you might want to think about working with memory-mapped i/o. It’s supposed to be blazingly fast, and might be a good idea in your case, if you don’t want to got the full-blown service part.

Edit:
I just remembered that there is also Minetest, a Minecraft-clone. If I recall it correct, they are using a SQLite database for storing the chunks and everything around it. Maybe it’s a good idea to have a look at their source. :slight_smile:

And another one - if you are going for a dedicated service, you might also want to keep an eye on Redis (no link, as I’m a newbie… :pensive: ). It can handle tens of thousands requests per second - although you might want to limit that, so your game has some resources left to work with.

14

Not the best suggestion here, but adding to your -external app to service your primary app- idea, You could look at running your database as a system service with manual start/auto start… hey PunkBuster does this… [If you have any Battlefield game installed that uses PunkBuster, look at your services list in Task Manager, see that thing running? PnkBstrA.exe]

Anyone ever used Magix Music Maker will know of FireBird…

WOW

I thought firebird was Magix’s own thing…

Interesting… and it supports ADO.NET 2.0! [I have not dug too far into it at this point so unsure of standalone/non-install-ability]

Hope that added to the debate…

42

15

Firebird is an excellent database being originally derived from the InterBase database engine. The problem with Firebird is that the support is poor leading to the issue of rather a small set of documentation.

16

And the reason for it being slower: I had forgotten to remove the original code call while testing. Idiot! Haha!

It’s made a huge difference. There is without doubt a noticeable hiccup, but it’s reduced from over half a second to maybe 30-40ms, which at this stage is acceptable. I’m sure I’ll have to revisit the problem later, but for now this will get me moving again and onto more development rather than tuning.

Many thanks for all your input on this, all very interesting and I’ll certainly be taking a look into these options.
Cheers.

2 Likes