通过存储过程实现分页

 

在我们试用VS.net2005进行数据库的操作时，我们经常会用到GridView控件。当数据库中的记录很多时，我们会对显示进行分页，而大部分时候我们都是使用GirdView的默认的分页机制。

GridView的默认机制非常方便，完全可以实现零代码，只要你在允许分页里面打上勾就可以了。但是方便是有代价的，它是以性能作为代价，这跟它的实现机制有关系。当我们使用这种默认分页时，GridView会向数据库提取所有的记录，然后发送到客户端，再根据请求的当前页和每页的最多记录数来截取需要的那些记录，而将其余的记录抛弃。 这样每次我们重新绑定一次，就会重复一次这样的过程。 如果记录少的时候，这当然无所谓，但是当一个数据库中有几十万条记录的时候，这将是一个非常大的负担。

为了避免这样的负担，GridView也提供了自定义分页，这样我们可以把所有的事情都在服务器端完成，最后发送到客户端的记录就是我们要的那几条，这样可以大大减小网络负担。 下面我将说明如何通过存储过程来实现获取我们需要的记录。

假设我们有一个表，表结构为Employees(EmployeeID int Primary Key, EmployeeName varchar(10), Salary moeny);

下面来定义一个存储过程：

**********************************************************************

Declare Procedure PagedResult

(

@startRowIndex int, /* 要开始取的第一条 */

@maximumRows int /* 每一页的记录数 */

)

AS

Declare @tempTable Table

(

ID int Identity,

EmployeeID int

)

Insert into @tempTable(EmployeeID)

Select EmployeeID From Employees

Select * From @tempTable t

inner join Employees e

on t.EmployeeID = e.EmployeeID

Where t.ID Between @startRowIndex And ( @startRowIndex + @mamimumRows - 1)Order By EmployeeID

**********************************************************************

 

在这个存储过程中我们把原表Employees中的主键逐一取出放入表变量@tempTable中，并给每行插入一个标识列(因为在删除Employees中记录的时候会出现漏洞，所有EmployeeID值不一定是联系的，所以另外插入一个标识列).然后我们可以根据ID的值来获取记录。这里之所以不把整个Employees表存入@tempTable，而只是插入EmployeeID的原因是EmployeeID是主键，在上面有聚簇索引，这样存取非常方便。 这样我们就可以只发送需要的记录到客户端，可以很大地减小网络负担。

上面的方法可行，但是仍然存在着一点问题，比如我们要把所有的EmployeeID都存入@tempTable中，这里面可能有很多是无用功，比如我们就需要前面5条，结果却将50万个EmployeeID都存了进去，这无用功也太大了。所以我们仍然可以优化它， 以下是优化的代码：

**********************************************************************

Declare Procedure PagedResult
(
@startRowIndex int, /* 要开始取的第一条 */
@maximumRows int /* 每一页的记录数 */
)
AS
Declare @tempTable Table
(
ID int Identity,
EmployeeID int
)

/* 改变的地方 */
Decalre @maxCount int
Set @maxCount = @startRowIndex + @maximumRows - 1

Set ROWCOUNT @maxCount

Insert into @tempTable(EmployeeID)
Select EmployeeID From Employees

Select * From @tempTable t
inner join Employees e
on t.EmployeeID = e.EmployeeID
Where t.ID Between @startRowIndex And ( @startRowIndex + @mamimumRows - 1)Order By EmployeeID

**********************************************************************

SET ROWCOUNT n 命令用于告诉DBMS最多只要去 n 条记录，这样就可以减少开支，只需要取出前面n条记录的EmployeeID存入@tempTable中，而不用整个表的EmployeeID都移进去。

这种方式对上面将所有的的EmployeeID都取入@tempTable无疑是一种性能上的提高，但是它仍然存在缺点。第一、比如说如果我要取的记录是数据库中的最后几条，这样我同样需要把所有EmployeeID取入@tempTable中； 第二、我们存入@tempTable中的数据实际上是存储在系统自带的临时数据库tempdb中,而tempdb有容量上的限制，如果数据超过它的容量，可能会导致数据丢失，甚至是系统崩溃。 所以这样我们仍然需要改进它。

 

那能不能做到我不用@tempTable，而且是我需要哪几条记录我就直接去那几条记录呢？答案是可以的，但是我们首先来看一个等下讲要用到的特性。

 

**********************************************************************

Declare @selected_id int
Select @Selected_id = EmployeeID From Employees

**********************************************************************

这个语句SQLSERVER 是如何运行的呢？ 很显然，@Selected_id 只会存储最后一次赋给他的EmployeeID。那SQLSERVER是怎么给他赋值的呢？是将所有记录都去出来，一次次赋值给@Selected_id，然后一次次地覆盖先前的@Selected_id呢，还是怎样？ SQLSERVER知道@Selected_id只会有一个值，所以它会只运行一次，取出最后一条记录，然后把最后一条记录的EmployeeID赋给@Selected_id.

这样我们可以根据这个特性进行优化，代码如下：

**********************************************************************

Declare Procedure PagedResult
( @startRowIndex int, /* 要开始取的第一条 */
@maximumRows int /* 每一页的记录数 */
)

AS

Declare @fisrt_id int

SET ROWCOUNT @startRowIndex
Select @first_id = EmployeeID From Employees Order By EmployeeID

Set ROWCOUNT @maximumRows

Select * From Employees
Where EmployeeID > @startRowIndex
Order By EmployeeID
**********************************************************************

 

@first_id用来表示我们要取的第一条记录的EmployeeID，然后我们将要去的数据限定为@maximumRows条，这样我们就完全不需要额外的操作来实现。

听说SQLSERVER2005还有更好的方法， 用Row_number()方法来实现，但是由于在我的电脑上没有调试成功，所以这里就不介绍了。

这篇文章的内容是我从几篇英文资料中整合过来的，不知道算是原创还是翻译。为了不侵犯原作者们的权益，就定位为翻译好了。

我是一个技术博客新手，如果有出现错误，请各位指出，在这里谢过了。