Can't get second level cache working with Query
raffaeu
default Hashtable as I am just testing this feature.
I have configured the hibernate.cfg.xml in the following way:
<property
name="cache.provider_class">NHibernate.Cache.HashtableCacheProvider</
property>
<!-- You have to explicitly enable the second level cache -->
<property name="cache.use_second_level_cache">true</property>
<!-- cache will expire in 2 minutes -->
<property name="cache.default_expiration">120</property>
<!-- I want also to cache queries -->
<property name="cache.use_query_cache">true</property>
And I have configured my entity to be cachable in the following way:
<class
name="CachableProduct"
table="[CachableProduct]"
dynamic-insert="true"
dynamic-update="true">
<cache usage="read-write"/>
<id name="ProductId">
<generator class="guid.comb" />
</id>
<property name="Name">
</property>
<property name="Price">
</property>
</class>
When I try to run the following unit test, it always print out two
SELECT statements, so it clearly doesn't cache the entity. But if I
write a unit test calling Load<T> or Get<T> from two different
sessions, the 2nd level cache works, so I believe I have an issue just
with the query cache.
using (var session = factory.OpenSession())
{
using (var tx =
session.BeginTransaction(IsolationLevel.ReadCommitted))
{
Console.WriteLine("*** FIRST SESSION ***");
var result = session
.CreateCriteria(typeof(CachableProduct))
.SetCacheable(true)
.Add(Restrictions.Eq("Name", "PC"))
.List<CachableProduct>();
tx.Commit();
}
}
using (var session = factory.OpenSession())
{
using (var tx =
session.BeginTransaction(IsolationLevel.ReadCommitted))
{
Console.WriteLine("*** SECOND SESSION ***");
var result = session
.CreateCriteria(typeof(CachableProduct))
.SetCacheable(true)
.Add(Restrictions.Eq("Name", "PC"))
.List<CachableProduct>();
tx.Commit();
}
}
raffaeu
*** FIRST SESSION ***
NHibernate: select cachablepr0_.ProductId as ProductId4_, cachablepr0_.Name as Name4_, cachablepr0_.Price as Price4_ from [CachableProduct] cachablepr0_ where cachablepr0_.Name=@p0;@p0 = 'PC' [Type: String (4000)]
Cached queries 0
*** SECOND SESSION ***
NHibernate: SELECT cachablepr0_.ProductId as ProductId4_0_, cachablepr0_.Name as Name4_0_, cachablepr0_.Price as Price4_0_ FROM [CachableProduct] cachablepr0_ WHERE cachablepr0_.ProductId=@p0;@p0 = c718b1ed-3993-454a-999a-9fad00c2f96d [Type: Guid (0)]
Cached queries 1
As you can see the query is cached but the second time it is still calling the DB, but in a different way.
I do not understand
raffaeu
The issue was that I was using the same session after the insert, to generate the cached query, and clearly, it was not working as expected.
This code works perfectly:
using (var session = factory.OpenSession()) { using (var tx = session.BeginTransaction(IsolationLevel
.ReadCommitted))
{
// I am creating non-cachable products
var products = MockFactory.MakeCachableProducts();
products.ForEach(p => session.Save(p));
tx.Commit();
}
}
using (var session = factory.OpenSession())
{
using (var tx = session.BeginTransaction(IsolationLevel.ReadCommitted))
{
Console.WriteLine("*** FIRST SESSION ***");
var result = session
.CreateQuery("from CachableProduct p where p.Name = :name")
.SetString("name", "PC")
.SetCacheable(true)
.List<CachableProduct>();
Console.WriteLine("Cached queries {0}", factory.Statistics.QueryCacheHitCount);
Console.WriteLine("Second level hits {0}", factory.Statistics.SecondLevelCacheHitCount);
tx.Commit();
}
}
using (var session = factory.OpenSession())
{
using (var tx = session.BeginTransaction(IsolationLevel.ReadCommitted))
{
Console.WriteLine("*** SECOND SESSION ***");
var result = session
.CreateQuery("from CachableProduct p where p.Name = :name")
.SetString("name", "PC")
.SetCacheable(true)
.List<CachableProduct>();
Console.WriteLine("Cached queries {0}", factory.Statistics.QueryCacheHitCount);
Console.WriteLine("Second level hits {0}", factory.Statistics.SecondLevelCacheHitCount);
tx.Commit();
}
}