Improving Database Performance

In the following sections, you learn two methods of increasing the database performance of your ASP.NET pages. First, you learn how to use SQL stored procedures from an ASP.NET page. By packaging your SQL statements in stored procedures, you get faster database performance.

Next , you look at both OLE DB and SQL connection pooling. You learn how to configure and take advantage of connection pooling in your Web applications to open database connections faster.

Using SQL Stored Procedures

You can execute a SQL statement from within an ASP.NET page in two ways. You can execute the statement directly from code in the page, or you can package the SQL statement as a stored procedure and execute the stored procedure from the page.

Building stored procedures takes a little more work than executing SQL statements directly on a page. However, you can improve the performance and maintainability of your application by taking advantage of stored procedures.

Another advantage of using stored procedures is that you can package multiple SQL statements in a stored procedure and execute them as a group . For example, you can create one stored procedure that contains multiple SQL Update statements so that you can update the records in multiple tables at once.

You can use all the programming features of the Transact-SQL language when building stored procedures. Transact -SQL includes support for variables , conditionals, loops and functions. Using these features, you can build very complex mini-programs within a stored procedure.

Stored procedures can also be used to shield your ASP.NET pages from the particular implementation of the tables in your database. If you make changes in the database tables that you use in your application, you can change your stored procedures without making any changes to your ASP.NET pages.

In any case, after you set up the parameters for a SQL command, executing the command as a stored procedure requires a trivial amount of work. We'll start with a page that doesn't use a stored procedure and modify it to use one. Listing 9.20 contains a simple ASP.NET page that inserts a new record into the Products table.

Listing 9.20 SqlInsert.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <% Dim conNorthwind As SqlConnection Dim strInsert As String Dim cmdInsert As SqlCommand conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Northwind" ) strInsert = "Insert Products ( ProductName, UnitPrice ) Values ( 'Milk', 12.45 )" cmdInsert = New SqlCommand( strInsert, conNorthwind ) conNorthwind.Open() cmdInsert.ExecuteNonQuery() conNorthwind.Close() %> New Product Added! 

The C# version of this code can be found on the CD-ROM.

The page in Listing 9.20 adds a new product to the Products database table. The new record is added by passing values for the ProductName and UnitPrice table columns .

To modify the page in Listing 9.20 so that it uses a stored procedure, you need to complete the following four steps:

1. Create a SQL stored procedure that contains the statement that you want to execute.

2. Import the System.Data namespace.

3. Pass the name of the stored procedure to the instance of the SqlCommand class.

4. Set the CommandType property of the SqlCommand class to the value CommandType.StoredProcedure .

The first step is to actually create the SQL stored procedure. You can create a stored procedure by using either the Microsoft SQL Server Enterprise Manager or Query Analyzer. For this example, use Query Analyzer.

Run Query Analyzer by going to Start, Programs, Microsoft SQL Server, and then Query Analyzer. Enter your login name and password, and select from the drop-down list the database where you want to create your stored procedure.

Next, type the following statement in the Query window:

 Create Procedure InsertProducts (   @ProductName NVarchar( 80 ),   @UnitPrice Money ) As Insert Products ( ProductName, UnitPrice ) Values ( @ProductName, @UnitPrice ) 

After you enter this statement, click the Execute Query button (the green VCR play button) to execute the statement and create a new stored procedure named InsertProducts (see Figure 9.7).

Figure 9.7. Creating a stored procedure with Query Analyzer.

The InsertProducts stored procedure has two input parameters named @ProductName and @UnitPrice . The @ProductName parameter is declared as an NVarchar parameter with a maximum length of 80 characters . The @UnitPrice parameter is declared as a Money parameter.

The body of the stored procedure appears after the As keyword. The InsertProducts stored procedure simply executes a SQL Insert statement using the two parameters.

You need to make three modifications to the page in Listing 9.20 to execute your InsertProducts stored procedure. The modified page appears in Listing 9.21.

Listing 9.21 SqlStoredProcedure.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <% Dim conNorthwind As SqlConnection Dim strInsert As String Dim cmdInsert As SqlCommand conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Northwind" ) cmdInsert = New SqlCommand( "InsertProducts", conNorthwind ) cmdInsert.CommandType = CommandType.StoredProcedure cmdInsert.Parameters.Add( "@ProductName", "Milk" ) cmdINsert.Parameters.Add( "@UnitPrice", 12.45 ) conNorthwind.Open() cmdInsert.ExecuteNonQuery() conNorthwind.Close() %> New Product Added! 

The C# version of this code can be found on the CD-ROM.

First, notice that you import the System.Data namespace on the second line of the page. You need to import the System.Data namespace to use the CommandType enumeration that you use later in the page.

Next, when initializing the SqlCommand class, you pass the name of the InsertProduct stored procedure. When you execute the command, this stored procedure will be executed.

Finally, you set the CommandType property of the SqlCommand class to the value CommandType.StoredProcedure . Setting the CommandType causes the command to be interpreted as the name of a stored procedure rather than a standard SQL statement. (The default value of CommandType is Text .)

You also can use stored procedures when executing queries with a SQL Select statement and a DataReader . The steps for doing so are exactly the same. For example, the page in Listing 9.22 displays the value of the au_lname column for all the records in the Authors database table.

Listing 9.22 SqlStoredProcedureQuery.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <% Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim dtrAuthors As SqlDataReader conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" ) cmdSelect = New SqlCommand( "SelectAuthors", conPubs ) cmdSelect.CommandType = CommandType.StoredProcedure conPubs.Open() dtrAuthors = cmdSelect.ExecuteReader() While dtrAuthors.Read()   Response.Write( "<li>" )   Response.Write( dtrAuthors( "au_lname" ) ) End While dtrAuthors.Close() conPubs.Close() %> 

The C# version of this code can be found on the CD-ROM.

The page in Listing 9.22 executes a stored procedure named SelectAuthors . This stored procedure is executed when the ExecuteReader() method is called.

The SelectAuthors stored procedure takes no parameters. It simply returns all the author last names from the Authors table. The SelectAuthors stored procedure can be created with the following statement:

 Create Procedure SelectAuthors As Select au_lname From Authors 

Retrieving Return Values and Output Parameters

In the preceding section, you saw how you can use input parameters with a stored procedure to pass values to a stored procedure from an ASP.NET page. SQL stored procedures also support return values and output parameters for passing information back from a stored procedure.

Using Return Values

Every stored procedure exits with a return value, even if you don't capture it. By default, every stored procedure returns the value . However, you can return any integer you please from a stored procedure by using the Return statement.

Suppose that you want to retrieve a count of the number of records in the Authors database table by using a stored procedure. One way to do so would be to create the following stored procedure:

 Create Procedure GetAuthorCount As Return ( Select Count(*) From Authors ) 

The GetAuthorCount stored procedure returns a count of the number of rows in the Authors database table. This value is passed back from the Return statement.

CAUTION

You can use only integers with Return values. You cannot return a Null value.

After you create the GetAuthorCount stored procedure, you can use it in an ASP.NET page by creating a Return parameter. The page in Listing 9.23 illustrates how to do so.

Listing 9.23 SqlGetAuthorCount.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <% Dim conPubs As SqlConnection Dim cmdAuthorCount As SqlCommand Dim parmReturnValue As SqlParameter Dim intAuthorCount As Integer conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" ) cmdAuthorCount = New SqlCommand( "GetAuthorCount", conPubs ) cmdAuthorCount.CommandType = CommandType.StoredProcedure parmReturnValue = cmdAuthorCount.Parameters.Add( "ReturnValue", SqlDbType.Int ) parmReturnValue.Direction = ParameterDirection.ReturnValue conPubs.Open() cmdAuthorCount.ExecuteNonQuery() intAuthorCount = cmdAuthorCount.Parameters( "ReturnValue" ).Value conPubs.Close() %> There are <%=intAuthorCount%> records in the Authors table 

The C# version of this code can be found on the CD-ROM.

In Listing 9.23, an instance of the SqlParameter class is created to represent the return value from the GetAuthorCount stored procedure. This parameter is created with the following two statements:

 parmReturnValue = cmdAuthorCount.Parameters.Add( "ReturnValue", SqlDbType.Int ) parmReturnValue.Direction = ParameterDirection.ReturnValue 

The first statement creates a parameter named ReturnValue with a SQL Int data type. This parameter is added to the Parameters collection of the SqlCommand class and assigned to a variable named parmReturnValue .

The second line assigns a direction to the parameter. By default, parameters are input parameters. Because you want your parameter to represent a return value, you must assign the value ParameterDirection.ReturnValue to the parameter's Direction property.

Using Output Parameters

Using output parameters is similar to using return values. However, output parameters have a couple of important advantages. You can use an output parameter to pass Varchar , Int , Money , or values of any other data type from a stored procedure. A return value, on the other hand, can return only integer values.

Another advantage of output parameters is that you can include multiple output parameters in a stored procedure. A Microsoft SQL Server stored procedure can have a maximum of 1,024 parameters (including both input and output parameters), but it can have only one return value.

The following stored procedure, for example, can be used to retrieve an author's last name given the author's first name. The stored procedure has one input parameter named @ firstname and one output parameter named @lastname . (The output parameter is marked with the special SQL keyword Output .)

 Create Procedure GetLastname (   @firstname Varchar( 20 ),   @lastname Varchar( 20 ) Output ) As Select @lastname = au_lname From Authors Where au_fname = @firstname 

The page in Listing 9.24 illustrates how you would use this stored procedure in an ASP.NET page.

Listing 9.24 SqlGetAuthorLastname.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <% Dim conPubs As SqlConnection Dim cmdLastName As SqlCommand Dim parmLastName As SqlParameter Dim strLastName As String conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" ) cmdLastName = New SqlCommand( "getLastname", conPubs ) cmdLastName.CommandType = CommandType.StoredProcedure ' FirstName Input Parameter cmdLastName.Parameters.Add( "@firstname", "Ann" ) ' LastName Output Parameter parmLastName = cmdLastName.Parameters.Add( "@lastname", SqlDbType.Varchar ) parmLastName.Size = 40 parmLastName.Direction = ParameterDirection.Output ' Execute Command conPubs.Open() cmdLastName.ExecuteNonQuery() ' Retrieve value of output parameter If Not IsDBNull( cmdLastName.Parameters( "@lastname" ).Value ) Then   strLastName = cmdLastName.Parameters( "@lastname" ).Value Else   strLastName = "Unknown" End If conPubs.Close() %> The last name is <%=strLastName%> 

The C# version of this code can be found on the CD-ROM.

In Listing 9.24, the @lastname output parameter is declared with three lines of code. The first line creates the parameter, assigns the parameter a Varchar data type, and adds the parameter to the Parameters collection of the SqlCommand class. The next line specifies the maximum size of the output parameter. Because the @lastname parameter can return 40 characters, the Size property is assigned the value 40 . Finally, the direction of the parameter, ParameterDirection.Output , is assigned to the Direction property.

After the command is executed, you can capture the value of the output parameter from the Parameters collection. Notice how the IsDBNull() method is used to check whether the output parameter has a value. You don't want to attempt to assign the value of the parameter to a string variable if nothing is returned. You want to assign the value of the output parameter to the strLastname variable only when an author with that first name exists.

Executing Complex Stored Procedures

One important benefit of using stored procedures is that they enable you to package together multiple SQL statements and execute them as a group. In this section, you examine two ASP.NET code samples that use multiple SQL statements.

The first sample demonstrates how to use a stored procedure to get the identity value of a new row entered into a table. The following stored procedure adds a new product to the Products database table and returns the value of the identity column for the new record entered in the table:

 Create Procedure AddProduct (   @ProductName NVarchar( 80 ),   @UnitPrice Money ) As Insert Products ( ProductName, UnitPrice )   Values ( @ProductName, @UnitPrice ) Return @@Identity 

The @@Identity global variable contains the last value inserted into a SQL table's identity column. The AddProduct stored procedure adds a new product to the Products table and returns the value of the @@Identity variable.

The page in Listing 9.25 illustrates how you would use the AddProduct stored procedure from an ASP.NET page (see Figure 9.8).

Listing 9.25 SqlAddProduct.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <% Dim conNorthwind As SqlConnection Dim cmdAddProduct As SqlCommand Dim parmProductID As SqlParameter Dim intProductID As Integer conNorthwind = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=Northwind" ) cmdAddProduct = New SqlCommand( "AddProduct", conNorthwind ) cmdAddProduct.CommandType = CommandType.StoredProcedure ' Add Return Value Parameter parmProductID = cmdAddProduct.Parameters.Add( "ReturnValue", SqlDbType.Int ) parmProductID.Direction = ParameterDirection.ReturnValue ' Add firstname Input Parameter cmdAddProduct.Parameters.Add( "@ProductName", "Biscuits" ) ' Add lastname Input Parameter cmdAddProduct.Parameters.Add( "@UnitPrice", 23.87 ) conNorthwind.Open() cmdAddProduct.ExecuteNonQuery() intProductID = cmdAddProduct.Parameters( "ReturnValue" ).Value conNorthwind.Close() %> The ID for the new product is <%=intProductID%> 

The C# version of this code can be found on the CD-ROM.

Figure 9.8. Retrieving the @@Identity value.

The page in Listing 9.25 contains three parameters: two input parameters and a return value. The two input parameters represent the new product name and price. The return value retrieves the value of the identity column when the new product is inserted into the database table.

Another situation in which using a stored procedure to execute multiple statements is valuable concerns inserting nonduplicate records in a database. Suppose that you want to insert a product into the Products table only if a record for the product doesn't already exist.

The following stored procedure uses the SQL Exists keyword to check for the existence of a product before inserting a record:

 Create Procedure AddUniqueProduct (   @ProductName NVarchar( 80 ),   @UnitPrice Money ) As If Exists   (   Select ProductName   From Products   Where ProductName = @ProductName   )   Return 1 Else   Insert Products ( ProductName, UnitPrice )   Values ( @ProductName, @UnitPrice ) 

The AddUniqueProduct stored procedure checks whether a product with a product name already exists by using the Exists keyword. If Exists returns True , the stored procedure exits with the value 1 . If the product doesn't already exist, the product is added and the stored procedure exits with its default value of .

The page in Listing 9.26 illustrates how to use this stored procedure from within an ASP.NET page.

Listing 9.26 SqlAddUniqueProduct.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <% Dim conNorthwind As SqlConnection Dim cmdAddunique As SqlCommand Dim parmReturnValue As SqlParameter conNorthwind = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=Northwind" ) cmdAddUnique = New SqlCommand( "AddUniqueProduct", conNorthwind ) cmdAddUnique.CommandType = CommandType.StoredProcedure ' Add Return Value Parameter parmReturnValue = cmdAddUnique.Parameters.Add( "ReturnValue", SqlDbType.Int ) parmReturnValue.Direction = ParameterDirection.ReturnValue ' Add product name Input Parameter cmdAddUnique.Parameters.Add( "@ProductName", "Milk" ) ' Add unit price name Input Parameter cmdAddUnique.Parameters.Add( "@UnitPrice", 43.89 ) ' Execute Command conNorthwind.Open() cmdAddUnique.ExecuteNonQuery() If cmdAddUnique.Parameters( "ReturnValue" ).Value = 0 Then   Response.Write( "New Product Added!" ) Else   Response.Write( "Product already exists!" ) End If conNorthwind.Close() %> 

The C# version of this code can be found on the CD-ROM.

The page in Listing 9.26 passes two input parameters to the addUniqueProduct stored procedure and retrieves the return value. If the return value is , the message New Product Added! is displayed. Otherwise, the message Product already exists! is displayed.

Improving Performance with Connection Pooling

Opening a connection is a database- intensive task. It can be one of the slowest operations that you perform in an ASP.NET page. Furthermore, a database has a limited supply of connections, and each connection requires a certain amount of memory overhead (approximately 40 kilobytes per connection).

If you plan to have hundreds of users hitting your Web site simultaneously , the process of opening a database connection for each user can have a severe impact on the performance of your Web site.

Fortunately, you can safely ignore these dire warnings if you take advantage of connection pooling. When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When you close the connection, the connection is placed back in the pool.

Connection pooling is enabled for both OleDb and SqlClient connections by default. OleDb connection pooling is handled by the OLE DB .NET provider. SqlClient connection pooling is handled by Windows 2000 component services.

To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool.

Realize that even very small differences in the connection string can thwart connection pooling. Connections are pooled only when they are opened with connection strings that exactly match character by character. For this reason, it is wise to create your connection string in one place and use the same connection string within all your ASP.NET pages. For example, you can place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection. Another option is to place the connection string in Application state within the Global.asax file.

NOTE

The web.config file and Application state are both discussed in Chapter 15, "Creating ASP.NET Applications."

To take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the Close() method, the connection is never added back to the connection pool.

When using SQL connection pooling, you can place additional options in a connection string to modify how connection pooling works. For example, you can specify the minimum and maximum size of the connection pool or even completely disable connection pooling.

Here's a list of the connection pooling options that you can add to the SQL Server connection string:

• Connection Lifetime Destroys a connection after a certain number of seconds. The default value is 0, which indicates that connections should never be destroyed .

• Connection Reset Indicates whether connections should be reset when they are returned to the pool. The default value is true.

• Enlist Indicates whether a connection should be automatically enlisted in the current transaction context. The default value is true.

• Max Pool Size The maximum number of connections allowed in a single connection pool. The default value is 100.

• Min Pool Size The minimum number of connections allowed in a single connection pool. The default value is 0.

• Pooling Determines whether connection pooling is enabled or disabled. The default value is true.