分页实现方法的性能比较

我们先给出几种主要的分页方法和核心语句,然后直接给出结论,有兴趣的读者可以看看后面的数据

几种常用存储过程分页方法

TopN方法

select Top(@PageSize) from TableName where ID Not IN

(Select Top ((@PageIndex-1)*@PageSize) ID from Table Name where .... order by ... )

where .... order by ...

临时表

declare @indextable table(id int identity(1,1),nid int,PostUserName nvarchar(50))

declare @PageLowerBound int

declare @PageUpperBound int

set @PageLowerBound=(@pageindex-1)*@pagesize--下限

set @PageUpperBound=@PageLowerBound+@pagesize--上限

set rowcount @PageUpperBound

insert into @indextable(nid,PostUserName) select ReplyID,PostUserName from TableName order by ......

select * from TableName p,@indextable t where p.ID=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

CTE--2005新语法,类似临时表,但是生命周期稍微不同,这里只是他的一个运用

with cte_temp--定义零时表,PageIndex是一个计算字段,储存了搜索结果的页号

As (ceiling((Row_Number() over(order by .... )-1)/@pagesize as int) as PageIndex,* from TableName where.....)
select * from cte_temp where pageindex=@pageindex-1;

结论:

TopN在小页数下最快,如果在10页以下,可以考虑用它,CTE和临时表时间很稳定,CTE消耗的时间比临时表多,但是不会引起tempdb的暴涨和IO增加

性能比较

试验环境:win2003server,Sqlserver2005,库大小2,567,245行,没有where子句,试验时每页大小50,页码作为变量

取0,3,10,31,100,316,1000,3162...页,也就是10的指数,试验结果如下

页数	TopN	CTE	临时表(有缓存)	临时表(无缓存)	公司正在使用的存储过程	CTE改进
1	3	12	10	101	457	7302
3	15	7	79	5524	464	7191
10	127	5504	88	3801	464	6116
32	588	9672	122	3601	976	7602
100	4680	9738	166	4235	486	7151
316	45271	9764	323	3867	522	7255
1000	Null	9806	869	2578	635	8948
3162	Null	9822	2485	4110	12460	8210
10000	Null	9754	7812	11926	14250	7359
31623	Null	9775	18729	33218	15249	7511
100000	Null	Null	31538	55569	17139	6124

数据解释和分析

临时表分为有没有缓存两种时间,CTE就是上面的方法,CTE改进只是把选入CTE临时表的列数减少了,只选取了页号和主键,Null表示时间无法计算(时间太长),数据单位是毫秒.

从上面的数据可以看到,TopN在前32页都是有优势的,但是页数增大后,性能降低很快,CTE改进比CTE有所进步,平均进步两秒左右,但是还是比临时表慢,但是考虑临时表会增大日志文件的大小,引起大量IO,CTE也就有他自己的优势,公司现在正在使用的存储过程效率不错,但是在页码靠后的情况下性能会降低

存储过程分页实现代码，2005 T-SQL新增功能

今天看了看2005中T-SQL的新增功能，心血来潮，试了试利用CTE方法做的分页，感觉还不错。下面贴一下TopN方法和CTE方法的数据分页的存储过程，写的还算是比较通用。性能分析我以后会进一步研究一下。
代码还是先来一个测试用表，代码如下：

SET ANSI_NULLS ON
2

GO
3

SET QUOTED_IDENTIFIER ON
4

GO
5

CREATE TABLE [dbo].[testTable](
6

[id] [int] IDENTITY(1,1) NOT NULL,
7

[testDate] [datetime] NOT NULL CONSTRAINT [DF_testTable_testDate] DEFAULT (getdate()),
8

[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
9

[description] [nchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
10

[orderColum] [float] NOT NULL,
11

CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED
12

(
13

[id] ASC
14

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
15

) ON [PRIMARY]
16

填充一下表，加了3W条记录，倒是不多。

declare @i int
2

set @i = 1
3

while @i < 30001
4

begin
5

INSERT INTO testTable([name],[description],[orderColum])
6

VALUES('names', 'descriiption', @i * rand())
7

set @i = @i +1
8

end

1、TopN方法：这个方法就不多说了，相当普遍，页码少的时候比较好使。
[代码]

=============================================
2

-- Author: <Author,,microant>
3

-- Create date: <2007年7月5日,,>
4

-- Description: <Description,selectTopN分页方法,>
5

-- =============================================
6

CREATE PROCEDURE [dbo].[sp_SelectTopN](
7

-- Add the parameters for the stored procedure here
8

@TableName varchar(200) = 'testTable', --表名
9

@PageSize int = 15, --页面大小
10

@PageIndex int =2 , --页面的序号
11

--@IsCountNull bit =1, --返回记录是否为空
12

@IsAsc bit = 1 , --是否卫升序,升序为1，降序为0
13

@OderColumName varchar(200) = null, --排序字段名
14

@KeyID varchar(50) = 'id', --主键
15

@Conditions varchar(500) = null --查询条件
16

)
17

AS
18

set nocount on
19

declare @strSql nvarchar(1000)
21

declare @tempstr nvarchar(1000)
22

declare @orderstr nvarchar(400)
23

--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
25

if @IsAsc = 1
26

begin
27

if(@OderColumName is null or @OderColumName = '')
28

set @orderstr = ' order by ' + @KeyID + ' asc'
29

else
30

set @orderstr = ' order by ' + @OderColumName + ' asc'
31

end
32

else
33

begin
34

if(@OderColumName is null or @OderColumName = '')
35

set @orderstr = ' order by ' + @KeyID + ' desc'
36

else
37

set @orderstr = ' order by ' + @OderColumName + ' desc'
38

end
39

--查询条件是否添加
41

if @Conditions is null
42

begin
43

set @tempstr = 'select top ' + str(@PageSize*@PageIndex) +' ' +@KeyID +' from ' + @TableName + @orderstr;
44

set @strSql = 'select top ' + str(@PageSize)+ ' * from ' + @TableName + ' where ' + @KeyID +' not in ( ' + @tempstr +')' + @orderstr;
45

end
46

else
47

begin
48

set @tempstr = 'select top ' + str(@PageSize*@PageIndex) +' ' +@KeyID +' from ' + @TableName+ ' where ' + @Conditions + ' ' + @orderstr;
49

set @strSql = 'select top ' + str(@PageSize)+ ' * from ' + @TableName + ' where ' + @Conditions + ' and ' + @KeyID +' not in (' + @tempstr +')' + @orderstr;
50

end
51

print @strSql
52

exec sp_executesql @strSql
53

set nocount off
54

[测试]

DECLARE @return_value int
2

EXEC @return_value = [dbo].[sp_SelectTopN]
3

@TableName = N'testTable',
4

@PageSize = 30,
5

@PageIndex = 4,
6

@IsAsc = 0,
7

@OderColumName = N'orderColum',
8

@KeyID = N'id',
9

@Conditions = 'id > 50'
10

SELECT 'Return Value' = @return_value
11

go
12

      2、CTE方法：
            CTE（常见表表达式）是一个可以由定义语句引用的临时命名的结果集，和临时表比较相似。一般形式如下，
            WITH <cte_alias>(<column_aliases>)
            AS
            (
              <cte_query>
            )
            SELECT *
            FROM <cte_alias>
            但是比临时表慢,但是考虑临时表会增大日志文件的大小,引起大量IO,CTE也就有他自己的优势。性能分析详情参见http://blog.csdn.net/yizhu2000/archive/2007/06/03/1636573.aspx

      [代码]

-- =============================================
2

-- Author: <Author,,microant>
3

-- Create date: <Create Date,,20070705>
4

-- Description: <Description,,CTE分页>
5

-- =============================================
6

CREATE PROCEDURE [dbo].[sp_CTE](
7

-- Add the parameters for the stored procedure here
8

@TableName varchar(200) = 'testTable', --表名
9

@PageSize int = 15, --页面大小
10

@PageIndex int =2 , --页面的序号
11

--@IsCountNull bit =1, --返回记录是否为空
12

@IsAsc bit = 1 , --是否卫升序,升序为1，降序为0
13

@OderColumName varchar(200) = null, --排序字段名
14

@KeyID varchar(50) = 'id', --主键
15

@Conditions varchar(500) = null --查询条件
16

)
17

AS
18

-- SET NOCOUNT ON added to prevent extra result sets from
20

-- interfering with SELECT statements.
21

SET NOCOUNT ON;
22

declare @strSql nvarchar(1000)
24

declare @tempstr nvarchar(1000)
25

declare @orderstr nvarchar(400)
26

declare @ctestr nvarchar(400)
27

--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
29

if @IsAsc = 1
30

begin
31

if(@OderColumName is null or @OderColumName = '')
32

set @orderstr = ' order by ' + @KeyID + ' asc'
33

else
34

set @orderstr = ' order by ' + @OderColumName + ' asc'
35

end
36

else
37

begin
38

if(@OderColumName is null or @OderColumName = '')
39

set @orderstr = ' order by ' + @KeyID + ' desc'
40

else
41

set @orderstr = ' order by ' + @OderColumName + ' desc'
42

end
43

--CTE
45

set @ctestr ='with Table_CET
46

as
47

(
48

select
49

CEILING((ROW_NUMBER() OVER (' + @orderstr + '))/' + str(@PageSize) + ') as page_num, *
50

from ' + @TableName +
51

')' ;
52

set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex)+ ' and ' + @Conditions;
55

print @strSql
57

begin
58

exec sp_executesql @strSql;
59

end