How to Cache a large amount of data from database

Question

My website uses linq-to-sql to load about 50k rows of data from a database. This data is static and never changes. It works similar to a spam filter and all 50k rows of patterns need to be loaded.

What's the best way to program this? for optimal performance?

Answer 1

Loading the entire thing into a single static readonly data-structure (it being immutable means once constructed it can be safely used from many threads) would give the greatest overall performance per lookup.

However, it would lead to a long start-up time, which may not be acceptable. In that case you may consider loading each item as accessed, but that brings concurrency issues (since you are mutating a data-structure used by multiple threads).

In between is the option of loading all indices on start-up, and then adding the rest of the information on a per-access basis, with finer-grained locks to reduce lock contention.

Or you can ignore the lot and just load from the database as needed. This does have some advantages in terms of performance just because memory is not used for rarely-used information. It'll be a whole lot easier if you ever suddenly find that you do have to allow the data to change.

No one comes out as the only reasonable way to go in general, it'll depend on specifics of application, data, and usage patterns.