sqlserver2008千万条数据分页查询
在做⼀个管理全市⼈⼝信息系统时，数据量⼤约⼋百三⼗万，测试时是按照⼀千万条数据量来的，开始优化时出现各种问题，使⽤过各种⽅法，最终优化分页查询任何⼀页在10秒以内
感谢孙伟，个⼈稍加修改
--=============================================
-- Author: <Author，张世民>
-- Create date: <Create 2012/05/19>
-- Description: <；千万条数据分页查询优化>
-- =============================================
--/*-----存储过程分页处理孙伟 2005-03-28创建 -------*/
--/*----- 对数据进⾏了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
distinct查询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<2or@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<2or@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*(@p
age-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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论