Using "cached" LINQ entities

Question

Hi,

i've got the problem that i implemented a WCF-service which is using LINQ entities... To generate the content of the response to a service call, requires a high amount of LINQ/SQL selects...

Whatever, the service is not doing any updates or inserts to the DB, so i'd like to implement a kind of "caching" of the LINQ entities ( also the DB-content is not growing in the millions... it will indeed be limited to about 63.000 mainentries ( with sub-dependancies ) like User -> Orders )

Also the service response does not have to contain the 100% up2date data, so updates of the data in the background shouldn't be anything to think about here

Ok, so currently my plan is as follows:

• Grab ALL the relevant tables from the database with LINQ so it has all the objects "cached" ( the ToList() function should do it, right? )
• Replace the entity object via a kind of smart threading every x-minutes/hours ( like grabbing the data from db again, lock the currently cached linq-DataContent and replace it with the new one.. )

So, what is your opinion... do you think i should/could do it like this? I really need to increase the responsetime of the service, but optimizing the SQL ( e.g. lesser amount of selects ) is not really an option for me, as all the LINQ stuff is already implemented..

Thanks in advance!

Answer 1

I think the approach you suggest is appropriate. Anyway, unless you are sure that all database registers in the relevant classes are always accessed, I would modify it so that instead of retrieving the whole tables at startup, it retrieves the registers the first time they are needed. Something like this:

GetRegister(id)
    if cache has not register with that id
        retrieve register from database
        store register in cache
    return cached register

This way you have your cache implemented, yet you are not wasting memory unnecessarily.

Answer 2

This sounds like premature optimization to me. Consider:

1. There are many things which can slow down an app like this. DB queries may not be the slow part at all. If the slow part is L2E compilation, caching results is the wrong solution.
2. Doing cache invalidation correctly is usually harder than optimizing queries. There are, of course, exceptions.
3. The EF already caches entity instances in a context. Make sure you're not reinventing the wheel.

In other words, write correct code first, abstracted enough that you can plug in a cache if you have to (the Repository pattern is good for this). Profile it, find the slow parts, and fix those first.