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

收藏品。原帖地址：http://community.csdn.net/Expert/topic/5619/5619808.xml?temp=.4093286 

机器情况
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 秒，太神奇了

靠,没什么希奇的.楼主也不仔细看看.
========================================
step4里的这句
select top 20 a.userid from
(
select top 900020 userid from t_userinfo order by userid asc
) a order by a.userid desc
根本就是废话,相当于
select top 20 userid from t_userinfo order by userid asc
因为,你最外围还有个order by a.userid asc.
所以,你里边的一个升序,又一个降序根本是在浪费资源.
再看看step3里,那可是not in.

step 3、4的结果都是取第900000条后的20条数据，这种取法在分页获取数据的时候常用到。
step3：
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
)
这是最常见的写法，为什么不用where userid>900000?因为可能会有userid不连续的情况，比如中间某些userid被删除了，如果是取userid>900000的，当然就直接用where userid>900000就行了。
step3用到了not in，所以在这里头索引扫描的开销是最大的。

setp 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
最里层select top 900020 userid from t_userinfo order by userid asc
是先取出900020条userid，并且生序排列，就是1～900020，因为是主键，所以很快的，如果你在查询分析器单独执行而看到执行很久才执行完，那是因为显示出来的原因。
然后
select top 20 a.userid from
(
select top 900020 userid from t_userinfo order by userid asc
) a order by a.userid desc
把刚才那个结果集再降序排序，取前20条，就是900020～900001，
最后就是把原表t_userinfo和上面这个结果集做一下连接，获得userid之外的其他列内容。