- 浏览: 552399 次
- 性别:
- 来自: 南京
文章分类
- 全部博客 (350)
- Sybase (30)
- SQL SERVER2005 (14)
- 数据库 (27)
- SSH框架 (27)
- WebService (21)
- 下载-软件收藏 (15)
- 随笔-日常使用 (9)
- Flex 相关 (13)
- Linux (11)
- Web (64)
- XML相关 (9)
- Socket相关 (1)
- Elipse (3)
- 统计报表 (11)
- 线程相关 (3)
- Java相关 (37)
- JAVASCRIPT (19)
- JAVA反射 (3)
- JSP标签 (3)
- 随笔-其他 (2)
- 随笔-设计模式 (3)
- 随笔-架构师相关 (1)
- 下载-源码 (7)
- 下载-帮助文档 (1)
- 下载-插件 (6)
- 技术-.NET (2)
- 技术-Excel VBA (8)
- 应用-地图相关 (2)
- 应用-GSM短信猫 (5)
- 应用-单点登录 (3)
- Android相关 (3)
最新评论
-
sucheng2016:
发现jconn4.jar 里面有getBlob(String) ...
Sybase15驱动包的问题com.sybase.jdbc3.jdbc.SybDriver -
sucheng2016:
java.lang.UnsupportedOperationE ...
Sybase15驱动包的问题com.sybase.jdbc3.jdbc.SybDriver -
ok123zxx:
没下文了吗
通过 Tomcat Advanced I/O 获得高性能的 Ajax tocmat6+CometProcessor -
q1345111:
大家这个问题 尚未完成方法 com.sybase.jdbc3. ...
Sybase15驱动包的问题com.sybase.jdbc3.jdbc.SybDriver -
bdk82924:
heshujing217187 写道问题同1楼一样,求解!换j ...
Sybase15驱动包的问题com.sybase.jdbc3.jdbc.SybDriver
转自:http://www.iteye.com/topic/230211
平台与环境:
CPU:Intel(R) Pentium(R) Dual T2390 1.86GHz
内存:1G(系统正常启动后约占300M空间)
硬盘:SATA 160G 8M Cache
系统:windowsxp+Sql Server 2005 sp2
测试数据:共100万条
分页测试代码:
1)row_number的两种分页方式:分别用top和between过滤
2)包含子查询结果的三种分页方式
共5种方式。
方式1:
每页显示200条
分页至10万条之后的第两百条记录
- PROCEDURE [dbo].[proc_select_moauser1]
- AS
- BEGIN
- SET NOCOUNT ON;
- declare @tdiff datetime
- set @tdiff=getdate()
- select top 200 * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber>100000
- select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
- END
PROCEDURE [dbo].[proc_select_moauser1] AS BEGIN SET NOCOUNT ON; declare @tdiff datetime set @tdiff=getdate() select top 200 * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber>100000 select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END
响应时间:156ms-210ms
方式2:
每页显示200条
分页至10万条之后的第两百条记录
- PROCEDURE [dbo].[proc_select_moauser2]
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- -- Insert statements for procedure here
- declare @tdiff datetime
- set @tdiff=getdate()
- select * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber between 100000 and 100200
- select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
- END
PROCEDURE [dbo].[proc_select_moauser2] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @tdiff datetime set @tdiff=getdate() select * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber between 100000 and 100200 select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END
响应时间:153ms-176ms
方式3
每页显示200条
分页至10万条之后的第两百条记录
- PROCEDURE [dbo].[proc_select_moauser3]
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- declare @tdiff datetime
- set @tdiff=getdate()
- select top 200 * from moa_user a where uid not in(select top 100000 uid from moa_user b order by uid)
- select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
- END
PROCEDURE [dbo].[proc_select_moauser3] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @tdiff datetime set @tdiff=getdate() select top 200 * from moa_user a where uid not in(select top 100000 uid from moa_user b order by uid) select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END
响应时间:270ms-290ms
方式4:
每页显示200条
分页至10万条之后的第两百条记录
- PROCEDURE [dbo].[proc_select_moauser4]
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- declare @tdiff datetime
- set @tdiff=getdate()
- -- Insert statements for procedure here
- select * from ( select TOP 200 * FROM ( SELECT TOP 100000 * from moa_user ORDER BY uid ASC ) as amoaUser ORDER BY uid DESC ) as bmoaUser ORDER BY uid ASC
- select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
- END
PROCEDURE [dbo].[proc_select_moauser4] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @tdiff datetime set @tdiff=getdate() -- Insert statements for procedure here select * from ( select TOP 200 * FROM ( SELECT TOP 100000 * from moa_user ORDER BY uid ASC ) as amoaUser ORDER BY uid DESC ) as bmoaUser ORDER BY uid ASC select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END
响应时间:950ms
方式5:
每页显示200条
分页至10万条之后的第两百条记录
- PROCEDURE [dbo].[proc_select_moauser5]
- AS
- BEGIN
- -- SET NOCOUNT ON added to prevent extra result sets from
- -- interfering with SELECT statements.
- SET NOCOUNT ON;
- -- Insert statements for procedure here
- declare @tdiff datetime
- set @tdiff=getdate()
- -- Insert statements for procedure here
- SELECT TOP 200 * FROM moa_user WHERE (uid > (SELECT MAX(uid) FROM (SELECT TOP 100000 uid FROM moa_user ORDER BY uid) AS temp_moa_user)) ORDER BY uid
- select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
- END
PROCEDURE [dbo].[proc_select_moauser5] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @tdiff datetime set @tdiff=getdate() -- Insert statements for procedure here SELECT TOP 200 * FROM moa_user WHERE (uid > (SELECT MAX(uid) FROM (SELECT TOP 100000 uid FROM moa_user ORDER BY uid) AS temp_moa_user)) ORDER BY uid select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END
响应时间:135ms
发表评论
-
Mysql 的语法
2015-05-17 20:54 5131、启动 停止 bin/mysql.server star ... -
DbUtils的使用
2013-01-28 19:56 1223DbUtils的使用 今天在看一段源码的时候,看到了可以使用 ... -
NoSQL非关系型数据库
2013-01-22 15:39 908转:http://baike.baidu.com/view/2 ... -
mysql 导入导出函数、存储过程
2012-06-22 16:20 2908mysql常用导出数据命令: 1.mysql导出整个数 ... -
JDBC分页工具类
2012-02-01 15:37 1144转自:http://xdwangiflytek.iteye.c ... -
几种数据库的SQL判断表是否存在
2012-02-01 15:35 923Oracle: declarevar_exis ... -
Oracle中实现分页的方法 .
2011-12-12 09:27 1320转:http://blog.csdn.net/andkylee ... -
转:ASE中数据结果集分页功能的三种实现方法 .
2011-12-12 09:26 1089转:http://blog.csdn.net/andkylee ... -
Oracle的定时任务创建
2011-09-09 13:02 920drop sequence s_CarInfoID ... -
数据库下载地址 更新中....
2011-08-02 13:49 802ORACLE 下载(官网) http://www.oracl ... -
Java 调用 Oracle 存储过程返回结果集
2011-07-07 20:01 1415转自:http://wxy0327.itpub.net/ ... -
HugeTable 连接
2011-07-05 13:20 1023JAVA连接HugeTable的代码如下 ... -
Hsqldb安装与使用
2011-07-04 19:45 1055转自:http://fengyongfa2006.blog.1 ... -
SQL语句修改字段类型报错
2011-05-06 10:03 1957Sybase 12.5数据库执行 alter ta ... -
MySql数据库
2010-06-05 14:33 9211、安装 正在使用5.1版本 ,下载地址 ... ... -
Linux下启动Sybase ASE和IQ
2010-02-01 10:01 4648启动和关闭Sybase ASE sybase@*** ... -
Oracle Win自动备份数据BAT文件
2009-12-21 22:24 1424@echo off REM ################ ... -
Oracle常用及非常用函数
2009-09-22 16:47 778转自: http://wangyu.iteye.com/blo ... -
JDBC使用addBatch一次插入 万条数据
2009-09-22 16:26 2212public class Conn { pub ... -
Linux下启动Oralce
2009-09-22 14:45 875su - oracle sqlplus /nolog ...
相关推荐
sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程
想在存储过程中分页的朋友可以使用, sqlserver存储过程万能分页
sqlserver+group by分组查询分页存储过程
1.只需要提供Sql语句和每页的记录数,页数就可以了 2,速度超快哟,100W记录1~3秒就分出来了 3,对于存储过程特别好用
SQL Server 存储过程的分页方案比拼
sqlserver 存储过程分页(支持千万级)算法研究
sqlserver 分页调用的存储过程 t-sql 语句
SQL Server 2000下的通用分页存储过程.sql
SQL Server 存储过程及Oracle SQL语句分页
SQL server2008中通用分页存储过程,表名,每页长度,页码都是动态赋值。
通用的Sql Server分页程序,并且符合BootStrap Table 的调用标准.
SQL Server 存储过程的分页方案比拼.doc
很好的通过存储过程实现分页的技术,很好的通过存储过程实现分页的技术,很好的通过存储过程实现分页的技术
千万级数据分页查询存储过程SQLServer 有实例
sqlserver 存储过程分页
asp+sqlserver2005万能分页存储过程源码 sqlserver2005源码下载,很灵活的
sqlserver 支持多表联合查询分页存储过程 sqlserver 支持多表联合查询分页存储过程 sqlserver 支持多表联合查询分页存储过程 sqlserver 支持多表联合查询分页存储过程
SqlServer 分页 存储过程
使用系统游标分页,数据越大越能显示高效
SQLSERVER高效率分页存储过程,数据量在2千万下查询分页只需要1-2秒,优化良好....