A New DB ?

As part of this work I have converted my data store from SQL Server Compact Edition to the key-value pair database by Raptor, from NuGet and documented here. Although its not noticibly faster (data load times were never a limiting factor on my engine) it can store more than SQL CEs 500mb of data – which I was bumping into when generating a full data set.

Its pretty easy to use and works just like a persistent dictionary, so would be very easy to swap out for another no-SQL database in the future. Happily I took the precaution of hiding my SQL CE implementation behind an IDictionary interface so there was zero impact of swapping it over.

Combining this with a nice fast generic QuadTree implementation found here I can now genreate, store and carry out two dimensional indexes of a large object graph for landscape decoration (shrubs, rocks etc.) Rather than use a database index  to carry out 2D rectangle searches I just use a QuadTree index in memory and save it to the database as an object in its own right. When its deserialized into memory its as fast as it can be and doesn’t rely on the database provider to implement spatial indexing.

During profiling the method most called, and one which I can’t really improve on is the Rectangle intersect function. This checks whether one rectangle (typically based on the uses View Frustum) intersects a rectangle of interest (typically a planted region, or individual plant). This is at the heart of the 2D quadtree index and is called many, many times. To be sure I had the best implementation I just used the System.Drawing.RectangleF implementation hoping that the boffins at Microsoft had got it right. This only really shows up on the profile becasue it is called so many times – not because it is inherently inefficient. 60 Frames a second I parse the entire object QuadTree against the current View Frustum and reject all those rectangles which dont intersect the View Frustum bounding box. I recurse into those that can be seen until I have a list of visible objects.

Something tells me that I can actually cache the results of my visibility check and only refresh it if the users viewpoint changes significantly, but I’ll wait to do more profiling before I dive into that solution. The fact that VS.net 2015 Community Edition included the excellent Microsoft Profiler for free really helps the process of understanding the code performance.

After all the work to convert from SQL Server Compact Edition to RaptorDB I got a nasty shock when I examined the file size of my stored data – 15GB. It took a huge amount of time to open (I now understand it reads all its indexes into RAM when the db is opened) and once loaded was indeed really fast; but the slow open time and massive disk size just made it a poor choice

Scores : RaptorDB 0 : SQL Server Compact Edition 1

This made me search for a more reliable database, in-process, just to use as a key-value store. I stumbed across the database built into every Windows OS – “Jet”. Originally a derivative from Microsoft’s acquisition of the FoxPro database technology this is the database format used to store the Windows Registry key-value store, so is pretty robust and performant. The API is a pig, but luckily a .net wrapper and associated IDictionary implementation is available on NuGet. It was a simple drop-in for the RaptorDB and once adjusted to take complex type (explained here ) rather than just simple types, could easily replace it.

Scores : RaptorDB 0 : ESENT 1

The only other thing to look out for is the built-in use of BinaryFormatter to serialize all the data being stored. Since I already use my own custom serializer and store/retrieve only byte arrays, I wanted to bypass that and luckily it implements a plugin field save/retrieve delegate to allow me to do just that.




2 thoughts on “A New DB ?

    • I understand that the other popular .net NoSqlDb RavenDB uses ESENT as its back end storage engine, so if you need an in-proc high performance key-value store you can download the PersistentDictionary implementation from NuGet and give it a go.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s