The excellent code is similar, and the bad code is different. The rule is actually not important, and it is important to develop a good habit. - Translator's title
5 rules in the performance of DB2 web applications
Author: Glenn Stephens August 2003
content
Introduction Rule 1: Search Rule 2: Optimize Database Rule 3: Improve Paging Rules 4: Use Storage Procedure Rules 5: Use Cache Summary
Introduction
Speed and availability are important performance requirements for network development, and they are not difficult to achieve. Applying some simple rules will increase the performance of network applications. In this article, you will learn about 5 rules for developing fast Microsoft® ASP.NET web applications using Borland® C # Builder and IBM® DB2® Universal Database (UDB) 8.1 Developing Fast Microsoft® ASP.NET Web applications.
Analysis of the performance of web applications requires some means to detect performance of each operation. To do this, I created a Timediff class (see Program List 1), which can calculate the use of database operations. You can use the TIMEDIFF class's test results as a baseline that measures database operation performance, which is most effective. I also created a LotsofRecords table that cooperated with the Timediff class (see Program Listing 2), which contains 10,000 records that you can observe performance differences between different technologies by observing it. DB2 has an internal buffer pool, once a query is run, the internal buffer pool is enabled, so the second query is faster. When the query speed is detected, ignore the result before the buffer pool is enabled and the result is used.
Program list 1. Timediff class
Using system;
Namespace Effecience
{
///
/// This class is used for time calculation. In the example,
/// We will use it to detect the speed of the database operation,
/// In order to make a comparison.
///
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.toTString ();
}
}
}
}
Program List 2. LotsofRecords table definition
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");
Below, we have begun to introduce these rules that improve the performance of DB2 UDB web applications. I will first introduce the basic rules of the database performance and then list some performance rules that are suitable for developing ASP.NET applications with Borland Data Provider.
Rule 1: Retrieves on demand
If you can only remember a rule, you must remember this: Search as needed. If you are a loyal audience for the "Survivor" TV show, you will remember that the participants have guaranteed each person with sufficient food. This approach is equally applicable to database development. If your application can run on request, you will rationally leave the database and network resources to other applications. This sounds very simple, but we still come to see an example.
Suppose there is a table containing 10,000 row records and 10 fields, and a web page that needs to display all records but only 3 fields. Many developers often use the "SELECT *" statement to select all the fields:
Select * from Glenn.lotsofRecords
This approach should be avoided, and should strive to retrieve only the required fields. You can define the fields to retrieve in the SQL statement, for example:
SELECT
Keycol, Col1, Col2, Col7
From
Glenn.lotsofrecords
In the source program included in this article, there are two ASP.NET pages: one is RETRIEVINGALLDS.ASPX, which performs the first query; the other is RETRIEVINGLIMITEDFIELDS.ASPX, which executes the second query, which only retrieves the required fields.
Using the Timediff class, the first query was executed for 1.622 seconds, and the second query was executed for 1.311 seconds. The latter is only 80% of the former, not only time, but also reduces network data blockage between web applications and database servers.
This example only limits the search field, you can also use the WHERE statement to limit the number of records. Where statement can limit the number of records returned by the server (see Program Listing 3). To remember, the less recorded data sent over the network, the more benefit of the application, database, user, and network.
Rule 2: Optimize the database
Sometimes your web application may have a good run, but you want it to be better. A simple way to reduce search time is to create an index for a specific field. If a query is to search for products within a price range (see Program List 3), but you don't have an index for the price field, then you will spend more time for returning data. Once an index is established, DB2 will quickly return to the results you want.
Program List 3. Using the index to enter the database search
SELECT
ProductCode, ProductName, Description, Unitprice
From
Glenn.productlist
WHERE
Unitprice> 20.00
Optimizing the database is not just to create an index for the search field, you should collect the relevant DB2 information as much as possible to make the application run better. Regularly access some of the relevant Web site or newsgroups such as IBM DB2 Developer Domain and Comp.Databases.ibm-DB2 (Comp.Databases.ibm-DB2), which is very good for keeping DB2 development skills. Method. You should also strive to familiarize yourself with the tools included with DB2, such as the DB2 index suggestter (INDEX Advisor). The DB2 index suggestter can return the best index list according to the query you submit and the connected database.
Rules 3: Improve paging using the OLAP function of DB2 UDB
In ASP.NET, one of the most common operations is that the table pagination is displayed. The default setting of the DataGrid component in the ASP.NET is to return all the records you need to return to the client, and then display the corresponding record according to the selected page (see Figure 1 and Program Listing 4).
Figure 1 Pipe setting of DataGrid
Program List 4 Using DataGrid Built-in Mechanism
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.TimedIfference;
}
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, retrieve 10,000 records from the database will soon accounted for the network bandwidth, especially when you only need to view 10 or 20 records. Users will find that the time to load the ASP.NET page is too long, and it is likely to time out. Imagine if there are millions of records in the database, then your app will run slowly only to stop execution. Therefore, a better way to retrieve data.
Fortunately, the DataGrid component allows custom paging. You can calculate which records need to be displayed, then only the corresponding records are retrieved from the server. The later version of DB2 UDB has a big feature, that is the OLAP function, which allows you to perform various records. For example, only some records can be implemented:
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 using DataGrid custom paging, you need to get a DataSet of a specific page. GetDatabasePage method can retrieve result set regardless of the number of pages and 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);
Cn.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 specification 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);
BDPArameter 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 loadingledataPage (int Pageno)
{
// Display the page contents
Int pagecount;
DataSet DSData = GetDatabase (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, System.Web.ui.WebControls.DataGridPageChangeDeventargs E)
{
LoadSingledataPage (E.NewpageIndex);
}
By customize paging, the number of retrieval records from 10,000 will be limited to 20, and the occupied network resource is about 0.2%. In my local Timediff class detection, use the custom paging to use the time 0.5 ~ 0.7 seconds, while using the default paging mechanism for 0.9 ~ 1.5 seconds.
Rule 4: Using the stored procedure
If a SQL statement to the DB2 server is sent, the execution process is as follows:
(1) DB2 UDB Server performs syntax check for SQL statements; (2) Generate a stored procedure execution plan; (3) Data returns the application.
When using a stored procedure, the first two steps have been completed. After the stored procedure is compiled, only the stored procedure name and parameters are transferred to the database server when calling. Therefore, the decrease in execution time has won performance advantages. But this advantage is that only when the result set returned is very large.
Let's take an example. Built a productList table first:
Create Table "Glenn". "ProductList" ("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 shopper ";
- DDL Statements for primary key on table "glenn". "ProductList" Alter Table "Glenn". "ProductList"
Add constraint "CC1053568050795" PRIMARY Key
("ProductCode");
Then check the following:
Select ProductList.ProductCode, ProductList.ProductName, ProductList.Description, ProductList.Unitprice, ProductList.ImageURL
From Glenn.ProductList As ProductList
Where productlist.categorycode = 2;
The above query is easy to turn into a stored procedure. The DB2 Development Center (DB2 Development Center) contains an excellent stored procedure wizard that allows you to easily generate stored procedures (see Figure 2).
Figure 2 Creates a new stored procedure with the stored procedure wizard of the DB2 Development Center
After the Storage Procedure Wizard is started, you only need to select the corresponding table, fields, and rules, and the creation process of the stored procedure is completed by the wizard.
A extreme useful place to store the process wizard is that it can make easy creation of the input parameters of the stored procedure. Create a SQL stored procedure (DB2 can also create a Java stored procedure), you can select "Category Code" as the input parameter of the stored procedure (see Figure 3).
Figure 3 Create a stored procedure input parameter
After completing, the stored procedure created is as follows:
Create Procedure Glenn.getProductSINCATEGORY (In Catcode Integer)
Dynamic Result Sets 1
-------------------------------------------------- ----------------------
- SQL Stored Procedure
-------------------------------------------------- ----------------------
P1: Begin
- Declare Cursor
DECLE 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
When using the Stored Procedure Wizard, you can return the stored procedure to the result of more than one query. In this way, the maximum advantage of the stored procedure can be exerted. If the result set is small, use the stored procedure may be slower than using the query. You should continue to test data access.
Using the Borland Data Provider calls the stored procedure and calling query vary, mainly BDPDataAdapter's BDPCommand object must set CommandType to CommandType.StoredProcedure and set CommandText to store procedure names. In addition, the stored procedure parameters are required in the parameter set of the BDPCommand object.
Once the parameters are defined, simply populate the DataSet: private void getproductsviasticprocedure (int categorycode) {
CMDGetDataViaStoredProc.Parameters [0] .value = categorycode;
BDPDataAdapter Da = New BDPDataAdapter (cmdgetdataViaStoredProc);
Da.fill (DSProducts, "Products");
DataGrid1.databind ();
}
Rules 5: Use cache as much as possible
One of the biggest features of ASP.NET is the cache. The principle of the cache is simple, and the content that is regularly accessed is stored in memory. You do not need to go to the database or retrieve data over the network when access. The information accessed in memory is always more faster than accessing resources through other processes or networks.
So how do you use a cache? There are several ways in ASP.NET. One way is to define a page indicator identifier in the ASP.NET page header to automatically manage page cache. If you read my previous article, "Building IBM DB2 UDB is established" (Build ASP.NET Web Sites with IBM DB2 Universal Database), it should be familiar with this technology. By including the OutputCache directive, you can cache the entire page:
<% @ Page language = "c #" debug = "true" codebehind = "Webform1.aspx.cs" autoeventwireup = "false"
Inherits = "MyNewwebApplication.Webform1"%>
<% @ Outputcache duration = "300" VarybyParam = "none" VarybyCustom = "Browser"%>
Another method is to use the Cache object built by the PAGES object. When placing the content into the cache, use the Cache object's INSERT method; when the content is removed from the cache, the default collection of the Cache object is used.
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";
Connsopping.open ();
CategoriesDataSet = New DataSet ();
Dacategories.Fill (CategoriesDataSet, "Categories");
Cache.insert ("Categories", CategoriesDataSet;
Connsopping.close ();
} else {
Retrievalmethod = "cache";
CategoriesDataSet = (Dataset) Cache ["categories"];
}
Diff.stop ();
LBLDETAILS.TEXT = "Retrieval from the" RetrievalMethod "IN" Diff.TimedIfferenceExT "Seconds";
DataGrid1.datasource = categoriesDataSet;
DataGrid1.DataMember = "categories";
DataGrid1.databind ();
}
When the page is previously requested, I used the data search for this machine to be used for 0.9 seconds, but the retrieved data from the cache is almost useless, because the test result of the Timediff class is 00:00:00. It can be seen that the use of caching is a simple and effective way to accelerate web applications.
If you need to update your data, it is also possible to use the expiration policy of the cache object. You only need to use the overload version of the INSERT method to specify the expiration time. Below is a way to automatically refresh the "categories" cache every 6 hours:
Cache.insert ("categories", categoriesdataset, null,
System.Web.caching.cache.NoabsoluteExpiration,
Timespan.fromhai (6));
to sum up
In the perfect world, applications always perform fast, unlimited users, and do not account for any network resources. But we are not in these, so you should use the effective rules summarized in this article and strive to improve the performance of the web application.
Note: The copyright belongs to the original author, and some of the content is deleted and modified. Error is inevitable, and the criticism will contact Lucentoff@sohu.com.
