SQL 分页存储过程优化-优快云博客

前段时间由于项目需求想上网搜个通用分页存储过程先用着
本以为非常简单的事竟然让我大失所望
竟然没有一个能够满足我的需求,不是效率太低就是限制太多不符合实际需要
bug也是一顿狂冒,更有甚者执行都执行不了
我想应该很多朋友都有这样的遭遇吧
于是一气之下决定自己研究

在众多分析性能的文章中,很庆幸翻到了这一篇http://www.codeproject.com/aspnet/PagingLarge.asp(感谢园子里一位朋友的提示,具体名字已经不记得了),对分页存储过程有兴趣的朋友可以去看,写的很详尽,随便PF一下国外同行的认真和专业.

在上面提到的那篇文章的一开始大力推崇使用RowCoun的t方法,
可是由于原文中提供的方法不支持非unique字段的排序
大多场合都并不适用
文章末尾连作者自己提到,已经开始考虑改用cursor方法
可我对RowCount方法算是情有独中,于是对原文中该方法进行了改进
改进后的方法已基本上满足我的需要,现在发布出来,有用的朋友可以下载试用

修改记录:
    1)增加对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)
    2)增加记录总数输出参数
    3)修改原过程若干BUG
    4)修改PK字段只能是int型的bug(刚发现的,^_^)

注:
     1)如表名参数为多表连接时,sort列必须指定表名;
     2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
     3)由于时间原因没有大量测试,如有BUG,请您提出,立刻修正;
     4)由于时间关系,只写了一个简单示例,需要的朋友可以下载
            分页示例

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Paging_RowCount] ' ) and OBJECTPROPERTY (id, N ' IsProcedure ' ) = 1 )
2

drop procedure [ dbo ] . [ Paging_RowCount ]
3

GO
4

SET QUOTED_IDENTIFIER ON
6

GO
7

SET ANSI_NULLS ON
8

GO
9

-- -------------------------------------------------------------
10

-- 分页存储过程(使用RowCount) --edit by SiBen
11

-- summary:
12

-- 获取表或表集合的分页数据
13

-- 当多表连接时,sort列必须指定表名
14

-- -------------------------------------------------------------
15

CREATE PROCEDURE Paging_RowCount
17

(
18

@Tables varchar ( 1000 ),
19

@PK varchar ( 100 ),
20

@Sort varchar ( 200 ) = NULL ,
21

@PageNumber int = 1 ,
22

@PageSize int = 10 ,
23

@Fields varchar ( 1000 ) = ' * ' ,
24

@Filter varchar ( 1000 ) = NULL ,
25

@Group varchar ( 1000 ) = NULL ,
26

@RecordCount int = 0 output
27

)
28

AS
29

/*Default Sorting*/
31

IF @Sort IS NULL OR @Sort = ''
32

SET @Sort = @PK
33

/*Find the @PK type*/
35

DECLARE @SortTable varchar ( 100 )
36

DECLARE @SortName varchar ( 100 )
37

DECLARE @PKTable varchar ( 100 )
38

DECLARE @PKName varchar ( 100 )
39

DECLARE @strSortColumn varchar ( 200 )
40

DECLARE @operator char ( 2 )
41

DECLARE @type varchar ( 100 )
42

DECLARE @prec int
43

/*Set sorting variables.*/
45

IF CHARINDEX ( ' DESC ' , @Sort ) > 0
46

BEGIN
47

SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
48

SET @operator = ' < '
49

END
50

ELSE
51

BEGIN
52

IF CHARINDEX ( ' ASC ' , @Sort ) > 0
53

SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
54

ELSE
55

SET @strSortColumn = @Sort
56

SET @operator = ' > '
58

END
59

/* Set PK,Sort name */
61

IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
62

BEGIN
63

SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
64

SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
65

END
66

ELSE
67

BEGIN
68

SET @SortTable = @Tables
69

SET @SortName = @strSortColumn
70

END
71

IF CHARINDEX ( ' . ' , @PK ) > 0
72

BEGIN
73

SET @PKTable = SUBSTRING ( @PK , 0 , CHARINDEX ( ' . ' , @PK ))
74

SET @PKName = SUBSTRING ( @PK , CHARINDEX ( ' . ' , @PK ) + 1 , LEN ( @PK ))
75

END
76

ELSE
77

BEGIN
78

SET @PKTable = @Tables
79

SET @PKName = @PK
80

END
81

SELECT @type = t.name, @prec = c.prec
83

FROM sysobjects o
84

JOIN syscolumns c on o.id = c.id
85

JOIN systypes t on c.xusertype = t.xusertype
86

WHERE o.name = @SortTable AND c.name = @SortName
87

IF CHARINDEX ( ' char ' , @type ) > 0
89

SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
90

DECLARE @strPageSize varchar ( 50 )
92

DECLARE @strStartRow varchar ( 50 )
93

DECLARE @strFilter varchar ( 1000 )
94

DECLARE @strSimpleFilter varchar ( 1000 )
95

DECLARE @strGroup varchar ( 1000 )
96

/*Default Page Number*/
98

IF @PageNumber < 1
99

SET @PageNumber = 1
100

101

/*Set paging variables.*/
102

SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
103

SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
104

105

/*Set filter & group variables.*/
106

IF @Filter IS NOT NULL AND @Filter != ''
107

BEGIN
108

SET @strFilter = ' WHERE ' + @Filter + ' '
109

SET @strSimpleFilter = ' AND ' + @Filter + ' '
110

END
111

ELSE
112

BEGIN
113

SET @strSimpleFilter = ''
114

SET @strFilter = ''
115

END
116

IF @Group IS NOT NULL AND @Group != ''
117

SET @strGroup = ' GROUP BY ' + @Group + ' '
118

ELSE
119

SET @strGroup = ''
120

121

/*Get rows count.*/
122

DECLARE @str_Count_SQL nvarchar ( 500 )
123

SET @str_Count_SQL = ' SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
124

EXEC sp_executesql @str_Count_SQL ,N ' @TotalCount int=0 output ' , @RecordCount output
125

126

/*Execute dynamic query*/
127

IF @PKTable = @SortTable and @PKName = @SortName
128

BEGIN
129

EXEC (
130

'
131

DECLARE @SortColumn ' + @type + '
132

SET ROWCOUNT ' + @strStartRow + '
133

SELECT @SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
134

SET ROWCOUNT ' + @strPageSize + '
135

SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' = @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
136

'
137

)
138

END
139

ELSE
140

BEGIN
141

/* Get PK Type */
142

DECLARE @pktype varchar ( 100 )
143

DECLARE @pkprec int
144

145

SELECT @pktype = t.name, @pkprec = c.prec
146

FROM sysobjects o
147

JOIN syscolumns c on o.id = c.id
148

JOIN systypes t on c.xusertype = t.xusertype
149

WHERE o.name = @PKTable AND c.name = @PKName
150

151

IF CHARINDEX ( ' char ' , @pktype ) > 0
152

SET @pktype = @pktype + ' ( ' + CAST ( @pkprec AS varchar ) + ' ) '
153

154

/*Execute dynamic query*/
155

EXEC (
156

'
157

DECLARE @SortColumn ' + @type + '
158

DECLARE @SortNullValue ' + @type + '
159

DECLARE @PKStartValue ' + @pktype + '
160

SET @SortNullValue=CAST( '''' as ' + @type + ' )
161

SET ROWCOUNT ' + @strStartRow + '
162

SELECT @SortColumn= isNull( ' + @strSortColumn + ' ,@SortNullValue), @PKStartValue = ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
163

SET ROWCOUNT ' + @strPageSize + '
164

SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull( ' + @strSortColumn + ' ,@SortNullValue) ' + @operator + ' @SortColumn or (isNull( ' + @strSortColumn + ' ,@SortNullValue)=@SortColumn and ' + @PK + ' <=@PKStartValue)) ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
165

'
166

)
167

END
168

GO
169

SET QUOTED_IDENTIFIER OFF
170

GO
171

SET ANSI_NULLS ON
172

GO
173

174