這是在網(wǎng)上看到的一個分頁存儲過程，整理了一下，以后有空再試試。聽說適用于大型數(shù)據(jù)庫的哦。
1
CREATE
PROCEDURE
pagination3
2
@tblName
varchar
(
255
),
--
表名
3
@strGetFields
varchar
(
1000
)
=
'
*
'
,
--
需要返回的列
4
5
@fldName
varchar
(
255
)
=
''
,
--
排序的字段名
6
@PageSize
int
=
10
,
--
頁尺寸（每頁記錄數(shù)）
7
@PageIndex
int
=
1
,
--
頁碼
8
@doCount
bit
=
0
,
--
返回記錄總數(shù), 非0值則返回記錄數(shù)
9
@OrderType
bit
=
0
,
--
設(shè)置排序類型, 非0值則降序
10
@strWhere
varchar
(
1500
)
=
''
--
查詢條件 (注意: 不要加 where)
11
AS
12
declare
@strSQL
varchar
(
5000
)
--
主語句
13
declare
@strTmp
varchar
(
110
)
--
臨時變量
14
declare
@strOrder
varchar
(
400
)
--
排序類型
15
if
@doCount
!=
0
16
begin
17
if
@strWhere
!=
''
18
set
@strSQL
=
'
select count(*) as Total from [
'
+
@tblName
+
'
] where
'
+
@strWhere
19
else
20
set
@strSQL
=
'
select count(*) as Total from [
'
+
@tblName
+
'
]
'
21
end
--
> 以上代碼的意思是如果@doCount傳遞過來的不是0，就執(zhí)行總數(shù)統(tǒng)計。以下的所有代碼都是@doCount為0的情況
22
else
23
begin
24
if
@OrderType
!=
0
--
> 降序(desc)
25
begin
26
set
@strTmp
=
'
<(select min
'
27
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] desc
'
--
如果@OrderType不是0，就執(zhí)行降序，這句很重要！
28
end
29
else
--
> 升序(asc)
30
begin
31
set
@strTmp
=
'
>(select max
'
32
set
@strOrder
=
'
order by [
'
+
@fldName
+
'
] asc
'
33
end
34
35
if
@PageIndex
=
1
--
> 頁碼
36
begin
37
if
@strWhere
!=
''
38
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
] where
'
+
@strWhere
+
'
'
+
@strOrder
39
else
40
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
]
'
+
@strOrder
--
如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
41
end
42
else
43
begin
--
以下代碼賦予了@strSQL以真正執(zhí)行的SQL代碼
44
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@fldName
+
'
] from [
'
+
@tblName
+
'
]
'
+
@strOrder
+
'
) as tblTmp)
'
+
@strOrder
45
if
@strWhere
!=
''
46
set
@strSQL
=
'
select top
'
+
str
(
@PageSize
)
+
'
'
+
@strGetFields
+
'
from [
'
+
@tblName
+
'
] where [
'
+
@fldName
+
'
]
'
+
@strTmp
+
'
([
'
+
@fldName
+
'
]) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
[
'
+
@fldName
+
'
] from [
'
+
@tblName
+
'
] where
'
+
@strWhere
+
'
'
+
@strOrder
+
'
) as tblTmp) and
'
+
@strWhere
+
'
'
+
@strOrder
47
end
48
end
49
exec
(
@strSQL
)
50
GO
?
上面的這個存儲過程是一個通用的存儲過程，其注釋已寫在其中了。??
select
top
頁大小
*
from
table1
where
id
>
(
select
max
(id)
from
(
select
top
((頁碼
-
1
)
*
頁大小) id
from
table1
order
by
id)
as
T )
order
by
id
在大數(shù)據(jù)量的情況下，特別是在查詢最后幾頁的時候，查詢時間一般不會超過9秒；而用其他存儲過程，在實踐中就會導(dǎo)致超時，所以這個存儲過程非常適用于大容量數(shù)據(jù)庫的查詢。
?
但，在將本存儲過程應(yīng)用于“辦公自動化”系統(tǒng)的實踐中時，筆者發(fā)現(xiàn)這第三種存儲過程在小數(shù)據(jù)量的情況下，有如下現(xiàn)象：??
1、分頁速度一般維持在1秒和3秒之間。?
?
2、在查詢最后一頁時，速度一般為5秒至8秒，哪怕分頁總數(shù)只有3頁或30萬頁。??
雖然在超大容量情況下，這個分頁的實現(xiàn)過程是很快的，但在分前幾頁時，這個1－3秒的速度比起第一種甚至沒有經(jīng)過優(yōu)化的分頁方法速度還要慢，借用戶的話說就是“還沒有ACCESS數(shù)據(jù)庫速度快”，這個認(rèn)識足以導(dǎo)致用戶放棄使用您開發(fā)的系統(tǒng)。??
筆者就此分析了一下，原來產(chǎn)生這種現(xiàn)象的癥結(jié)是如此的簡單，但又如此的重要：排序的字段不是聚集索引！
聚集索引有兩個最大的優(yōu)勢：??
1、以最快的速度縮小查詢范圍。?
2、以最快的速度進(jìn)行字段排序。?
?
第1條多用在查詢優(yōu)化時，而第2條多用在進(jìn)行分頁時的數(shù)據(jù)排序。?
而聚集索引在每個表內(nèi)又只能建立一個，這使得聚集索引顯得更加的重要。聚集索引的挑選可以說是實現(xiàn)“查詢優(yōu)化”和“高效分頁”的最關(guān)鍵因素。??
但要既使聚集索引列既符合查詢列的需要，又符合排序列的需要，這通常是一個矛盾。??
筆者前面“索引”的討論中，將fariqi，即用戶發(fā)文日期作為了聚集索引的起始列，日期的精確度為“日”。這種作法的優(yōu)點，前面已經(jīng)提到了，在進(jìn)行劃時間段的快速查詢中，比用ID主鍵列有很大的優(yōu)勢。??
但在分頁時，由于這個聚集索引列存在著重復(fù)記錄，所以無法使用max或min來最為分頁的參照物，進(jìn)而無法實現(xiàn)更為高效的排序。而如果將ID主鍵列作為聚集索引，那么聚集索引除了用以排序之外，沒有任何用處，實際上是浪費了聚集索引這個寶貴的資源。??
為解決這個矛盾，筆者后來又添加了一個日期列，其默認(rèn)值為getdate()。用戶在寫入記錄時，這個列自動寫入當(dāng)時的時間，時間精確到毫秒。即使這樣，為了避免可能性很小的重合，還要在此列上創(chuàng)建UNIQUE約束。將此日期列作為聚集索引列。?
?
有了這個時間型聚集索引列之后，用戶就既可以用這個列查找用戶在插入數(shù)據(jù)時的某個時間段的查詢，又可以作為唯一列來實現(xiàn)max或min，成為分頁算法的參照物。??
經(jīng)過這樣的優(yōu)化，筆者發(fā)現(xiàn)，無論是大數(shù)據(jù)量的情況下還是小數(shù)據(jù)量的情況下，分頁速度一般都是幾十毫秒，甚至0毫秒。而用日期段縮小范圍的查詢速度比原來也沒有任何遲鈍。?
?
聚集索引是如此的重要和珍貴，所以筆者總結(jié)了一下，一定要將聚集索引建立在：??
1、您最頻繁使用的、用以縮小查詢范圍的字段上；?2、您最頻繁使用的、需要排序的字段上。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
