ADO.NET 1.1 Performance Guidelines - Stored Procedures

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman


Contents

Use Stored Procedures

Stored procedures generally provide improved performance in comparison to SQL statements that are run directly. The following list explains the benefits of stored procedures compared to running data access logic directly from the middle tier of your application:

  • The database can prepare, optimize, and cache the execution plan so that the execution plan can be reused at a later time.
  • Stored procedures pass less information over the network on the initial request, because they only need to transmit the procedure name and the parameters. Everything else is already at the server.
  • Stored procedures abstract SQL statements from the client and business object developers and put responsibility for their maintenance in the hands of SQL experts.
  • Stored procedures also provide maintenance and security benefits.

References

For more information about the security benefits of stored procedures and about how you can use them as a countermeasure for SQL injection, see the following:


Use CommandType.Text with OleDbCommand

If you use an OleDbCommand object to call a stored procedure, use the CommandType.Text enumeration with the ODBC call syntax. If you use CommandType.StoredProcedure, ODBC call syntax is generated by the provider anyway. By using explicit call syntax, you reduce the work of the provider.

You should also set the type and length of any parameters that the stored procedure requires. Set the type and length of the parameters to prevent the provider from performing an additional round trip to obtain the parameter information from the database. The following code fragment demonstrates how to use ODBC call syntax and CommandType.Text, and how to explicitly set parameter information.

using (OleDbConnection conn = new OleDbConnection(connStr))
{
 OleDbCommand cmd = new OleDbCommand("call CustOrderHist(?)", conn);
 cmd.CommandType = CommandType.Text;
 OleDbParameter param = cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5);
 param.Value = "ALFKI";
 conn.Open();
 OleDbDataReader reader = cmd.ExecuteReader();
 try
 {
   // List each product.
   while (reader.Read())
     Console.WriteLine(reader.GetString(0));
 }
 finally
 {
   reader.Close();
 }
} // Dispose is called on conn here


Use CommandType.StoredProcedure with SqlCommand

If you are using the SqlCommand object, use CommandType.StoredProcedure when you call stored procedures. Do not use CommandType.Text because it requires extra parsing. The following code fragment shows how to set the CommandType property to avoid extra parsing on the server.

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("UpdateCustomerProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(...


Consider Using Command.Prepare

If your application runs the same set of SQL queries multiple times, preparing those queries by using the Command.Prepare method may give you better performance. In ADO.NET, the SqlCommand.Prepare method calls the sp_prepare stored procedure for SQL Server 7. The SqlCommand.Prepare method calls sp_prepexec for SQL Server 2000 and later. SqlCommand.Prepare makes these calls instead of running a regular batch remote procedure call (RPC). The following code fragment shows how to use Command.Prepare.

cmd.CommandText =
  "insert into Region (RegionID, RegionDescription) values (@id, @desc)";

cmd.Parameters.Add ( "@id", SqlDbType.Int, 4, "RegionID") ;
cmd.Parameters.Add ( "@desc", SqlDbType.NChar, 50, "RegionDescription") ;

cmd.Parameters[0].Value = 5;
cmd.Parameters[1].Value = "North West";
cmd.Prepare();
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 6;
cmd.Parameters[1].Value = "North East";
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 7;
cmd.Parameters[1].Value = "South East";
cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 8;
cmd.Parameters[1].Value = "South West";
cmd.ExecuteNonQuery();

Using the Prepare method does not yield a benefit if you are only going to run the statement one or two times. The next version of SQL Server will better leverage how plans are cached, so using it would not make a difference. You should only use the Prepare method for those statements that you run multiple times.


Use Output Parameters Where Possible

Use output parameters and ExecuteNonQuery to return small amounts of data instead of returning a result set that contains a single row. When you use output parameters and ExecuteNonQuery to return small amounts of data, you avoid the performance overhead that is associated with creating the result set on the server.

The following code fragment uses a stored procedure to retrieve the product name and unit price for a specific product that is contained in the Products table in the Northwind database.

void GetProductDetails( int ProductID,
                       out string ProductName, out decimal UnitPrice )
{
 using( SqlConnection conn = new SqlConnection(
       "server=(local);Integrated Security=SSPI;database=Northwind") )
 {
   // Set up the command object used to run the stored procedure.
   SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn );
   cmd.CommandType = CommandType.StoredProcedure;
   // Establish stored procedure parameters.
   //  @ProductID int INPUT
   //  @ProductName nvarchar(40) OUTPUT
   //  @UnitPrice money OUTPUT
   // Must explicitly set the direction of the output parameters.
   SqlParameter paramProdID =
            cmd.Parameters.Add( "@ProductID", ProductID );
   paramProdID.Direction = ParameterDirection.Input;
   SqlParameter paramProdName =
            cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
   paramProdName.Direction = ParameterDirection.Output;
   SqlParameter paramUnitPrice =
            cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
   paramUnitPrice.Direction = ParameterDirection.Output;
   conn.Open();
   // Use ExecuteNonQuery to run the command.
   // Although no rows are returned, any mapped output parameters
   // (and potential return values) are populated
   cmd.ExecuteNonQuery( );
   // Return output parameters from stored procedure.
   ProductName = paramProdName.Value.ToString();
   UnitPrice = (decimal)paramUnitPrice.Value;
 }
}



Consider SET NOCOUNT ON for SQL Server

When you use SET NOCOUNT ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF, the count is returned. Using SET NOCOUNT ON can improve performance because network traffic can be reduced. SET NOCOUNT ON prevents SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure or batch of SQL statements.

For example, if you have eight operations in a stored procedure, eight messages are returned to the caller. Each message contains the number of rows affected by the respective statement. When you use SET NOCOUNT ON, you reduce the processing that SQL Server performs and the size of the response that is sent across the network.

Note In Query Analyzer, the DONE_IN_PROC message is intercepted and displayed as "N rows affected".

Personal tools