关于查询效率，100w数据，查询只要1秒，与您分享

[comszsoft]

机器情况
p4: 2.4
内存: 1 G
os: windows 2003
mssqlserver 2000
目的: 查询性能测试,比较两种查询的性能

step by step

-- setp 1.
-- 建表
create table t_userinfo
(
userid int identity(1,1) primary key nonclustered,
nick varchar(50) not null default '',
classid int not null default 0,
writetime datetime not null default getdate()
)
go

-- 建索引
create clustered index ix_userinfo_classid on t_userinfo(classid)
go

-- step 2.

declare @i int
declare @k int
declare @nick varchar(10)
set @i = 1
while @i<1000000
begin
set @k = @i % 10
set @nick = convert(varchar,@i)
insert into t_userinfo(nick,classid,writetime) values(@nick,@k,getdate())
set @i = @i + 1
end
-- 耗时 08:27 ，需要耐心等待

-- step 3.
select top 20 userid,nick,classid,writetime from t_userinfo
where userid not in
(
select top 900000 userid from t_userinfo order by userid asc
)

-- 耗时 8 秒 ,够长的

-- step 4.
select a.userid,b.nick,b.classid,b.writetime from
(
select top 20 a.userid from
(
select top 900020 userid from t_userinfo order by userid asc
) a order by a.userid desc
) a inner join t_userinfo b on a.userid = b.userid
order by a.userid asc

-- 耗时 1 秒，太快了吧，不可以思议

-- step 5 where 查询
select top 20 userid,nick,classid,writetime from t_userinfo
where classid = 1 and userid not in
(
select top 90000 userid from t_userinfo
where classid = 1
order by userid asc
)
-- 耗时 2 秒

-- step 6 where 查询
select a.userid,b.nick,b.classid,b.writetime from
(
select top 20 a.userid from
(
select top 90000 userid from t_userinfo
where classid = 1
order by userid asc
) a order by a.userid desc
) a inner join t_userinfo b on a.userid = b.userid
order by a.userid asc

-- 查询分析器显示不到 1 秒，太神奇了

[Twoboy]

哈哈，UP就是：啊！！呸。。。。。。

[JustLovePro]

mark

[gwallan]

mark

[fxbird]

怎么写分页才能快点？

[perddy]

mark

[avon2006]

收收....

[tanjun132]

学习下，呵呵

[ttg520]

mark

[tomghfeng]

mark

[anison]

mark

[hq2008]

d

[sinkiangscorpio]

耗时8秒和耗时1秒的语句，两个top的值不一样，一个是900000，另一个是90000

[Scarroot]

mark

[wz]

mark

[洋溢2016]

kankan

[shsjj312]

学习了!

[penghao122]

楼主叫什么>>>>

[jomakzhao]

顶楼主啊

[worlddba]

up

[lngjx]

学习了，都是精华的东西呀，查询检索里面有很多学问的