ADO .NET- Usando o pool de conexões

Nos dias atuais a grande maioria das aplicações acessa um banco de dados relacional, e, efetuar a conexão com um banco de dados geralmente envolve várias etapas que consomem tempo. Assim, um canal físico como um socket deve ser estabelecido, o handshake inicial com o servidor deve ocorrer, a seqüência de conexão informação deve ser analisada, a ligação deve ser autenticado pelo servidor, o controle deve ser executado para a mobilização na transação atual , e assim por diante.

Na prática, a maioria dos aplicativos usam apenas uma ou algumas configurações diferentes para realizar tais conexões. Isto significa que durante a execução do aplicativo, muitas conexões idênticas serão repetidamente abertas e fechadas. Para minimizar o custo de abrir e fechar conexões, o ADO. NET usa uma técnica de otimização chamada: Pool de conexão.

Um Pool de conexões reduz o número de vezes que serão necessárias a abertura de novas conexões. O gerenciador do Pool controla conexões mantendo ativas um conjunto de conexões para cada configuração de conexão usada. Sempre que um usuário faz uma chamada para abrir uma conexão, o gerenciador procura verificar se existe uma conexão disponível no pool; se existir uma conexão ativa no pool de conexão, ele devolve a conexão para o chamador em vez de abrir uma nova conexão economizando assim recursos.

Da mesma forma quando o aplicativo chama o método Close para fechar a conexão, o gerenciador devolve a conexão para o conjunto de conexões ativas no Pool de conexões em vez de fechá-la realmente e, quando a ligação é retornada ao pool, ele está pronta para ser reutilizada na próxima chamada de abertura.

O provedores para SQL Server e Oracle encapsulam a funcionalidade de connection-pooling. Um pool de conexões existe para cada string de conexão especificada quando você abre uma nova conexão. A cada vez que você abre uma nova conexão com uma string de conexão que foi usada anteriormente, a conexão é tomada do Pool. Somente se você especificar uma string de conexão diferente o provedor irá criar um novo pool de conexões.

Uma vez criado, um Pool de conexões não é destruído até que o processo ativa seja encerrado ou que o tempo de vida (lifetime) da conexão seja excedido.

Pensando nisso você pode controlar algumas características do seu Pool de conexões usando as configurações para a string de conexão exibidas a seguir.

Parâmetros de configuração da string de conexão que controle o Pool de conexões:

Parâmetro de Configuração Descrição
Connection Lifetime Define o tempo máximo em segundos que uma conexão pode existir no Pool antes de ser fechada.
O tempo da conexão é verificado somente quando a conexão é retornada ao Pool. Esta definição é útil
para minimizar o tamanho do Pool se o mesmo não for usado com frequência e também garante um bom
balanceamento de carga. O valor padrão é 0, o que significa que as conexões do Pool permanecem por
todo o processo atual.
Connection Reset Suportado somente pelo provedor do SQL Server, e, define se as conexões são resetadas assim que forem
tomadas do Pool. O valor padrão (True) garante que o estado da conexão será resetado mas requer uma
comunicação com o banco de dados.
Max Pool Size Define o número máximo de conexão que estarão no Pool. As conexões são criadas e incluídas no Pool
até atingir este valor, se uma requisição para uma conexão for feita e não houver conexão disponível
o chamador será bloqueado até que uma conexão esteja disponível até ocorrer o timeout. O valor padrão é 100.
Min Pool Size Define o número mínimo de conexões que existirão no Pool.
Na criação do Pool este número é criado e definido e durante o período de manutenção ou quando uma
conexão for requisitada as conexões são incluídas no Pool para garantir o número mínimo de conexões
disponíveis. O valor padrão é 10.
Pooling O valor padrão é True. Para não ter uma conexão controlada pelo Pool defina o valor para False.

Agora vamos mostrar alguns exemplos usando código para tornar mais claros os conceitos abordados:

Abaixo vemos um exemplo mostrando que a cada string de conexão diferente será criado um novo Pool de conexões e a cada vez que uma nova conexão for aberta com uma string de conexão que foi usada anteriormente, a conexão é tomada do Pool.

Assim no exemplo abaixo em C#, como temos duas strings de conexões distintas serão criados dois Pools de conexão:

using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=Northwind"))
    {
        connection.Open();  //O Pool A é criado.
    }

using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=pubs"))
    {
        connection.Open();   // O Pool B é criado pois a string de conexão mudou.
    }

using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=Northwind"))
    {
        connection.Open();  // A conexão é gerenciada pelo Pool A.
    }

Vejamos outro exemplo em VB .NET com o Oracle:

Dim Factory As DbProviderFactory = DbProviderFactories.GetFactory("Macoratti.Oracle") 
Dim Conn1 As DbConnection = Factory.CreateConnection() 
Conn1.ConnectionString = "Host=Teste;User ID=Macoratti;Password=1234; " & "Service Name=ORCL;Min Pool Size=50" 

Conn1.Open()  ' O Pool A é criado e preenchido com número minimo de conexões definido 

Dim Conn2 As DbConnection = Factory.CreateConnection() 
Conn2.ConnectionString = "Host=Teste;User ID=Carlos;Password=5678; " & "Service Name=ORCL;Min Pool Size=100" 

Conn2.Open()  ' O Pool B é criado pois a string de conexão usada é diferente

Dim Conn3 As DbConnection = Factory.CreateConnection() 
Conn3.ConnectionString = "Host=Teste;User ID=Macoratti;Password=1234; " & "Service Name=ORCL;Min Pool Size=50" 

Conn3.Open()  ' A conexão Conn3 é obtida do Pool A

Vejamos outro exemplo que demonstra a configuração de um pool de conexões que contém um mínimo de 5 e um máximo de 15 conexões e onde as conexões expiram depois de 10 minutos (600 segundos) e são resetadas a cada vez que a conexão é obtida do Pool.

Este exemplo mostra também como usar a configuração do Pool para obter um objeto Connection que não esta no Pool (Pooling=False) , isto é útil se sua aplicação usa uma única conexão por um longo período com o banco de dados.

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Obtendo uma conexão do Pool
        Using con As New SqlConnection
            ' Configura a string de conexão para o objeto SqlConnection
            con.ConnectionString = "Data Source=.\sqlexpress;Database=Northwind" & _
            ";Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=15;" & _
            "Connection Reset=True;Connection Lifetime=600;"
            'Abre a conexão
            con.Open()
            ' Acessa o banco de dados 
            ' Fecha a conexão
            ' A conexão retorna ao Pool para ser reusada
            con.Close()
            ' No final do bloco using, o Dispose chama o método Close
            ' o qual retorna a conexão ao Pool
        End Using
        ' Obtendo uma conexão fora do Pool
        Using con As New SqlConnection
            ' Configura a string de conexão para o objeto SqlConnection
            con.ConnectionString = "Data Source=.\sqlexpress;Database=" & _
            "Northwind;Integrated Security=SSPI;Pooling=False;"
            ' Abre a conexão
            con.Open()
            ' Acessa o banco de dados
            ' Fecha a conexão
            con.Close()
        End Using
        MsgBox("Operação completa")
    End Sub

Lembrando que embora os provedores ODBC e OLEDB suportem o Pool de conexões eles não efetuam a implementação com as classes do ambiente gerenciado da plataforma .NET e a configuração do Pool não é feita da mesma forma que os provedores SQL Server e Oracle.

O Pool de conexões para ODBC é gerenciado pelo ODBC Driver Manager e configurado pela ferramenta ODBC Data Source Administrator no painel de controle; já o Pool de conexões para OLE DB é gerenciado pela implementação nativa OLE DB.

O provedor para SQL Server CE não suporta o Pool de conexões pois o SQL Server CE suporta apenas uma única conexão concorrente.

Fechando corretamente as conexões

O gerenciador do Pool de conexões periodicamente procura por conexões não usadas que não foram fechadas pelo método Close ou Dispose para reativá-las. Se sua aplicação não fechar as conexões de forma explicita usando Close ou Dispose, pode causar uma certa demora para usar a conexão pois ela terá que ser ativada, por isso tenha certeza de sempre fechar as conexões. Além disso o gerenciador remove a conexão do Pool depois de um tempo que ela não for usada.

No código abaixo a conexão pode não ser fechada se ocorrer uma exceção na execução do método teste();

     SqlConnection conn = new SqlConnection(myConnectionString);
     conn.Open();
     teste();
     conn.Close();                 
Dim conn As New SqlConnection(myConnectionString) 
conn.Open() 
teste() 
conn.Close()
C# VB .NET

A forma correta de fechar a conexão seria usar o seguinte código:

      SqlConnection conn = new SqlConnection(myConnectionString);
      try
      {
            conn.Open();
            teste();
      }
      finally 
      {
            conn.Close();
      }
 
Dim conn As New SqlConnection(myConnectionString) 
Try 
    conn.Open() 
    teste() 
Finally 
    conn.Close() 
End Try
C# VB .NET

ou ainda:

     using (SqlConnection conn = new SqlConnection(myConnectionString))
      {
            conn.Open();
            teste();
      }		 
Using conn As New SqlConnection(myConnectionString) 
    conn.Open() 
    teste() 
End Using
C# VB .NET

Observe que no primeiro exemplo chamamos o método Close explicitamente enquanto no segundo fazemos com que o compilador gere um chamada implícita a conn.Dispose(). O bloco using garante que o método Dispose seja chamado imediatamente após o término do bloco.

De forma geral feche sempre as suas conexões e tenha por base o seguinte lema sobre conexões : "Seja tardio para abrí-las e rápido para fechá-las"

A partir da versão 2.0 da ADO .NET existem dois novos métodos para limpar o Pool de conexões:

Se houver conexões em uso no momento da chamada desses métodos eles são marcados de forma apropriada e quando elas forem fechadas serão descartadas e não retornarão ao Pool.

Para encerrar vejamos mais um exemplo com uma explicação de como funciona a utilização do Pool de conexões:

Vamos criar um novo projeto do tipo Windows Forms no Visual Basic 2008 Express Edition com o nome poolConexoes e no formulário padrão vamos incluir um botão de comando Button(name=btnIniciar) e um controle ListBox(name=lstbPool) conforme o leiaute abaixo:

Primeiro vamos declarar o namespaces System.Data.SqlClient :

Imports System.Data.SqlClient

A seguir definimos a string de conexão onde definimos os valores mínimo e máximo para o Pool de conexões:

Const connString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\dados\Cadastro.mdf;" & _
                                           "Integrated Security=True;" & _
                                           "Connect Timeout=30;" & _ 
                                            "User Instance=True;" & _ 
                                            "Min Pool Size=3;" & _ 
                                            "Max Pool Size=3"

 

Vamos definir o código abaixo associado ao evento Click do botão Iniciar:

Private Sub btnIniciar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIniciar.Click

While True
    exibirData()
   MsgBox("Pressione [Enter] para continuar...")
End While

End Sub

A rotina exibirData() é data abaixo:

Private Sub exibirData()

        Dim dr As SqlDataReader = Nothing

        Dim cmdTexto As String = "SELECT date=getdate()"
        Dim cn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(cmdTexto, cn)

        Try
            cn.Open()
            dr = cmd.ExecuteReader()
            If dr IsNot Nothing Then
                While dr.Read()
                    lstbPool.Items.Add(dr("date").ToString())
                End While
            End If
        Finally
            If dr IsNot Nothing Then
                dr.Close()
            End If
            cn.Close()
        End Try
    End Sub

Executando o projeto iremos obter o resultado abaixo:

Não há nada de mais neste código, ele apenas exibe no controle ListBox a cada Enter a data e hora do SQL Server.

Vejamos o que acontece a cada vez que a conexão  é aberta pelo comando :   cn.Open() 

1- O gerenciador do Pool(Data Provider) instancia uma classe chamada SqlConnectionPoolManager e invoca o seu método GetPooledConnection passando para ele a string de conexão;

2- O gerenciado do Pool(Data Provider) examina todos os Pools existentes verificando se existe um Pool que use a mesma string de conexão;

3- No exemplo, não existe, e então ele cria um novo objeto ConnectionPool passando a string de conexão como identificador único do Pool;

4- A seguir ele alimenta o objeto ConnectionPool com 3 conexões , pois definimos que o número de conexões mínimas é igual a 3 (Min Pool Size=3)

5- Quando o ExecuteReader é invocado, a instância de SqlConnection usa uma das conexões do Pool para executar a consulta e obter a data e hora;

Obs: Você pode usar o Query Analizer e o SQL Server Profiler bem como as procedures sp_who ou sp_who2 para monitorar as conexões do Pool.

Dessa forma temos uma visão geral básica de como funciona um Pool de conexões e de como podemos usá-lo em nossas aplicações. A utilização correta do Pool de conexões pode incrementar o desempenho da sua aplicação

Eu sei é apenas ADO .NET, mas eu gosto...

Referências:

José Carlos Macoratti