User Connection ratio for application RRS feed

  • Question

  • I am designing a web application and using some underlying reader classes. I see the number of connections to database are much more than users, may be 5-6 times. I think the number of user and connections should be same or almost same. I ran some vanilla code with out underlying reader class and found out that number of user and connections are same(almost).

    Underlying reader class use type data set and extend reader class for type dataset for crud operations.

    I am not creating any thread or starting another connection and try to use the same connection and in fact uses same connection string.


    Friday, June 16, 2006 2:45 PM

All replies

  • You are probably using connection polling, if I am not mistaken the first hit to the DB opens as many connections as defined in the poll.

    You can run your applicaiton in debug mode while having the SQL profiler open and see what happens on each of the lines that interacts with the DB



    Saturday, June 17, 2006 1:26 PM
  • I see multiple connections are created on 2nd call which creates second connection. When I use enterprise library , I see only one connection. My other test extends data reader and I see multiple connections. Should there be only one connection per user? what triggers multiple connections?
    Monday, June 19, 2006 2:49 PM
  • I'm not sure why you're seeing multiple connections per user, but the goal of your architecture should concentrate more on the length of time each connection is being used for.

    While it's certainly important to minimize the number of connections to your DB, minimizing the time required for each connection will likely have a greater overall impact on your application's performance.

    The reason for this is ADO.NET's aggresive use of connection pooling. By default there are a maximum 20 connections in the pool. As long as your total number of simultaneous connections is not greater than 20 then the connections being used are grabbed from the pool and the cost to utilize that connection is almost nothing.

    You can easily increase the number of connections held in the pool by modifying your connection string. (See documentation.)

    So even if a single request causes 5 connections to the server, as long as the length of time that any one connection is actively open (not available in the pool), then you're probably OK.

    One thing you should definitly check on is to make sure your connections are always opened within a using statement. Using statements will ensure that even if your application raises an exception while the connection is open it will always be closed (returned to the pool) before your request is complete. This is very important, otherwise there could be connection leaks.

    That said, you should use SQL Profiler to see exactly what those connections are executing in terms of SQL. That should help you identify what's going on.
    Tuesday, June 20, 2006 3:26 AM
  • Thanks for your help. I am right now testing in debug with one user using enterprise library. The app creates first connection at first open statement DatabaseFactory.CreateDatabase();

    Now I try to read another table and update the row. at next create database command another connection is created and soon there are 6 connections.

    Do every user creates six connections (common connection string for all users)? or it will stop after max connections specified in connection string?

    I saw a blog for DAAB 2.0 connection manager extension. Is that something which can help in reducing the number of connections and hence performance?




    Tuesday, June 20, 2006 12:40 PM
  • It will stop and wait for connections to become available from the connection pool if your pool limit is reached.

    There is a timeout for this (typically 60 seconds), after which an exception will be thrown by the .Open method of your SqlConnection instance.
    Tuesday, June 20, 2006 9:58 PM
  • Make sure you are explicitly releasing the connections to the free pool, either with a Close or Dispose call. Otherwise, the connections will appear to be  "in use" longer then they should be, and the connection pool will have to open new connections instead of reusing existing connections.


    Wednesday, June 21, 2006 3:31 AM
  • Connections are created based on default pool size; unless, specified in connection string and increments based on increment pool specified.
    Monday, July 10, 2006 8:39 PM