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 分页存储过程

  • 2014年11月18日 11:14
  • 44KB
  • 下载

通用sqlserver分页存储过程

通用sqlserver分页存储过程 来自:http://www.cnblogs.com/nzperfect/archive/2007/05/08/738999.html 单主键: C...

SqlServer存储过程分页代码(支持亿万庞大数据量)

Sql语句如下USE [Test]goSET ansi_nulls ONgoSET quoted_identifier ONgoALTER PROCEDURE [dbo].[Page] @tblNam...
  • WuLex
  • WuLex
  • 2017年03月07日 16:11
  • 151

sqlserver 分页存储过程

  • 2014年09月10日 08:52
  • 7KB
  • 下载

SQLServer分页的存储过程

  • 2007年10月10日 23:16
  • 918B
  • 下载

通用分页存储过程,兼容SqlServer2000,2005,2008

存储过程: --创建存储过程 CREATE PROCEDURE [dbo].[SP_GetRecordPaged] ( @tblName varchar(255), ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQLServer分页存储过程的一点心得
举报原因:
原因补充:

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