sqlserver 如何进行分页查询?

DB , 评论

developer.aliyun.com /article/263817

sqlserver数据库分页查询一直是sqlserver的短板

假设有表tbicxx_100000, 字段CICID, DFKRQ…(其他省略)，数据10000条, 字段CICID聚集索引，DFKRQ无索引，sqlserver版本：2012

准备测试数据: SELECT TOP 100000 * INTO tbicxx_100000 FROM tbicxx

页数靠后测试

  • 分页查询每页30条，查询第1500页（即第45001-45030条数据)

第一种方案、最简单、普通的方法：

1
2
3
4
5
6
7
8
SELECT TOP 30
       *
FROM tbicxx_100000
WHERE CICID NOT IN (
                       SELECT TOP 45000 CICID FROM tbicxx_100000 ORDER BY DFKRQ DESC, CICID DESC
                   )
ORDER BY DFKRQ DESC,
         CICID DESC
 平均查询100次所需时间：45s/19s(45s秒表示原文所载数据,19s为实测数据)

第二种方案：

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM
(
    SELECT TOP 30
           *
    FROM
    (SELECT TOP 45030 * FROM tbicxx_100000 ORDER BY DFKRQ DESC, CICID DESC) f
    ORDER BY f.DFKRQ ASC,
             f.CICID DESC
) s
ORDER BY s.DFKRQ DESC,
         s.CICID DESC
 平均查询100次所需时间：138S/50s

第三种方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT *
FROM tbicxx_100000 w1,
(
    SELECT TOP 30
           CICID
    FROM
    (
        SELECT TOP 50030
               CICID,
               DFKRQ
        FROM tbicxx_100000
        ORDER BY DFKRQ DESC,
                 CICID DESC
    ) w
    ORDER BY w.DFKRQ ASC,
             w.CICID ASC
) w2
WHERE w1.CICID = w2.CICID
ORDER BY w1.DFKRQ DESC,
         w1.CICID DESC

 平均查询100次所需时间：21S/10s

第四种方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT *
FROM tbicxx_100000 w1
WHERE CICID IN (
                   SELECT TOP 30
                          CICID
                   FROM
                   (
                       SELECT TOP 45030
                              CICID,
                              DFKRQ
                       FROM tbicxx_100000
                       ORDER BY DFKRQ DESC,
                                CICID DESC
                   ) w
                   ORDER BY w.DFKRQ ASC,
                            w.CICID ASC
               )
ORDER BY w1.DFKRQ DESC,
         w1.CICID DESC

平均查询100次所需时间：20S/10s

第五种方案：

1
2
3
4
5
6
7
8
9
10
11
12
SELECT w2.n,
       w1.*
FROM tbicxx_100000 w1,
(
    SELECT TOP 50030
           ROW_NUMBER() OVER (ORDER BY DFKRQ DESC, CICID DESC) n,
           CICID
    FROM tbicxx_100000
) w2
WHERE w1.CICID = w2.CICID
      AND w2.n > 50000
ORDER BY w2.n ASC
 平均查询100次所需时间：15S/10s

页数靠前测试

  • 查询第1000-1030条记录

第一种方案：

1
2
3
4
5
6
7
8
SELECT TOP 30
       *
FROM tbicxx_100000
WHERE CICID NOT IN (
                       SELECT TOP 1000 CICID FROM tbicxx_100000 ORDER BY DFKRQ DESC, CICID DESC
                   )
ORDER BY DFKRQ DESC,
         CICID DESC
 平均查询100次所需时间：80s/15s

第二种方案：

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM
(
    SELECT TOP 30
           *
    FROM
    (SELECT TOP 1030 * FROM tbicxx_100000 ORDER BY DFKRQ DESC, CICID DESC) f
    ORDER BY f.DFKRQ ASC,
             f.CICID DESC
) s
ORDER BY s.DFKRQ DESC,
         s.CICID DESC  
平均查询100次所需时间：30S/10s

第三种方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT *
FROM tbicxx_100000 w1,
(
    SELECT TOP 30
           CICID
    FROM
    (
        SELECT TOP 1030
               CICID,
               DFKRQ
        FROM tbicxx_100000
        ORDER BY DFKRQ DESC,
                 CICID DESC
    ) w
    ORDER BY w.DFKRQ ASC,
             w.CICID ASC
) w2
WHERE w1.CICID = w2.CICID
ORDER BY w1.DFKRQ DESC,
         w1.CICID DESC

 平均查询100次所需时间：12S/3s

第四种方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT *
FROM tbicxx_100000 w1
WHERE CICID IN (
                   SELECT TOP 30
                          CICID
                   FROM
                   (
                       SELECT TOP 1030
                              CICID,
                              DFKRQ
                       FROM tbicxx_100000
                       ORDER BY DFKRQ DESC,
                                CICID DESC
                   ) w
                   ORDER BY w.DFKRQ ASC,
                            w.CICID ASC
               )
ORDER BY w1.DFKRQ DESC,
         w1.CICID DESC
 平均查询100次所需时间：13S/5s

第五种方案：

1
2
3
4
5
6
7
8
9
10
11
12
SELECT w2.n,
       w1.*
FROM tbicxx_100000 w1,
(
    SELECT TOP 1030
           ROW_NUMBER() OVER (ORDER BY DFKRQ DESC, CICID DESC) n,
           CICID
    FROM tbicxx_100000
) w2
WHERE w1.CICID = w2.CICID
      AND w2.n > 1000
ORDER BY w2.n ASC
平均查询100次所需时间：14S/7s
  • 由此可见在查询页数靠前时，效率3>4>5>2>1，页码靠后时5>4>3>1>2，再根据用户习惯，一般用户的检索只看最前面几页，因此选择3 4 5方案均可，若综合考虑方案5是最好的选择，但是要注意SQL2000不支持row_number()函数(第5种方案)，由于时间和条件的限制没有做更深入、范围更广的测试，有兴趣的可以仔细研究下。

以第四种方案编写的通用分页存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_Page_v2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sys_Page_v2]
GO
 
ALTER PROCEDURE [dbo].[sys_Page_v2]
@sys_Table nvarchar(100),    --查询表名
@sys_Key varchar(50),        --主键
@sys_Fields nvarchar(500),    --需要查询的字段
@sys_PageIndex int,        --返回第多少页数据
@sys_PageSize int,        --每页行数 
@sys_Where nvarchar(3000),    --查询条件(可为空)
@sys_Order nvarchar(100),    --排序字段(可为空)
@sys_Begin int        --开始位置(可为空)

AS

SET NOCOUNT ON
SET ANSI_WARNINGS ON

IF @sys_PageSize < 0 OR @sys_PageIndex < 0
BEGIN        
RETURN
END

DECLARE @new_where1 NVARCHAR(3000)
DECLARE @new_order1 NVARCHAR(100)
DECLARE @new_order2 NVARCHAR(100)
DECLARE @Sql NVARCHAR(4000)
DECLARE @SqlCount NVARCHAR(4000)
DECLARE @PCount int --总页数输出
DECLARE @RCount int --总记录数输出


DECLARE @Top int

if(@sys_Begin <=0)
    set @sys_Begin=0
else
    set @sys_Begin=@sys_Begin-1

IF ISNULL(@sys_Where,'') = ''
    SET @new_where1 = ' '
ELSE
    SET @new_where1 = ' WHERE ' + @sys_Where

IF ISNULL(@sys_Order,'') <> '' 
BEGIN
    SET @new_order1 = ' ORDER BY ' + Replace(@sys_Order,'desc','')
    SET @new_order1 = Replace(@new_order1,'asc','desc')

    SET @new_order2 = ' ORDER BY ' + @sys_Order
END
ELSE
BEGIN
    SET @new_order1 = ' ORDER BY '+@sys_Key+' DESC'
    SET @new_order2 = ' ORDER BY '+@sys_Key+' ASC'
END

SET @SqlCount = 'SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'   ---CEILING 向上取整 floor向下取整
            + CAST(@sys_PageSize AS NVARCHAR)+') FROM ' + @sys_Table + @new_where1

print @SqlCount 
EXEC SP_EXECUTESQL @SqlCount,N'@RCount INT OUTPUT,@PCount INT OUTPUT',
               @RCount OUTPUT,@PCount OUTPUT

IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize)    --如果输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数
BEGIN
    SET @sys_PageIndex =  CEILING((@RCount+0.0)/@sys_PageSize)
END

set @sql = 'select '+ @sys_fields +' from ' + @sys_Table + ' w1 '
    + ' where '+ @sys_Key +' in ('
        +'select top '+ ltrim(str(@sys_PageSize)) +' ' + @sys_Key + ' from '
        +'('
            +'select top ' + ltrim(STR(@sys_PageSize * @sys_PageIndex + @sys_Begin)) + ' ' + @sys_Key + ' FROM ' 
        + @sys_Table + @new_where1 + @new_order2 
        +') w ' + @new_order1
    +') ' + @new_order2

print(@sql)

Exec(@sql)

/*

调用示例 

DECLARE @return_value int

EXEC    @return_value = [dbo].[sys_Page_v2]
        @sys_Table = N'tbicxx_100000',--表名
        @sys_Key = N'cicid', --主键 根据主键来排序分页
        @sys_Fields = N'*',--需要的字段
        @sys_PageIndex = 100,--需要第100页
        @sys_PageSize = 90, --每90行一页    
        @sys_Where =N'', 
        @sys_Order =N'',
        @sys_Begin =N''

SELECT  'Return Value' = @return_value

GO

*/











赵无敌Developer & Designer

苟利国家生死以,岂因祸福避趋之。