个人总结:
本人以前写的一篇ASP.NET通用分页程序 用到的“通用分页存储过程”在视图下没办法走索引效率很低,在大数据时很明显,现换以下存储过程:
ALTER procedure sp_pagesite
(
/*
@Tables 表名 必选
@PrimaryKey 主关键字 必选
@pagesize 页码大小 可选 默认值:1
@pageindex 当前页 可选 默认值:1
@docount 是否只统计总记录数 可选 默认值:否
@Fields 选择字段 可选 默认:所有字段
@Sort 排序语句,不带Order By 可选
@Filter 过滤语句,不带Where 可选
*/
@Tables nvarchar(400),
@PrimaryKey nvarchar(100),
@pagesize int=1,
@pageindex int=1,
@docount bit=0,
@Fields varchar(1000) = '*',
@Sort varchar(1000) = NULL,
@Filter varchar(1000) = NULL)
as
set nocount on
DECLARE @strFilter varchar(1000)
DECLARE @strSort varchar(1000)
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
IF @Sort IS Not NULL And @Sort!=''
Begin
Set @strSort='order by '+@Sort+''
End
if(@docount=1)
exec('select count('+@PrimaryKey+') from '+@Tables+' '+@strFilter+'')
else
begin
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
create table #pageindex(id int identity(1,1) not null,nid int)
set rowcount @PageUpperBound
exec(
'
insert into #pageindex(nid)
select '+@PrimaryKey+' from '+@Tables+' '+@strFilter+' '+@strSort+'
select O.'+@Fields+'
from '+@Tables+' O,#pageindex p
where O.'+@PrimaryKey+'=p.nid and p.id>'+@PageLowerBound+' and p.id<='+@PageUpperBound+' order by p.id
'
)
drop table #PageIndex
end
set nocount off
(
/*
@Tables 表名 必选
@PrimaryKey 主关键字 必选
@pagesize 页码大小 可选 默认值:1
@pageindex 当前页 可选 默认值:1
@docount 是否只统计总记录数 可选 默认值:否
@Fields 选择字段 可选 默认:所有字段
@Sort 排序语句,不带Order By 可选
@Filter 过滤语句,不带Where 可选
*/
@Tables nvarchar(400),
@PrimaryKey nvarchar(100),
@pagesize int=1,
@pageindex int=1,
@docount bit=0,
@Fields varchar(1000) = '*',
@Sort varchar(1000) = NULL,
@Filter varchar(1000) = NULL)
as
set nocount on
DECLARE @strFilter varchar(1000)
DECLARE @strSort varchar(1000)
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
IF @Sort IS Not NULL And @Sort!=''
Begin
Set @strSort='order by '+@Sort+''
End
if(@docount=1)
exec('select count('+@PrimaryKey+') from '+@Tables+' '+@strFilter+'')
else
begin
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
create table #pageindex(id int identity(1,1) not null,nid int)
set rowcount @PageUpperBound
exec(
'
insert into #pageindex(nid)
select '+@PrimaryKey+' from '+@Tables+' '+@strFilter+' '+@strSort+'
select O.'+@Fields+'
from '+@Tables+' O,#pageindex p
where O.'+@PrimaryKey+'=p.nid and p.id>'+@PageLowerBound+' and p.id<='+@PageUpperBound+' order by p.id
'
)
drop table #PageIndex
end
set nocount off
注:此存储过程需在一个主健(PrimaryKey)为索引,而ROW_NUMBER()不用,各有各的好处,看情况而用!
在非视图下,用ROW_NUMBER分页时(Row_Number()虽然它必须先为10万条记条生成RowNumber,但是在百万级数据下效率不会很差),用日期排序只要不把大文本数据读出来效率还是行的。或者用id进行排序那把大文本数据读出来也还行。
而上面的方法就只要提供一个主健(PrimaryKey)为索引不管是视图还是表读取效率都还行。
附文章:SQL Server 2005下的分页SQL
其实基本上有三种方法:
1、使用SQL Server 2005中新增的ROW_NUMBER
几种写法分别如下:
1
SELECT TOP 20 * FROM (SELECT
2
ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3
*
4
FROM
5
dbo.mem_member) _myResults
6
WHERE
7
RowNumber > 10000
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
SELECT * FROM (SELECT
2
ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3
*
4
FROM
5
dbo.mem_member) _myResults
6
WHERE
7
RowNumber between 10000 and 10020
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
WITH OrderedResults AS
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
SELECT *
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
FROM OrderedResults
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
WHERE RowNumber between 10000 and 10020
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
不管哪种写法,性能都不理想。在8,9万条数据的情况下要运行6秒左右。
2、使用临时表再加存储过程
1
BEGIN
2
DECLARE @PageLowerBound int
3
DECLARE @PageUpperBound int
4
5
-- Set the page bounds
6
SET @PageLowerBound = 10000
7
SET @PageUpperBound = 10020
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
-- Create a temp table to store the select results
10
Create Table #PageIndex
11
(
12
[IndexId] int IDENTITY (1, 1) NOT NULL,
13
[Id] varchar(18)
14
)
15
16
-- Insert into the temp table
17
declare @SQL as nvarchar(4000)
18
SET @SQL = 'INSERT INTO #PageIndex (Id)'
19
SET @SQL = @SQL + ' SELECT'
20
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
21
SET @SQL = @SQL + ' m_id'
22
SET @SQL = @SQL + ' FROM dbo.mem_member'
23
SET @SQL = @SQL + ' ORDER BY NameC'
24
25
-- Populate the temp table
26
exec sp_executesql @SQL
27![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
-- Return paged results
29
SELECT O.*
30
FROM
31
dbo.mem_member O,
32
#PageIndex PageIndex
33
WHERE
34
PageIndex.IndexID > @PageLowerBound
35
AND O.[m_Id] = PageIndex.[Id]
36
ORDER BY
37
PageIndex.IndexID
38
39
drop table #PageIndex
40
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
而使用这种方法,在同样的情况下用时只需1秒。
看样子,row_number是个鸡肋。
3、如果觉得临时表不好,还可以使用SET ROWCOUNT
1
begin
2
DECLARE @first_id varchar(18), @startRow int
3
4
SET ROWCOUNT 10000
5
SELECT @first_id = m_id FROM mem_member ORDER BY m_id
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
SET ROWCOUNT 20
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
SELECT m.*
10
FROM mem_member m
11
WHERE m_id >= @first_id
12
ORDER BY m.m_id
13![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
SET ROWCOUNT 0
15
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
不过,这种方法有缺点。按ID排序就快,按其他字段排序就慢。