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, Jun 07 '11 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, Jun 07 '11 at 16:56
Sorry that should have been my point, Are you sure the database is only hit once for the linq version? — Rippo, Jun 07 '11 at 17:18
Yes, I have confirmed this using NHibernate logs, database is only hit once. — kay.one, Jun 07 '11 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, Jun 20 '11 at 14:43
Have you compared the generated SQL statements if they are exactly the same? — cremor, Jun 21 '11 at 05:53
Interesting... Please show the sql statement that Linq generated for you — Anubis, Jun 22 '11 at 06:07
Can you use the sql analyze to see what sql is used. You would be able to see if both queries return only 1 record or that the second return a set of records. Then only gets the first in the application. — Peter, Jun 24 '11 at 06:29

Chris · Accepted Answer · 2011-06-22T14:17:18.130

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.

NHibernate Linq Query is 3x slower than HQL

1 Answers1