Important: Read the disclaimer before reading this article.

© 2003 International Business Machines Corporation. All rights reserved.

Introduction

Speed. Scalability. These are the mantras of Internet development and they are not necessarily difficult to achieve. Applying a few simple rules can mean the difference between a slow or fast Internet application. In this article, you will learn five rules for developing fast Microsoft® ASP.NET Web apps using Borland® C#Builder^TM and IBM® DB2® Universal Database^TM (UDB) 8.1.

To analyze the performance of your Web apps, you need some way of measuring the performance of each operation. To do this, I created the TimeDiff class (see Listing 1) that will be used to calculate the time of certain database operations. You will use this class as a benchmark on the database operations, to see which operations perform most efficiently. As well as using the TimeDiff class, I created a table called LOTSOFRECORDS (see Listing 2) that has 10,000 records you can use to see the performance differences between various techniques. DB2 has an internal buffer pool that starts once a query is run, so that a second query run against the data will be faster. When you are performing the queries, ignore the first few results and only use the later results once the buffer pool is warmed up.

Listing 1. The TimeDiff class

 
using System;
 
 
namespace Effeciency 
{ 
	/// <summary> 
	/// This class is used to time operations. In our example 
	/// we will time the speed of certain database operations 
	/// to compare them later. 
	/// </summary> 
	public class TimeDiff 
	{ 
		DateTime StartTime; 
		DateTime EndTime; 
 
		public TimeDiff() {} 
 
		public void Start() 
		{ 
			StartTime = DateTime.Now; 
		} 

 
		public void Stop() 
		{ 
			EndTime = DateTime.Now; 
		} 

 
		public string TimeDifferenceText 
		{ 
			get 
			{ 
				TimeSpan TimeDifference = EndTime - StartTime; 
				return TimeDifference.ToString(); 
			} 
		} 
	} 
} 

Listing 2. The DDL for the LOTSOFRECORDS table

 
CREATE TABLE "GLENN   "."LOTSOFRECORDS"  ( 
		  "KEYCOL" INTEGER NOT NULL , 
		  "COL1" CHAR(50) , 
		  "COL2" CHAR(50) , 
		  "COL3" CHAR(50) , 
		  "COL4" CHAR(50) , 
		  "COL5" CHAR(50) , 
		  "COL6" CHAR(50) , 
		  "COL7" CHAR(50) , 
		  "COL8" CHAR(50) , 
        	  "COL9" CHAR(50) , 
		  "COL10" CHAR(50) ) 
		 IN "USERSPACE1" ; 
COMMENT ON TABLE "GLENN   "."LOTSOFRECORDS" IS 
'Table designed to Contain Lots of Records'; 

 
-- DDL Statements for primary key on Table "GLENN   "."LOTSOFRECORDS" 

 
ALTER TABLE "GLENN   "."LOTSOFRECORDS" 
	ADD CONSTRAINT "CC1058255334652" PRIMARY KEY 
		("KEYCOL"); 

Let's begin with the rules for efficient Web applications for DB2 UDB. I will start with the basics of efficiency within the database, and then list some efficiency rules that are specific to ASP.NET applications with the Borland Data Providers.

Back to top

Rule 1: Take only what you need

If you only remember one rule, make sure it's this one: Take only what you need. If you were a fan of the television show Survivor, you probably remember that contestants rationed their food so that there was enough for everyone. The same is true for database development. If you take only what you need for your application to do what it needs to do, there will be database and network resources left for everyone else. It sounds simple enough, but let's look at an example.

Let's say that you have a table with 10,000 rows and with 10 fields in the table, and you have a Web page that needs to display all of the records but with only three fields displayed. Many developers often take the easy way out and select all the fields using a "select *" operation:

 
select * from GLENN.LOTSOFRECORDS 

Instead, you should be aiming at retrieving only the fields you need. In your SQL you should define that you only want to retrieve certain fields, such as:

 
select 
  KEYCOL, COL1, COL2, COL7 
from 
  GLENN.LOTSOFRECORDS 

In the application that accompanies this article, I have supplied two ASP.NET pages. The first is called RetrievingAllFields.aspx and it performs the first query. The other is called RetrievingLimitedFields.aspx and it retrieves only the fields that are required.

Using the TimeDiff class against these database queries, I retrieved the results of the first query in 1.622 seconds and the results of the second query in 1.311 seconds. Just by retrieving the limited number of fields, the operation takes only 80% of the time to retrieve the data. Not only does it take less time to perform the operation, but you have less traffic on the network between your application and the database server.

I am only selecting limited fields in this example, but you should also use the WHERE clause in an SQL statement to limit the number of records retrieved. The WHERE clause allows you to limit the number of records that are returned by the server (see Listing 3 for an example). Just remember that sending fewer records from the database (and only the columns you need for those records) across the network is better for your application, your database, your users, and your network.

Back to top

Rule 2: Learn how to optimize your database

Sometimes you may have an application that is working well but you would like it to perform better. One simple way of decreasing the time for a particular search is to create an index on a particular field. If you have a query that searches for products where the cost is between certain values (see Listing 3), but you do not have an index defined for the price field, returning the data may take a while. Once an index is defined, DB2 will use the index to get you the results that you want, faster.

Listing 3. A database search that could take advantage of an index

 
SELECT 
  PRODUCTCODE, PRODUCTNAME, DESCRIPTION, UNITPRICE 
FROM 
  GLENN.PRODUCTLIST 
WHERE 
  UNITPRICE > 20.00 

Not only should you look at creating indexes for fields that you will search on, it is also advisable to gather as much information as you can on DB2 so that your applications perform better. Web sites such as the IBM DB2 Developer Domain, or newsgroups such as comp.databases.ibm-db2, are a great way of keeping your DB2 development skills up to date.

You should also try to become more familiar with the tools that ship with DB2. One such tool is the DB2 index advisor. The DB2 index advisor lets you pass in a list of queries and the database you are connecting to, and will return back the list of indexes that are best suited for the database.

Back to top

Rule 3: Improving Paging using DB2 UDB's OLAP functions

One of the most common operations in ASP.NET is to display a grid that you can page through. Unfortunately, the default behavior for the ASP.NET DataGrid is to bring all the records required for the grid back to the client and then calculate which records to display based on the page selected (see Figure 1 and Listing 4).

Listing 4. Using the inbuilt paging mechanism of a DataGrid

 
TimeDiff diff = new TimeDiff(); 
private DataSet GetProductsDataSet() { 
	diff.Start(); 
	string connString = ConfigurationSettings.AppSettings["database"]; 
	BdpConnection conn = new BdpConnection(connString); 
	BdpDataAdapter da = new BdpDataAdapter("select KEYCOL, " + 
		"COL1, COL2, COL7 FROM GLENN.LOTSOFRECORDS "+ 
		"ORDER BY KEYCOL ASC", conn); 
	DataSet ds = new DataSet(); 
	da.Fill(ds, "Table1"); 
	diff.Stop(); 
	return ds; 
} 
 
private void BindToTheData() 
{ 
		dataGrid1.DataSource = GetProductsDataSet(); 
		dataGrid1.DataMember = "Table1"; 
		dataGrid1.DataBind(); 
		label1.Text = diff.TimeDifferenceText; 
} 
 
private void Page_Load(object sender, System.EventArgs e) 
{ 
	if (!IsPostBack) { 
		BindToTheData(); 
	} 
} 
 
private void dataGrid1_PageIndexChanged(object source,
System.Web.UI.WebControls.DataGridPageChangedEventArgs e) 
{ 
	//Change the active page of the data 
	dataGrid1.CurrentPageIndex = e.NewPageIndex; 
	BindToTheData(); 
} 

If you use the LOTSOFRECORDS table as an example, retrieving ten thousand rows from the database would chew up the network bandwidth in a flash, especially when you only need to view ten or twenty records at a time. Your users may find that the time for the ASP.NET page to load would be too long and would be prone to timeout. Imagine if the application had several million records—your application would crawl to a halt. You need a better way to retrieve the data.

Thankfully, the DataGrid component allows you to implement custom data retrieval for a page. This allows you to calculate which records you need to display so that you only retrieve the records from the server in order to display a single page of data.

A powerful feature of the later versions of DB2 UDB is the OLAP functions that allow you to retrieve a range of records. In order to retrieve only certain records, you would need a query such as:

 
SELECT * FROM 
  (SELECT KEYCOL, COL1, COL2, COL7, rownumber() 
   over(ORDER BY KEYCOL ASC) AS rn 
   FROM GLENN.LOTSOFRECORDS 
   ORDER BY KEYCOL ASC) AS a1 
   WHERE a1.rn BETWEEN 100 AND 120 

When you use custom paging on this DataGrid, you will need a routine that obtains a DataSet for a particular page. The GetDataByPage method allows you to retrieve the result set you want regardless of the page number and the page size:

 
private DataSet GetDataByPage(int PageNo, int PageSize, out int NumberOfPages) 
{ 
	int startRecord = (PageNo - 1) * PageSize + 1; 
	int endRecord = startRecord + PageSize - 1; 
 
	string connString = ConfigurationSettings.AppSettings["database"]; 
	BdpConnection conn = new BdpConnection(connString); 
	conn.Open(); 
 
	//Get the number of Pages 
	string sRecordCount = "select count(*) from GLENN.LOTSOFRECORDS"; 
	BdpCommand cmdGetRecordCount = new BdpCommand(sRecordCount, conn); 
	int intRecordCount = (int)cmdGetRecordCount.ExecuteScalar(); 
	NumberOfPages = intRecordCount / PageSize; 
	if (intRecordCount % PageSize > 0) 
	  NumberOfPages++; 
 
	//Get the data specifically for the page 
	string sSQL = 
		"SELECT * FROM " + 
		"  (SELECT KEYCOL, COL1, COL2, COL7, rownumber() " + 
		"   over(ORDER BY KEYCOL ASC) AS rn " + 
		"   FROM GLENN.LOTSOFRECORDS " + 
		"   ORDER BY KEYCOL ASC) AS a1 " + 
		"   WHERE a1.rn BETWEEN ? AND ?"; 
	BdpCommand cmdSel = new BdpCommand(sSQL, conn); 
	BdpParameter prmStart = 
	  cmdSel.Parameters.Add("StartRecord", BdpType.Int32); 
	prmStart.Value = startRecord; 
	BdpParameter prmEnd = 
	  cmdSel.Parameters.Add("EndRecord", BdpType.Int32); 
	prmEnd.Value = endRecord; 
	BdpDataAdapter da = new BdpDataAdapter(cmdSel, conn); 
	DataSet ds = new DataSet(); 
	da.Fill(ds, "Table1"); 
 
	diff.Stop(); 
	return ds; 
} 
 
private void LoadSingleDataPage(int pageNo) 
{ 
	//Display the Page contents 
	int PageCount; 
	DataSet dsData = GetDataByPage(pageNo+1, 
		dataGrid1.PageSize, out PageCount); 
	dataGrid1.VirtualItemCount = PageCount * dataGrid1.PageSize; 
	dataGrid1.CurrentPageIndex = pageNo; 
	dataGrid1.DataSource = dsData; 
	dataGrid1.DataBind(); 
} 
 
private void Page_Load(object sender, System.EventArgs e) 
{ 
	if (!IsPostBack) 
	{ 
		LoadSingleDataPage(0); 
	} 
} 
 
private void dataGrid1_PageIndexChanged(object source,<br />
System.Web.UI.WebControls.DataGridPageChangedEventArgs e) 
{ 
	LoadSingleDataPage(e.NewPageIndex); 
} 

By applying custom data paging in this way, to limit the number of records retrieved from the database from ten thousand down to twenty, you will use roughly 0.2% of the network resources you would have originally used. By comparing the time difference using the TimeDiff class on my local machine, when using the custom paging it took between 0.5 and 0.7 seconds, but when I used the default paging mechanism it took between 0.9 and 1.5 seconds.

Back to top

Rule 4: Using stored procedures

Let's look at what happens whenever you send an SQL statement to a DB2 server:

1. The SQL is parsed by the DB2 UDB Server;
2. An execution plan is created for the stored procedure;
3. The data is returned to your application.

When you use a stored procedure, points one and two are already taken care of for you. Once a stored procedure is compiled, only the stored procedure name and parameters are passed to the database server. As a result, you get a performance benefit due to the reduction in time. However, you are only likely to get a benefit if the result set that you are returning is quite large.

The next listing demonstrates a simple query that requests a collection of rows for the table PRODUCTLIST based on the product's category code:

 
 CREATE TABLE "GLENN   "."PRODUCTLIST"  ( 
		  "PRODUCTCODE" VARCHAR(20) NOT NULL , 
		  "PRODUCTNAME" VARCHAR(50) NOT NULL , 
		  "DESCRIPTION" VARCHAR(255) , 
		  "UNITPRICE" DOUBLE NOT NULL , 
		  "CATEGORYCODE" INTEGER , 
		  "IMAGEURL" CHAR(150) ) 
		 IN "USERSPACE1" ; 
COMMENT ON TABLE "GLENN   "."PRODUCTLIST" IS 
'A list of Products in the Shopping Cart'; 
 
-- DDL Statements for primary key on Table "GLENN   "."PRODUCTLIST" 
 
ALTER TABLE "GLENN   "."PRODUCTLIST" 
	ADD CONSTRAINT "CC1053568050795" PRIMARY KEY 
		("PRODUCTCODE"); 

The next query is something that you could easily convert to a stored procedure.

 
SELECT PRODUCTLIST.PRODUCTCODE, PRODUCTLIST.PRODUCTNAME, PRODUCTLIST.DESCRIPTION,
           PRODUCTLIST.UNITPRICE, PRODUCTLIST.IMAGEURL 
           FROM GLENN.PRODUCTLIST AS PRODUCTLIST 
           WHERE PRODUCTLIST.CATEGORYCODE = 2; 

The Development Center product that ships with DB2 includes an excellent wizard that allows you to create a stored procedure with a minimum of fuss (see Figure 2).

Once you start the wizard, you will be given the option of selecting which tables, fields, and selection criteria you want to use. IBM has done a thorough job of easing you through the creation process of stored procedures with the wizard, so you should take advantage of it.

One thing that is extremely useful about the wizard is the ease with which you can create input parameters for the stored procedure. Creating an SQL stored procedure (DB2 also has the ability to create stored procedures in Java), you can limit the rows selected in the Category Code that is passed as a parameter in the stored procedure (see Figure 3).

After the wizard was finished, I ended up with the stored procedure shown below:

 
CREATE PROCEDURE GLENN.GETPRODUCTSINCATEGORY ( IN CATCODE INTEGER ) 
    DYNAMIC RESULT SETS 1 
------------------------------------------------------------------------ 
-- SQL Stored Procedure 
------------------------------------------------------------------------ 
P1: BEGIN 
    -- Declare cursor 
    DECLARE cursor1 CURSOR WITH RETURN FOR 
        SELECT PRODUCTLIST.PRODUCTCODE, PRODUCTLIST.PRODUCTNAME, 
        PRODUCTLIST.DESCRIPTION,
        PRODUCTLIST.UNITPRICE, 
        PRODUCTLIST.IMAGEURL 
           FROM GLENN.PRODUCTLIST AS PRODUCTLIST 
           WHERE PRODUCTLIST.CATEGORYCODE = CATCODE; 
 
    -- Cursor left open for client application 
    OPEN cursor1; 
END P1 

The main concept you should use for a stored procedure is that you should be trying to get as much use from the network trip to the database server as you can. When using the Stored Procedure Wizard, you can request that the stored procedure returns the results of more than one query. By using this methodology, you can get even the most benefit from the stored procedure. If you do have a small result set, it may turn out that when you use the stored procedure it will be slower than by using a query. You should always endeavor to do performance testing of your data access.

There is a difference in calling a stored procedure using the Borland Data Providers than calling a query. The main difference is that the BdpCommand object that your BdpDataAdapter uses must have its CommandType set to CommandType.StoredProcedure and the CommandText set to the name of the stored procedures. You will also need to define any parameters to the stored procedure in the BdpCommand's Parameters collection.

Once you have the parameters defined, it's just a matter of using the BdpDataAdapter's Fill method to populate a DataSet:

 
private void GetProductsViaStoredProcedure(int CategoryCode) { 
	cmdGetDataViaStoredProc.Parameters[0].Value = CategoryCode; 
	BdpDataAdapter da = new BdpDataAdapter(cmdGetDataViaStoredProc); 
	da.Fill(dsProducts, "Products"); 
	dataGrid1.DataBind(); 
} 

Back to top

Rule 5: Caching as much as you can

One of ASP.NET's most powerful features is caching. The premise of caching is simple—store in memory what you will access frequently, so that you do not have to go to the database or across the network to retrieve it again. Accessing information from memory will always be faster than accessing some resource in another process or across a network.

So how does caching work? In ASP.NET there are several methods. One method is to declare a page directive at the top of your ASP.NET page so that the page will manage the caching of the paging automatically. If you have read my previous article, Build ASP.NET Web Sites with IBM DB2 Universal Database, you should already be familiar with this technique. You can cache an entire page by incorporating the OutputCache declaration:

 
<%@ Page language="c#" Debug="true" Codebehind="WebForm1.aspx.cs" 
AutoEventWireup="false" 
Inherits="MyNewWebApplication.WebForm1" %> 
 
<%@ OutputCache Duration="300" VaryByParam="None" VaryByCustom="browser" %> 

Another way of using the cache is by using the Pages built in Cache object. When you want to put content into the cache, use the Insert method, and when you want to retrieve the value, use the default collection for the Cache object.

 
private void Page_Load(object sender, System.EventArgs e) 
{ 
	TimeDiff diff = new TimeDiff(); 
 
	diff.Start(); 
	string retrievalMethod; 
	DataSet CategoriesDataSet; 
	if (Cache["Categories"] == null) { 
		retrievalMethod = "Database"; 
		connShopping.Open(); 
		CategoriesDataSet = new DataSet(); 
		daCategories.Fill(CategoriesDataSet, "Categories"); 
		Cache.Insert("Categories", CategoriesDataSet); 
		connShopping.Close(); 
	} else { 
		retrievalMethod = "Cache"; 
		CategoriesDataSet = (DataSet)Cache["Categories"]; 
	} 
	diff.Stop(); 
	lblDetails.Text = "Retrieval from the " + retrievalMethod + 
		" in " + diff.TimeDifferenceText + " seconds"; 
	dataGrid1.DataSource = CategoriesDataSet; 
	dataGrid1.DataMember = "Categories"; 
	dataGrid1.DataBind(); 
} 

The first time this page was requested, it took 0.9 seconds on my local computer when the data was being retrieved from the database, but retrieving the data from the Cache took almost no time, as the TimeDiff class stated that the time to retrieve it was 00:00:00 seconds. As you can see, the cache is a simple and powerful way of speeding up your Web application.

You may also want to use the Cache objects expiration policy, in case your data gets updated at regular intervals. To do this you would just need to use the overloaded version of the Insert method, specifying a timeout. Here's a way of making the "Categories" cache refresh itself every 6 hours:

 
Cache.Insert("Categories", CategoriesDataSet, null, 
	System.Web.Caching.Cache.NoAbsoluteExpiration, 
	TimeSpan.FromHours(6)); 

Back to top

Conclusion

In a perfect world, your apps would run instantly, would support an unlimited number of users, and would not consume any network resources. Because we don't live in this perfect world yet, you should try to take advantage of one or more of the efficiency rules outlined in this article. But always stay on the lookout for more tips as they become available. Good application efficiency is worth its weight in gold, so always strive to keep your applications performing better than ever.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Back to top

Download

Information about download methods

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Rule 1: Take only what you need
• Rule 2: Learn how to optimize your database
• Rule 3: Improving Paging using DB2 UDB's OLAP functions
• Rule 4: Using stored procedures
• Rule 5: Caching as much as you can
• Conclusion
• Disclaimer
• Download
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers