Increase paging efficiency when there is a large amount of data

As we discussed in the previous tutorial, paging can be implemented in two ways:

• Default Page-you only need to select the Enable Paging of the Smart tag of data Web control. However, when you browse the page, although you only see a small amount of data, objectDataSource still reads all data each time.
• Custom paging-improves performance by reading only the data that the user needs to browse from the database. Obviously, this method requires more work.

The default paging function is very attractive because you only need to select a checkbox. however, it reads all the data each time. This method is not suitable when there is a large amount of data or a large number of concurrent users. in this case, we must use custom pages to achieve better system performance.

One of the key points of custom paging is to write a query statement that returns only required data. fortunately, Microsoft SQL Server 2005 provides a new keyword, through which we can write a query that reads the required data. in this tutorial, we will learn how to use the new keyword of Microsoft SQL Server 2005 In the GridView to implement custom paging. the user-defined page looks the same as the default page, but when you switch from one page to another, the efficiency is several orders of magnitude.

Note: The program for improving the performance of custom pages depends on the total data volume and database load. At the end of this tutorial, we will use data to describe the performance benefits of custom pages.

Step 1: understand the process of custom Paging

When data is paged, the data displayed on the page depends on the requested page and the number of records displayed on each page. for example, imagine that we will pagination 81 products, with 10 entries per page. when we browse the first page, we need product 1 to product 10. when browsing the second page, we need product 11 to product 20, and so on.

There are three related variables:

• Start Row Index-the Index of the first Row of data is displayed on the page. This value can be obtained by adding 1 to the number of records displayed on each page by page Index. for example, if 10 pieces of data are displayed on one page, For the first page (the index on the first page is 0), the index on the first line is 0*10 + 1, or 1; for the second page (index is 1), the index of the first row is 1*10 + 1, that is, 11.
• Maximum Rows-Maximum number of records per page. maximum rows is called because the data displayed on the last page may be smaller than the page size. for example, if 81 records are displayed on each page, the last page, that is, the ninth page, contains only one record. the number of records not displayed on the page is greater than the value of Maximum Rows.
• Total Record Count-displays the Total number of data records. you do not need to know what data is displayed on the page, but the total number of records will affect the page. for example, if 81 product records are displayed on a page with 10 records per page, the total number of pages is 9.

For the default page, the Start Row Index is obtained by adding 1 to the page Index and the number of records on each page. Maximum Rows is the number of records on each page. when the default page is used, no matter which page of data is displayed, all data is read, and the indexes of all rows are known, so that the value of Start Row Index is no longer available. in addition, the total number of records can be obtained through the total number of DataTable items.

Only Maximum Rows records starting from Start Row Index are returned on the custom page. There are two points to note:

• We must associate the data to be paged with a row index so that the required data can be returned from the specified Start Row Index.
• We need to provide the total number of data records 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 the DAL and BLL.

Step 2: return the total number of records to be paged

Before learning how to return the data required to display the page, let's take a look at how to get the total number of data records, because this information is required during the configuration interface. We use SQLCOUNT aggregate functionFor example, to return the total number of records in the Products table, we can use the following statement:

SQL
1 2	SELECT COUNT(*) FROM Products

We add a method in the DAL to return this information. This method is named TotalNumberOfProducts () and runs the preceding SQL statement.

Open the Northwind in the App_Code/DAL folder. xsd. right-click ProductsTableAdapter In the designer and select Add Query. as we learned in the previous tutorial, this will allow us to add a new DAL method, which will execute the specified SQL or stored procedure when called. like the preceding TableAdapter method, add an SQL statement for this statement.

Figure 1: use SQL Statement

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

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

The next step is to write SQL statements.

Figure 3: Use the select count (*) FROM Products statement

Finally, name this method TotalNumberOfProducts.

Figure 4: Name the method TotalNumberOfProducts

After clicking, 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 in BLL. Open the ProductsBLL class file and add a TotalNumberOfProducts method. All we need to do is call the DAL TotalNumberOfProducts method.

C #
1 2 3 4	public int TotalNumberOfProducts() { return Adapter.TotalNumberOfProducts().GetValueOrDefault(); }

The TotalNumberOfProducts method of DAL returns an integer that can be empty, while the TotalNumberOfProducts method of the ProductsBLL class returns a standard integer. call the GetValueOrDefault method. If an empty integer is null, the default value 0 is returned.

Step 3: return the required data records

Next we will create a method in DAL and BLL to accept Start Row Index and Maximum Rows, and then return the appropriate records. first, let's look at the required SQL statements. the challenge is to allocate indexes to Records on the entire page to return the Maximum records number of Records records starting from the Start Row Index.

If there is already a column in the database table as an index, everything will become very simple. we will first think that the ProductID field of the Products table can meet this condition. The ProductID of the first Product is 1, the second is 2, and so on. however, when a product is deleted, this sequence will be left at intervals, so this method will not work.

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

• Use the new feature ROW_NUMBER () Keyword-SQL Server 2005 of SQL Server 2005, which can sort records in a certain order, each record is associated with a level. This level can be used as the row index of each record.
• UseSet rowcount statementIt can be used to specify the number of records to be processed;Table variablesIs a T-SQL variable that can store tables, andTemporary tablesSimilar. This method can be used in both Microsoft SQL Server 2005 and SQL Server 2000 (the ROW_NUMBER () method can only be used in SQL Server 2005 ).
  
  
  This idea is to create a table variable for the data to be paged. This table variable contains an IDENTITY column as the master key. in this way, each record on the page needs to be associated with a row index (through the IDENTITY column) in the table variable. once the table variable is generated, the SELECT statement connecting to the database table is executed to obtain 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 the previous page-for example, the data on the first few pages. however, for pages close to the end, the efficiency of this method is similar to that of default pages.

This tutorial uses ROW_NUMBER () to implement custom paging. For more information about table variables and set rowcount, seeA More Efficient Method for Paging Through Large Result Sets.

Use ROW_NUMBER () to associate a level with each returned record:

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

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

SQL
1 2 3	SELECT 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 the product is sorted by price and each line has a level.

Figure 5: each row of the returned record has a Price Rank

Note: ROW_NUMBER () is only one of the many row-level functions in SQL Server 2005. For more information about ROW_NUMBER (), including other row-level functions, seeReturning Ranked Results with Microsoft SQL Server 2005.

When the order by column name (UnitPrice) in the OVER clause is used for sorting, SQL Server sorts the results. to improve the query performance of large data volumes, you can add non-clustered indexes to the columns used for sorting. more performance considerationsRanking Functions and Performance in SQL Server 2005.

The level information returned by ROW_NUMBER () cannot be directly used in the WHERE clause. in the Select clause after the From clause, ROW_NUMBER () can be returned and used in the WHERE clause. for example, the following statement returns the results of ProductName, UnitPrice, and ROW_NUMBER () using a Select statement after the From statement, and then returns the product with the price rank between 11 and 20 using a WHERE clause.

SQL
1 2 3 4 5 6 7	SELECT 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

Furthermore, we can return the data of the page for the given Start Row Index and Maximum Rows based on this method.

SQL
1 2 3 4 5 6 7	SELECT PriceRank, ProductName, UnitPrice FROM (SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products ) AS ProductsWithRowNumber WHERE PriceRank > <i>StartRowIndex</i> AND PriceRank <= (<i>StartRowIndex</i> + <i>MaximumRows</i>)

Note: we will see later in this tutorial that the StartRowIndex provided by ObjectDataSource starts from 0, while the ROW_NUMBER () value starts from 1. therefore, the WHERE clause returns records whose PriceRank is greater than StartRowIndex and smaller than StartRowIndex + MaximumRows.

We already know how to return data for a specific page based on the given Start Row Index and Maximum Rows using ROW_NUMBER (). Now we need to implement it in the DAL and BLL.

First, we need to determine which order to classify. here we use the product name in alphabetical order. this means that the sorting function cannot be implemented at the same time. in the subsequent tutorial, we will learn how to implement this function.

We used SQL statement to create the DAL method. however, the T-SQL parser in Visual Stuido used by TableAdapter wizard cannot identify the ROW_NUMBER () method with OVER syntax. therefore, we need to create this DAL method using a stored procedure. select server explorer (Ctrl + Alt + S) from the view menu and expand NORTHWND. MDF node. right-click the stored procedure and choose Add a new stored procedure (see figure 6 ).

Figure 6: Add a stored procedure to the Products Page

This stored procedure takes two integer input parameters-@ startRowIndex and @ maximumRows-and uses ROW_NUMBER () to sort the data in ProductName fields, returns records greater than @ startRowIndex and less than or equal to @ startRowIndex + @ maximumRows. add the following code to the stored procedure and save it.

SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

CREATE PROCEDURE dbo.GetProductsPaged ( @startRowIndex int, @maximumRows int ) AS SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, CategoryName, SupplierName FROM ( SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName, ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank FROM Products ) AS ProductsWithRowNumbers WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

After creating the stored procedure, take some time to test it. right-click the Stored Procedure named GetProductsPaged in Server Explorer and select execute. visual Studio allows you to enter the parameters @ startRowIndex and @ maximumRows (see figure 7 ). enter different values to see what the result is.

Figure 7: input values for @ startRowIndex and @ maximumRows Parameters

After you enter the value of the parameter, you will see the result. The result in Figure 8 is that the values of both parameters are 10.

Figure 8: Data to be displayed on the second page

After the stored procedure is complete, we can create the ProductsTableAdapter method. Open Northwind. xsd, right-click ProductsTableAdapter, and select Add Query. Select an existing stored procedure.

Figure 9: using an existing stored procedure to create a DAL Method

Next, select the stored procedure to be called. Select GetProductsPaged from the drop-down list.

Figure 10: Select GetProductsPaged

Next, select the data type returned by the stored procedure: Table value, single value, and no value. Because GetProductsPaged returns multiple records, select the table value.

Figure 11: Specify the returned Table value for the stored procedure

Finally, name the method. Like the previous method, select Fill a able and Return a DataTable, and name the first one FillPaged and the second one GetProductsPaged.

Figure 12: naming methods: FillPaged and GetProductsPaged

In addition to creating a DAL method to return the products of a specific page, we need to do the same in BLL. like the DAL method, BLL's GetProductsPaged method has two integer input parameters: Start Row Index and Maximum Rows, and returns records within the specified range. create this method in ProductsBLL and only call the GetProductsPaged of DAL.

C #
1 2 3 4 5	[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)] public Northwind.ProductsDataTable GetProductsPaged(int startRowIndex, int maximumRows) { return Adapter.GetProductsPaged(startRowIndex, maximumRows); }

You can take any name for the parameters of the BLL method. But we will immediately see that choosing startRowIndex and maximumRows will make it much easier for us to configure ObjectDataSource.