0

This is a follow up question to: Is it necessary to deconstruct singleton sql connections?

As a few comments there stated it is bad design to use a singleton for sql connection instead of doing multiple usings.

What intrigues me there though is one statement that the performance of the using variant is better than that of the singleton variant. Now as stated by me that it is a bad design is clear to me (I know most pros and cons for singletons there...especially the cons). What surprised me though was the performance statement.

As normally I would think: Ok opening and closing sql connections for 100-1000 times during a programs run SHOULD be less performant than doing this only once. Thus my question is: Is the performance of the non singleton variant really better and if so why?

Singletonexample:

public class SimpleClass
{
    // Static variable that must be initialized at run time. 
    public static SqlConnection singletonConnection;

    // Static constructor is called at most one time, before any 
    // instance constructor is invoked or member is accessed. 
    static SimpleClass()
    {
        singletonConnection = new SqlConnection("Data Source.....");
    }
}

Usings example:

using (SqlConnection connection = new SqlConnection("Data ...")) 
{
  ....
}
Community
  • 1
  • 1
Thomas
  • 2,886
  • 3
  • 34
  • 78
  • What type of performance? Speed, memory usage, etc... I think the users in the other question are absolutely correct. It's true opening a connection once is faster, but not worth the risk of potential memory leaks. Also, keeping the connection open prevents all other access to the DB. This is not generally a favorable situation. – Cory Sep 18 '15 at 06:55

1 Answers1

2

Basically the answer is simple:

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on. In practice, most applications use only one or a few different configurations for connections.

This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

You shouldn't use singleton as some kind of 'performance accelerator' because it is not what it is used for. By using it to store one static SQL connection you are exposing yourself for many memory and connection problems. How you are supposed to close connection? How are you supposed to release memory consumed? When one connection is closed, you are closing it for all application users. How you are planning to reconnect with that approach?

What "connection pooling" basically means is that even if you are creating many SqlConnection objects, as long as they do not differ with connection string, it is possible to reuse existing connection.

Some detailed info can be found there.

Community
  • 1
  • 1
kamil-mrzyglod
  • 4,948
  • 1
  • 20
  • 29
  • so in essence ADO.NET itself already optimizes the multi connection part and thus reduces the openings and closings that are REALLY done? – Thomas Sep 18 '15 at 07:01
  • Wouldn't have guessed that ADO.NET is using such optimizations. Quite a nice surprise there. Tnx. So in essence even IF singletons would bring a performance increase, that advantage it is as good as optimized away by ADO.NET (as it optimizes the non singleton variants). Thus no real advantage for singletons and only disadvantages. Thank! – Thomas Sep 18 '15 at 07:05
  • You can think about it as semi-singletons - ADO.NET will create only as many connections as it needs. – kamil-mrzyglod Sep 18 '15 at 07:07
  • That is exactly how I interpreted your post and comment. – Thomas Sep 18 '15 at 07:18