本文共 3648 字，大约阅读时间需要 12 分钟。
创建模拟数据
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-- 插入数据declare @i intdeclare @k intdeclare @nick varchar(10)set @i = 1while @i<10000000beginset @k = @i % 10set @nick = convert(varchar,@i)insert into t_userinfo(nick,classid,writetime) values(@nick,@k,getdate())set @i = @i + 1endset statistics profile on--查看执行过程
表变量--执行时间2-3秒
declare @tempTb table(id int)insert @tempTbselect 1 union allselect 2 union allselect 3 union allselect 4 union allselect 5;--select * from @tempTbselect a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfo inner join @tempTb on classid=idwhere writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid ascunion all--执行时间5-6秒
select a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfowhere classid =1 and writetime>'2013-03-05 17:49:33.247' order by userid ascunion allselect top 1000000 userid from t_userinfowhere classid =2 and writetime>'2013-03-05 17:49:33.247' order by userid ascunion allselect top 1000000 userid from t_userinfowhere classid =3 and writetime>'2013-03-05 17:49:33.247' order by userid ascunion allselect top 1000000 userid from t_userinfowhere classid =4 and writetime>'2013-03-05 17:49:33.247' order by userid ascunion allselect top 1000000 userid from t_userinfowhere classid =5 and writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid asc分步查询--执行时间4-5秒
select a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfowhere classid =1 and writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid ascselect a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfowhere classid =2 and writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid ascselect a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfowhere classid =3 and writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid ascselect a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfowhere classid =4 and writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid ascselect a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfowhere classid =5 and writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid asc分步查询--执行时间2-3秒
set statistics profile onselect a.userid,b.nick,b.classid,b.writetime from(select top 20 a.userid from(select top 1000000 userid from t_userinfowhere classid in(1,2,3,4,5) and writetime>'2013-03-05 17:49:33.247' order by userid asc) a order by a.userid desc)a inner join t_userinfo b on a.userid = b.useridorder by a.userid asc
转载地址：http://reunb.baihongyu.com/