SQL Server分页3种方案比拼

标签: sql server | 发表时间:2013-02-23 01:56 | 作者:guoxuepeng123
出处:http://blog.csdn.net
建立表: 

CREATE TABLE [TestTable] ( 
[ID] [int] IDENTITY (1, 1) NOT NULL , 
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , 
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , 
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , 
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] 
GO 



插入数据:(2万条,用更多的数据测试会明显一些) 
SET IDENTITY_INSERT TestTable ON 

declare @i int 
set @i=1 
while @i<=20000 
begin 
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX') 
    set @i=@i+1 
end 

SET IDENTITY_INSERT TestTable OFF 



------------------------------------- 

分页方案一:(利用Not In和SELECT TOP分页) 
语句形式: 
SELECT TOP 10 * 
FROM TestTable 
WHERE (ID NOT IN 
          (SELECT TOP 20 id 
         FROM TestTable 
         ORDER BY id)) 
ORDER BY ID 


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

------------------------------------- 

分页方案二:(利用ID大于多少和SELECT TOP分页) 
语句形式: 
SELECT TOP 10 * 
FROM TestTable 
WHERE (ID > 
          (SELECT MAX(id) 
         FROM (SELECT TOP 20 id 
                 FROM TestTable 
                 ORDER BY id) AS T)) 
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/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1 
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1 
set nocount off 



其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。 
建议优化的时候,加上主键和索引,查询效率会提高。 



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

在实际情况中,要具体分析。 
作者:guoxuepeng123 发表于2013-2-23 9:56:13 原文链接
阅读:42 评论:0 查看评论

相关 [sql server] 推荐:

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

SQL Server 面试

- - SQL - 编程语言 - ITeye博客
在SQL语言中,一个SELECT…FROM…WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询. 子查询分为嵌套子查询和相关子查询两种. 嵌套子查询的求解方法是由里向外处理,即每个子查询在其上一级查询处理之前求解,子查询的结果作为其父查询的查询条件. 子查询只执行一次,且可以单独执行;.

SQL Server优化50法

- - CSDN博客推荐文章
虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷).   I/O吞吐量小,形成了瓶颈效应.   没有创建计算列导致查询不优化.   内存不足网络速度慢查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 中的事务

- - CSDN博客推荐文章
       事务要有非常明确的开始和结束点,SQL Server 中的每一条数据操作语句,例如SELECT、INSERT、UPDATE和DELETE都是隐式事务的一部分. 即使只有一条语句,系统也会把这条语句当做一个事务,要么执行所有的语句,要么什么都不执行.         事务开始之后,事务所有的操作都会写到事务日志中,写到日志中的事务,一般有两种:一是针对数据的操作,例如插入、修改和删除,这些操作的对象是大量的数据;另一种是针对任务的操作,例如创建索引.

SQL Server优化50法

- - CSDN博客数据库推荐文章
  虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:. 没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷). I/O吞吐量小,形成了瓶颈效应. 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 查询步骤 - pursuer.chen

- - 博客园_首页
标签:SQL SERVER/MSSQL SERVER/数据库/DBA/查询步骤.       查询步骤是很基础也挺重要的一部分,但是我还是在周围发现有些人虽然会语法,但是对于其中的步骤不是很清楚,这里就来分解一下其中的步骤,在技术内幕系列里面都会有讲到.  TOP于ORDER BY的关系. INSERT INTO Customers VALUES(1,'深圳'),(2,'广州'),(3,'武汉'),(4,'上海'),(5,'北京').

sql server复灾 你懂了吗?

- brett80 - 博客园-首页原创精华区
很多时候我们不小心错误delete了一下,或者update一下怎么办,或者直接把数据库删除了,怎么办呢,是不是就一定没有办法呢. 下面让我来教大家我现学现卖的两招. 做之前我们要设置数据库恢复模式:. 首先我们创建一个表:插入几条数据. 我们现在有五条数据了,我们对数据做一个备份. 做任何差异备份,和日志之前,一定要做一个完整备份.

监控 SQL Server 的运行状况

- Bloger - 博客园-首页原创精华区
Microsoft SQL Server 2005 提供了一些工具来监控数据库. 动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的运行状况、诊断问题和优化性能. 常规服务器动态管理对象包括:. dm_db_*:数据库和数据库对象. dm_exec_*:执行用户代码和关联的连接.

SQL Server 2012的安全性概述

- - CSDN博客推荐文章
SQLServer 2012整个安全体系结构从顺序上可以分为认证和授权两个部分,其安全机制可以分为5个层级. 这些层级由高到低,所有的层级之间相互联系,用户只有通过了高一层的安全验证,才能继续访问数据库中低一层的内容. 客户机安全机制——数据库管理系统需要运行在某一特定的操作系统平台下,客户机操作系统的安全性直接影响到SQL Server2012的安全性.

SQL Server分页3种方案比拼

- - CSDN博客推荐文章
插入数据:(2万条,用更多的数据测试会明显一些) . 分页方案一:(利用Not In和SELECT TOP分页) . SELECT TOP 页大小 * .           (SELECT TOP 页大小*页数 id .          FROM 表 . 分页方案二:(利用ID大于多少和SELECT TOP分页) .