郁闷。。这两个SQL语句效率竟然差这么多！老手进来瞧瞧
语句1：
SELECT m.item1,m.item2,m.item3,m.item4,m.item5,
count(1) AS 个数
FROM tmpL m,E n
WHERE m.item5> 0 and m.item6 > 0 and
m.item1 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item2 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item3 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item4 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item5 IN (a,b,c,d,e,f,g,h,i,j)
GROUP BY m.item1,m.item2,m.item3,m.item4,m.item5
having count(1)> 100
语句2：
select *,个数 from (
SELECT m.item1,m.item2,m.item3,m.item4,m.item5,
sum(case when m.item1 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item2 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item3 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item4 IN (a,b,c,d,e,f,g,h,i,j)
AND m.item5 IN (a,b,c,d,e,f,g,h,i,j) then 1 else 0 end) AS 个数
FROM tmpL m,E n
WHERE m.item5> 0 and m.item6 is NULL
GROUP BY m.item1,m.item2,m.item3,m.item4,m.item5
) m
where 个数> 100
-----------------------------
以下为模拟数据脚本：
drop table E
GO
--建表脚本:
create table E
(
id int,
a int,
b int,
c int,
d int,
e int,
f int,
g int,
h int,
i int,
j int
)
go
-- 模拟生成10万条包含0-10的数据
declare @i1 int,@i2 int,@i3 int,@i4 int,@i5 int,@i6 int,@i7 int,@i8 int,@i9 int,@i10 int,@x int,@tt int
select @x=1
while @x <=100000
begin
select @tt=cast(rand()*1000 as int)%15
select @i1=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i2=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i3=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i4=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i5=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i6=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i7=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i8=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i9=(CASE when @tt <=10 then @tt else 0 end)
select @tt=cast(rand()*1000 as int)%15
select @i10=(CASE when @tt <=10 then @tt else 0 end)
insert into E(id, a, b, c, d, e,f,g,h,i,j)
select @x,@i1,@i2,@i3,@i4,@i5,@i6,@i7,@i8,@i9,@i10
set @x=@x+1
end
GO
SELECT item as item1 into tmpC FROM
(select a as item,count(a) as CNT from E WHERE a> 0 GROUP BY a
UNION ALL select b as item,count(b) as CNT from E WHERE b> 0 GROUP BY b
UNION ALL select c as item,count(c) as CNT from E WHERE c> 0 GROUP BY c
UNION ALL select d as item,count(d) as CNT from E WHERE d> 0 GROUP BY d
UNION ALL select e as item,count(e) as CNT from E WHERE e> 0 GROUP BY e
UNION ALL select f as item,count(f) as CNT from E WHERE f> 0 GROUP BY f
UNION ALL select g as item,count(g) as CNT from E WHERE g> 0 GROUP BY g
UNION ALL select h as item,count(h) as CNT from E WHERE h> 0 GROUP BY h
UNION ALL select i as item,count(i) as CNT from E WHERE i> 0 GROUP BY i
UNION ALL select j as item,count(j) as CNT from E WHERE j> 0 GROUP BY j
) m GROUP BY item having(SUM(CNT)> =100)
Go
SELECT T1.item1 as item1,T2.item1 as item2,T3.item1 as item3,
T4.item1 as item4,T5.item1 as item5,T6.item1 as item6,
T7.item1 as item7,T8.item1 as item8,T9.item1 as item9,
T10.item1 as item10
INTO tmpL
FROM tmpC T1 FULL JOIN tmpC T2 ON T1.item1 <T2.item1 OR T2.item1 IS NULL
FULL JOIN tmpC T3 ON T2.item1 <T3.item1 OR T3.item1 IS NULL
FULL JOIN tmpC T4 ON T3.item1 <T4.item1 OR T4.item1 IS NULL
FULL JOIN tmpC T5 ON T4.item1 <T5.item1 OR T5.item1 IS NULL
FULL JOIN tmpC T6 ON T5.item1 <T6.item1 OR T6.item1 IS NULL
FULL JOIN tmpC T7 ON T6.item1 <T7.item1 OR T7.item1 IS NULL
FULL JOIN tmpC T8 ON T7.item1 <T8.item1 OR T8.item1 IS NULL
FULL JOIN tmpC T9 ON T8.item1 <T9.item1 OR T9.item1 IS NULL
FULL JOIN tmpC T10 ON T9.item1 <T10.item1 OR T10.item1 IS NULL
where T1.item1> 0
GO
哪个语句快我先保密~
大家说说看法以及原因~
[解决办法]
原因是
AND m.item2 IN (a,b,c,d,e,f,g,h,i,j)
这样的条件速度快不了
估计1的速度快些
[解决办法]
看起来似乎第1个快些
join的时候已经过滤掉了大量数据
第2个是join的结果还可能会有大量的数据, 然后再计算并过滤
[解决办法]
路过
保密~ .............
[解决办法]
接分是种好习惯
