20CN网络安全小组论坛 - 网站建设 - MSSQLServer2005中的Row

论坛: 网站建设标题: MSSQLServer2005中的Row_Number测试

复制本贴地址

作者: NetFog [q70213526]

版主

今天在翻看自己写的一些程序的时候.看到以前学MSSQL SERVER 2005时的一些日记,发上来大家看看.恐有不对这处.欢迎指出.

测试表名:TblTest
数据量:500万条
建表语句:

代码:


USE [TestDB]
GO
/****** 对象:  Table [dbo].[TblTest]    脚本日期: 09/03/2006 08:58:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TblTest](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[AritcleTitle] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Ftitle] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Author] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[AddTime] [datetime] NULL,
	[otime] [datetime] NULL CONSTRAINT [DF_TblTest_otime]  DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

1、Row_Number()一定要使用在建立了clustered index的字段上，测试中，clustered index建立在otime字段上
（一）：对ID使用Row_Number
select * from(select Id, AritcleTitle, ROW_NUMBER() OVER(order by id desc) as row from TblTest) a where row between 20 and 30
执行时间：68359MS
此处虽然ID为主键，但因为他不是clustered index列,使用row_number()执行速度照样很慢，一分多钟
所以我们可以肯定，不必再对他进行下文（二）的一些其他测试，速度只会更慢

（二）：对有Clustered Index的otime字段使用Row_Number,下面一》和二》是对比测试
一》,order by otime desc
执行:
select * from(select Id, AritcleTitle, ROW_NUMBER() OVER(order by otime desc) as row from TblTest) a where row between 20 and 30
执行时间：187MS
由于我将clustered index建立在otime上，otime是连续的时间排列,500W数据取21条数据，在我机子上执行速度特别快,只有一百多毫秒，在服务器上会更快
所以，我们在SQL SERVER 2005中的分页完全可以使用Row_Number()来分页，不必使用复杂的方法（通常使用TOP/临时表/表变量）
下面我们继续测试，上面测试的取得的结果是数据库中的后一部分数据,下面来测试中间部分的数据

执行:
select * from(select Id, AritcleTitle, ROW_NUMBER() OVER(order by otime desc) as row from TblTest) a where row between 2500000 and 2500020
执行时间：5781MS
这是到得:返回结果取得了500W数据中的第2499981条到2500001条数据，执行结果比较理想，不到6秒.
我们继续测试，取得数据库前部分的数据

执行：
select * from(select Id, AritcleTitle, ROW_NUMBER() OVER(order by otime desc) as row from TblTest) a where row between 4999980 and 5000000
执行时间：11765MS
这里返回了第1条到第21条的数据，一共21条,执行时间约12秒,从上面测试结果来看，时间慢慢增长。时间消耗主要在索引的读取上。

二》，order by otime asc
执行:
select * from(select Id, AritcleTitle, ROW_NUMBER() OVER(order by otime asc) as row from TblTest) a where row between 20 and 30
执行时间：46MS
执行:
select * from(select Id, AritcleTitle, ROW_NUMBER() OVER(order by otime asc) as row from TblTest) a where row between 2500000 and 2500020
执行时间：5046MS
执行：
select * from(select Id, AritcleTitle, ROW_NUMBER() OVER(order by otime asc) as row from TblTest) a where row between 4999980 and 5000000
执行时间：9562MS

小结：在使用asc和desc时，个人感觉asc还是有一点点的区别，asc快一点点

在MS SQL Server 2005 中继承了MS SQL Server 2000的TOP，在Oracle只有RowNumber，以前我们用SQL Server 2000时，分页基本使用到TOP和MAX等，TOP在SQL SERVER 2000高效分页中功不可没，现在我们又有了Row_Number这个好东西,所以现在我们考虑将两者结合起来，用于高效分页.如果要使用Row_Number来分页,则用到上面的对比测试结果就了，达到高效的目的。当然。对于SQL Server数据库，我们一般会采用分区的原理。500万条数据我们肯定会分将其分区了。

地主发表时间: 06-12-30 23:09

回复: NetFog [q70213526]

版主

日的。乱七八糟。。
原来俺真不适合写日志的。只有自己看得懂。将就一下吧。

B1层发表时间: 06-12-30 23:10

论坛: 网站建设