select A.idx, A.mem_id, A.mem_name
, (select count(id) from tb_order where ad_flag=N'Y' and mem_id=A.mem_id and gender='M') as count_m
, (select count(id) from tb_order where ad_flag=N'Y' and mem_id=A.mem_id and gender='W') as count_w
, (select count(id) from tb_order
where ad_flag=N'Y' and mem_id=A.mem_id and oldg='10') as count_10
, (select count(id) from tb_order
where ad_flag=N'Y' and mem_id=A.mem_id and oldg='20') as count_20
, (select count(id) from tb_order
where ad_flag=N'Y' and mem_id=A.mem_id and oldg='30') as count_30
, (select count(id) from tb_order
where ad_flag=N'Y' and mem_id=A.mem_id and oldg='40') as count_40
, (select count(id) from tb_order
where ad_flag=N'Y' and mem_id=A.mem_id and oldg='50') as count_50
, (select count(id) from tb_order
where ad_flag=N'Y' and mem_id=A.mem_id and oldg='60') as count_60
from tb_member A
where문 부분은 제외하고 대충 이런식으로 통계 테이블을 뿌려주고 있는데요.
데이터가 수백만건이 되니까 이런 서브쿼리로는 속도가 너무 느려요.
집계 카운팅을 하는 대상은 tb_order 테이블 하나뿐인데, 이걸 효율적으로 select 해올수 있는
쿼리는 어떻게하면 될까요?
Comment 4
-
군고구마
2014.09.03 19:04
-
항해자™
2014.09.03 19:06
select A.idx, A.mem_id, A.mem_name, sum(case getder when 'M' then 1 end) as count_m, sum(case getder when 'W' then 1 end) as count_w, sum(case oldg when '10' then 1 end) as count_10, sum(case oldg when '20' then 1 end) as count_20, sum(case oldg when '30' then 1 end) as count_30, sum(case oldg when '40' then 1 end) as count_40, sum(case oldg when '50' then 1 end) as count_50, sum(case oldg when '60' then 1 end) as count_60from dbo.tb_member as ainner joindbo.tb_order as bon b.mem_id = a.mem_idwhere b.ad_flag = 'Y'group byA.idx, A.mem_id, A.mem_namego -
항해자™
2014.09.03 19:11
그런데 이거,,, 이렇게 봐야 하는거 아닌지,,,,,,
select sum(case getder when 'M' then 1 end) as count_m
, sum(case getder when 'W' then 1 end) as count_m
, sum(case oldg when '10' then 1 end) as count_10
, sum(case oldg when '20' then 1 end) as count_20
, sum(case oldg when '30' then 1 end) as count_30
, sum(case oldg when '40' then 1 end) as count_40
, sum(case oldg when '50' then 1 end) as count_50
, sum(case oldg when '60' then 1 end) as count_60
from dbo.tb_member as a
inner join
dbo.tb_order as b
on b.mem_id = a.mem_id
where b.ad_flag = 'Y'
go
-
애즈원
2014.09.03 20:05
지금 잠시 약식으로 실행을 해봤는데
엄청난!! 속도 향상이 있는것 같네요.
정말 감사합니다.
case 문을 이용해 보세요.
이게 좀 더 좋을 듯 하네요.