SQLServer分页存储过程的一点心得

转载 2007年09月21日 08:42:00

   最近参与一个对原有MS SQLSERVER2000+ASP系统升级到.NET的开发项目。其中,针对某一个数据记录较多的表的查询用的比较多,而且该查询的条件组合比较复杂,包括分页,按特定字段排序,按特定条件查询。

  参考了netkillerbaseSQL Server 存储过程的分页,开始决定采用效率最高的“方案二”,即通过ID标识来比较大小,从而快速检索出所需的记录。

  为了方便读者,我在这里简单列出前面列出的参考文章中的3种分页查询存储过程的核心T-SQL语句:

方案一:

SELECT TOP 页大小 * 
FROM TestTable 
WHERE (ID NOT IN 
(
SELECT TOP 页大小*页数 id 
FROM 表 
ORDER BY id)) 
ORDER BY ID 

方案二:

SELECT TOP 页大小 * 
FROM TestTable 
WHERE (ID > 
(
SELECT MAX(id) 
FROM (SELECT TOP 页大小*页数 id 
FROM 表 
ORDER BY id) AS T)) 
ORDER BY ID 

方案三:(利用SQL的游标存储过程分页)

create procedure XiaoZhengGe 
@sqlstr nvarchar(4000), --查询字符串 
@currentpage int--第N页 
@pagesize int --每页行数 
as 
set nocount on 
declare @P1 int--P1是游标的id 
@rowcount int 
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output 
select ceiling(1.0*@rowcount/@pagesizeas 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1 
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1 
set nocount off 

上文作者使用查询分析器比较过3种方案,结论如下:
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用

  按照“方案二”写好查询存储过程后,测试中发现按照某些字段排序时,会出现记录遗漏的情况。经过分析表中的记录发现,原来是因为该字段内的值有重复,即用来排序的字段不能作为标识来比较大小。

  找到原因后,对方案二和方案一进行了一个整合。先使用方案二中的排序字段的大小比较,来初步筛选符合条件的记录,然后再使用方案一中的ID标识来验证找到记录是否合法。最后得到的T-SQL语句类似如下:

SELECT TOP 页大小 * 
FROM TestTable 
WHERE (ordercol >= SELECT MAX(ordercol) 
FROM (SELECT TOP 页大小*页数 ordercol 
FROM TestTable 
ORDER BY ordercol ))  and (ID NOT IN
(
SELECT TOP 页大小*页数 id 
FROM TestTable 
ORDER BY ordercol)) 
ORDER BY ordercol 

  这种方式继承了“方案一”的缺点,即在记录数相当大,而且页码靠后时,(SELECT TOP 页大小*页数 id
FROM TestTable ORDER BY ordercol)所得到的记录集会消耗相当大的内存。但是,于此同时,前面先进行的比较判断(ordercol >= SELECT MAX(ordercol) FROM (SELECT TOP 页大小*页数 ordercol FROM TestTable
ORDER BY ordercol ))所得到的记录数量却并不大,加上比较查询的效率远远高于not in,所以最终的执行效率还是可以让人接受的。

  实际项目所使用的表中有1万多条记录,采用该分页存储过程查询倒数前5页,查询执行时间低于1秒。而原来的asp程序使用recordset的move方法,查询执行时间超过2秒。

 

《SQLServer》------常用的分页查询语句

前言 在SqlServer中,分页查询是经常用到的查询语句,一个好的分页查询语句,不能能将代码省略,还能提高运行效率,下面我们来探讨一下SQLServer中的分页查询语句。 具体的业务逻辑是这样的,我...
  • u013067402
  • u013067402
  • 2016年10月24日 12:01
  • 5792

高效的SQLSERVER分页查询(推荐)

Sqlserver数据库分页查询一直是Sqlserver的短板,闲来无事,想出几种方法,假设有表ARTICLE,字段ID、YEAR...(其他省略),数据53210条(客户真实数据,量不大),分页查询...
  • JavaAlpha
  • JavaAlpha
  • 2013年11月22日 16:34
  • 3507

JAVA 后台拼接分页查询SQL oracle/sqlserver/db2/达梦数据库通用

JAVA 后台拼接分页查询SQL
  • luoyongsir
  • luoyongsir
  • 2014年07月28日 11:20
  • 1704

高效的SQLSERVER分页查询(推荐)

Sqlserver数据库分页查询一直是Sqlserver的短板,闲来无事,想出几种方法,假设有表ARTICLE,字段ID、YEAR...(其他省略),数据53210条(客户真实数据,量不大),分页查询...
  • qiaqia609
  • qiaqia609
  • 2014年11月24日 14:53
  • 152893

智能将SqlServer的查询语句转换为分页语句

主要用到了jsqlparser,前面有篇博客介绍过: JAVA - Sql解析工具jsqlparser简单使用 为了给Mybatis分页插件增加对sqlserver的支持,专门写了这样一个独...
  • isea533
  • isea533
  • 2015年01月27日 13:17
  • 4494

有关 SQLserver + JavaWeb端 多条件结果集 分页显示 的一点总结

三种实现多条件结果集分页的方式,可能还有其他方法
  • zx48822821
  • zx48822821
  • 2016年12月13日 09:59
  • 1163

SQLSERVER 存储过程实现分页查询 C#后台获取查询结果集

一、为什么要用分页查询 二、怎样用分页查询 三、存储过程编写 四、后台获取结果集...
  • XWL1992
  • XWL1992
  • 2014年12月30日 17:04
  • 1020

SQL 高效分页(百万条数据)ROW_NUMBER() OVER (ORDER BY id) | 分页

第一种方法效率最高 SELECT TOP 页大小 * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM ta...
  • Fanbin168
  • Fanbin168
  • 2014年12月05日 17:07
  • 8397

SQL server 海量数据库的查询优化及分页算法(收藏)

http://www.w17x.com/AritcleDisplay.aspx?id=661 具体如下: 如何在有着1000万条数据的MS SQL SERVER数据库中实现快速的数据提取和数据分页...
  • hliq5399
  • hliq5399
  • 2016年05月19日 09:51
  • 4419

Oracle、MySql、SQLServer 数据库中分页查询中的关键字总结

(一)、 mysql的分页查询
  • langjian2012
  • langjian2012
  • 2014年10月17日 22:13
  • 6646
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQLServer分页存储过程的一点心得
举报原因:
原因补充:

(最多只允许输入30个字)