1.引言
在列表查询时由于数据量非常多,一次性查出来会非常慢,就算一次查出来了,也不能一次性显示给客户端,所以要把数据进行分批查询出来,每页显示一定量的数据,这就是数据要分页。
2.常用的数据分页方法
我们经常会碰到要取n到m条记录,就是有分页思想,下面罗列一下一般的方法。
我本地的一张表 tbl_FlightsDetail,有300多W记录,主键 FlightsDetailID(Guid),要求按照FlightsDetailID排序 取 3000001 到3000010 之间的10条记录,也是百万级。
方法1 定位法 (利用ID大于多少)
语句形式:
select top 10 * from tbl_FlightsDetail where FlightsDetailID>( select max(FlightsDetailID) from ( select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID ) as t ) order by FlightsDetailID
执行计划:
先查出 top 300000,再聚合取这个集合中最大的Id1,再过滤 id大于id1的集合(上图中使用到索引),再取top 10 条。
方法2 (利用Not In)
语句形式:
select top 10* from tbl_FlightsDetail where FlightsDetailID not in ( select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID ) order by FlightsDetailID
执行计划:
和方法一类似,只是过滤where条件不一样,这里用到的是not in,上图中没有用到索引,耗时8秒。如果 FlightsDetailID不是索引的话,方法1和该方法将差不多。
方法3 (利用颠颠倒倒top)
语句形式:
select top 10* from ( select top 3000010* from tbl_FlightsDetail order by FlightsDetailID ) as t order by t.FlightsDetailID desc
执行计划:
先取 前面3000010条记录,再倒序,这时再取前面10条即是300001 到300010条记录,没有用到索引,耗时11秒
方法4 (ROW_NUMBER()函数)
语句形式:
select * from ( select *,ROW_NUMBER() OVER (ORDER BY FlightsDetailID) as rank from tbl_FlightsDetail ) as t where t.rank between 3000001 and 3000010
执行计划:
Sql 2005版本或以上支持,也没用到索引,耗时2秒,速度还不错。
方法5 (利用IN)
此方法是由 金色海洋(jyk)阳光男孩
回复的,飞常感谢,语句形式:
select top 10 * from tbl_FlightsDetail where FlightsDetailID in( select top 10 FlightsDetailID from( select top 3000010 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID ) as t order by t.FlightsDetailID desc ) order by FlightsDetailID
执行计划:
多次执行之后一般维持在4秒左右,用到索引,非常不错,计划图还很长,只截取部分,可能是绕的多一点。
3.千万级分页存储过程
大家百度一下这个标题立马会出现很多相关信息,都大同小异,我自己拷贝的一个,应项目的需要,修改了一个排序的bug以及添加了返回总记录数,如下:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --分页存储过程 CREATE PROCEDURE [dbo].[sp_Paging] ( @Tables nvarchar(1000), --表名/视图名 @PrimaryKey nvarchar(100), --主键 @Sort nvarchar(200) = NULL, --排序字段(不带order by) @pageindex int = 1, --当前页码 @PageSize int = 10, --每页记录数 @Fields nvarchar(1000) = N'*', --输出字段 @Filter nvarchar(1000) = NULL, --where过滤条件(不带where) @Group nvarchar(1000) = NULL, --Group语句(不带Group By) @TotalCount int OUTPUT --总记录数 ) AS DECLARE @SortTable nvarchar(100) DECLARE @SortName nvarchar(100) DECLARE @strSortColumn nvarchar(200) DECLARE @operator char(2) DECLARE @type nvarchar(100) DECLARE @prec int --设定排序语句 IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') SET @operator = '<=' END ELSE BEGIN SET @strSortColumn = REPLACE(@Sort, 'ASC', '') SET @operator = '>=' END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END --设置排序字段类型和精度 SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize nvarchar(50) DECLARE @strStartRow nvarchar(50) DECLARE @strFilter nvarchar(1000) DECLARE @strSimpleFilter nvarchar(1000) DECLARE @strGroup nvarchar(1000) IF @pageindex <1 SET @pageindex = 1 SET @strPageSize = CAST(@PageSize AS nvarchar(50)) --设置开始分页记录数 SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50)) --筛选以及分组语句 IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' ' SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' --计算总记录数 DECLARE @TotalCountSql nvarchar(1000) SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT --执行查询语句 EXEC( ' DECLARE @SortColumn ' + @type + ' SET ROWCOUNT ' + @strStartRow + ' SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' SET ROWCOUNT ' + @strPageSize + ' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' ' )
现在我们来测试一下:
DECLARE @return_value int, @TotalCount int EXEC @return_value = [dbo].[sp_Paging] @Tables = N'tbl_FlightsDetail', @PrimaryKey = N'FlightsDetailID', @Sort = N'FlightsDetailID', @pageindex = 299999, @PageSize = 10, @Fields = '*', @Filter = NULL, @Group = NULL, @TotalCount = @TotalCount OUTPUT SELECT @TotalCount as N'@TotalCount' SELECT 'Return Value' = @return_value
执行计划:
看时间的确是快,执行计划显示4个查询
查询1,是利用系统表获取排序字段、类型和精度,这个很快,全是索引。
查询2,返回总记录数,第一次会慢点,后面就很快了。
查询3 和查询4(用到索引) 才是我们要分页取的数据,查询3 是排序,取一个最大的值赋给变量,查询4是大于这个变量的值 取数据,直接看sql语句,把上面的exec动态语句改成如下:
DECLARE @SortColumn varchar(40) --即 top 3000001,取出最大的 id覆盖@SortColumn SET ROWCOUNT 3000001 SELECT @SortColumn= FlightsDetailID FROM tbl_FlightsDetail ORDER BY FlightsDetailID --即 top 10 SET ROWCOUNT 10 SELECT * FROM tbl_FlightsDetail WHERE FlightsDetailID >= @SortColumn ORDER BY FlightsDetailID
你会发现,原来它跟我们标题2 常用的数据分页方法 中的 方法1 定位 类似,原来奥秘在这。
4.小结
还有一些用游标、表变量的那个性能差不作考虑。分页存储过程看起来挺复杂的,语句多,其实都在判断,在左组装,右组装,最终组装成类似 标题2中常用的分页方法中的 的一种语句,掌握了常用的数据分页方法,大家就可以自己写了,当然还有其它的方法,大家可以分享出来。
原文地址:http://www.cnblogs.com/qqlin/archive/2012/11/01/2745161.html
相关推荐
除此之外,她还是SQL Server Magazine的优秀编辑和专栏作家,她还写作了大量的SQL Server类书籍,包括著名的Inside Microsoft SQL Server2000。 目录 前言 致谢 引言 第1章 SQL Server 2005 的安装与升级 1.1 ...
9.1.5 使用GridView控件分页显示数据 9.1.6 对GridView控件中的数据进行排序 9.2 其他数据控件的应用 9.2.1 使用Repeater控件显示数据 9.2.2 在Repeater控件中分页显示数据 9.2.3 使用DetailsView控件操作一条...
DMV查找慢速查询、通过APM解决方案查询报告、SQLServer扩展事件、SQL Azure查询性能洞察等相关内容。 本文来自博客园,由火龙果软件Anna编辑、推荐。SQLServer的一个重要功能是内置于其中的所有动态管理视图(DMV)...
4、图片上传、富文本编辑器,都是有的,富文本编辑器已经集成了图片上传, 5、还有日期选择器,都是有的 6、博客分类,内容、留言,后台都可以管理 7、热门博客,热门新闻,最新新闻,数据都是来自于后台配置 8、上...
4、图片上传、富文本编辑器,都是有的,富文本编辑器已经集成了图片上传, 5、还有日期选择器,都是有的 6、博客分类,内容、留言,后台都可以管理 7、热门博客,热门新闻,最新新闻,数据都是来自于后台配置 8、上...
0技术,采用Microsoft Access/SQL Server 2000/2005/2008存储过程进行多层架构开发的内容管理系统。其功能设计主要面向中大型企业、各个行业、事业单位以及政府机关等复杂功能站点。系统已建立文章系统、图片系统、...
4、图片上传、富文本编辑器,都是有的,富文本编辑器已经集成了图片上传, 5、还有日期选择器,都是有的 6、博客分类,内容、留言,后台都可以管理 7、热门博客,热门新闻,最新新闻,数据都是来自于后台配置 8、上...
•实例7-7 将SQL Server数据库中的数据导入到Excel工作表(ADO) 177 •实例7-8 将SQL Server数据库中的数据导入到Excel工作表(DAO) 178 •实例7-9 查询获取SQL Server数据库的数据(ADO) 180 •实例7-10 查询...
实例7-7 将SQL Server数据库中的数据导入到Excel工作表(ADO) 实例7-8 将SQL Server数据库中的数据导入到Excel工作表(DAO) 实例7-9 查询获取SQL Server数据库的数据(ADO) 实例7-10 查询获取SQL Server...
支持浏览者、用户、管理员三种角色,数据存储在SQLserver2012数据库中(压缩包中含有数据库备份文件),网站包含基本的增删改查,用户的查找、评论等功能。此外还有富文本编辑器、web服务实现的验证码功能、分页功能...
实例7-7 将SQL Server数据库中的数据导入到Excel工作表(ADO) 实例7-8 将SQL Server数据库中的数据导入到Excel工作表(DAO) 实例7-9 查询获取SQL Server数据库的数据(ADO) 实例7-10 查询获取SQL Server...
(2)打开sqlserver的企业管理器,将数据库目录下的sqlserver2000数据库数据文件附加到sqlserver2000。 (2)启动MyEclipse,选择文件->导入->导入现有的工程到WorkPlace,把项目导入 (3)发布xwfb这个web项目到tomcat...
摘要:.NET源码,控件组件,GridView 环境下使用GridView控件的9个经典实例,数据库文件是SqlServer,存放在目录的Database文件夹下,这9个应用实例分别是: 1、数据绑定并实现分页功能 2、选中编辑取消删除数据项...
新学了下AspNetPager,所以自己做了一个简单的小练习 使用VS2005与SQL Server 2005
数据库修改为SQL Server 2008 R2 Express (以防用户没有安装SQL Server 2008 R2) 增加 Metadata 产生默认值 如Id、Adddate等 2010年04月27日 项目创建 开发环境及工具: VS2010+SQL Server 2008 体现功能: ...
使用Microsoft SQL Server 2005中新的公共语言运行时特性和XML数据类型;为简单和复杂的更新生成逻辑和使用存储过程;使用ASP.NET 2.0中的新数据访问性设计Web应用程序——包括无代码数据绑定功能。 内容简介 本书...
(2)打开sqlserver的企业管理器,将数据库目录下的sqlserver2000数据库数据文件附加到sqlserver2000。 (2)启动MyEclipse,选择文件->导入->导入现有的工程到WorkPlace,把项目导入 (3)发布xwfb这个web项目到tomcat...