What is the best storage for my data and what engine?

dansot · 2012-12-31 19:52:08

I have a database of ~7 million strings. There's no other data, just a huge list of strings.

I need fast (indexed) access to the list and it must be encrypted. I am currently using SQLite3.

Problem is, the database needs to be distributed and the database size is about 500MB which puts a burden on the distribution system and end users.

The database will also be growing fairly quickly.

So, what are my options? Thanks!

EDIT: Fixed my mistake of putting K instead of MB

Last edited by dansot (2013-01-01 13:57:12)

ab · 2013-01-01 08:25:46

500K is a pretty small amount of data.
How much will it grow?

You have to resolve problems one by one.

1. Distributed access
I suppose that the better is to use a Client-Server architecture, by which you can let the list stay on the server, then ask for query from clients.
But if you need the whole content to be available on clients, then you should better use a revision number, and only send the difference (modified/deleted items since client revision number)

2. Storage/Encryption
SQLite3 is good, you can encrypt the content (you can use e.g. the encryption available in our SynSQLite3.pas unit).
If your data can fit in memory, maintaining the list in memory is faster, and you can compress + encrypt the content when persisting the content as file (SynLZ is very fast).

But we need to know more about your expectations, and how your application work.

You do not need to use all mORMot features, but you may have at hand all needed bricks to build this system, very easily.

dansot · 2013-01-01 08:37:32

I already do incremental updates but the initial distribution needs to be the full database.

I am already using SynSQLite3 and it works well. The only issue I have is the file size which I would reduce if I could.

I can't have it all in memory because it is too large.

Typically I am adding about 40K entries a day.

So am just asking to make sure I have made the best choice and that there isn't another option that might be better.

SynBigTable also worked well but I couldn't encrypt it.

Last edited by dansot (2013-01-01 08:39:33)

ab · 2013-01-01 08:48:14

SynBigTable would give better result than SQLite3 about performance.
About size, it will be perhaps a little smaller for a list of strings.

SQLite3 is a good choice.
What you can do with SynBigTable is encrypt each string individually.

dansot · 2013-01-01 09:12:27

I'll give that a try and see if it impacts performance too much.

ab · 2013-01-01 10:55:08

Do not use full AES process perhaps, but a simple XOR with a fixed sized table, perhaps indexed with the string length.

dansot · 2013-01-01 13:58:10

I just realized I screwed up the question. It's 500MB, not 500K.

ab wrote:

500K is a pretty small amount of data.
How much will it grow?

ab · 2013-01-01 15:49:06

For 500MB, SynBigTable will still be able to map the file in memory, so should continue to work.
But SQLite3 has a much powerful set of commands for searching, including index creation.

mORMot Open Source

#1 2012-12-31 19:52:08

What is the best storage for my data and what engine?

#2 2013-01-01 08:25:46

Re: What is the best storage for my data and what engine?

#3 2013-01-01 08:37:32

Re: What is the best storage for my data and what engine?

#4 2013-01-01 08:48:14

Re: What is the best storage for my data and what engine?

#5 2013-01-01 09:12:27

Re: What is the best storage for my data and what engine?

#6 2013-01-01 10:55:08

Re: What is the best storage for my data and what engine?

#7 2013-01-01 13:58:10

Re: What is the best storage for my data and what engine?

#8 2013-01-01 15:49:06

Re: What is the best storage for my data and what engine?

Board footer