关闭

通过存储过程实现分页

461人阅读 评论(0) 收藏 举报
 

在我们试用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()方法来实现,但是由于在我的电脑上没有调试成功,所以这里就不介绍了。

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

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


 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:247963次
    • 积分:3396
    • 等级:
    • 排名:第10222名
    • 原创:21篇
    • 转载:306篇
    • 译文:3篇
    • 评论:42条