CODEWITHSUNDEEP
nhibernate
Wei Ma
Wei Ma

Reputation: 3165

Nhibernate add Option for queries

I have recently had performance problem with Nhibernate generated SQL as described in

Nhibernate generate plain sql query instead of execution statement

I also found a link describing similar experience from the one and only Jeff Atwood at

http://legeronline.blogspot.ca/2009/03/evils-of-slow-paramaterized-query-plans.html

Does anyone know if there is anyway to add an "Optimize Uknown" option to Nhibernate?

Upvotes: 2

Views: 1281

Answers (2)

mcfea
mcfea

Reputation: 1139

This example is a little more verbose:

public class OptionInterceptor: EmptyInterceptor
{
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        var parameters = sql.GetParameters();
        var paramCount = parameters.Count();

        if (paramCount == 0)
            return sql;

        string optionString = " OPTION (OPTIMIZE FOR (";

        for (var i = 0; i < paramCount; i++)
        {
            var comma = i > 0 ? "," : string.Empty;
            optionString = optionString + comma + "@p" + i + " UNKNOWN";
        }

        optionString = optionString + "))";

        var builder = new SqlStringBuilder(sql);

        builder.Add(optionString);

        return builder.ToSqlString();
    }
}

Then in your sessionfactory/initializer:

configuration.ExposeConfiguration(x =>
    {
        x.SetInterceptor(new OptionInterceptor());
    });

Upvotes: 3

Firo
Firo

Reputation: 30813

  • you could look into extending the MsSqlDialect or
  • implement IConnectionProvider to inject your own commandwrapper which adds the hint when ExecuteReader() is called

Upvotes: 0

Related Questions