在做一個管理全市人口信息系統時，數據量大約八百三十萬，測試時是按照一千萬條數據量來的，開始優化時出現各種問題，使用過各種方法，最終優化分頁查詢任何一頁在10秒以內
感謝孫偉，個人稍加修改
--
=============================================
--
Author: <Author,張世民>
--
Create date: <Create 2012/05/19>
--
Description: <千萬條數據分頁查詢優化>
--
=============================================
--
/*-----存儲過程 分頁處理 孫偉 2005-03-28創建 -------*/
--
/*----- 對數據進行了2分處理使查詢前半部分數據與查詢后半部分數據性能相同 -------*/
ALTER
PROCEDURE
[
dbo
]
.
[
GetPageList
]
(
@tableName
nvarchar
(
200
),
--
--要顯示的表或多個表的連接
@idField
nvarchar
(
150
),
--
--主表的主鍵
@fieldsName
nvarchar
(
500
)
=
'
*
'
,
--
--要顯示的字段列表
@pageSize
int
=
10
,
--
--每頁顯示的記錄個數
@page
int
=
1
,
--
--要顯示那一頁的記錄
@pageCount
int
=
1
output,
--
--查詢結果分頁后的總頁數
@Counts
int
=
1
output,
--
--查詢到的記錄數
@fieldSort
nvarchar
(
200
)
=
null
,
--
--排序字段列表或條件
--
@Sort bit = 0, ----排序方法，0為升序，1為降序(如果是多字段排列Sort指代最后一個排序字段的排列順序(最后一個排序字段不加排序標記)--程序傳參如：' SortA Asc,SortB Desc,SortC ')
@where
nvarchar
(
1000
)
=
null
,
--
--查詢條件,不需where
@Dist
bit
=
0
--
--是否添加查詢字段的 DISTINCT 默認0不添加/1添加
)
AS
SET
NOCOUNT
ON
Declare
@sqlTmp
nvarchar
(
1000
)
--
--存放動態生成的SQL語句
Declare
@strTmp
nvarchar
(
1000
)
--
--存放取得查詢結果總數的查詢語句
Declare
@strID
nvarchar
(
1000
)
--
--存放取得查詢開頭或結尾ID的查詢語句
Declare
@strSortType
nvarchar
(
10
)
--
--數據排序規則A
Declare
@strFSortType
nvarchar
(
10
)
--
--數據排序規則B
Declare
@SqlSelect
nvarchar
(
50
)
--
--對含有DISTINCT的查詢進行SQL構造
Declare
@SqlCounts
nvarchar
(
50
)
--
--對含有DISTINCT的總數查詢進行SQL構造
if
@Dist
=
0
begin
set
@SqlSelect
=
'
select
'
set
@SqlCounts
=
'
Count(*)
'
end
else
begin
set
@SqlSelect
=
'
select distinct
'
set
@SqlCounts
=
'
Count(DISTINCT
'
+
@idField
+
'
)
'
end
/*
if @Sort=0
begin
*/
set
@strFSortType
=
'
'
set
@strSortType
=
'
DESC
'
/*
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
*/
--
------生成查詢語句--------
--
此處@strTmp為取得查詢結果數量的語句
if
@where
is
null
or
@where
=
''
--
沒有設置顯示條件
begin
set
@sqlTmp
=
@fieldsName
+
'
From
'
+
@tableName
set
@strTmp
=
@SqlSelect
+
'
@Counts=
'
+
@SqlCounts
+
'
FROM
'
+
@tableName
set
@strID
=
'
From
'
+
@tableName
end
else
begin
set
@sqlTmp
=
+
@fieldsName
+
'
From
'
+
@tableName
+
'
where (1>0)
'
+
@where
set
@strTmp
=
@SqlSelect
+
'
@Counts=
'
+
@SqlCounts
+
'
FROM
'
+
@tableName
+
'
where (1>0)
'
+
@where
set
@strID
=
'
From
'
+
@tableName
+
'
where (1>0)
'
+
@where
end
--
--取得查詢結果總數量-----
exec
sp_executesql
@strTmp
,N
'
@Counts int out
'
,
@Counts
out
declare
@tmpCounts
int
if
@Counts
=
0
set
@tmpCounts
=
1
else
set
@tmpCounts
=
@Counts
--
取得分頁總數
set
@pageCount
=
(
@tmpCounts
+
@pageSize
-
1
)
/
@pageSize
/*
*當前頁大于總頁數 取最后一頁*
*/
if
@page
>
@pageCount
set
@page
=
@pageCount
--
/*-----數據分頁2分處理-------*/
declare
@pageIndex
int
--
總數/頁大小
declare
@lastcount
int
--
總數%頁大小
set
@pageIndex
=
@tmpCounts
/
@pageSize
set
@lastcount
=
@tmpCounts
%
@pageSize
if
@lastcount
>
0
set
@pageIndex
=
@pageIndex
+
1
else
set
@lastcount
=
@pagesize
--
//***顯示分頁
if
@where
is
null
or
@where
=
''
--
沒有設置顯示條件
begin
if
@pageIndex
<
2
or
@page
<=
@pageIndex
/
2
+
@pageIndex
%
2
--
前半部分數據處理
begin
if
@page
=
1
set
@strTmp
=
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
else
begin
set
@strTmp
=
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
where
'
+
@idField
+
'
>(select max(
'
+
@idField
+
'
) from (
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
*
(
@page
-
1
)
as
Varchar
(
20
))
+
'
'
+
@idField
+
'
from
'
+
@tableName
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
+
'
) AS TBMinID)
'
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
end
end
else
begin
set
@page
=
@pageIndex
-
@page
+
1
--
后半部分數據處理
if
@page
<=
1
--
最后一頁數據顯示
set
@strTmp
=
@SqlSelect
+
'
*from (
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@lastcount
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
order by
'
+
@fieldSort
+
'
'
+
@strSortType
+
'
) AS TempTB
'
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
else
set
@strTmp
=
@SqlSelect
+
'
* from (
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
where
'
+
@idField
+
'
<(select min(
'
+
@idField
+
'
) from(
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
*
(
@page
-
2
)
+
@lastcount
as
Varchar
(
20
))
+
'
'
+
@idField
+
'
from
'
+
@tableName
+
'
order by
'
+
@fieldSort
+
'
'
+
@strSortType
+
'
) AS TBMaxID)
'
+
'
order by
'
+
@fieldSort
+
'
'
+
@strSortType
+
'
) AS TempTB
'
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
end
end
else
--
有查詢條件
begin
if
@pageIndex
<
2
or
@page
<=
@pageIndex
/
2
+
@pageIndex
%
2
--
前半部分數據處理
begin
if
@page
=
1
set
@strTmp
=
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
where 1=1
'
+
@where
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
else
begin
set
@strTmp
=
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
where
'
+
@idField
+
'
>(select max(
'
+
@idField
+
'
) from (
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
*
(
@page
-
1
)
as
Varchar
(
20
))
+
'
'
+
@idField
+
'
from
'
+
@tableName
+
'
where (1=1)
'
+
@where
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
+
'
) AS TBMinID)
'
+
'
'
+
@where
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
end
end
else
begin
set
@page
=
@pageIndex
-
@page
+
1
--
后半部分數據處理
if
@page
<=
1
--
最后一頁數據顯示
set
@strTmp
=
@SqlSelect
+
'
* from (
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@lastcount
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
where (1=1)
'
+
@where
+
'
order by
'
+
@fieldSort
+
'
'
+
@strSortType
+
'
) AS TempTB
'
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
else
set
@strTmp
=
@SqlSelect
+
'
* from (
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
as
VARCHAR
(
4
))
+
'
'
+
@fieldsName
+
'
from
'
+
@tableName
+
'
where
'
+
@idField
+
'
<(select min(
'
+
@idField
+
'
) from(
'
+
@SqlSelect
+
'
top
'
+
CAST
(
@pageSize
*
(
@page
-
2
)
+
@lastcount
as
Varchar
(
20
))
+
'
'
+
@idField
+
'
from
'
+
@tableName
+
'
where (1=1)
'
+
@where
+
'
order by
'
+
@fieldSort
+
'
'
+
@strSortType
+
'
) AS TBMaxID)
'
+
'
'
+
@where
+
'
order by
'
+
@fieldSort
+
'
'
+
@strSortType
+
'
) AS TempTB
'
+
'
order by
'
+
@fieldSort
+
'
'
+
@strFSortType
end
end
--
----返回查詢結果-----
exec
sp_executesql
@strTmp
--
print @strTmp
SET
NOCOUNT
OFF
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
