NHibernate Linq Query is 3x slower than HQL

Question

I have 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>())

Answer 1

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:

1. .Where(c => c.SeriesId == seriesId && c.SeasonN... SQL is executed
2. .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.