In the data data cache for performance in .Net applications

Question

We have an application (rules engine) in which there are many tables in memory to execute certain business rules. This engine is also used to write to the database if necessary.

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

The problem is that we want to cache the data inside the application, so it loads when the application starts, and then changes only when the database changes.

Any recommendations?

We tend to create a database service that will process all Inserts, Updates, and Deletes and queue them to reduce the load on the database server (there are also many indexes in transaction tables). In addition, we are thinking about allowing the database service to sit on top and serve all reports / other applications that need direct access to the database.

The goal here is to reduce the number of remote databases for selective queries for each query and prioritize transactions. In addition, so that people accessing applications do not load the database server.

Rules Engine - C # desktop application, reports and other applications based on web interfaces.

What would be the best way? I also thought about removing all indexes from my transaction table and having a trigger in the new table that would be a copy but indexed to retrieve the report.

Answer 1

Perhaps you should take a look at distributed caching solutions (in terms of performance and scalability). In short, I deal with scalable database services supported by a distributed cache (so that multiple database services get the same cache).

Here's an article that discusses distributed caching, including various approaches to database synchronization. And here is a blog post listing several parameters in .NET for distributed caching.

Answer 2

I did something similar with an indecently complex rules engine. Ultimately, I set it up so that the data was serialized centrally (with a process for releasing new changes, as a result of which a new copy was serialized and a memory location was available somewhere). At boot time, each app server checks to see if they have an updated version of blob, and if it doesn’t retrieve it (and save it locally).

Then all you have to do is deserialize the data in memory. There are no db hits, except that it sometimes captures a new blob. This also means that the server application can work while the db server is not offline (provided that it has a cached copy of blob). He also periodically interrogated new updates during work, of course, but only for “there is a new code” (he still did not need to hit the main tables).

Answer 3

You might be interested in this article. It uses xml to store a read-only copy of the database (in memory). And XPath for the request. Currently, you will of course prefer to query linq.