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

Orbital

Is the generated sql statement from the linq test exactly the same for all 5000 iterations? – Thurston

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. – Orbital

Sorry that should have been my point, Are you sure the database is only hit once for the linq version? – Thurston

Yes, I have confirmed this using NHibernate logs, database is only hit once. – Orbital

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? – Nelsonnema

Have you compared the generated SQL statements if they are exactly the same? – Mchail

Interesting... Please show the sql statement that Linq generated for you – Buffybuford

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. – Sweep

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.

Galegalea