几百万条以上数据分页SQL2005, SQL2008最后一页查询超时、卡死解决方案
大概在2年前遇到过,标题中的问题,当时研究了几天很是纠结没能彻底解决问题,后来也找了很多方法没能解决问题。最近又遇到这个问题,实在是不解决也不行了,冷静的想了想,完善了一下分页查询的方法,现在把代码贴上,给大家参考,若有什么漏洞,请及时联系吉日嘎拉,有错我会积极修正。希望不要重复浪费生命,直接拿过去用就可以了,在通用权限管理系统组件里也用了这个方法在进行分页,最近维护一个每天有10万多IP访问的网站,也是用了这个分页存储过程,分页效率还可以,最后一页没在出现卡死状态,若有问题及时联系作者QQ:252056973,欢迎大家交流分享。
最后一页分页一卡死,整个网站的性能都会非常明显的下降,不知道为啥,微软有这个BUG一直没处理好。希望SQL2012里不要有这个问题就好了。
参考代码如下:
--=============================================
--Author:吉日嘎拉
--Createdate:2012年02月23日
--Description:2012年02月23日编码规范化
--=============================================
ALTERPROCEDURE[dbo].[GetRecordByPage]
@TableNameVARCHAR(4000),--表名
@SelectFieldVARCHAR(4000),--要显示的字段名(不要加select)
@WhereConditionalVARCHAR(4000),--查询条件(注意:不要加where)
@SortExpressionVARCHAR(255),--排序索引字段名
@PageSizeINT=20,--页大小
@PageIndexINT=1,--页码
@RecordCountINTOUTPUT,--返回记录总数
@SortDireVARCHAR(5)='DESC'--设置排序类型,非0值则降序
AS
BEGIN
DECLARE@commandTextVARCHAR(8000)--主语句
DECLARE@TopNINT--获取前几条记录
DECLARE@PageCountINT--总共会是几页
DECLARE@TopLimitINT--获取多少条记录
DECLARE@SQLRowCountNVARCHAR(4000)--用于查询记录总数的语句
DECLARE@SQLOrderVARCHAR(400)--排序类型
DECLARE@SQLTempVARCHAR(4000)--临时变量
SET@SortExpression=LTRIM(RTRIM(@SortExpression))
SET@SortDire=UPPER(LTRIM(RTRIM(@SortDire)))
--这里是计算整体记录行数
IF@RecordCountISNULL
BEGIN
IF@WhereConditional!=''
BEGIN
SET@SQLRowCount='SELECT@RecordCount=COUNT(1)FROM'+@TableName+'WHERE'+@WhereConditional
END
ELSE
BEGIN
SET@SQLRowCount='SELECT@RecordCount=COUNT(1)FROM'+@TableName
END
END
--SELECT@RecordCount=@@ROWCOUNT
EXECsp_executesql@SQLRowCount,N'@RecordCountINTOUT',@RecordCountout
IF@RecordCountISNULL
BEGIN
SET@RecordCount=0
END
--这里是控制页数最多少
SET@PageCount=@RecordCount/@PageSize+1
--这里检查当前页的有效性
IF(@PageIndex<1)
BEGIN
SET@PageIndex=1
END
--这里限制最后一页的有效性
IF(@PageIndex>@PageCount)
BEGIN
SET@PageIndex=@PageCount
END
IF@SortDire!='ASC'
BEGIN
SET@SQLTemp='<(SELECTMIN'
SET@SQLOrder='ORDERBY'+@SortExpression+'DESC'
END
ELSE
BEGIN
set@SQLTemp='>(SELECTMAX'
set@SQLOrder='ORDERBY'+@SortExpression+'ASC'
END
--这里是调试信息
--SELECT@SQLOrder
--获取几条数据?吉日嘎拉2010-11-02更新
SET@TopN=@RecordCount-@PageSize*(@PageIndex-1)
IF@TopN>@PageSize
BEGIN
SET@TopN=@PageSize
END
SET@TopLimit=@PageSize*(@PageIndex-1)
IF@TopLimit>@RecordCount
BEGIN
SET@TopLimit=@RecordCount
END
SET@commandText='SELECTTOP'+STR(@TopN)+''+@SelectField+'FROM'
+@TableName+'WHERE'+@SortExpression+@SQLTemp+'('
+RIGHT(@SortExpression,LEN(@SortExpression)-CHARINDEX('.',@SortExpression))+')FROM(SELECTTOP'+STR(@TopLimit)
+''+@SortExpression+'FROM'+@TableName+@SQLOrder+')ASTableTemp)'
+@SQLOrder
IF@WhereConditional!=''
SET@commandText='SELECTTOP'+STR(@TopN)+''+@SelectField+'FROM'
+@TableName+'WHERE'+@SortExpression+@SQLTemp+'('
+RIGHT(@SortExpression,LEN(@SortExpression)-CHARINDEX('.',@SortExpression))+')FROM(SELECTTOP'+STR(@TopLimit)
+''+@SortExpression+'FROM'+@TableName+'WHERE'+@WhereConditional+''
+@SQLOrder+')ASTableTemp)AND'+@WhereConditional+''+@SQLOrder
IF@PageIndex=1
BEGIN
--第一页的显示效率提高
SET@SQLTemp=''
IF@WhereConditional!=''
SET@SQLTemp='WHERE'+@WhereConditional
SET@commandText='SELECTTOP'+STR(@TopN)+''+@SelectField
+'FROM'+@TableName+@SQLTemp+''+@SQLOrder
END
ELSE
BEGIN
--解决大数据最有一页卡死的问题
IF@PageIndex=@PageCount
BEGIN
IF@SortDire='ASC'
BEGIN
SET@SQLOrder='ORDERBY'+@SortExpression+'DESC'
END
ELSE
BEGIN
SET@SQLOrder='ORDERBY'+@SortExpression+'ASC'
END
SET@SQLTemp=''
IF@WhereConditional!=''
SET@SQLTemp='WHERE'+@WhereConditional
SET@commandText='SELECTTOP'+STR(@TopN)+''+@SelectField
+'FROM'+@TableName+@SQLTemp+''+@SQLOrder
SET@commandText='SELECT'+@SelectField
+'FROM('+@commandText+')ASTableTempORDERBY'+@SortExpression+''+@SortDire
END
END
EXEC(@commandText)
--这个是调试程序用的
--SELECT@commandText
END
将权限管理、工作流管理做到我能力的极致,一个人只能做好那么很少的几件事情。
相关阅读
- 悦糖APP帮你通过精准数据预防糖尿病
- 百度地图五一出行大数据在哪看 百度地图五一出行数据哪里人多
- 我叫mt3手游安卓和ios数据互通吗 安卓版和ios版能一起玩吗
- 360云盘什么时间清空数据 360云盘清空全部数据吗
- 好分数新版显示暂无数据怎么回事 好分数显示暂无数据怎么办
- 闪金贷数据导入出现失败怎么办 闪金贷数据导入出现失败解决办法
- 微信公众号阅读数不正常是怎么回事 微信公众号阅读数数据低下怎么解决
- 我在微博的2016在哪看 我在微博的2016数据查看方法
- 2016微信个人数据报告是真是假 微信2016拉黑删除屏蔽朋友圈数据是真的吗
- 微信2016年我的数据在哪看 微信2016年我的数据观看方法介绍
《几百万条以上数据分页SQL2005, SQL2008最后一页查询超时、卡死解决方案》由网友“旧人哭”推荐。
转载请注明:http://www.modouwifi.com/jiaocheng/052011235R021.html