[轉]SQL Server 存儲過程的分頁方案比拼

  1

SQL Server 存儲過程的分頁，這個問題已經讨論過幾年了，很多朋友在問我，是以在此發表一下我的觀點

  2

建立表：

  3

  4

CREATE TABLE [TestTable] (

  5

 [ID] [int] IDENTITY (1, 1) NOT NULL ,

  6

 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

  7

 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

  8

 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

  9

 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL 

 10

) ON [PRIMARY]

 11

 GO

 12

 13

 14

 插入資料：(2萬條，用更多的資料測試會明顯一些)

 15

 SET IDENTITY_INSERT TestTable ON

 16

 17

 declare @i int

 18

 set @i=1

 19

 while @i<=20000

 20

 begin

 21

     insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')

 22

     set @i=@i+1

 23

 end

 24

 25

 SET IDENTITY_INSERT TestTable OFF

 26

 27

 28

 29

 -------------------------------------

 30

 31

 分頁方案一：(利用Not In和SELECT TOP分頁)

 32

 語句形式：

 33

 SELECT TOP 10 *

 34

 FROM TestTable

 35

 WHERE (ID NOT IN

 36

           (SELECT TOP 20 id

 37

          FROM TestTable

 38

          ORDER BY id))

 39

 ORDER BY ID

 40

 41

 42

 SELECT TOP 頁大小 *

 43

 44

 45

           (SELECT TOP 頁大小*頁數 id

 46

          FROM 表

 47

 48

 49

 50

 51

 52

 分頁方案二：(利用ID大于多少和SELECT TOP分頁）

 53

 54

 55

 56

 WHERE (ID >

 57

           (SELECT MAX(id)

 58

          FROM (SELECT TOP 20 id

 59

                  FROM TestTable

 60

                  ORDER BY id) AS T))

 61

 62

 63

 64

 65

 66

 67

 68

          FROM (SELECT TOP 頁大小*頁數 id

 69

                  FROM 表

 70

 71

 72

 73

 74

 75

 76

 分頁方案三：(利用SQL的遊标存儲過程分頁)

 77

 create  procedure XiaoZhengGe

 78

 @sqlstr nvarchar(4000), --查詢字元串

 79

 @currentpage int, --第N頁

 80

 @pagesize int --每頁行數

 81

 as

 82

 set nocount on

 83

 declare @P1 int, --P1是遊标的id

 84

  @rowcount int

 85

 exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output

 86

 select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 目前頁 

 87

 set @currentpage=(@currentpage-1)*@pagesize+1

 88

 exec sp_cursorfetch @P1,16,@currentpage,@pagesize 

 89

 exec sp_cursorclose @P1

 90

 set nocount off

 91

 92

 其它的方案：如果沒有主鍵，可以用臨時表，也可以用方案三做，但是效率會低。

 93

 建議優化的時候，加上主鍵和索引，查詢效率會提高。

 94

 95

 通過SQL 查詢分析器，顯示比較：我的結論是:

 96

 分頁方案二：(利用ID大于多少和SELECT TOP分頁）效率最高，需要拼接SQL語句

 97

 分頁方案一：(利用Not In和SELECT TOP分頁)   效率次之，需要拼接SQL語句

 98

 分頁方案三：(利用SQL的遊标存儲過程分頁)    效率最差，但是最為通用

 99

100

在實際情況中，要具體分析。