Redundant Dictionaries, Simulated Database or Physical Database?

Question

Good morning,

At the moment I am needing some help from someone more experienced with database issues than me. I need to work with two sets of (String, Int32) pairs. The first one has about 160,000 pairs, and the second one has about 1,620,000 pairs. The question is that every part of this last collection's strings is contained on the first set... That being, I thought about simulating a database in the RAM memory by implementing three dictionaries:

Dictionary<String, Int32> to store a string and something like it's "primary key", Dictionary<Int32, Int32> connecting each primary key to the value needed Dictionary<Tuple<Int32, Int32>, Int32> to store a pair of primary keys and another value.

Using two dictionaries and storing the redundant information, the program allocates about 200Mb of RAM. Although that is not a crucial issue, I would like to reduce it. So I tried using the three dictionaries above, and, as I expected, the allocated memory was reduced a lot, to about 90Mb. However, lookup performance when searching by string was reduced, taking about twice the time taken by the first, simpler but redundant approach.

So now I am thinking about implementing this as a SQLite database. The problem is lookup time: it should be as fast as possible. Is it possible, using this kind of database, to get lookup times similar to RAM access times?

Thank you very much.

Answer 1

Possibly. Your RAM database does not handle anythin gbut pure comparisons well as it has no index structures. You dont give any search time so far.... it may be that with the proper use of an index or two you can beat your dictionary time quite well. A LOT depends here on the specific query behavior and query execution times in RAM. Without this information there is not a lot we can say.

In general, also, specific data structures can be a lot faster than generic databases. They are less flexible, though. Really depends on how you retrieve data here.