二十五：大数据量时提高分页的效率

作者：heker2007 字体：[增加减小] 类型：转载时间：2016-05-07我要评论

上一篇我们介绍过利用GirdView控件内置的分页功能进行分页，但是熟悉的人都知道，那不是真正意义上的分页，本文就带着大家利用利用存储过程创建高效的分页。导言

如我们在之前的教程里讨论的那样,分页可以通过两种方法来实现:

１.默认分页–你仅仅只用选中data Web control的智能标签的Enable Paging ; 然而,当你浏览页面的时候,虽然你看到的只是一小部分数据,ObjectDataSource 还是会每次都读取所有数据

２.自定义分页–通过只从数据库读取用户需要浏览的那部分数据,提高了性能. 显然这种方法需要你做更多的工作.

默认的分页功能非常吸引人,因为你只需要选中一个checkbox就可以完成了.但是它每次都读取所有的数据,这种方式在大数据量或者并发用户多的情况下就不合适.在这样的情况下,我们必须通过自定义分页来使系统达到更好的性能.

自定义分页的一个重点是要写一个返回仅仅需要的数据的查询语句.幸运的,Microsoft SQL Server 2005 提供了一个新的keyword,通过它我们可以写出读取需要的数据的查询.在本教程里,我们将学习在GridView里如何使用Microsoft SQL Server 2005 的这个新的k eyword来实现自定义分页.自定义分页和默认分页的界面看起来一样,但是当你从一页转到另一页时,在效率上差了几个数量级.

注意:自定义分页带来的性能提升程序取决于数据的总量和数据库的负载.在本教程的最后我们会用数据来说明自定义分页带来的性能方面的好处.

第一步: 理解自定义分页的过程

给数据分页的时候,页面显示的数据取决于请求的是哪一页和每页显示多少条.比如,想象以下我们给81个product分页,每页显示10条.当我们浏览第一页时,我们需要的是prod uct 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 的值.

我们在DAL里添加一个方法来返回这个信息.这个方法名为TotalNumberOfProducts() ,它会执行上面的SQL语句.

打开App_Code/DAL 文件夹里的Northwind.xsd .然后在设计器里右键点ProductsTa bleAdapter ,选择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语句.

最后给这个方法命名为TotalNumberOfProducts.

DAL的TotalNumberOfProducts方法返回一个可空的整型,而需要ProductsBLL类的T otalNumberOfProducts方法返回一个标准的整型.调用GetValueOrDefault方法,如果可为空的整型为空,则返回默认值,0.

第三步: 返回需要的数据记录

下一步我们要在DAL和BLL里创建接受Start Row Index 和Maximum Rows 的方法,然后返回合适的记录.我们首先看看需要的SQL语句.我们面临的挑战是需要为整个分页

的记录分配索引,用来返回从Start Row Index 开始的Maximum Records number of rec ords 条记录.

如果在数据库表里已经有一个列作为索引,那么一切会变的很简单.我们首先会想到Pro ducts 表的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 变量, 和temporar y tables 类似. 这个方法在Microsoft SQL Server 2005 和SQL Server 2000都可以用 (R OW_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 变量和S ET ROWCOUNT 的技术,请看 A More Efficient Method for Paging Through Large Resul t Sets.

以下语句用来使用ROW_NUMBER()将一个等级和返回的每条记录关联:

?

1

2

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