在sqlsver查询分析器中直接运行即可(提示:运行时间大约30分钟) 如有需要请认真研究代码,会大有收获。
--change tools|options setting to discard query results
set nocount on;
use testdb;
go
if object_id('dbo.RNBenchmark') is not null
drop table dbo.RNBenchmark;
go
if object_id('dbo.SalesBM') is not null
drop table dbo.SalesBM;
go
if object_id('dbo.SalesBMIdentity') is not null
drop table dbo.SalesBMIdentity;
go
if object_id('dbo.SalesBMCursor') is not null
drop table dbo.salesBMCursor;
go
if object_id('dbo.RNTechniques') is not null
drop table dbo.RNTechniques;
create table dbo.RNTechniques
(
tid int not null primary key,
technique varchar(25) not null
);
insert into dbo.RNTechniques (tid,technique)values(1,'Subquery')
insert into dbo.RNTechniques (tid,technique)values(2,'Identity')
insert into dbo.RNTechniques (tid,technique)values(3,'Cursor')
insert into dbo.RNTechniques (tid,technique)values(4,'Row_Number')
go
create table dbo.RNBenchmark
(
tid int not null references dbo.RNTechniques(tid),
numrows int not null,
runtimemcs bigint not null,
primary key(tid,numrows)
);
go
create table dbo.SalesBM
(
empid int not null identity primary key,
qty int not null
);
create index idx_qty_empid on dbo.SalesBM(qty,empid);
go
create table dbo.SalesBMIdentity(empid int,qty int,rn int identity);
go
create table dbo.SalesBMCursor(empid int,qty int,rn int);
go
declare
@maxnumrows as int,
@steprows as int,
@curnumrows as int,
@dt as datetime;
set @maxnumrows=100000;
set @steprows=10000;
set @curnumrows=10000;
while @curnumrows<=@maxnumrows
begin
truncate table dbo.SalesBM;
insert into dbo.SalesBM(qty)
select cast(1+999.9999999999*rand(checksum(newid())) as int)
from dbo.Nums
where n<=@curnumrows;
--'subquery'
dbcc freeproccache with No_INFOMSGS;
dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET @dt=getdate();--use sysdatetime() after to 2008
select empid,qty,
(
select count(*)
from dbo.SalesBM as s2
where s2.qty<s1.qty
or
(
s2.qty=s1.qty and s2.empid<=s1.empid
)
) as rn
from dbo.SalesBM as s1
order by qty,empid;
insert into dbo.RNBenchmark(tid,numrows,runtimemcs)
values
(
1,@curnumrows,datediff(ms,@dt,getdate())
);
--'identity'
truncate table dbo.SalesBMIdentity;
dbcc freeproccache with No_INFOMSGS;
dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;
set @dt=getdate();
insert into dbo.SalesBMIdentity(empid,qty)
select empid,qty from dbo.salesBM order by qty,empid;
select empid,qty,rn from dbo.SalesBMIdentity;
insert into dbo.RNBenchmark(tid,numrows,runtimemcs)
values(2,@curnumrows,datediff(ms,@dt,getdate()));
--cursor
truncate table dbo.SalesBMCursor;
dbcc freeproccache with No_INFOMSGS;
dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;
set @dt=getdate();
declare @empid as int,@qty as int,@rn as int;
begin tran
declare rncursor cursor FAST_FORWARD
for
select empid,qty from dbo.salesBM order by qty,empid;
open rncursor;
set @rn=0;
fetch next from rncursor into @empid,@qty;
while @@fetch_status=0
begin
set @rn=@rn+1;
insert into dbo.SalesBMCursor(empid,qty,rn)
values
(
@empid,@qty,@rn
);
fetch next from rncursor into @empid,@qty;
end
close rncursor;
deallocate rncursor;--delete rncursor;
commit tran;
select emPid,qty,rn from dbo.SalesBMCursor;
insert into dbo.RNBenchmark(tid,numrows,runtimemcs)
values
(3,@curnumrows,datediff(ms,@dt,getdate()));
--row_number
dbcc freeproccache with No_INFOMSGS;
dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;
set @dt=getdate();
select empid,qty, row_number() over(order by qty,empid)as rn
from dbo.SalesBM;
insert into dbo.RNBenchmark(tid,numrows,runtimemcs)
values (4,@curnumrows,datediff(ms,@dt,getdate()));
set @curnumrows=@curnumrows+@steprows;
end
--pivot table
SELECT numrows,
[Subquery],[Cursor],[Identity],[Row_Number]
from
(
select technique,numrows,runtimemcs
from dbo.RNBenchmark as b
join dbo.RNTechniques as t
on b.tid=t.tid
) as d
pivot(max(runtimemcs) for technique
in([subquery],[identity],[cursor],[row_number])
) as p
order by numrows,[Subquery],[Cursor],[Identity],[Row_Number];
分享到:
相关推荐
今天,老师带偶们复习了一下数据库中的分页,总体来说,今天感觉还不错,因为以前学的还没忘。好了,进入正题,首先,说说top的方法top方法其实就是将你要查的的页数的数据前得数据去掉 再取前几例: 代码如下: 一...
sqlserver2008不支持关键字limit ,所以它的分页sql查询语句将不能用MySQL的方式进行,幸好sqlserver2008提供了top,rownumber等关键字,这样就能通过这几个关键字实现分页。 下面是本人在网上查阅到的几种查询脚本...
临近春节,心早已飞了不在工作上了,下面小编给大家整理些数据库的几种分页查询。 Sql Sever 2005之前版本: select top 页大小 * from 表名 where id not in ( select top 页大小*(查询第几页-1) id from 表名 ...
比如:数据库分页,你不用再关心记录总数怎么取,不用考虑各种数据库之间的差异,最关键的是绝对的高效。再比如LOB字段的处理,可以直接通过增、删、改、查等接口处理,遇到较大的LOB,也可以用流的方式处理,避免...
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视为电子化的文件柜——存储电子文件的处所,用户可以对文件中的数据运行新增、截取、更新、删除等操作。 ...
实现网站全文检索有几种常见方案,比如应用数据库全文检索,开源搜索引擎,使用Google API等, 本文我们将就如何使用SQL Server 2005多快好省地建立网站全文检索展开探讨。 二、全文检索技术说明 1、应用背景 ...
14、查询从多少行到多少行的记录(可以用在web开发中的分页显示) select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b ) where row_id between 15 and 20 15、对公共授予访问权 ...
-Window的实例方法GetCloseReference等以及ActiveWindow的静态方法GetCloseReference等,其中的Close全部改为Hide。 -增加TabStrip中Tab控件可关闭属性EnableClose(默认为false)以及两个方法GetShowReference和...
-Grid的BoundField增加NullDisplayText属性,用于处理数据库中的null值,如果没有设置则默认为空字符串。 -修正DatePicker中的一个bug(31/01/2010将会返回NULL)使用DateFormatString来生成SelectedDate属性...
ASP.NET程序中常用的三十三种代码 1. 打开新的窗口并传送参数: 传送参数: response.write("<script>window.open(’*.aspx?id="+this.DropDownList1.SelectIndex+"&id1="+...+"’)</script>") 接收参数: ...