Virtual Database in Memory

Question

Imagine the following:

I have a table of 57,000 items that i regularly use in my application to figure out things like targeting groups etc.

instead of querying the database 300,000 times a day, for a table that hardly ever changes it's data, is there a way to store its information in my application and poll data in memory directly? Or, do I have to create some sort of custom datatype for each row and iterate through testing each row, to check for the results i want?

After some googling, the closest thing i could find is in-memory database

thank you, - theo

Answer 1

Read up on Caching

It sounds like this is application level data rather than user level, so you should look into "Caching Application Data"

Here are some samples of caching datatables

Answer 2

SQLite supports in-memory tables.

Answer 3

For 57,000 items that you will be querying against (and want immediately available) I would not recommend implementing just simple caching. For that many items I'd either recommend a distributed memory cache (even if it's only one machine) such as Memcache, Velocity etc or to go with your initial idea of using an in memory database.

Also if you use any full fledged ORM such as NHibernate you can implement it to use clients for the distributed caching tools with almost no work. Many of the major clients have NHibernate implementations for them including Memcache, Velocity and some others. This might be a better solution as you can have it where it's only caching data it truly is using and not all the data it might need.

Answer 4

If you only need to find rows using the same key all the time, a simple Dictionary<Key, Value> could very well be all that you need. To me, 57,000 items doesn't really sound that much unless each row contains a huge amount of data. However, if you need to search by different columns, an in-memory database is most likely the way to go.