时间：2021-07-01 10:21:17 帮助过：29人阅读
Database connections are an expensive and limited resource. Your approach to connection management can significantly affect the overall performance and scalability of your application. Issues to consider include acquiring and releasing connections, pooling, and authentication. To improve database connection performance and scalability, apply the following strategies to your connection management policy:
Acquire connections late and release them early. Opening connections before they are needed reduces the number of connections that are available and increases resource pressure. Close connections quickly to ensure that they can be reused as soon as possible. Do not hold on to connections. Holding on to connections reduces the connections that are available to other code and increases resource pressure. The general pattern is to open and close connections on a per-method basis.
Explicitly call the Close or Dispose methods on SqlConnection objects as soon as you finish using them to release the resources that they use. Do not wait for the connection to fall out of scope. The connection is not returned to the pool until garbage collection occurs. This delays the reuse of the connection and negatively affects performance and scalability. The following are guidelines to consider. These guidelines are specific to SqlConnectionbecause of the way it is implemented. These guidelines are not universal for all classes that have Close and Dispose functionality.
Note Closing a connection automatically closes any active DataReader objects that are associated with the connection.
The following Visual Basic® .NET code snippet shows how to explicitly close a connection as soon as the connection is no longer needed.
Try
conn.Open()
cmd.ExecuteNonQuery()
customerCount = paramCustCount.Value
Catch ex As Exception
‘ ? handle exception
Finally
‘ This is guaranteed to run regardless of whether an exception occurs
‘ in the Try block.
If Not(conn is Nothing) Then
conn.Close()
End If
End Try
The following example shows how to close connections in C#.
public void DoSomeWork()
{
SqlConnection conn = new SqlConnection(connectionString);
? try
{
conn.Open();
// Do Work
}
catch (Exception e)
{
// Handle and log error
}
finally
{
if(null!=conn)
conn.Close();
}
}
The using statement simplifies code for C# developers by automatically generating a try and finally block when the code is compiled. This ensures that the Dispose method is called even if an exception occurs. The following code fragment shows how to use the using statement.
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
. . .
} // Dispose is automatically called on the conn variable here
The C# compiler converts this code into the following equivalent code, which has a try and finally block to ensure that the Dispose method on the SqlConnection object is called, regardless of whether an exception occurs.
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
}
finally
{
conn.Dispose();
}
One limitation of the using statement is that you can only put a single type in the parentheses. If you want to ensure that Dispose is called on additional resources, you must nest the using statements as shown in the following example.
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand("CustomerRead");
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
Console.WriteLine(dr.GetString(0));
}
}
Note Using a nested using statement on the DataReader object is useful only if you need to perform further operations with the same connection after the inner using block. If you close the connection right away, this approach is of limited value because any active DataReader objects are closed automatically when the connection closes.
When you create a DataReader object, specify the CommandBehavior.CloseConnection enumeration in your call to ExecuteReader. This ensures that when you close the DataReader, the connection is also closed. The following code fragment shows how to use the CommandBehaviorenumeration.
// Create connection and command. Open connection. . . . SqlDataReader myReader= myCommand.ExecuteReader(CommandBehavior.CloseConnection); // read some data . . . myReader.Close(); // The connection and reader are closed.
The CommandBehavior.CloseConnection is especially helpful when you return a DataReader from a function, and you do not have control over the calling code. If the caller forgets to close the connection but closes the reader, both are closed when the DataReader is created by using CommandBehavior.CloseConnection. This is shown in the following code fragment.
public SqlDataReader CustomerRead(int CustomerID)
{
//... create connection and command, open connection
return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
//... client code
SqlDataReader myReader = CustomerRead(10248);
//... read some data
myReader.Close(); // reader and connection are closed
If you perform a single Fill or Update operation, do not open the connection before you call the Fill method, because the DataAdapter automatically opens and closes the connection for you. The following code fragment shows how to call Fill.
DataSet dSet = new DataSet("test");
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sqlQuery,conn);
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
dAdapter.Fill(dSet); // The connection was not explicitly opened.
// The connection is opened and closed by the DataAdapter automatically.
The SqlDataAdapter automatically opens the connection, runs the selected command, and then closes the connection when it is finished. This enables the connection to be open for the shortest period of time.
Note that if you need to perform multiple file or update operations, you need to open the connection before the first Fill or Update method and close it after the last one. Alternatively, you could wrap multiple Fill or Update operations inside a C# using block to ensure that the connection is closed after the last use.
If you need to monitor or check connection status and you are using an OleDbConnection, consider handling the StateChange event, and avoid checking the State property. This approach helps to minimize round trips.
Using the State property increases application overhead, because each call results in a call to the OLE DB DBPROP_CONNECTIONSTATUS property (if the connection is an OleDbConnection) for an open connection.
Note The .NET Framework 2.0 (code named "Whidbey"), at the time of writing, provides an updated OLE DB .NET Data Provider that resolves this problem.
The following code fragment shows how to implement the StateChange event. This event is raised when the state of the connection changes from open to closed or from closed to open.
OleDbConnection conn = new OleDbConnection(connStr);
// Set up a connection state change handler.
conn.StateChange += new StateChangeEventHandler(OnStateChange);
. . .
// StateChange event handler.
protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
Console.WriteLine("The current Connection state has changed from {0} to {1}.",
args.OriginalState, args.CurrentState);
}
Note The ODBC provider also incurs similar overhead when using the State property.
Creating database connections is expensive. You reduce overhead by pooling your database connections. Make sure you call Close or Dispose on a connection as soon as possible. When pooling is enabled, calling Close or Dispose returns the connection to the pool instead of closing the underlying database connection.
You must account for the following issues when pooling is part of your design:
The following list details the pooling mechanisms that are available, and it summarizes pooling behavior for the .NET Framework data providers:
You can monitor connection pooling to determine that it is working as expected and to help you identify the best minimum and maximum pool sizes.
Monitoring Pooling on a Computer that is Running SQL Server
You can monitor the number of open connections to SQL Server by using the SQL Server SQLServer:General Statistics performance counter object. This object is available only on a computer that is running SQL Server.
The connections are not specific to one particular application. If there are multiple applications accessing the server, this object reflects the total number of open connections for every application. Figure 12.2 shows the SQLServer:General Statistics object in the Performance Monitor tool.
Figure 12.2: Performance monitor showing the SQLServer:General Statistics counter
When monitoring SQLServer:General Statistics, you should observe the following:
Monitoring Pooling Using the .NET Framework
The .NET Framework Data Provider for SQL Server provides several counters. The following counters are of particular significance:
The SqlClient: Current # connection pools counter indicates the number of connection pools that are currently in use. A large number of pools indicates that a pool is not being shared across clients. Using different connection strings creates new pools.
The SqlClient: Peak # pooled connections counter indicates the maximum number of connections that are currently in use. If this value remains at its peak, consider measuring the performance impact of increasing the Max Pool Size attribute in your connection string. The default value is 100. If you see this value at its peak in conjunction with a high number of failed connections in the SqlClient: Total # failed connects counter, consider changing the value and monitoring performance.
Note These SqlClient counters may not be reset in .NET Framework version 1.1 when you stop and then restart an application. To reset the counters, stop the application and exit System Monitor, and then start the application and System Monitor again.
For more information about pooling connections, see the following resources on MSDN:
For more information about pooling connections, see the following Knowledge Base articles:
For more information about how to reset the .NET counters, see Knowledge Base article 314429, "BUG: Performance Counters for SQL Server .NET Data Provider Are Not Reset," at http://support.microsoft.com/default.aspx?scid=kb;en-us;314429.
关于ADO.Net SqlConnection的性能优化
标签：select cep nts handler sts iso cti file sid