We are implementing a excelsheet based identification of user given records feature in our medium-scale ASP.Net MVC 4 web application. The excelsheet can contain up to 50000 records which have to be identified using stored procedures on the SQL server. Two different stored procedures are relevant for this task, second one is called just in case first one didn't deliver any results. The stored procedures perform rather cost intensive calculations as they cover multiple similarity search scenarios like soundex, old family name, compound first- or lastname etc. One stored procedure call takes an average of 350ms to complete on a single core Xeon E5-2680@2.70GHz, SQL Server 2012 version 11.0.3153.0.
As the identification task can take several hours, we run it
outside of the MVC thread context where each task receives it's own OpenAccess
context (SimpleInjector LiftimeScopeLifestyle).
We were seeing the identification task thread throwing
an OpenAccessException (connection closed) after approx. 9 minutes of
execution (using the same connection id for all stored procedure calls).
Setting the OpenAccess Runtime.CloseScopesWithActiveTransaction to
false seems to prolong the time until the error occurs but it doesn't solve the
issue.
Exception details:
2015-08-01 03:07:06,363 [12] ERROR Reber.Common.Logger.Log`1[[Nareg.Business.Service.IdentificationService, Nareg.Business, Version=1.0.0.0, Culture=neutral, PublicKeyToken=
null
]] [IIS APPPOOL\Nareg] - Error
while
identifying excel records:
Telerik.OpenAccess.OpenAccessException: Ungültige Operation. Die Verbindung ist geschlossen. ---> System.InvalidOperationException: Ungültige Operation. Die Verbindung ist geschlossen.
bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.ConnectionImp.begin()
bei OpenAccessRuntime.Relational.conn.PsPoolConnection.begin()
bei OpenAccessRuntime.Relational.RelationalStorageManager.obtainConnection(Boolean forWriting)
bei OpenAccessRuntime.Relational.RelationalStorageManager.GetDatastoreConnection(ManagedTransaction managedTx, Boolean& isNew)
bei OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.GetConnection(Boolean& isNew)
--- Ende der internen Ausnahmestapelüberwachung ---
bei OpenAccessRuntime.ExceptionWrapper.Throw()
bei OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x, Boolean needsRollback)
bei OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.GetConnection(Boolean& isNew)
bei Telerik.OpenAccess.OpenAccessContextBase.ExecuteQuery[T](String commandText, CommandType commandType, DbParameter[] parameters)
bei Nareg.Data.OpenAccess.Repository.PersonRepository.GetSearchResults(SearchFilter filter, Int32 skip, Int32 take, String orderBy, Boolean findSimilar)
in
c:\Daten\Source\gdk\Nareg\Nareg.Data.OpenAccess\Repository\PersonRepository.cs:Zeile 138.
bei Nareg.Business.Service.SearchService.GetSearchResults[TResult](SearchFilterViewModel filterViewModel, DataSourceRequest request, Int32& totalCount, Boolean findSimilar)
in
c:\Daten\Source\gdk\Nareg\Nareg.Business\Service\SearchService.cs:Zeile 290.
There are no useful hints neither in the eventlog nor the SQL server profiler log for that given time frame, we can just observe the connection Audit Logout. During the time of the context, no save changes are performed.
Is the OpenAccess context closed by some kind of OpenAccess connection-pool cleanup task? Can you explain this?
Are there any best-practices related to batch operations or
database calls using OpenAccess?
Thanks for any hints.