0

Update

On a completely unrelated search, I have found this: Lightweight SQL database which doesn't require installation which lists a few possible options with about the same goals.

Original post

We have a desktop .Net/WPF app, with large (for a desktop app) amounts of data stored: it's has layouts, templates, products list and technical specs, and many more stuff.

Today it's stored in an Access DB, but we have hit the Access limitations pretty hard: it's not very fast, the DB weights 44Mb (which results in a large setup package), and more importantly, it's a pain to use with version control, because we can't merge the data from a branch to another. For instance, we might create a branch to add a few products, but then we have to add them manually in the trunk when we merge. We could use SQL scripts, but writing advanced SQL scripts for Access is a pain.

Basically, I want to replace the MS Access DB with another storage format, because Access is not well adapted.

I had tought of using JSON files that would be unzipped during or after install, but I'm a bit afraid of performance problems.
I'm also thinking of splitting the data into multiple files with multiple formats, depending on its usage, but using different formats might get complicated or annoying to develop.

Performance

Some parts of the DB are accessed pretty often and should be performance-optimized, whereas others are accessed maybe 1 or 2 times per work session, and using a poor-performance but high-compression format could be OK.

Size

We want the installer to be the smallest possible, so the library should be small, and the format should use small files. Using a library that adds 5 Mb to the installer file is out of the question.

Compatibility

The software must be able to run on .Net 4 (not 4.5), and it would be great if it ran on Windows XP (even though we're thinking more and more of just abandoning it going forward, it's still more than 7% of our market share).

Moreover, it should not need to install a server (like MS Access or SQLite) because it will be installed on end-user's computers, and we don't want to bloat them.

Versionning

It should be easy to version the data and the DB structure. The file should either be a text file (like JSON), or scripts should be easy to run in the continuous integration platform (like SQL server).

So, which technology would you use that answers all these contraints ?

Thanks !

Community
  • 1
  • 1
thomasb
  • 5,816
  • 10
  • 57
  • 92
  • My classmates use Firebird db for huge data used in video tracking software for windows (С#). They used PostgreSQL before that, but it's painful to install it on windows. – Ivan Ivanov Jul 18 '14 at 10:47
  • 1
    I don't agree that this question is a duplicate. The requirements described are clearly different. If anything, the requirements in the linked question are a subset of those in this. – Dolda2000 Aug 29 '14 at 03:06

2 Answers2

1

As for your version control pains, it sounds from your description that if I were you, I'd keep the raw data in text files that are version-controlled, and only have the build process produce the database from them. This way, you should be able to use SQLite.

Dolda2000
  • 25,216
  • 4
  • 51
  • 92
0

I would go for SQLite in your case, since the files are self-contained and easy to locate (hence easy to save on a version control system), installer is small, and performance is good. http://www.sqlite.org/

Teddy Engel
  • 996
  • 6
  • 17
  • After consideration, it seems that SQLite files are binary, but look like text files. That scares me a bit for the "merge" part, because it means that a developer might merge his or her modifications, thinking it will work, and corrupting the data. – thomasb Jul 30 '14 at 15:01