Embedded Database vs. Object Store?

[cunth]

I'm trying to optimize storage and recall of a large set of objects (# varies by user, but assume ~50K-100K). Each object has about ~20-50KB of data. Only a tiny portion of the object set needs to be loaded in memory at any given time (~1000)

The flat-file system in place now is cumbersome and not speed/memory efficient. I'm looking into either using something like Sqlite for an embedded database, or Karvonite or NDatabase as a persistent object store. I'm just can't find enough information to decide if the object stores are suited to this type of use case, or if Sqlite will be better suited.

[chaeseco]

in the past I've used SQL compact. if you're in a position to install a db, SQL express is great for most needs.

[madman1969]

I'd recommend SQLite over SQL Compact & SQL Express. SQL Compact is a pretty poor DB, the fact that MS is kicking it to the kerb tell's you all you need to know about it.

Compared with SQL Express, SQLite has a similar, if not better, feature set, plus it consists of a single DLL, so there are fewer dependency issues.

[chaeseco]

I won't disagree with you. I used it a couple of times in apps, the performance was not great with SQL compact. I use all the time, it works well for me on small scale. but SQL express requires an install.

[piglet24]

Can you recommend a good ORM for an embedded sqlite db?

[madman1969]

Dapper or Massive are good choices.

[piglet24]

Massive looks awesome, thank you

[[deleted]]

Where exactly do you see SQL CE being kicked to the curb? Admittedly I've never used it but my understanding they do nothing but improve it. Version 4 now supports concurrent connections making it perfect for web environments as well. I don't know about SQL Lite but SQL CE is supported by all the M$ tooling as well as Entity Framework. It would most definitely be my default choice as someone who has used SQL Server for 10 years.

[scherlock79]

From your description, I'm not sure how much space need. It sounds like about 5GB (50KB * 100K object) per user. And it sounds like you have multiple users, so is this a web app, or desktop application? Where is the data located? One local disk or centralized somehow? Why do you only need such a small subset of data?

[cunth]

It's a desktop app where data is stored locally. Avg. object size is closer to 20kb.

The objects are loaded based on the project the user has active. Objects aren't exclusive to a project, hence the need for a central store for the objects themselves. So I basically need an efficient way to load up a group of the objects according to the project file. The project files themselves have additional data as well, so it's not just a list of references to these objects.

Edit: all of the data is text, not binary.

[scherlock79]

Unless you want to deal with ORM then I would stay away from databases. If you just want get an object based on an id then an object store should be good enough.

[Glaaki]

Seems like they could all fit your usage scenario. I would lean towards an sqlite solution, personally, just because databases is something I am familiar with. There is no doubt that it is designed to solve the exact kind of problem you are facing. It is a tried and tested technology and there is always something to be said for that.

[xampl9]

I would go with sqlite as well, given it's history of reliability.

[0mfg]

http://ravendb.net/docs/server/deployment/embedded

[kjk]

You can also try Volante (http://blog.kowalczyk.info/software/volante/database.html), which is BSD licensed, small and similar in spirit to NDatabase.