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

Is the generated sql statement from the linq test exactly the same for all 5000 iterations? — Rippo
– Rippo, Commented Jun 7, 2011 at 16:53
I don't think its the SQL statement, since the cached version that doesn't even hit the database is 8 seconds compared to less than 1. in both cases the database is only hit once. — kay.one
– kay.one, Commented Jun 7, 2011 at 16:56
Sorry that should have been my point, Are you sure the database is only hit once for the linq version? — Rippo
– Rippo, Commented Jun 7, 2011 at 17:18
Yes, I have confirmed this using NHibernate logs, database is only hit once. — kay.one
– kay.one, Commented Jun 7, 2011 at 17:24
I have noticed the top 1 exists only in the HQL query. The LINQ query probally is getting the first result AFTER the query was made. Can you check that? — Pedro
– Pedro, Commented Jun 20, 2011 at 14:43

Chris · Accepted Answer · 2011-06-22 14:17:18Z

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.

Collectives™ on Stack Overflow

NHibernate Linq Query is 3x slower than HQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related