In Memory Data Cache for Performance in .Net Applications

Question

Hey All -

We have an application (rules engine) that has a lot of tables in memory to perform certain business rules. This engine is also used for writing back to the database when needed.

The DB structure is denormalized, and we have 5 transactional tables, that also sometimes need to be queried for reporting.

The issue here is, we want to cache the data inside the app, so it loads on App startup, and then only changes if the DB changed.

Any recommendations?

We are leaning towards creating a DB service, that will handle all Inserts, Updates and Deletes, and queue them to decrease load on the DB server (the transactional tables have loads of indexes also). Also, we are thinking of enabling the DB service to sit on top and serve all reports / other apps that need direct DB access.

The aim here ofcourse is to decrease DB hits for Select queries per request, and prioritize transactions. Also to ensure people accessing apps dont bring the DB server down.

Rules Engine is a C# desktop app, reporting and other apps are web based.

What would be the best way to go about this? I did also think of removing all indexes from my transactional table, and having a trigger insert into a new table which would be a copy, but indexed for report retrieval.

Answer 1

I've done something similar with an obscenely complex rules engine. Ultimately, I set it up so that the data was serialized centrally (with a process to release new changes, causing a new copy to be serialized and the blob stored somewhere accessible). During load, each app-server would check whether they have the up to date version of the blob, and if not fetch it (and store it locally).

Then all it has to do is deserialize the data into memory. No db hit, except for occasionally grabbing the new blob. It also means the app-server can work while the db server is offline (as long as it has a cached copy of the blob). It also polled periodically for new updates while running, of course - but only to the "is there a new blob" code (it still didn't need to hit the main tables).

Answer 2

You may be interested in this article It uses xml to store a readonly copy of the database (in memory). And XPath to query. Nowadays you'd prefer to query with linq, of course.

Answer 3

You should perhaps look at distributed caching solutions (from both performance and scalability point of view). In short, I am taking about scalable DB Services backed by distributed cache (so that multiple DB services get served by same cache).

Here's the article that discusses distributed caching including various approaches for database synchronization. And here is the blog post that list few options in .NET for distributed caching.