I 开发者_如何学JAVAhave a simple test that runs a query 5000 times. The linq version of the query takes over 3 times the HQL and the cached Linq version is significantly slower than the cached version of HQL
HQL:
session.CreateQuery(String.Format("from Episode where SeriesId='{0}' and SeasonNumber='{1}' and EpisodeNumber='{2}'", seriesId, seasonNumber, episodeNumber))
.SetMaxResults(1)
.SetCacheable(true)
.UniqueResult<Episode>();
Linq:
session.Query<Episode>()
.Where(c => c.SeriesId == seriesId && c.SeasonNumber == seasonNumber && c.EpisodeNumber == episodeNumber)
.Cacheable()
.FirstOrDefault();
Here are the results
HQL: Cached: less than a second No-Cache: 5 seconds LINQ: Cached: 8 seconds No-Cache: 15 seconds
I just want to make sure that I'm experiencing an expected overhead and not something that I'm doing wrong.
if that over head is there and there is not much I could do, can you suggest maybe a middle ground, that would require less strings but provide better performance?
Note:
My cache setting in Fluent Nhibernate
.Cache(c => c.UseQueryCache().UseSecondLevelCache().UseMinimalPuts().ProviderClass<HashtableCacheProvider>())
I guess the problem is the following. This query:
session.Query<Episode>()
.Where(c => c.SeriesId == seriesId && c.SeasonNumber == seasonNumber && c.EpisodeNumber == episodeNumber)
.Cacheable()
.FirstOrDefault();
loads all episodes from the database, puts them into cache, and then returns the first instance of the collection. When FirstOrDefault
is called, the query for Where(c => c.SeriesId == seriesId && c.SeasonNumber == seasonNumber && c.EpisodeNumber == episodeNumber)
is executed and then FirstOrDefault
is applied on the whole sequence returned.
Something like:
.Where(c => c.SeriesId == seriesId && c.SeasonN...
SQL is executed.FirstOrDefault()
is evaluated over all elements of collection of 1.
So if you try something like
session.Query<Episode>()
.Where(c => c.SeriesId == seriesId && c.SeasonNumber == seasonNumber && c.EpisodeNumber == episodeNumber)
.Cacheable()
.SetMaxResults(1)
.UniqueResult();
it should behave the same like your HQL query.
精彩评论