ADO.NET 2.0 Performance Guidelines - Commands

From Guidance Share
Jump to navigationJump to search

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


Validate SQL Input and Use Parameter Objects

Validate all the input data that you use in SQL commands. Do not permit the client to retrieve more data than it should. Also, do not trust user input, and do not permit the client to perform operations that it should not perform. Doing so helps to lower the risk of SQL injection. By rejecting invalid data early before you issue a command that has the invalid data, you can improve performance by eliminating unnecessary database requests.

Use Parameter objects when you build database commands. When you use Parameter objects, each parameter is automatically type checked. Checking the type is another effective countermeasure you can use to help prevent SQL injection. Ideally, use Parameter objects in conjunction with stored procedures to improve performance. For more information about using parameters, see "Parameters" later in this chapter.


Using Parameters with Stored Procedures

The following code sample illustrates how to use the Parameters collection. 

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
                      "@au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In the code sample, the @au_id parameter is treated as a literal value and not as code that can be run. Also, the parameter is checked for type and length. In the code fragment, the input value cannot be longer than 11 characters. If the data does not conform to the type or length that is defined by the parameter, an exception is generated.

Using stored procedures alone does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures may be susceptible to SQL injection if the stored procedures use unfiltered input. For example, the following code fragment is susceptible to SQL injection. 

SqlDataAdapter myCommand = new SqlDataAdapter ("LoginStoredProcedure '" +
           Login.Text + "'", conn);


Using Parameters with Dynamic SQL

If you cannot use stored procedures, you can still use parameters with dynamic SQL as shown in the following code fragment. 

SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn);
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
                       SqlDbType.VarChar, 11);
parm.Value = Login.Text;



Retrieve Only the Columns and Rows You Need

Reduce unnecessary processing and network traffic by retrieving only the columns and the rows you need. Do not use the SELECT * query. This is poor practice because you might not know the schema, or it might change. It is easy to retrieve more data than you expect. Consider a scenario where you want four columns, but you perform an operation by using the SELECT * query on a 400-column table. In that scenario, you receive many more results than you expect. Instead, use WHERE clauses to filter the rows.

References


Support Paging Over Large Result Sets

If you have a large result set that contains many rows of data, consider whether you can implement a paging technique to batch the retrieval of data. Batching the retrieval of data helps to reduce database server load, to reduce network traffic, and to put fewer memory requirements on the data access client. For more information, see "Paging Records" later in this chapter.


Batch SQL Statements to Reduce Round Trips

Batching is the process of grouping several SQL statements in one trip to the server. The syntax in the following code fragment calls a stored procedure (that groups several queries) to return multiple result sets. The code uses the NextResult method of the DataReader object to advance to the next result set. NextResult can be called multiple times, and it returns true when another result set exists. It returns false when there are no more result sets. 

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "ReadCustomerAndOrders";
// The stored procedure returns multiple result sets.
SqlDataReader myReader = cmd.ExecuteReader();

if (myReader.read())
//... read first result set 

reader.NextResult(); 

if (myReader.read())
//... read

If you build complex SQL strings dynamically, you can use a StringBuilder object to reduce the performance cost of building the strings.


References


Use ExecuteNonQuery for Commands That Do Not Return Data

If you want to run commands that do not retrieve data, use the ExecuteNonQuery method. For example, you would use ExecuteNonQuery for the following types of commands:

  • Data Definition Language commands such as CREATE and ALTER
  • Data Modification Language commands such as INSERT, UPDATE, and DELETE
  • Data Control Language commands such as GRANT and REVOKE.

The following code fragment shows an update to the customer table that uses ExecuteNonQuery. 

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(
   "UPDATE Customer SET Freight = 45.44 WHERE CustomerID = 10248", conn);
cmd.ExecuteNonQuery();



Use ExecuteScalar to Return Single Values

If you want to retrieve a single value from your query by using a function such as COUNT(*) or SUM(Price), you can use a stored procedure output parameter, and then use the Command.ExecuteNonQuery method. This eliminates the overhead that is associated with creating a result set.

The following stored procedure returns the number of rows in a Customers table. 

CREATE PROCEDURE GetNumberOfCustomers(
@CustomerCount int OUTPUT)
AS
SELECT @CustomerCount = COUNT(*)
FROM Customers

To call the stored procedure, use the following code. 

static int GetCustomerCount()
{
  int customerCount = 0;

  SqlConnection conn = new SqlConnection("server=(local);" +
    "Integrated Security=SSPI;database=northwind");
  SqlCommand cmd = new SqlCommand("GetNumberOfCustomers", conn );
  cmd.CommandType = CommandType.StoredProcedure;

  SqlParameter paramCustCount =
    cmd.Parameters.Add("@CustomerCount", SqlDbType.Int );
    paramCustCount.Direction = ParameterDirection.Output;

  try
  {
    conn.Open();
    cmd.ExecuteNonQuery();
    customerCount = (int)paramCustCount.Value;
  }
  finally
  {
    if(null!=conn)
      conn.Close();
  }
  return customerCount;
}

If you do not have control over the stored procedure, and if the stored procedure returns the number of rows as a return value, then you can use Command.ExecuteScalar as shown in the following code fragment. The ExecuteScalar method returns the value of the first column of the first row of the result set. 

static int GetCustomerCountWithScalar()
{
  int customerCount = 0;

  SqlConnection conn = new SqlConnection(
     "server=(local);Integrated Security=SSPI;database=northwind");
  SqlCommand cmd = new SqlCommand("GetCustomerCountWithScalar", conn );
  cmd.CommandType = CommandType.StoredProcedure;

  try
  {
    conn.Open();
    customerCount = (int)cmd.ExecuteScalar();
  }
  finally
  {
    if(null!=conn)
      conn.Close();
  }
  return customerCount;
}

The previous code fragment requires the following stored procedure. 

CREATE PROCEDURE GetCustomerCountWithScalar
AS
SELECT COUNT(*) FROM Customers



Use CommandBehavior.SequentialAccess for Very Wide Rows or for Rows with BLOBs

Use the CommandBehavior.SequentialAccess enumeration for very wide rows or for rows that contain binary large object (BLOB) data. This permits you to return specific bytes from the retrieved row instead of returning the entire row. Returning the entire row may consume large amounts of memory because of the BLOB data.

When you use CommandBehavior.SequentialAccess, the BLOB data is retrieved only when you reference it. For example, you can call the GetBytes method. The GetBytes method permits you to control the precise number of bytes that are read. The following code fragment shows how to use CommandBehavior.SequentialAccess. 

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

Also, if you are performing optimistic locking against a table with very wide rows or against rows that contain BLOB data, use timestamps. Use timestamps instead of comparing all the fields in the table to the original versions. Using time stamps reduces the number of arguments by a value that is equal to n/2+1.

References


Do Not Use CommandBuilder at Run Time

CommandBuilder objects such as SqlCommandBuilder and OleDbCommandBuilder automatically generate the InsertCommand, UpdateCommand, and DeleteCommand properties of a DataAdapter. The CommandBuilder objects generate these properties based on the SelectCommand property of the DataAdapter. CommandBuilder objects are useful when you are designing and prototyping your application. However, you should not use them in production applications. The processing required to generate the commands affects performance. Manually create stored procedures for your commands, or use the Visual Studio® .NET design-time wizard and customize them later if necessary.

Retrieved from "http://guidanceshare.com/index.php?title=ADO.NET_2.0_Performance_Guidelines_-_Commands&oldid=5076"