This is a migrated thread and some comments may be shown as answers.

Long running batch database calls using open access

1 Answer 102 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
RIT
Top achievements
Rank 1
RIT asked on 03 Aug 2015, 09:27 AM

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.

1 Answer, 1 is accepted

Sort by
0
Thomas
Telerik team
answered on 04 Aug 2015, 10:09 AM
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:

http://docs.telerik.com/data-access/feature-reference/api/context-api/backend-configuration/feature-ref-api-context-api-backend-config-connectionpool-config

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.
Regards,
Thomas
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Tags
Development (API, general questions)
Asked by
RIT
Top achievements
Rank 1
Answers by
Thomas
Telerik team
Share this question
or