Slow query in MS-SQL + Fluent NHibernate

Question

TL;DR: A query using wildcards/like takes four times longer to complete in Fluent NHibernate than executing exactly the same query using Management Studio. This only happens in MS-SQL, while Postgres works just fine. They query only returns two hits.

I'm a querying a 100 million rows table using wildcards (like operator) using Fluent NHibernate, like this:

    return await foobarQuery.Select(e => e.Id).ToListAsync();

I have console logged the produced SQL Query to be this:

select
        foobar0_.[Id] as col_0_0_
    from
        [FooBar] foobar0_
    where
        exists (
            select
                foobarfi1_.[Id]
            from
                [FooBarFile] foobarfi1_
            where
                foobar0_.[Id]=foobarfi1_.[FooBarId]
                and (
                    foobarfi1_.[FileName] like '%Lorem Ipsum%'
                )
        )
    order by
        foobar0_.[Id] asc;

When I execute this in Microsoft SQL Management Studio it takes approx 30 seconds. But the NHibernate query takes over two minutes to complete! It only returns two hits, so the culprit cannot be the handling of the returned data.

To make things even more interesting, when I try exactly the same thing in a Postgres database, it takes approx 15 seconds to complete, both in pgAdmin and from my Fluent NHibernate query. (I guess that I have to live with the fact that Postgres i three times faster, this question is why the NHibernate query is so much slower.)

So the question is, what can cause MS SQL + Fluent NHibernate to become this slow? It takes almost four times as long as a direct query in Management Studio, while there is almost no equivalent overhead when using Postgres.

Sample Code:

public static async Task<ICollection<int>> GetFooBarIdsAsync(ISession session)
{
    Expression<Func<FooBarFile, bool>> predicate = null;
    predicate = predicate.OR(ef => ef.FileName.Like("%lorem Ipsum%"));

    var foobars = session.Query<FooBar>();
    foobars = foobars.Where(e => e.FooBarFiles.AsQueryable().Any(predicate));

    var sw = Stopwatch.StartNew();
    var result = await foobars.Select(e => e.Id).ToListAsync();
    var time = sw.Elapsed;

    return result;
}

Answer 1

So this could be a sql-parameter-sniffing issue. You can look at this article.

https://dba.stackexchange.com/questions/11710/nhibernate-parameter-sniffing-sql-server-2005-vs-sql-server-2008

In a nutshell, you're trying to get

"option(recompile)"

to be a part of your query to avoid the caching/parameter-sniffing issue.

Another possible "trick" .. is to add a "always equates with a random value" clause.

foobarfi1_.[FileName] like '%Lorem Ipsum%' AND 'abc123changesEveryTime' = 'abc123changesEveryTime'

With entity-framework, I do this with

System.Linq.IQueryable qry = /* all the other stuff to start your query */

string randomParameterSniffingBuster = Guid.NewGuid().ToString("N"); 
qry = qry.Where(ent => randomParameterSniffingBuster.Equals(randomParameterSniffingBuster)); 

This is similar to what "we" did back in the old days to avoid a web-cache. (the web-cache and ORM database code are not related at all, outside of the idea of the "trick")

www.somesite.com/thingThatCachesTooMuch?fakevalue=abc123changesEveryTime