- 浏览: 113256 次
- 性别:
- 来自: 深圳
文章分类
此转载源自李洪根的blog.作者是微软的MVP!希望大家参考以下3种方案,按实际情况选择!
建立表:
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的游标存储过程分页) 效率最差,但是最为通用
在实际情况中,要具体分析。
更多的讨论见:
http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515
建立表:
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的游标存储过程分页) 效率最差,但是最为通用
在实际情况中,要具体分析。
更多的讨论见:
http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515
发表评论
-
redis工具
2017-08-15 14:12 357介绍网站 http://www.cnblogs.com/s ... -
mysql in 子查询 效率慢 优化(转)
2016-03-22 15:33 493参考文档 http://www.cnblogs.com/xh8 ... -
Deadlock found when trying to get lock; try restarting transaction
2015-12-02 20:13 1083批量插入数据遇到异常 this.getJdbcTemplate ... -
关于批量插入数据之我见(100万级别的数据,mysql)
2015-11-27 14:27 1618http://blog.csdn.net/frinder/ar ... -
MySQL行级锁、表级锁、页级锁详细介绍
2015-11-18 23:55 983数据库锁 http://www.jb51.net/artic ... -
Redis的三种启动方式
2015-07-15 15:38 446参考文档 http://www.tuicool.com/ar ... -
mysql 创建函数
2014-11-30 17:27 885如何进入mysql的cmd命令行模式? 参考:http://s ... -
cmd命令行登录MySQL的步骤
2014-11-30 17:06 1278先打开cmd窗口,怎么打开就不用说了吧,开始菜单运行-cmd回 ... -
创建带 IN 和 OUT 参数的 MySQL 存储过程
2014-10-24 15:37 626参考文献: http://www.oschina.net/tr ... -
sqlserver如何清除死锁?
2014-01-02 10:01 499Exec dbo.sp_lock begin de ... -
MS Sql Server 中主从库的配置和使用介绍-数据库备份
2013-11-14 11:52 1138博客链接地址: http://technet.microsof ... -
mysql 分库分表
2013-10-30 11:01 696分表是分散数据库压力的好方法。 分表,最直白的意思,就是将一 ... -
WITH (NOLOCK)
2013-06-14 10:37 682缺点: 1.会产生脏读 2.只适用与select查 ... -
JDBC C3P0封装
2013-05-30 15:06 937c3p0 was designed to be butt-si ... -
文档-数据库函数-数据类型
2013-05-21 11:13 622SQL SERVER 2005数据类型,与函数大全文档 -
Northwind数据库Pubs数据库文件下载
2013-05-06 19:07 864Northwind数据库文件 Pubs数据库文件 -
SQL Server 2005 客户端程序 Management Studio
2013-03-30 19:15 689Microsoft SQL Server Management ... -
Oracle官方文档库
2013-01-16 23:25 828http://www.oracle.com/technetwo ... -
ORACLE数据库访问方式
2012-11-19 23:01 849isql*plus url: http://localhos ... -
设置mysql自增ID的起始值
2012-11-18 20:28 1915Hello! 如果觉得不错的话,并且不吝转载时, 请顺便添加上 ...
相关推荐
SQL Server 分页方案比拼
mybatis中,sqlserver分页
sqlserver分页sql
sql server分页技术 详解 sql server分页技术 详解 sql server分页技术 详解
sqlserver分页查询语句;sqlserver分页查询语句;sqlserver分页查询语句;
sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程
关于SQL Server SQL语句查询分页数据的解决方案
sql server分页代码 --分页【top】:不支持复合主键 SELECT TOP 10 * from Lend where id not in (SELECT TOP ((2-1)*10) id from lend ORDER BY id) ORDER BY id ; SELECT TOP 10 * from Lend where id not ...
sql server 分页大全, 分页存储过程,分页TSQL , 分页语法,语句! 最全的SQL Server2000 分页方法。 可放心下载
SQL Server 存储过程的分页方案比拼
SQLServer数据库分页,提供SQLServer内部分页
SQL server2008中通用分页存储过程,表名,每页长度,页码都是动态赋值。
sql server2005 分页查询语句
sqlserver 分页调用的存储过程 t-sql 语句
简易的sqlserver分页功能,只需要将页码传入即可,默认id倒序排列,一页十条,也可以通过传入不同参数,对不同字段,进行正序或倒序排列。 使用方法:1、将该类导入实体类包中 2、在需要分页的地方创建该类对象,并...
通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
java 连接sqlserver数据库查询,并分页显示
mysql,oracle,sqlserver分页
jsp分页代码,很简单的分页代码! jsp+sqlserver分页代码
SQL Server 存储过程的分页方案比拼.doc