Improving the Performance of ASP.NET Database Applications Through Stored Procedures

With all the advances made in .NET, many developers are beginning to rely more and more on the front end of an application to provide functionality that makes data conform to the specific business rules of an application. Although ASP.NET pages are compiled and execute faster than regular ASP pages, keeping the business logic in the page is still not a manageable or very maintainable process for most organizations.

In most cases, the database server ends up being the most durable box from a hardware point of view. Most developers will take no chances on whether the database server has an adequate number of processors, memory, disk space, or built-in backup devices. Yet, for most operations, the database server, when properly configured, is one of the least resource-intensive machines. Consider the following machine configurations for a fictitious Web application.

Machine

Specifications

Web server

Pentium 4 800 MHz, 1 GB RAM, RAID 5 18.1 GB SCSI Disk Array

Database server

Quad-Pentium 4 Xeon 800 MHz, 4 GB ECC-RAM, RAID 1 Disk Array for Operating System, RAID 5 72 GB SCSI Disk Array for Database and Log Files, Redundant Network Interface Cards, and Redundant Power Supplies

This is where the use of stored procedures can really boost the performance of your application. Consider the basic chain of events that happens in a Web request.

  1. The user navigates to the Web page, enters some information in a form, and then submits that form to the Web server.

  2. The Web server parses the information and, as directed, performs some action to validate and/or collect the information the user submitted.

  3. A response is sent to the user, usually in the form of a Web page notifying her of success or failure.

  4. The user continues through the application.

The goal is to limit the number of times that the user must send information to the Web server, yet at the same time keep the application from being drawn down by too much code in the pages. Consider the following steps from the computer's point of view.

  1. A request is received from a user, containing some data.

  2. Is this data in a valid format? If so, put it in the database. If not, handle the errors.

  3. Can a connection be made to the database? If so, go ahead with execution; if not, handle the errors.

  4. Did the database get the data? If so, good; if not, handle the errors.

  5. Is the client still there? If so, send response; if not, terminate the connection.

Obviously, the Web server has plenty to do without having to deal with a lot of added code in the pages. And the preceding steps don't even take into consideration "dangling" objects that may have been created and not destroyed.

Next, we change the code from Listings 11.1 and 11.2 to reflect the use of a stored procedure to return data to a page. From there, it's merely a matter of creating stored procedures that perform the tasks that we need them to perform. This model creates an efficient two-tier application.

Warning

As with any ASP.NET application, when collecting data from form, you should use Server.HTMLEncode(Request.Form("objectName") to prevent cross-site scripting attacks. This method encodes the information as a string literal that SQL Server understands, eliminating risks such as malicious users inserting "; TRUNCATE TABLE MASTER" into a textbox named "txtFirstName" on a Web page and having SQL Server execute it.


Some developers seem to be uneasy about using stored procedures from Web-based applications. This unease most often comes from the confusion regarding the Command/SQLCommand object, ADO or ADO.NET, and the use of building parameters with the stored procedure. As covered in Chapter 4, the setting of parameters via code is done to "prequalify" the data by setting their type. That keeps SQL Server and .NET from having to spend time trying to figure out the type of the data (integer, string, and so on) being sent. In our case, we are executing a stored procedure with no parameters. The code in Listing 11.4 shows the procedure created for this example.

Note

Using the steps outlined in the Adding the ASPNET User to the SQL Server Logins section earlier in the chapter, you must configure the public role to have EXEC permissions on the stored procedure created and used in Listings 11.4 and 11.5.


Listing 11.4 sp_GetCustomersOrders
 CREATE PROCEDURE sp_GetCustomersOrders AS SELECT tblCustomer.LastName, tblCustomer.FirstName, tblOrder.OrderDate FROM tblOrder INNER JOIN tblCustomer ON tblCustomer.ID = tblOrder.CustomerID ORDER BY tblCustomer.LastName GO 

Note that the query is asking only for what we need. In Listing 11.3, we used SELECT * and only certain columns were shown on the page. We did so to emphasize that if you don't need it, don't ask for it. In the interest of application scalability, being a minimalist must apply.

Now, let's take a look at the modified ASP.NET code in Listing 11.5. Note that we had to add a line to handle the date being returned by the stored procedure.

Listing 11.5 ShowCustomers SubRoutine
 Private Sub ShowCustomers()      'This is just a simple function to get      'things started, showing the collection      'and displaying tblCustomer.      'Initialize the connection object with      'the connection string      Dim conn As New SqlConnection(connString)      'Also, initialize the command object with      'the SQL to be executed      Dim cmd As New SqlCommand("exec sp_GetCustomersOrders", conn)      conn.Open()      Dim dReader As SqlDataReader = cmd.ExecuteReader (CommandBehavior.CloseConnection)      While dReader.Read        Response.Write(dReader.GetString(0))        Response.Write(" " & dReader.GetString(1))        Response.Write(" " & dReader.GetDateTime(2))        Response.Write("BR")      End While      dReader.Close()      conn.Close()        End Sub 

By using a stored procedure, we have increased efficiency. Specifically, stored procedures are compiled on the database server and execute rapidly compared to dynamic queries. In Listing 11.1, the SELECT statement had to be interpreted each time by SQL Server before any data was returned. At this point, both the page and the query have been compiled; from a performance perspective, this is a very nice feature of the .NET Framework.



Database Access with Visual Basic. NET
Database Access with Visual Basic .NET (3rd Edition)
ISBN: 0672323435
EAN: 2147483647
Year: 2003
Pages: 97

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net