Caching data/persisting objects in LINQ

[Andrus]

In Winform RDLC reports running in local mode I use expressions like

=Customer.GetName(Fields!CustiId.Value)

GetName() family of functions are implemented in static assembly
(CodeModule) as

public static string GetName(string id) {
var q= from k in db.Customers
where k.id==id
select k;
foreach (Customer xx in q)
return xx.Name;
return "";
}

There are large number of such methods, for every table and for every
column.

during report rendering ReportViewer makes a lot of calls to GetName(), many
of which are duplicate.
Each call causes data baccess over internet. Report rendering is very slow.

How to force LINQ to cache results?

Andrus.

 

[Marc Gravell]

First - like yourself I'm fairly new to LINQ; however, reading a few
posts it is alleged to have a cache at the identity level, such that
if you query for the same object repeatedly (within the same context),
you get back the same instance. I will investigate this in a moment...

Is this marked as a primary key? and is it the same data-context?

However, LINQ doesn't really include result-set caching (above
identity); another option might be to handle this above LINQ in
something as simple as a Dictionary<id,string>.

Finally - a minor aid to performance (obviously the round-trip is the
biggest killer here...) -

var q = (
from k in db.Customers
where k.id==id
select k.Name).SingleOrDefault();
return q ?? "";

The "select k.Name" limits to a single column fetched (but if LINQ
*does* include instance caching by identity, this might break this);
the SingleOrDefault() optimises for single-row, and evaluates
immediately (not deferred) to a string.

Marc

 

[Marc Gravell]

Using following LINQ gives following stats from SqlConnection; this is
understandable (pretty-much what I anticipated), and reflects LINQ re-
querying the database (as shown by 21 selects and 40 rows), and then
(when processing the results) noticing that it has this record in the
cache already (as shown by RefEquals). So LINQ here has only saved the
cost of building the instance, but has done a round-trip. So to avoid
round-trips, you will need to cache *outside* of LINQ.

string cs =
ConfigurationManager.ConnectionStrings["NWIND"].ConnectionString;
using (SqlConnection conn = new SqlConnection(cs)) {
conn.StatisticsEnabled = true;
conn.Open();
using (NWind.Northwind db = new NWind.Northwind(conn)) {
var query = (from cust in db.Customers
select cust).Take(20);
List<Customer> custs = new List<Customer>(query);
foreach (Customer first in custs) {
Customer second = (from cust in db.Customers
where cust.CustomerID ==
first.CustomerID
select
cust).Single();
Trace.WriteLine(ReferenceEquals(first, second),
"RefEquals: " + first.CustomerID);
}
}
IDictionary dict = conn.RetrieveStatistics();
foreach (object key in dict.Keys) {
Trace.WriteLine(dict[key], key.ToString());
}
}

(all 20 match on RefEquals)
NetworkServerTime: 160
BytesReceived: 17693
UnpreparedExecs: 21
SumResultSets: 21
SelectCount: 21
PreparedExecs: 0
ConnectionTime: 660
ExecutionTime: 761
Prepares: 0
BuffersSent: 21
SelectRows: 40
ServerRoundtrips: 21
CursorOpens: 0
Transactions: 0
BytesSent: 12968
BuffersReceived: 21

 

[Andrus]

First - like yourself I'm fairly new to LINQ; however, reading a few

posts it is alleged to have a cache at the identity level, such that
if you query for the same object repeatedly (within the same context),
you get back the same instance. I will investigate this in a moment...

Is this marked as a primary key? and is it the same data-context?

Yes, my each table has primary key (single or compisite).
I'm working with singe database at a time which contain different schemas.

However, LINQ doesn't really include result-set caching (above
identity); another option might be to handle this above LINQ in
something as simple as a Dictionary<id,string>.

Yes, I have tried to create my own home-brew cache and described issues in
some
threads in this newsgroup.
To simplify my work I switched to Nhibernate which has built-in cache
support.

I think it is better to implement object caching and return whole object
always.

MS Entity Framework should contain persistence manager. How to use it for
caching ?

Finally - a minor aid to performance (obviously the round-trip is the
biggest killer here...) -

var q = (
from k in db.Customers
where k.id==id
select k.Name).SingleOrDefault();
return q ?? "";

The "select k.Name" limits to a single column fetched (but if LINQ
*does* include instance caching by identity, this might break this);
the SingleOrDefault() optimises for single-row, and evaluates
immediately (not deferred) to a string.

To simplify caching I'm planning to read whole row always in db. In this
cace I do'nt need to implement column level caching. All caches I have
studied implement row level caching only.
This makes cache implementation, updating and invalidation easier.

Can you re-confirm, has Linq identity (row level) caching built in ?

For sliced table navigation I need to use page-level caching as in MSDN
DataGridView caching sample. In this case cache is invalidated (objects
released) when user uses page up keys to move some pages forward.
I have no idea should page level caching be in top on identity cache or
should it be separate cache system.

Andrus.

 

[Marc Gravell]

The instance/identity cache means that if your database query returns
a row it already knows about, locally, then you are returned the
existing instance (not a fresh one with the same identity). It still
(according to the simple test described) needs to execute the query to
find this out. I agree that this approach has some issues and some
nicities like simplicity - but it doesn't sit well when round-trips
are at a premium.

Marc