LINQtoSQL caching question

Question

I have been doing a lot of reading but not coming up with any good answers on LinqToSql caching...I guess the best way to ask my question is to just ask it.

I have a jQuery script calling a WCF service based on info that the script is getting from the 1st two cells of each row of a table. Basically its looping through the table, calling the service with info from the table cells, and updating the row based on info returned from the service.

The service itself is running a query based on the info from the client basically in the form of:

Dim b =  From r In db.batches _
                    Where r.TotalDeposit = amount _
                    And r.bDate > startDate AndAlso r.bDate < endDate _
                    Select r

Using firebug I noticed that each response was taking anywhere between 125ms-3secs per. I did some research and came across a article about caching LINQ objects and applied it to my project. I was able to return stuff like the count of the object (b.Count) as a Response in a page and noticed that it was caching, so I thought I was cooking with grease...however when I tried running the above query against the cached object the times became a consistent 700ms, too long.

I read somewhere that LINQ caches automatically so I did the following:

    Dim t As New List(Of batch)
    Dim cachedBatch = From d In db.batches _
           Select d
    t = From r In cachedBatch _
                    Where r.TotalDeposit = amount _
                    And r.bDate > startDate AndAlso r.bDate < endDate _
                    Select r
    Return t

Now the query runs at a consistent 120-140ms response time...what gives??? I'm assuming its caching since running the query against the db takes a little while (< 35,000 records).

My question I guess then is, should I be trying to cache LINQ objects? Is there a good way to do so if I'm missing the mark?

As usual, thanks!!!

Answer 1

DO NOT USE the code in that linked article. I don't know what that person was smoking, but the code basically reads the entire contents of a table and chucks it in a memory cache. I can't think of a much worse option for a non-trivial table (and 35,000 records is definitely non-trivial).

Linq to SQL does not cache queries. Linq to SQL tracks specific entities retrieved by queries, using their primary keys. What this means is that if you:

1. Query the DataContext for some entities;
2. Change those entities (but don't call SubmitChanges yet);
3. Run another query that retrieves the same entities.

Then the results of #3 above will be the same entities you retrieved in (1) with the changes you made in (2) - in other words, you get back the existing entities that Linq is already tracking, not the old entities from the database. But it still has to actually execute the query in order to know which entities to load; change tracking is not a performance optimization.

If your database query is taking more than about 100 ms then the problem is almost certainly on the database side. You probably don't have the appropriate indexes on the columns that you are querying on. If you want to cache instead of dealing with the DB perf issue then you need to cache the results of specific queries, which you would do by keying them to the parameters used to create the query. For example (C#):

IEnumerable<Batch> GetBatches(DateTime startDate, DateTime endDate,
    Decimal amount)
{
    string cacheKey = string.Format("GetBatches-{0}-{1}-{2}",
        startDate, endDate, amount);
    IEnumerable<Batch> results = Cache[cacheKey];
    if (results != null)
    {
        return results;
    }
    results = <LINQ QUERY HERE>.ToList();
    Cache.Add(cacheKey, results, ...);
    return results;
}

This is fine as long as the results can't be changed while the item is in the cache, or if you don't care about getting stale results. If this is an issue, then it starts to become a lot more complicated, and I won't get into all of the subtleties here.

The bottom line is, "caching" every single record in a table is not caching at all, it's turning an efficient relational database (SQL Server) into a sloppy, inefficient in-memory database (a generic list in a cache). Don't cache tables, cache queries if you need to, and before you even decide to do that, try to solve the performance issue in the database itself.

For the record I should also note that someone seems to have implemented a form of caching based on the IQueryable<T> itself. I haven't tested this method, and I'm not sure how much easier it would be than the above to use in practice (you still have to specifically choose to use it, it's not automatic), but I'm listing it as a possible alternative.