二十五大数据量时提高分页的效率.docx
《二十五大数据量时提高分页的效率.docx》由会员分享，可在线阅读，更多相关《二十五大数据量时提高分页的效率.docx（27页珍藏版）》请在冰豆网上搜索。
二十五大数据量时提高分页的效率
在ASP.NET2.0中操作数据之二十五：
大数据量时提高分页的效率
作者：
heker2007字体：
[增加 减小]类型：
转载时间：
2016-05-07 我要评论
上一篇我们介绍过利用GirdView控件内置的分页功能进行分页，但是熟悉ASP.NET的人都知道，那不是真正意义上的分页，本文就带着大家利用利用存储过程创建高效的分页。
导言
如我们在之前的教程里讨论的那样,分页可以通过两种方法来实现:
１.默认分页–你仅仅只用选中dataWebcontrol的智能标签的EnablePaging;然而,当你浏览页面的时候,虽然你看到的只是一小部分数据,ObjectDataSource还是会每次都读取所有数据
２.自定义分页–通过只从数据库读取用户需要浏览的那部分数据,提高了性能.显然这种方法需要你做更多的工作.
默认的分页功能非常吸引人,因为你只需要选中一个checkbox就可以完成了.但是它每次都读取所有的数据,这种方式在大数据量或者并发用户多的情况下就不合适.在这样的情况下,我们必须通过自定义分页来使系统达到更好的性能.
自定义分页的一个重点是要写一个返回仅仅需要的数据的查询语句.幸运的,MicrosoftSQLServer2005提供了一个新的keyword,通过它我们可以写出读取需要的数据的查询.在本教程里,我们将学习在GridView里如何使用MicrosoftSQLServer2005的这个新的keyword来实现自定义分页.自定义分页和默认分页的界面看起来一样,但是当你从一页转到另一页时,在效率上差了几个数量级.
注意:
自定义分页带来的性能提升程序取决于数据的总量和数据库的负载.在本教程的最后我们会用数据来说明自定义分页带来的性能方面的好处.
第一步:
理解自定义分页的过程
给数据分页的时候,页面显示的数据取决于请求的是哪一页和每页显示多少条.比如,想象以下我们给81个product分页,每页显示10条.当我们浏览第一页时,我们需要的是product1到product10.当浏览第二页时,我们需要的是product11到product20,以次类推.
对于需要读取什么数据和分页的页面怎么显示,有三个相关的变量:
１.StartRowIndex–页面里显示数据的第一行的索引;这个值可以通过页的索引乘每页显示的记录的条数加1得到.例如,如果一页显示10条数据,那么对第一页来说（第一页的索引为0）,第一行的索引为0*10+1,or1;对第二页来说（索引为1）,第一行的索引为1*10+1,即11.
２.MaximumRows–每页显示的最多记录的条数.之所以称为“maximum”rows是由于最后一页显示的数据可能会比pagesize要小.比如,当以每页10条记录来显示81条时,最后一页也就是第九页只包含一条记录.没有页面显示的记录条数会大于MaximumRows的值.
３.TotalRecordCount–显示数据的总条数.不需要知道页面显示什么数据,但是记录总数会影响到分页.比如,如果对81条product记录分页,每页10条,那么总页数为9.
对默认分页来说,StartRowIndex是由页索引和每页的记录数加1得到,MaximumRows就是每页的记录数.使用默认分页时,不管是呈现哪页的数据,都是要读取全部的数据,所有每行的索引都是已知的,这样获取StartRowIndex变的没有价值.而且,记录的总条数是可以通过DataTable的总条数来获取的.
自定义分页只返回从StartRowIndex开始的MaximumRows条记录.在这里有两个要注意的地方:
１.我们必须把整个要分页的数据和一个rowindex关联起来,这样才能从指定的StartRowIndex开始返回需要的数据.
２.我们需要提供用来分页的数据的总条数.
在后面的两步里我们将写出和上面两点相关的SQL.除此之外，我们还将在DAL和BLL里完成相应的方法.
第二步:
返回需要分页的记录的总条数
在我们学习如何返回显示页面需要的数据之前,我们先来看看怎么获取数据的总条数.因为在配置界面的时候需要用到这个信息.我们使用SQL的COUNTaggregatefunction来实现这个.比如,返回Products表的总记录条数,我们可以用如下的语句:
?
1
2
SELECTCOUNT（*）
FROMProducts
我们在DAL里添加一个方法来返回这个信息.这个方法名为TotalNumberOfProducts（）,它会执行上面的SQL语句.
打开App_Code/DAL文件夹里的Northwind.xsd.然后在设计器里右键点ProductsTableAdapter,选择AddQuery.和我们在以前的教程里学习的那样,这样会允许我们添加一个新的DAL方法,这个方法被调用时会执行指定的SQL或存储过程.和前面的TableAdapter方法一样,为这个添加一个SQLstatement.
图1:
使用SQLStatement
在下一个窗体我们可以指定创建哪种SQL.由于查询只返回一个值–Products表的总记录条数–我们选择“SELECTwhichreturnsasingevalue”.
图2:
使用SELECTStatementthatReturnsaSingleValue来配置SQL
下一步是写SQL语句.
图3:
使用SELECTCOUNT（*）FROMProducts语句
最后给这个方法命名为TotalNumberOfProducts.
图4:
将方法命名为TotalNumberOfProducts
点击结束后,DAL里添加了一个TotalNumberOfProducts方法.这个方法返回的值可为空,而Count语句总是返回一个非空的值.
我们还需要在BLL中加一个方法.打开ProductsBLL类文件,添加一个TotalNumberOfProducts方法,这个方法要做的只是调用DAL的TotalNumberOfProducts方法.
?
1
2
3
4
publicintTotalNumberOfProducts（）
{
returnAdapter.TotalNumberOfProducts（）.GetValueOrDefault（）;
}
DAL的TotalNumberOfProducts方法返回一个可空的整型,而需要ProductsBLL类的TotalNumberOfProducts方法返回一个标准的整型.调用GetValueOrDefault方法,如果可为空的整型为空,则返回默认值,0.
第三步:
返回需要的数据记录
下一步我们要在DAL和BLL里创建接受StartRowIndex和MaximumRows的方法,然后返回合适的记录.我们首先看看需要的SQL语句.我们面临的挑战是需要为整个分页的记录分配索引,用来返回从StartRowIndex开始的MaximumRecordsnumberofrecords条记录.
如果在数据库表里已经有一个列作为索引,那么一切会变的很简单.我们首先会想到Products表的ProductID字段可以满足这个条件,第一个Product的ProductID为1,第二个为2,以此类推.然而当一个product被删除后,这个序列会留下间隔来,所以这个方法不行.
有两种可以把整个要分页的数据和一个rowindex关联起来的方法.
１.使用SQLServer2005的ROW_NUMBER（）Keyword–SQLServer2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关这个等级可以用来作为每条记录的rowindex.
２.使用SETROWCOUNT–SQLServer的SETROWCOUNTstatement可以用来指定有多少记录需要处理;tablevariables是可以存放表格式的T-SQL变量,和temporarytables类似.这个方法在MicrosoftSQLServer2005和SQLServer2000都可以用（ROW_NUMBER（）方法只能在SQLServer2005里用）.
这个思路是,为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里就和一个rowindex（通过IDENTITY列）关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SETROWCOUNT用来限制放到table变量里的记录的数量.
当SETROWCOUNT的值指定为StartRowIndex加上MaximumRows时,这个方法的效率取决于被请求的页数.对于比较前面的页来说–比如开始几页的数据–这种方法非常有效.但是对接近尾部的页来说,这种方法的效率和默认分页时差不多.
本教程用ROW_NUMBER（）来实现自定义分页.如果需要知道更多的关于table变量和SETROWCOUNT的技术,请看AMoreEfficientMethodforPagingThroughLargeResultSets.
以下语句用来使用ROW_NUMBER（）将一个等级和返回的每条记录关联:
?
1
2
3
SELECTcolumnList,
ROW_NUMBER（）OVER（orderByClause）
FROMTableName
ROW_NUMBER（）返回一个根据指定排序的表示每条记录的等级的值.比如,我们可以用以下居于查看根据价格来排序（降序）的每个product的等级:
?
1
2
3
SELECTProductName,UnitPrice,
ROW_NUMBER（）OVER（ORDERBYUnitPriceDESC）ASPriceRank
FROMProducts
图5是在VisualStudio里运行以上代码的结果.注意product根据价格排序,每行有一个等级.
图5:
返回的记录里每行有一个PriceRank
注意:
ROW_NUMBER（）只是SQLServer2005里很多排级的功能中的一种.想了解更多的ROW_NUMBER（）的讨论,包括其它的排级功能,请看ReturningRankedResultswithMicrosoftSQLServer2005.
当使用OVER从句里的ORDERBY列名（UnitPrice）来排级时,SQLServer会对结果排序.为了提升大数据量查询时的性能,可以为用来排序的列加上非聚集索引.更多的性能考虑参考RankingFunctionsandPerformanceinSQLServer2005.
ROW_NUMBER（）返回的等级信息无法直接在WHERE从句中使用.而在From后面的Select里可以返回ROW_NUMBER（）,并在WHERE从句里使用.比如,下面的语句使用一个From后的Select返回ProductName,UnitPrice,和ROW_NUMBER（）的结果,然后使用一个WHERE从句来返回pricerank在11到20之间的product.
?
1
2
3
4
5
6
7
SELECTPriceRank,ProductName,UnitPrice
FROM
（SELECTProductName,UnitPrice,
ROW_NUMBER（）OVER（ORDERBYUnitPriceDESC）ASPriceRank
FROMProducts
）ASProductsWithRowNumber
WHEREPriceRankBETWEEN11AND20
更进一步,我们可以根据这个方法返回给定StartRowIndex和MaximumRows的页的数据.
?
1
2
3
4
5
6
7
8
SELECTPriceRank,ProductName,UnitPrice
FROM
（SELECTProductName,UnitPrice,
ROW_NUMBER（）OVER（ORDERBYUnitPriceDESC）ASPriceRank
FROMProducts
）ASProductsWithRowNumber
WHEREPriceRank>StartRowIndexAND
PriceRank<=（StartRowIndex+MaximumRows）
注意:
我们在本教程的后面会看到,ObjectDataSource提供的StartRowIndex是从0开始的,而ROW_NUMBER（）的值从1开始.因此,WHERE从句返回会严格返回PriceRank大于StartRowIndex而小于StartRowIndex+MaximumRows的那些记录.
我们已经知道如何根据给定的StartRowIndex和MaximumRows用ROW_NUMBER（）返回特定页的数据.现在我们需要在DAL和BLL里实现它.
我们首先要决定根据什么排序来分级.我们这里用product名字的字母顺序.这意味着我们还不能同时实现排序的功能.在后面的教程里,我们将学习如何实现这样的功能.
在前面我们使用SQLstatement创建DAL方法.但是TableAdapter wizard使用的VisualStuido里的T-SQL解析器不能识别带OVER语法的ROW_NUMBER（）方法.因此我们要以存储过程来创建这个DAL方法.从viewmenu里选择serverexplorer（Ctrl+Alt+S）,展开NORTHWND.MDF的节点.右键点击存储过程,选择增加一个新的存储过程（见图6）.
图6:
为Products分页增加一个存储过程
这个存储过程带两个整型的输入参数-@startRowIndex和@maximumRows-并用ROW_NUMBER（）以ProductName字段排序,返回那些大于@startRowIndex并小于等于@startRowIndex+@maximumRows的记录.将以下代码加到存储过程里,然后保存.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATEPROCEDUREdbo.GetProductsPaged
（
@startRowIndexint,
@maximumRowsint
）
AS
SELECTProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,
UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued,
CategoryName,SupplierName
FROM
（
SELECTProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,
UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued,
（SELECTCategoryName
FROMCategories
WHERECategories.CategoryID=Products.CategoryID）ASCategoryName,
（SELECTCompanyName
FROMSuppliers
WHERESuppliers.SupplierID=Products.SupplierID）ASSupplierName,
ROW_NUMBER（）OVER（ORDERBYProductName）ASRowRank
FROMProducts
）ASProductsWithRowNumbers
WHERERowRank>@startRowIndexANDRowRank<=（@startRowIndex+@maximumRows）
创建完存储过程后,花点时间测试一下.右键在ServerExplorer点名为GetProductsPaged的存储过程,选择执行.VisualStudio会让你输入参数,@startRowIndex和@maximumRows（见图7）.输入不同的值查看一下结果是什么.
图7:
为@startRowIndex和@maximumRowsParameters输入值
输入参数的值后,你会看到结果.图8的结果为两个参数的值都为10的结果.
图8:
将在第二页里显示的数据
完成存储过程后,我们可以创建ProductsTableAdapter方法了.打开Northwind.xsd,右键点ProductsTableAdapter,选择AddQuery.选择使用已经存在的存储过程.
图9:
使用已经存在的存储过程创建DALMethod
下一步会要我们选择要调用的存储过程.从下拉列表里选择GetProductsPaged.
图10:
选择GetProductsPaged
下一步要选择存储过程返回的数据类型:
表值,单一值,无值.由于GetProductsPaged返回多条记录,所以选择表值.
图11:
为存储过程指定返回表值
最后给方法命名.象前面的方法一样,选择FillaDataTable和ReturnaDataTable,为第一个命名为FillPaged,第二个为GetProductsPaged.
图12:
命名方法为FillPaged和GetProductsPaged
除了创建一个DAL方法返回特定页的products外,我们需要在BLL里也这样做.和DAL方法一样,BLL的GetProductsPaged方法带两个整型的输入参数,分别为StartRowIndex和MaximumRows,并返回在指定范围内的记录.在ProductsBLL创建这个方法,仅仅调用DAL的GetProductsPaged就可以了.
?
1
2
3
4
5
[System.ComponentModel.DataObjectMethodAttribute（System.ComponentModel.DataObjectMethodType.Select,false）]
publicNorthwind.ProductsDataTableGetProductsPaged（intstartRowIndex,intmaximumRows）
{
returnAdapter.GetProductsPaged（startRowIndex,maximumRows）;
}
你可以为BLL方法的参数取任何名字.但是我们马上会看到,选择用startRowIndex和maximumRows会让我们在配置ObjectDataSource时方便很多.
第四步:
使用自定义分页配置ObjectDataSource
创建完BLL和DAL的方法后,我们可以准备创建一个GridView来使用自定义分页了.打开PagingAndSorting文件夹里的EfficientPaging.aspx,添加一个GridView,然后用ObjectDataSource来配置它.在我们以前的教程里,我们通常使用ProductsBLL类的GetProducts方法来配置ObjectDataSource.然而这一次,我们使用GetProductsPaged方法.GetProducts会返回所有的products而GetProductsPaged只返回特定的记录.
图13:
使用ProductsBLLClass类的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,和UnitPriceBoundFields.在智能标签里选择支持分页,GridView和ObjectDataSource的标记看起来应该和下面差不多:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
GridViewID="GridView1"runat="server"AutoGenerateColumns="False"
DataKeyNames="ProductID"DataSourceID="ObjectDataSource1"AllowPaging="True">
BoundFieldDataField="ProductName"HeaderText="Product"
SortExpression="ProductName"/>
BoundFieldDataField="CategoryName"HeaderText="Category"
ReadOnly="True"SortExpression="CategoryName"/>
BoundFieldDataField="SupplierName"HeaderText="Supplier"
SortExpression="SupplierName"/>
BoundFieldDataField="QuantityPerUnit"HeaderText="Qty/Unit"
SortExpression="QuantityPerUnit"/>
BoundFieldDataField="UnitPrice"
升级会员 