Long running batch database calls using open access

Thread is closed for posting
2 posts, 0 answers
  1. RIT
    RIT avatar
    21 posts
    Member since:
    Apr 2015

    Posted 03 Aug 2015 Link to this post

    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=, 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.

  2. Thomas
    Thomas avatar
    590 posts

    Posted 04 Aug 2015 Link to this post

    Hi Florian,

    the principal idea of OpenAccess closing connections is to prevent long running activities blocking the database server and potentially locking to many resources there. In your case however, you wish to perform those long running actions. I would therefore recommend to use the ADO connection pool:


    Please use the link above to configure your application so that it uses 
    config.ConnectionPool.Pool = ConnectionPoolType.ADO
    Then OpenAccess will not attempt to close connections actively. If the issue persists, then there must be another reason why the connection is no longer active and open. (I'm not sure what IIS can and will do.)

    Please get back to us when this is not solving the issue.
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top