Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Improving Performance with Connection Pooling

Posted by Ravi Varma Thumati on September 22, 2009

Database connections are precious resources. If you want your ASP.NET application to scale to handle the demands of thousands of users, then you need to do everything in your power to prevent database connections from being wasted.

Opening a database connection is a slow operation. Rather than open a new database connection each time you need to connect to a database, you can create a pool of connections that can be reused for multiple database queries.

When connection pooling is enabled, closing a connection does not really close the connection to the database server. Instead, closing the connection releases the database connection back into the pool. That way, the next time a database query is performed, a new connection to the database does not need to be opened.

When you use the SqlConnection object, connection pooling is enabled by default. By default, the ADO.NET framework keeps a maximum of 100 connections opened in a connection pool.

You need to be warned about two things in regard to connection pooling. First, when taking advantage of connection pooling, it is still very important to close your connections by calling the SqlConnection.Close() method. If you don’t close a connection, the connection is not returned to the pool. It might take a very long time for an unclosed connection to be reclaimed by ADO.NET.

Second, different connection pools are created for different connection strings. In particular, a different connection pool is created for each unique combination of connection string, process, application domain, and Windows identity.

An exact character-by-character match is performed on the connection string. For this reason, you should always store your connection strings in the web configuration file. Don’t hardcode connection strings inside your components. If there is a slight variation between two connection strings, then separate connection pools are created, which defeats the performance gains that you get from connection pooling.

The SqlConnection object supports two methods for clearing connection pools programmatically:

  • ClearAllPools Enables you to clear all database connections from all connection pools.
  • ClearPool Enables you to clear all database connections associated with a particular SqlConnection object.

These methods are useful when you are working with a cluster of database servers. For example, if you take a database server down, you can programmatically clear the connection pool to the database server that no longer exists.

You can control how connections are pooled by using the following attributes in a connection string:

  • Connection Timeout Enables you to specify the maximum lifetime of a connection in seconds. (The default value is 0, which indicates that connections are immortal.)
  • Connection Reset Enables you to reset connections automatically when retrieved from the connection pool (default value is true).
  • Enlist Enables you to enlist a connection in the current transaction context (default value is TRue).
  • Load Balance Timeout Same as Connection Timeout.
  • Max Pool Size Enables you to specify the maximum number of connections kept in the connection pool (default value is 100).
  • Min Pool Size Enables you to specify the minimum number of connections kept in the connection pool (default value is 0).
  • Pooling Enables you to turn on or off connection pooling (default value is true).

The page in Listing 16.6 displays a list of all the current user connections to a database in a GridView. Notice that the connection string used when connecting to the database creates a minimum connection pool size of 10 connections. (You’ll have to refresh the page at least once to see the 10 connections.)

Listing 16.6. ShowUserConnections.aspx
<%@ Page %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
    Private Sub Page_Load()
        Dim connectionString As String = "Min Pool Size=10; Data Source=.
   

  \SQLExpress;Integrated Security=True; AttachDbFileName=|DataDirectory|MyDatabase.mdf;User 

  Instance=True"
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand("SELECT * FROM master..sysprocesses WHERE hostname<>''",
   
   con)
        Using con
            con.Open()
            grdStats.DataSource = cmd.ExecuteReader()
            grdStats.DataBind()
        End Using
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <style>
        td,th
        {
            padding:2px;
        }
    </style>
    <title>Show User Connections</title>
</head>
<body>
    <form runat="server">
    <div>
 
    <h1>User Connections</h1>
 
    <asp:GridView Runat="server" />
 
    </div>
    </form>
</body>

</html>

Using the Command Object

The Command object represents a command that can be executed against a data source. In this section, you learn how to use the SqlCommand object to execute different types of database commands against Microsoft SQL Server.

Executing a Command

You can use the SqlCommand.ExecuteNonQuery() method to execute a SQL command that does not return a set of rows. You can use this method when executing SQL UPDATE, DELETE, and INSERT commands. You can also use this method when executing more specialized commands, such as a CREATE TABLE or DROP DATABASE command.

For example, the component in above includes Update () and Delete () methods that update and delete movie records.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: