One public/global connection or not?

One public/global connection or not?

• Vester

  Although there are same cases where special handling of connections is useful, I'm going to chime in on the side of only openning the connection when you actually need to do some work on it, and closing it when you are done. From a resource-use point of view, the connection pool should prevent there being overhead from actually connecting again, at least where it really matters, and your app logic is simpler/cleaner/easier to maintain that way. You also avoid having two separate threads trying to use the same connection at the same time, which can cause you serious headaches.
  
  

• Maartin

  im new to this sort of thing, and have had no experience before this year. So my education is VERY lacking in this area.
  But in the application I have just developed with sql server, I have only one connection which is opened when the program is loaded, and closed when the program is closed.

  Again I do not understand any of the internals of how this works, but from my point of view this way seems to have less code involved and would interact with the database less often, therefore increasing performance.

  
  

• Jim Nakashima - MSFT

  Maybe I am confusing the issue -- with Oracle, we have found the following:

  Reusing connection:

  • Creating and opening a connection can take 1-2 seconds (Oracle running on HPUX) ODP.NET 9.2.04
  • Re-using the connection does not have this overhead.
  • On the database, this one session is inactive and takes little resources
  • If I need to create a new thread, create the connection and dispose of it when it is completed.
  • When user exited the application, connection/session is released.

  Open/Close the connection -- no pooling

  • Every call to database takes the overhead of creating the connection
  • Application is very chatty -- Oracle listener takes a beating -- sometimes, with larger application, it can crowd other applications from getting to the listener -- almost like a denial of service.

  Open/Close -- using pooling

  • No overhead in establishing additional connections.
  • 1 - n (number of connections in pool) showing as inactive.
  • Since we did not have a physical middle tier, every user would have 1-n number of connections. (as opposed to one if it was re-used). Each session requires a handle to a unix process - which increases the number of processes on the box -- which are limited based upon kernel parameters.

  I am guessing that with SQL Server, there may be different results. Our application architecture and DBA restrictions made it difficult to use pooling -- although, it would have made things a little easier :-). That is why it is important to evaluate your specific environment constraints and make the appropriate decision.

  .

  
  

• LU MING

  Hi,

  I think it depends upon the type of application, database and infrastructure -- and sometimes, what the DBA recommends. I do not believe there is right way vs. wrong way.

  Personally, for windows applications running on Oracle, I like to keep the connection for the life of the application session for each user. This eliminates the overhead of creating new connections -- less traffic on the TNS Listener. It also make it easier (in oracle) to keep track of what your users are doing. Our Oracle DBAs do not like to have "extraneous" connections out there doing nothing. For web environments, I can see the benefit of connection pools...

  Evaluate your situation and design your app such that you can use both methodologies in your data access layer with little (or no) refactoring.

  Thanks,

  Craig

  
  

• Patdev

  But with respect to web apps and VS 2K5, here's another question. I created a dataset to take advantage of the new TableAdapters. Supposedly, all the connection logic is taken care of. But how Are connections created, used, and closed as needed, created for the lifetime of the application, or something else How do I figure it out

  Thanks.

  Randy

  
  

• petola

  How about if one is using an MS Access database, someone told me it doesn't support pooling, what would be the best strategy to use

  TIA

  
  

• Andibacke

  No, this is never a good idea. A connection is a havy thing and most of the time it will be just there for no reason, it will be idle most of the time.

  You must open a connection as late as posible and close a connection directly when you don't need it any more. Opening and closing a connection will not really close it but just put it in a pool. So when you open a connection for the first time, it will be opened. But when you open it the seconds time it will not be opened but you will just get an open connection from the pool.

  Why do you want a global connetion You only need a connection in you Data Access Layer in N-tier applications, so i think you never need a global connection because when you have a good application design, you need the connection only in 1 layer.

  Hope this is clear enough.

  
  
  

• Philip Belemezov

  Like you heard from the others, i think that have just one connection it’s "logically" correct.

  I ever think about the real things work, and how many connections you need from a application server to a database server... just one, correct

  In otherside, we have the connection pool, that just helps to jump the overhead generated when you are connecting, but i think (but not test) that is still low then a unique connection.

  In otherside again, i had some problems in 2 big projects that i work on using a centralized connection, were 2 webprojects.

  We had some problem about the DataReader’s, cannot be 2 in the same connection, retriving data.

  And we need to think that the Adapter have a "DataReader" inside too, then the problem persist with adapter.

  We do not solve that problem, that is a restriction... we work with pool.. that is the possible to do in our projects.

  []s

  
  

• kp6452

  Here is a link to MSDN that provides a number of resources about connection pooling for all the .NET data providers:

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

  Regards,

  Dave

  
  
  

• TaylorMichaelL

  Thanks for the tips Dave!

  Have you got any 'wonderful' description of connection pooling with MS Access :-)

  
  

• tharkaway

  There are two issues here. One, should you keep a single connection open for the life of the application The answer is an unqualified no. Keeping an open connection is very resource-intensive, and in most applications the percentage of time that you are actually interacting with the database is very small (most of the time is taken up by the user manipulating the data you have provided to them). The second part of the question is whether to keep a single Connection object, which you can open and close as necessary. This is not nearly as clear-cut, by my general answer would still be no. Connection pooling makes the actual construction of the Connection object hardly any more expensive than creating any other reference type. Also, if you have only one connection object, you cannot have more than one transaction active at the same time. In general, I would recommend not creating the connection until the moment you need it, and releasing it the moment you are done.
  
  

• rookie.NET2005

  No one :-)
  

• DesertWells

  ndat

  That approach certainly has less code involved, but it is also a very bad approach. When you think about it, what percentage of the time is your application actually accessing the database In most cases, less than 0.5%. The rest of the time your application is waiting for user input. In this case, you are sitting around with an idle database connection for long periods of time. This is bad, because keeping an open database connection uses system resources. While you might be technically increasing the performance of the parts of your application that access data, it is a very small benefit, and you are decreasing the performance of the rest of your application, and the rest of the system.

  
  
  

• jamierichards

  Although I suspect there may be edge cases and exception to every rule, 99.9% of the time you want to do as exactly as others have mentioned:

  open the connection at the very last possible moment and close the connection as early as possible

  Do not hold an open connection in your application.

  In fact, most of the time you will want to wrap your connection and other classes that implement IDisposable in a using statement such as

  using (SqlConnection connection = new SqlConnection(connectionString))

  {

  connection.Open();

  // do something

  connection.Close();

  }

  The using statement assures that even if an exception is thrown, the connection immediately gets closed, disposed and placed back into the pool ASAP.

  DataAdapters and TableAdapters handle the database connections for you. If the connection you hand them is not open, they will do just as mentioned above: open it at the last possible moment and close it as early as possible. If the connection you hand them happens to be open, they will leave it in that state for you.

  In fact, the best practice is to not open and close the database connection for the DataAdapters and TableAdapters, because it goes against the best practice of opening the database connection as late as possible and closing it as early as possible. Let the DataAdapters and TableAdapters do their own connection management behind the scenes.

  To be clear, it is not the database that is doing the connection pooling but the .NET provider classes or various driver managers ( i.e. ODBC Driver Manager ). So anyone that says Microsoft Access doesn't provide connection pooling is mixing up their technologies. If you are connecting to Microsoft Access via ODBC, the ODBC Driver Manager is responsible for the connection pooling, not Microsoft Access itself. If connection pooling is enabled, you darn well are getting connection pooling to Microsoft Access.

  Here is a wonderful description of connection pooling with SQL Server:

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

  Regards,

  Dave

  
  
  
• One public/global connection or not?