Nhibernate ver. 1, command-timeout not working

0
votes

We are using Castle Windsor with Nhibernate and we are having issues with command-timeouts. Basically this should be a straight forward issue to resolve but somehow Nhibernate ignores the property from the configuration file.

This is the Nhibernate configuration:

<factory id="nhibernate.factory">
            <settings>
                <item key="hibernate.command_timeout">3000</item>
                <item key="hibernate.show_sql">false</item>
                <item key="hibernate.connection.provider">NHibernate.Connection.DriverConnectionProvider</item>
                <item key="hibernate.connection.driver_class">NHibernate.Driver.SqlClientDriver</item>
                <item key="hibernate.connection.connection_string">#{coreDevConnectionString}</item>
                <item key="hibernate.dialect">NHibernate.Dialect.MsSql2000Dialect</item>
                <!--<item key="hibernate.cache.use_query_cache">true</item>-->
            </settings>
            <assemblies>
                <assembly>Midas.Persistence</assembly>
                <assembly>Midas.DbiCore</assembly>
            </assemblies

The timeouts is only 30 seconds. Here is a code example used:

Dim session As ISession = sessionManager.OpenSession()
Dim result As T
result = CType(session.Load(GetType(T), id), T)
session.Close()
setDirty(Of T)(result, False)
Return (result)

I have tried to set the property "hibernate.command_timeout" and "command_timeout" as well both nothing seems to be working.

I am getting an Nhibernate.AdoException which contains the following error message:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is the StackTrace: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Midas.Persistence.ScoreInformationMapper.Calculate(Int64 companyID, Int64 ModelID, Boolean ForceRecalculate, String& errorMessage, User user, InformationSource informationSource) at Midas.BusinessLogic.ScoreInformationController.CalculateScore2017(Int32 companyId, Int32 modelId, Boolean ForceRecalculate, User user, InformationSource informationSource) at Midas.BusinessLogic.ScoreInformationController.ReCalculateScore(ScoreInformation scoreInformation, User user, InformationSource informationSource, Boolean ForceCalculation, Boolean useRaiseEvent) at Midas.BusinessLogic.ScoreInformationController.AnyInformationUpdated(Object modifiedInformation, CompanyId companyId, User user, InformationSource informationSource) at Midas.BusinessLogic.ScoreInformationController.OwnershipInformationUpdated(Object sender, InstancePersistedEventArgs1 e) at System.EventHandler1.Invoke(Object sender, TEventArgs e) at Midas.BusinessLogic.OwnershipInformationController.Save(OwnershipInformation instance, User user, InformationSource informationSource) at Midas.Import.RegtidEjerregisterImportController.ProcessRow(DataRow[] sourceRows, User user, InformationSource informationSource, IImportMessageBuilder importMessageBuilder) at Midas.Import.ImportManager.ExecuteImport(IRowImportController rowImportController, SourceDataProviderConfig sourceDataProviderConfig, String informationSourceShortName)

1
nhibernateado.netcastle-windsor
Is the server able to respond? Can you connect to it from SSMS? 30 seconds is a lot. What is the full exception, including its call stack? You can get this with Exception.ToString(). This will show whether this was caused by a connection timeout (meaning you can't connect to the server at all). A command timeout would mean that the query is very expensive. 30 seconds for an ORM query is way too long. – Panagiotis Kanavos
The reason for the timeout is because of locks. The stacktrace clearly indicates a timeout and not a connection timeout. – JohnJackson
Post it here, don't read it aloud. In any case, if the problem is locks, you should fix your query, not cover it up with a larger timeout. Why is it slow? What is the execution plan? What is the actual query? Are there any missing indexes? You can get 300x (not 300%, 300%) times better performance with the proper index – Panagiotis Kanavos
If the query is good but processes a lot of data you are using the wrong tool altogether. All ORMs, not just NH are unsuitable for reporting queries. There are no entities with behaviour to return and map to, just DTOs. The queries they generate are not going to perform well in such cases. When you have a lot of data, you want to stream it, not munch it all in one go. – Panagiotis Kanavos
I have added the StackTrace. I will have a look at the indexes. – JohnJackson

1 Answers