Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to improve the efficiency of paging large amount of data". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. let's study and learn "how to improve the efficiency of paging with large amount of data"!

As we discussed in previous tutorials, paging can be achieved in two ways:

Default paging-you only use the Enable Paging that selects the smart tag of data Web control; however, when you browse the page, ObjectDataSource reads all the data every time, even though you see only a small portion of the data

Custom paging-improves performance by reading only the part of the data that the user needs to browse from the database. Obviously this method requires you to do more work.

The default paging feature is very attractive because you only need to select a checkbox to do it. But it reads all the data every time, which is not appropriate in the case of large amount of data or many concurrent users. In this case, we must make the system achieve better performance by customizing paging.

One of the key points of custom paging is to write a query that returns only the data you need. Fortunately, Microsoft SQL Server 2005 provides a new keyword through which we can write queries that read the data we need. In this tutorial, we will learn how to use the new keyword of Microsoft SQL Server 2005 to implement custom paging in GridView. Custom paging looks the same as the default paging interface, but when you move from one page to another, you are several orders of magnitude less efficient.

Note: the performance improvement program brought by custom paging depends on the total amount of data and the load of the database. At the end of this tutorial, we will use data to illustrate the performance benefits of custom paging.

When paging data, the data displayed on the page depends on which page is requested and how many pieces are displayed on each page. For example, imagine that we page 81 product pages with 10 pages each. When we browse the first page, what we need is product 1 to product 10. When browsing the second page, what we need is product 11 to product 20, and so on.

There are three related variables as to what data to read and how the paged page is displayed:

Start Row Index-the index of the first row of data displayed on the page; this value can be obtained by multiplying the index of the page by the number of records displayed on each page plus 1. For example, if a page displays 10 pieces of data, for the first page (the index of the first page is 0), the index of the first row is 0 * 10 + 1, or 1; for the second page (index 1), the index of the first row is 1 * 10 + 1, that is, 11.

Maximum Rows-the maximum number of records displayed per page. It is called "maximum" rows because the data displayed on the last page may be smaller than page size. For example, when 81 records are displayed with 10 records per page, the last page, page 9, contains only one record. No page displays more records than the value of Maximum Rows.

Total Record Count-displays the total number of pieces of data. You don't need to know what data the page displays, but the total number of records affects paging. For example, if 81 product records are paginated with 10 pages each, the total number of pages is 9. 5%.

For default paging, Start Row Index is obtained by adding 1 to the page index and the number of records per page, and Maximum Rows is the number of records per page. When using default paging, no matter which page of data is rendered, all the data is read, and all the indexes of each row are known, so it becomes worthless to get Start Row Index. Moreover, the total number of records can be obtained through the total number of DataTable.

Custom paging only returns Maximum Rows records starting with Start Row Index. Here are two things to pay attention to:

We must associate the entire paged data with a row index so that we can return the required data from the specified Start Row Index.

We need to provide the total number of pieces of data used for paging.

In the next two steps, we will write the SQL related to the above two points. In addition, we will also complete the corresponding methods in DAL and BLL.

Before we learn how to return the data needed to display the page, let's take a look at how to get the total number of data. Because you need to use this information when configuring the interface. We use SQL's COUNT aggregate function to do this. For example, to return the total number of records in the Products table, we could use the following statement:

SQL1 2SELECT COUNT (*) FROM Products

We add a method to DAL to return this information. This method, called TotalNumberOfProducts (), executes the above SQL statement.

Open the Northwind.xsd in the App_Code/DAL folder. Then right-click ProductsTableAdapter in the designer and select Add Query. As we learned in previous tutorials, this allows us to add a new DAL method that executes the specified SQL or stored procedure when called. As with the previous TableAdapter method, add a SQL statement for this.

Figure 1: using SQL Statement

In the next form, we can specify which SQL to create. Since the query returns only one value-the total number of records in the Products table-we choose "SELECT which returns a singe value".

Figure 2: using SELECT Statement that Returns a Single Value to configure SQL

The next step is to write the SQL statement.

Figure 3: using the SELECT COUNT (*) FROM Products statement

Finally, the method is named TotalNumberOfProducts.

Figure 4: name the method TotalNumberOfProducts

After clicking the end, a TotalNumberOfProducts method is added to the DAL. The value returned by this method can be null, while the Count statement always returns a non-null value.

We also need to add a method to BLL. Open the ProductsBLL class file and add a TotalNumberOfProducts method. All this method needs to do is call the TotalNumberOfProducts method of DAL.

Che 12 3 4public int TotalNumberOfProducts () {return Adapter.TotalNumberOfProducts () .GetValueOrDefault ();}

DAL's TotalNumberOfProducts method returns a nullable integer, while the ProductsBLL class's TotalNumberOfProducts method returns a standard integer. Call the GetValueOrDefault method, and return the default value, 0. 0, if the nullable integer is empty.

The next step is to create methods in DAL and BLL that accept Start Row Index and Maximum Rows, and then return the appropriate records. Let's first look at the required SQL statements. The challenge is to assign an index to the entire paged record to return Maximum Records number of records records starting with Start Row Index.

If there is already a column as an index in the database table, everything will be very simple. We first think that the ProductID field of the Products table satisfies this condition, the ProductID of the first Product is 1, the second is 2, and so on. However, when a product is deleted, the sequence leaves a gap, so this method does not work.

There are two ways to associate the entire paged data with a row index.

Using the new feature of ROW_NUMBER () Keyword-SQL Server 2005 of SQL Server 2005, it can arrange records in a certain order, and each record is related to a level that can be used as a row index for each record.

SET ROWCOUNT statement using SET ROWCOUNT-SQL Server can be used to specify how many records need to be processed; table variables is a T-SQL variable that can hold a table format, similar to temporary tables. This method can be used in both Microsoft SQL Server 2005 and SQL Server 2000 (ROW_NUMBER () method can only be used in SQL Server 2005).

The idea is to create a table variable for the data to be paged, with an IDENTITY column as the key in the table variable. Each record that needs to be paged is associated with a row index (through the IDENTITY column) in the table variable. Once the table variable is generated, the SELECT statement connecting the database table is executed to get the required records. Set ROWCOUNT is used to limit the number of records placed in the table variable.

When the value of SET ROWCOUNT is specified as Start Row Index plus Maximum Rows, the efficiency of this method depends on the number of pages requested. This method is very effective for comparing previous pages-such as the first few pages of data. But for pages near the end, this method is about as efficient as the default paging.

This tutorial uses ROW_NUMBER () to implement custom paging. If you need to know more about table variables and SET ROWCOUNT techniques, see A More Efficient Method for Paging Through Large Result Sets.

The following statement is used to use ROW_NUMBER () to associate a level with each record returned:

SQL1 2 3SELECT columnList, ROW_NUMBER () OVER (orderByClause) FROM TableName

ROW_NUMBER () returns a value that represents the level of each record sorted according to the specified order. For example, we can use the following to view the level of each product sorted (descending) by price:

SQL1 2 3SELECT ProductName, UnitPrice, ROW_NUMBER () OVER (ORDER BY UnitPrice DESC) AS PriceRank FROM Products

Figure 5 shows the result of running the above code in Visual Studio. Note that product is sorted by price, with one level for each line.

Figure 5: the returned record has one Price Rank per row

Note: ROW_NUMBER () is just one of the many rank functions in SQL Server 2005. To learn more about the discussion of ROW_NUMBER (), including other ranking features, see Returning Ranked Results with Microsoft SQL Server 2005.

When you use the ORDER BY column name (UnitPrice) in the OVER clause to rank, SQL Server sorts the results. To improve the performance of large data queries, nonclustered indexes can be added to the columns used for sorting. For more performance considerations, refer to Ranking Functions and Performance in SQL Server 2005.

The rank information returned by ROW_NUMBER () cannot be used directly in the WHERE clause. You can return ROW_NUMBER () in the Select after From and use it in the WHERE clause. For example, the following statement uses a Select after From to return the results of ProductName,UnitPrice and ROW_NUMBER (), and then uses a WHERE clause to return product with price rank between 11 and 20.

SQL1 2 3 4 5 6 7SELECT PriceRank, ProductName, UnitPrice FROM (SELECT ProductName, UnitPrice, ROW_NUMBER () OVER (ORDER BY UnitPrice DESC) AS PriceRank FROM Products) AS ProductsWithRowNumber WHERE PriceRank BETWEEN 11 AND 20

Further, we can return data for pages given Start Row Index and Maximum Rows according to this method.

SQL1 2 3 4 5 6 7SELECT PriceRank, ProductName, UnitPrice FROM (SELECT ProductName, UnitPrice, ROW_NUMBER () OVER (ORDER BY UnitPrice DESC) AS PriceRank FROM Products) AS ProductsWithRowNumber WHERE PriceRank > StartRowIndex AND PriceRank @ startRowIndex AND RowRank

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.