25-大数据量时提高分页的效率.doc

导言如我们在之前的教程里讨论的那样,分页可以通过两种方法来实现: 默认分页 你仅仅只用选中data Web control的 智能标签的Enable Paging ; 然而,当你浏览页面的时候,虽然你看到的只是一小部分数据,ObjectDataSource 还是会每次都读取所有数据 自定义分页 通过只从数据库读取用户需要浏览的那部分数据,提高了性能.显然这种方法需要你做更多的工作. 默认的分页功能非常吸引人,因为你只需要选中一个checkbox就可以完成了.但是它每次都读取所有的数据,这种方式在大数据量或者并发用户多的情况下就不合适.在这样的情况下,我们必须通过自定义分页来使系统达到更好的性能.自定义分页的一个重点是要写一个返回仅仅需要的数据的查询语句.幸运的,Microsoft SQL Server 2005 提供了一个新的keyword,通过它我们可以写出读取需要的数据的查询.在本教程里,我们将学习在GridView里如何使用Microsoft SQL Server 2005 的这个新的keyword来实现自定义分页.自定义分页和默认分页的界面看起来一样,但是当你从一页转到另一页时,在效率上差了几个数量级.注意:自定义分页带来的性能提升程序取决于数据的总量和数据库的负载.在本教程的最后我们会用数据来说明自定义分页带来的性能方面的好处.第一步: 理解自定义分页的过程给数据分页的时候,页面显示的数据取决于请求的是哪一页和每页显示多少条.比如,想象以下我们给81个product分页,每页显示10条.当我们浏览第一页时,我们需要的是product 1 到 product 10.当浏览第二页时,我们需要的是product 11 到 product 20,以次类推.对于需要读取什么数据和分页的页面怎么显示,有三个相关的变量: Start Row Index 页面里显示数据的第一行的索引; 这个值可以通过页的索引乘每页显示的记录的条数加1得到. 例如, 如果一页显示10条数据, 那么对第一页来说(第一页的索引为0), 第一行的索引为0 * 10 + 1, or 1; 对第二页来说(索引为1), 第一行的索引为1 * 10 + 1,即 11. Maximum Rows 每页显示的最多记录的条数. 之所以称为“maximum” rows 是由于最后一页显示的数据可能会比page size要小. 比如, 当以每页10条记录来显示81条时, 最后一页也就是第九页只包含一条记录. 没有页面显示的记录条数会大于Maximum Rows 的值. Total Record Count 显示数据的总条数. 不需要知道页面显示什么数据,但是记录总数会影响到分页. 比如, 如果对81条product记录分页,每页10条,那么总页数为9. 对默认分页来说,Start Row Index是由页索引和每页的记录数加1得到,Maximum Rows 就是每页的记录数.使用默认分页时,不管是呈现哪页的数据,都是要读取全部的数据,所有每行的索引都是已知的,这样获取Start Row Index变的没有价值.而且,记录的总条数是可以通过DataTable的总条数来获取的.自定义分页只返回从Start Row Index 开始的Maximum Rows条记录.在这里有两个要注意的地方: 我们必须把整个要分页的数据和一个row index关联起来,这样才能从指定的Start Row Index 开始返回需要的数据. 我们需要提供用来分页的数据的总条数. 在后面的两步里我们将写出和上面两点相关的SQL.除此之外，我们还将在DAL和BLL里完成相应的方法.第二步: 返回需要分页的记录的总条数在我们学习如何返回显示页面需要的数据之前,我们先来看看怎么获取数据的总条数.因为在配置界面的时候需要用到这个信息.我们使用SQL的COUNT aggregate function来实现这个.比如,返回Products表的总记录条数,我们可以用如下的语句:SELECT COUNT(*)FROM Products我们在DAL里添加一个方法来返回这个信息.这个方法名为TotalNumberOfProducts() ,它会执行上面的SQL语句.打开App_Code/DAL 文件夹里的 Northwind.xsd .然后在设计器里右键点ProductsTableAdapter ,选择Add Query.和我们在以前的教程里学习的那样,这样会允许我们添加一个新的DAL方法,这个方法被调用时会执行指定的SQL或存储过程.和前面的TableAdapter 方法一样,为这个添加一个SQL statement.图 1:使用 SQL Statement在下一个窗体我们可以指定创建哪种SQL .由于查询只返回一个值Products表的总记录条数我们选择“SELECT which returns a singe value”.图 2:使用 SELECT Statement that Returns a Single Value来配置SQL下一步是写SQL语句.图 3: 使用SELECT COUNT(*) FROM Products 语句最后给这个方法命名为TotalNumberOfProducts.图 4:将方法命名为 TotalNumberOfProducts点击结束后,DAL里添加了一个TotalNumberOfProducts方法.这个方法返回的值可为空,而Count语句总是返回一个非空的值.我们还需要在BLL中加一个方法.打开ProductsBLL类文件,添加一个TotalNumberOfProducts方法,这个方法要做的只是调用DAL的TotalNumberOfProducts方法.public int TotalNumberOfProducts() return Adapter.TotalNumberOfProducts().GetValueOrDefault();DAL的TotalNumberOfProducts方法返回一个可空的整型,而需要ProductsBLL类的TotalNumberOfProducts方法返回一个标准的整型.调用GetValueOrDefault方法,如果可为空的整型为空,则返回默认值,0.第三步: 返回需要的数据记录下一步我们要在DAL和BLL里创建接受Start Row Index 和Maximum Rows 的方法,然后返回合适的记录.我们首先看看需要的SQL语句.我们面临的挑战是需要为整个分页的记录分配索引,用来返回从Start Row Index 开始的Maximum Records number of records条记录.如果在数据库表里已经有一个列作为索引,那么一切会变的很简单.我们首先会想到Products表的ProductID字段可以满足这个条件,第一个Product的ProductID为1,第二个为2,以此类推.然而当一个product被删除后,这个序列会留下间隔来,所以这个方法不行.有两种可以把整个要分页的数据和一个row index关联起来的方法. 使用SQL Server 2005的ROW_NUMBER() Keyword SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index. 使用SET ROWCOUNT SQL Server的 SET ROWCOUNT statement 可以用来指定有多少记录需要处理; table variables 是可以存放表格式的T-SQL 变量, 和temporary tables类似. 这个方法在Microsoft SQL Server 2005 和SQL Server 2000都可以用 (ROW_NUMBER() 方法只能在SQL Server 2005里用).这个思路是,为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里就和一个row index(通过IDENTITY列)关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SET ROWCOUNT用来限制放到table变量里的记录的数量.当SET ROWCOUNT的值指定为Start Row Index 加上Maximum Rows时,这个方法的效率取决于被请求的页数.对于比较前面的页来说 比如开始几页的数据 这种方法非常有效. 但是对接近尾部的页来说,这种方法的效率和默认分页时差不多. 本教程用ROW_NUMBER()来实现自定义分页.如果需要知道更多的关于table变量和SET ROWCOUNT的技术,请看 A More Efficient Method for Paging Through Large Result Sets.以下语句用来使用ROW_NUMBER()将一个等级和返回的每条记录关联:SELECT columnList, ROW_NUMBER() OVER(orderByClause)FROM TableNameROW_NUMBER()返回一个根据指定排序的表示每条记录的等级的值.比如,我们可以用以下居于查看根据价格来排序(降序)的每个product的等级:SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRankFROM Products图5 是在Visual Studio里运行以上代码的结果. 注意product根据价格排序,每行有一个等级.图 5: 返回的记录里每行有一个Price Rank 注意: ROW_NUMBER() 只是 SQL Server 2005里很多排级的功能中的一种. 想了解更多的ROW_NUMBER()的讨论,包括其它的排级功能,请看 Returning Ranked Results with Microsoft SQL Server 2005.当使用OVER从句里的ORDER BY 列名(UnitPrice)来排级时,SQL Server会对结果排序.为了提升大数据量查询时的性能,可以为用来排序的列加上非聚集索引.更多的性能考虑参考Ranking Functions and Performance in SQL Server 2005.ROW_NUMBER()返回的等级信息无法直接在WHERE从句中使用.而在From后面的Select里可以返回ROW_NUMBER(),并在WHERE从句里使用.比如,下面的语句使用一个From后的Select返回ProductName,UnitPrice,和ROW_NUMBER()的结果,然后使用一个WHERE从句来返回price rank在11到20之间的product.SELECT PriceRank, ProductName, UnitPriceFROM (SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products ) AS ProductsWithRowNumberWHERE PriceRank BETWEEN 11 AND 20更进一步,我们可以根据这个方法返回给定Start Row Index 和Maximum Rows 的页的数据.SELECT PriceRank, ProductName, UnitPriceFROM (SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products ) AS ProductsWithRowNumberWHERE PriceRank StartRowIndex AND PriceRank = (StartRowIndex + MaximumRows)注意:我们在本教程的后面会看到, ObjectDataSource 提供的StartRowIndex是从0开始的,而ROW_NUMBER()的值从1开始.因此,WHERE从句返回会严格返回PriceRank大于StartRowIndex而小于StartRowIndex+MaximumRows的那些记录.我们已经知道如何根据给定的Start Row Index 和Maximum Rows 用ROW_NUMBER()返回特定页的数据.现在我们需要在DAL和BLL里实现它.我们首先要决定根据什么排序来分级.我们这里用product名字的字母顺序.这意味着我们还不能同时实现排序的功能.在后面的教程里,我们将学习如何实现这样的功能.在前面我们使用SQL statement创建DAL方法.但是TableAdapter wizard 使用的Visual Stuido里的T-SQL 解析器不能识别带OVER语法的ROW_NUMBER()方法.因此我们要以存储过程来创建这个DAL方法.从view menu里选择server explorer(Ctrl+Alt+S),展开NORTHWND.MDF 的节点.右键点击存储过程,选择增加一个新的存储过程(见图6).图 6: 为Products分页增加一个存储过程这个存储过程带两个整型的输入参数- startRowIndex和maximumRows- 并用ROW_NUMBER()以ProductName字段排序,返回那些大于startRowIndex并小于等于startRowIndex+maximumRows的记录.将以下代码加到存储过程里,然后保存.CREATE PROCEDURE dbo.GetProductsPaged( startRowIndex int, maximumRows int)AS SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, CategoryName, SupplierNameFROM ( 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 ProductsWithRowNumbersWHERE RowRank startRowIndex AND RowRank = (startRowIndex + maximumRows)创建完存储过程后,花点时间测试一下.右键在Server Explorer 点名为GetProductsPaged的存储过程,选择执行.Visual Studio 会让你输入参数, startRowIndex和maximumRows(见图7).输入不同的值查看一下结果是什么.图 7: 为 startRowIndex 和maximumRows Parameters输入值输入参数的值后,你会看到结果.图8的结果为两个参数的值都为10的结果.图 8: 将在第二页里显示的数据完成存储过程后,我们可以创建ProductsTableAdapter 方法了.打开Northwind.xsd ,右键点ProductsTableAdapter,选择Add Query.选择使用已经存在的存储过程.图 9: 使用已经存在的存储过程创建DAL Method 下一步会要我们选择要调用的存储过程.从下拉列表里选择GetProductsPaged .图10: 选择GetProductsPaged 下一步要选择存储过程返回的数据类型:表值,单一值,无值.由于GetProductsPaged 返回多条记录,所以选择表值.图 11: 为存储过程指定返回表值最后给方法命名.象前面的方法一样,选择Fill a DataTable 和Return a DataTable,为第一个命名为FillPaged ,第二个为GetProductsPaged.图 12: 命名方法为FillPaged 和GetProductsPaged除了创建一个DAL方法返回特定页的products外,我们需要在BLL里也这样做.和DAL方法一样,BLL的GetProductsPaged 方法带两个整型的输入参数,分别为Start Row Index 和Maximum Rows,并返回在指定范围内的记录.在ProductsBLL 创建这个方法,仅仅调用DAL的GetProductsPaged 就可以了.System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)public Northwind.ProductsDataTable GetProductsPaged(int startRowIndex, int maximumRows) return Adapter.GetProductsPaged(startRowIndex, maximumRows);你可以为BLL方法的参数取任何名字.但是我们马上会看到,选择用startRowIndex 和maximumRows 会让我们在配置ObjectDataSource 时方便很多.第四步: 使用自定义分页配置ObjectDataSource 创建完BLL和DAL的方法后,我们可以准备创建一个GridView 来使用自定义分页了.打开PagingAndSorting 文件夹里的EfficientPaging.aspx ,添加一个GridView ,然后用ObjectDataSource 来配置它.在我们以前的教程里,我们通常使用ProductsBLL 类的GetProducts 方法来配置ObjectDataSource .然而这一次,我们使用GetProductsPaged 方法.GetProducts 会返回所有的products而GetProductsPaged 只返回特定的记录.图 13: 使用ProductsBLL Class类的 GetProductsPaged方法 来配置ObjectDataSource 我们要创建一个只读的GridView,因此在INSERT, UPDATE, 和DELETE 标签下拉列表里选择(None).接下来ObjectDataSource 向导会让我们选择GetProductsPaged 方法的输入参数startRowIndex 和maximumRows 的值.在source里选择none.图 14: Sources 里选择None完成ObjectDataSource 向导后,GridView 会为每个product字段创建一个BoundField 或CheckBoxField .可以随意裁减GridView 的外观.我这里选择的是只显示ProductName, CategoryName, SupplierName, QuantityPerUnit, 和UnitPrice BoundFields.在智能标签里选择支持分页,GridView 和ObjectDataSource 的标记看起来应该和下面差不多: 如果你通过浏览器浏览页面,你会发现看不到GridView .图 15: GridView 没有被显示由于在ObjectDataSource 里的GetProductsPaged的startRowIndex和maximumRows的参数都为0,由SQL没有返回任何的记录因此GridView 看不到了.我们需要将ObjectDataSource 配置成为自定义分页来修补上面的问题.下面的步骤可以完成这个:1. 将ObjectDataSource的 EnablePaging 属性设为true 这样表示必须传两个参数给SelectMethod方法: 一个指定Start Row Index (StartRowIndexParameterName), 一个指定Maximum Rows (MaximumRowsParameterName). 2. 设置ObjectDataSource的 StartRowIndexParameterName 和MaximumRowsParameterName 属性 StartRowIndexParameterName 和MaximumRowsParameterName 属性是传给SelecMethod用来自定义分页的输入参数. 默认的参数名为startIndexRow and MaximumRows, 这就是在创建BLL里的GetProductsPaged方法时用这些给参数命名的原因. 如果你使用了其它的参数名字比如startIndex和maxRows你将不得不相应的设置ObjectDataSource的StartRowIndexParameterName和MaximumRowsParameterName(startIndex和maxRows). 3. 设置ObjectDataSource的 SelectCountMethod Property 为返回分页记录总数的方法的名字(TotalNumberOfProducts)调用ProductsBLL类的TotalNumberOfProducts方法返回总的记录数 . ObjectDataSource 需要这个信息来正确的显示页面. 4. 从ObjectDataSource的声明里移除startRowIndex and maximumRows 元素的标记当通过向导配置 ObjectDataSource 时, Visual Studio 自动为GetProductsPaged方法的参数增加了两个 元素. 设置EnablePaging 为true后, 这些参数会被自动传递;如果在声明代码里保留它们,那么ObjectDataSource会试图传递4个参数给GetProductsPaged和2个参数给TotalNumberOfProducts .如果你没有移除,当浏览页面的时候你会获得一个象这样的错误信息 : “ObjectDataSource ObjectDataSource1 could not find a non-generic method TotalNumberOfProducts that has parameters: startRowIndex, maximumRows.” 做完这些改动后,ObjectDataSource的声明代码看起来应该和下面差不多:注意EnablePaging和SelectCountMethod属性已经被设置了,被移除了.图16是属性窗口.图16: 使用自定义分页配置,ObjectDataSource 完成这些后,浏览页面.你会看到10条product按照字母排序被列出来了.每次翻一页看看.对用户来说现在还看不出来什么差别,因为自定义分页在大数据量的情况下效率才能显示出来.图17: 根据Product的 Name排序的数据的自定义分页注意:自定义分页时,ObjectDataSource的SelectCountMethod方法返回的page count值存在GridView的view state里.其它变量PageIndex,EditIndex,SelectedIndex,DataKeys集合等都存在control state里.control state和GridView的EnableViewState属性无关.由于PageCount的值在postback期间存在viewstate里,当你的页面上有链到上一页的link时,你需要开启GridView的view state(如果没有这个link,你可以禁用view state).点上一页link会引起postback,GridView会更新PageIndex属性.GridView会给PageIndex赋一个小于PageCount的值.如果禁用了view state,PageCount的值在postback时会丢失,PageIndex会被赋一个最大的整型值.然后GridView在根据PageSize乘PageCount来计算starting row index时会发生OverflowException异常.执行自定义分页和排序目前我们自定义分页时使用的排序字段是在创建GetProductsPaged存储过程时写死的.在GridView的智能标签里有一个Enable Sorting的checkbox,不幸的是,在前面的工作里加上排序功能仅仅只能将当前页的记录排序.比如,按照降序查看第一页的数据,第一页的product的顺序回反转.见图18,Carnarvon Tigers 成为第一条记录,而在它之后的71条记录被忽略了.排序时只排了显示在第一页的数据.图18: 只有当前页的数据被排序了发生这种情况的原因是调用完BLL的GetProductsPaged方法返回数据之后才排序.耳针个方法只返回特定页的记录.为了正确的排序,我们需要将排序表达式传到GetProductsPaged方法里,在返回特定页的数据前进行排序.我们将在后面的教程里完成这个功能.执行自定义分页和删除如果你开启GridView的删除功能,你会发现删除最后一页的最后一条记录时,GridView消失了,而不是正确的减掉PageIndex的值.在我们上面创建的GridView里开启删除来查看这个bug.到最后一页(第九页),由于我们有81条记录,每页显示10条,所以你会只看到一条记录,删除这条记录.在默认分页时,GridView会自动跳到第八页,这也是我们想要的结果.然而在自定义分页里, GridView却显示.发生这个的原因有点超出了本教程的范围,可以看Deleting the Last Record on the Last Page from a GridView with Custom Paging.简单的说是因为点Delete时,GridView是按这样的步骤工作的:1. 删除记录. 2. 按照给定的PageIndex和PageSize获取记录. 3. 检查PageIndex确保没有超过数据源的页的数量.如果是,GridView的PageIndex会自动减. 4. 使用第二步获取的记录绑定到GridView适当的页. 问题的根源在于第二步,当获取显示的记录时,使用的PageIndex仍然是最后一页的PageIndex.因此没有记录被返回.在第三步里GridView判断出PageIndex属性大于数据源的总页数(因为最后一页的最后一条数据被删除了) 就对PageIndex减1.在第四步里GridView试图将第二步获取的数据作为数据源进行绑定,但是没有任何数据,因此显示的GridView不见了.在默认分页里没有这个问题是因为在第二步还是返回的所有数据.我们可以用两种方法来修改这个.第一是为GridView的RowDeleted事件创建一个event handler来判断在删除页里有多少条记录,如果只有一条,那么这条肯定是最后一条,我们需要为PageIndex减1.当然我们希望只在删除成功后来修改PageIndex的值.我们需要用e.Exception属性是否为空来判断.这个方法之所以起作用是因为它在第一步和第二步之间修改了PageIndex的值.因此在第二步里正确的记录会被返回.见如下代码:protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e) / If we just deleted the last row in the GridView, decrement the PageIndex if (e.Exception = null & GridView1.Rows.Count = 1) / we just deleted the last row GridView1.PageIndex = Math.Max(0, GridView1.PageIndex - 1);另外一种办法是为ObjectDataSource的RowDeleted事件创建一个event handler,设置AffectedRows属性为1.在第一步删除记录后(在第二步之前),如果一行或多行记录被影响,GridView会更新PageIndex的值.然而ObjectDataSource 并没有设置AffectedRows,因此这一步不会执行.我们需要在删除操作成功的情况下手动设置AffectedRows.见下面的代码:protected void ObjectDataSource1_Deleted( object sender, ObjectDataSourceStatusEventArgs e) / If we get back a Boolean value from the DeleteProduct method and its tr