Npgsql nhibernate 57014 canceling statement due to statement timeout

Question

I'm getting random errors in some queries using Npgsql.

Here is the stack trace:

at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1597
   at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1497
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1487
   at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1955
   at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
   at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
   at LAVASPORT.DAO.MigrarCadeteDAO.findByCompania(Compania compania, String conexion, DateTime fechainicial, DateTime fechafinal) in D:\Aplicaciones\LavaSport\LAVASPORT\DAO\MigrarCadeteDAO.cs:line 125

And here is the message

InnerException = {"57014: canceling statement due to statement timeout"}

I'm not getting this exceptions all the time, just 1 or 2 times at day. The query in special that most send the exception is a high volume query.

Here is the query:

public IList<Cadete> findByCompania(Compania compania, String conexion,DateTime fechainicial,DateTime fechafinal)
        {
            try
            {
                DateTime fechaini = new DateTime(fechainicial.Year, fechainicial.Month, fechainicial.Day);
                DateTime fechafin = new DateTime(fechafinal.Year, fechafinal.Month, fechafinal.Day);
                var nhConfig = new Configuration().Configure(conexion);
                var sessionFactory = nhConfig.BuildSessionFactory();
                var session = sessionFactory.OpenSession();
                session.BeginTransaction();
                var query = session.CreateCriteria<Cadete>();
                query.CreateAlias("compania", "compania");
                query.Add(Restrictions.Eq("compania.id", compania.id))
                     .Add(Restrictions.Lt("fechaIngreso", fechafin))
                     .Add(Restrictions.Ge("fechaIngreso",fechaini));
                IList<Cadete> cadetes = query.List<Cadete>();
                return cadetes;
            }
            catch (Exception ex)
            {
               MessageBox.Show("Error : " + ex.Message);
            }
            return null;
        }

This are my NHibernate configuration and my web config file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory name="LAVASPORT">
    <property name="connection.driver_class">NHibernate.Driver.NpgsqlDriver</property>
    <property name="connection.connection_string">
      Server=localhost;database=LAVASPORT;user id=postgres;password=admin;MaxPoolSize=500;TimeOut=1000;
    </property>
    <property name="dialect">NHibernate.Dialect.PostgreSQLDialect</property>
    <property name="show_sql">true</property>
    <property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
    <mapping assembly="LAVASPORT"/>
  </session-factory>
</hibernate-configuration>

If I increase the timeout to more than 1000, I get errors on connections every time.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate"></section>
  <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --></configSections>
  <startup> 
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1"/>
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0"/>
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
    </providers>
  </entityFramework>
  <system.transactions>
    <defaultSettings timeout="10:00:00" />
  </system.transactions>
</configuration>

I would really appreciate any help.

Answer 1

First, note that the Timeout connection string parameter manages connection timeouts (i.e. NpgsqlConnection.Open()) rather than command execution timeout. Defaul command execution timeout is managed via the Command Timeout connection string parameter.

Beyond that, it seems like your commands are simply timing out. Since the default command timeout is 30 seconds, it would seem you have some serious performance issue with your queries, your database index, or something else. You need to carefully analyze the SQL being created by NHibernate and how it's executed by PostgreSQL.