34,576
社区成员
发帖
与我相关
我的任务
分享
--构造表tb1测试数据
create table tb1(ID int identity(1,1),C1 varchar(10),C2 int)
declare @C1 varchar(10),@C2 int
set @C1=1
while(@C1<=100)
begin
set @C2=1
while(@C2<=20)
begin
Insert into tb1(C1,C2) values('A'+@C1,@C2)
set @C2+=1
end
set @C1+=1
end
--构造表tb2测试数据
create table tb2(ID int identity(1,1),C1 int,C2 int)
declare @C1 int,@C2 int
set @C1=0
while(@C1<=999)
begin
set @C2=1
while(@C2<=45)
begin
Insert into tb2(C1,C2) values(@C1,@C2)
set @C2+=1
end
set @C1+=1
end
set statistics time on
--查询Sql
select t1.C1,t2.C1,COUNT(*) from tb1 t1,tb2 t2
where t1.C2=t2.C2
group by t1.C1,t2.C1
CREATE INDEX ix_tb1_c2_c1 ON tb1(c2,c1)
CREATE INDEX ix_tb2_c2_c1 ON tb2(c2,c1)
加索引之后可以快一点,但快不了多少。
应该是最终输出的结果集大导致效率不高, 毕竟你都没过滤;