Data Access SqlConnection.Open Intermittently Slow

[Jenna Guess]

We are using Telerik Data Access (2016 Q1) in several MVC Web applications.  We are finding that data access is quick for the first 3-4 times an action is attempted (i.e. page load with several DB queries, or page save with DB queries).  We've identified that the SqlConnection.Open is hanging.  It appears that this is called from Telerik.OpenAccess.RT.Adonet2Generic.Impl.DBDRiver.connect(), which is included in Telerik.OpenAccess.Runtime.dll.  Once it makes the connection, the SQL queries are fast and there are no other issues on the page.  We can consistently reproduce this behavior by entering data on a page and saving 3-4 times.  It works fine with no delays, then hangs on SqlConnection.Open, then works fine for a few more saves until it hangs again.  Is there some backend configuration on the Fluent Model that we should set to counteract this?  Thx.

[Jenna Guess]

We've made some progress by adding the below to the web.config.  

<configSections>
  <section name="openAccessConfiguration"
        type="Telerik.OpenAccess.Config.OpenAccessConfigSectionHandler, Telerik.OpenAccess"
        requirePermission="false" />
</configSections>
<openAccessConfiguration>
  <backendConfiguration name="ConnectionPoolConfiguration">
    <connectionPool pool="Integrated"
                    blockWhenFull="false"
                    activeConnectionTimeout="0"
                    reserved="3"
                    maxActive="100">
    </connectionPool>
  </backendConfiguration>
</openAccessConfiguration>

That got me entering data for a while until I got: Telerik.OpenAccess.OpenAccessException The connection pool has reached it's maxActive setting and a new database connection could not be opened. (Connection pool is full: Backend=MsSql;Driver=genericADO2;ConnectionString=data source=####;initial catalog=####;integrated security=True active 97/100 idle 2/10).  I increased maxActive to 200 and went a while longer before getting the same error for active 197/200 idle 2/10.  I've increased again to 300 just to see how far that gets us, but I don't understand why there are so many connections - we have maybe 10 users on this web application.  Does a new DB connection get created for every DB request?  Is there something I need to configure to override that behavior and/or to dispose previously created DB connections?  Thx.

 

 

 

 

[Boris Georgiev]

Hello Jenna,

Every database request will get a new database connection from the connection pool and if there are no connections in the connection pool there will be a slowdown. In your case the problem is when the connections are disposed. When you are using long living context there is a probability that connection to the database is not disposed after a request is done. The connections and transactions are closed when SaveChanges() or ClearChanges() methods are called. If you are using FlushChanges than a transaction and a connection will be open until you closed them. Also if you are querying the database but not obtaining the full result set, but only a part of the full result then the connection will be open until the context is disposed. In this case you should materialize the query through ToList() method before iterating over the result. 

The recommendation approaches are to use short living context, which means dispose the context after request to the database is done, materialize the query result before iterating over the result and to use SaveChanges() method instead FlushChanges().

If these recommendations are not helping, you should give us more information how you are using Telerik Data Access or code sample which demonstrates the issue.

Regards,
Boris Georgiev
Telerik by Progress

 

[Jenna Guess]

Thank you for your reply!  We do not use FlushChanges, we always use SaveChanges and an occasional use of ClearChanges.  Do I need to follow those by a call to Dispose() or are you saying that should be done automatically using SaveChanges/ClearChanges?  Thx.

[Boris Georgiev]

Hello Jenna,

All open connections created and used by OpenAccessContext are closed when the context is disposed. This is why our recommendation is to use a short living context. Also as I wrote in the previous answer, another case where the connection will stay open is when a result set of a query is not materialized in the memory but only a part of the result set, then the connection stays open until the context is disposed or the entire result set is materialized.

If you are using short living context and you are materializing the entire result set in the memory then the problem is something else. In this case, could you send us a code sample which demonstrates the issue?

Regards,
Boris Georgiev
Telerik by Progress

 

[Jenna Guess]

I believe we have resolved the issue by changing the configuration to the following:

<openAccessConfiguration>
  <backendConfiguration name="ConnectionPoolConfiguration">
    <connectionPool pool="Integrated"
                    blockWhenFull="true"
                    activeConnectionTimeout="120"
                    reserved="3"
                    maxActive="300">
    </connectionPool>
  </backendConfiguration>
</openAccessConfiguration>

Previously we had blockWhenFull set to false, and activeConnectionTimeout set to 0.  I believe that initially with all default settings (including maxActive set to 10), we had more than 10 connections being attempted in less than 2 minutes and it was slowing down because it was waiting the 2 minutes for connections to close.  Once we added the configuration, the maxActive number was much higher so it was taking longer to slow down, however once it reached that threshhold it slowed down becuase the activeConnectionTimeout of 0 was not closing any of the open connections.  Now that we have a higher maxActive setting but the default activeConnectionTimeout of 120, it seems to be working fine.  I'm still not entirely sure I understand how this all works and couldn't find a way to debug how the connectionPool was creating connections and disposing of them as that all seems to be buried deep within the Telerik.OpenAccess.dll.  But, for now we're stable.  Thanks for your feedback.