--in 查询1开销:43%
select * from tblstudentstatus where id in(select hid from tblhf group by hid);--with as 查询2开销:43%
with crh as
(
select hid from tblhf group by hid
)
select * from tblstudentstatus where id in(select * from crh)--临时表 查询3开销:13% 查询4开销:0%
declare @t table(hid int)
insert into @t(hid) (select hid from tblhf group by hid)
select * from tblstudentstatus where id in(select * from @t)
测试结果如上。
某文章说 用with as 提高效率,表变量实际上使用了临时表,从而增加了额外的I/O开销。
出自:http://jeemiss.javaeye.com/blog/642519
按ctrl+L
但我测试结果却是 表变量开销最小。
初学SQL,请教高手指点下。
select * from tblstudentstatus where id in(select hid from tblhf group by hid);--with as 查询2开销:43%
with crh as
(
select hid from tblhf group by hid
)
select * from tblstudentstatus where id in(select * from crh)--临时表 查询3开销:13% 查询4开销:0%
declare @t table(hid int)
insert into @t(hid) (select hid from tblhf group by hid)
select * from tblstudentstatus where id in(select * from @t)
测试结果如上。
某文章说 用with as 提高效率,表变量实际上使用了临时表,从而增加了额外的I/O开销。
出自:http://jeemiss.javaeye.com/blog/642519
按ctrl+L
但我测试结果却是 表变量开销最小。
初学SQL,请教高手指点下。
用了临时表会使后面的查询在索引中查找,而上面两个则是在索引中扫描的。
正如guguda2008所说的,虽然(查询3开销:13%)临时表有一定开销量,但(查询4开销:0%)查询的时候用到了索引,而提高了效率。