속도 비교 (이벤트 응모자 날짜별 뽑기)

두개의 셀렉트

 이벤트 응모자 날짜별로 뽑는 쿼리 ..

set statistics io on --쿼리 분석기 메세지에서 io 카운트

 

SELECT A0626 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-06-26')
  , A0627 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-06-27')
  , A0628 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-06-28')
  , A0629 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-06-29')
  , A0630 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-06-30')
  , A0701 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-07-01')
  , A0702 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-07-02')
  , A0703 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-07-03')
  , A0704 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-07-04')
  , A0705 = (SELECT COUNT(*) FROM E090604_water WHERE waterday = '2009-07-05')

 

스캔 20회 논리 20회

 

SELECT A0626 = isnull(SUM(CASE WHEN waterday = '2009-06-26' THEN 1 END), 0) --null 이면 0값
  , A0627 = isnull(SUM(CASE WHEN waterday = '2009-06-27' THEN 1 END), 0)
  , A0628 = isnull(SUM(CASE WHEN waterday = '2009-06-28' THEN 1 END), 0)
  , A0629 = isnull(SUM(CASE WHEN waterday = '2009-06-29' THEN 1 END), 0)
  , A0630 = isnull(SUM(CASE WHEN waterday = '2009-06-30' THEN 1 END), 0)
  , A0701 = isnull(SUM(CASE WHEN waterday = '2009-07-01' THEN 1 END), 0)
  , A0702 = isnull(SUM(CASE WHEN waterday = '2009-07-02' THEN 1 END), 0)
  , A0703 = isnull(SUM(CASE WHEN waterday = '2009-07-03' THEN 1 END), 0)
  , A0704 = isnull(SUM(CASE WHEN waterday = '2009-07-04' THEN 1 END), 0)
  , A0705 = isnull(SUM(CASE WHEN waterday = '2009-07-05' THEN 1 END), 0)
FROM E090604_water

스캔 1회 논리 2회

 

create index IDX_E090604_water__waterday on E090604_water(waterday)  인덱스 걸어주고 다시 해보니

 

위에건 스캔 20 논리10

아래건 스캔 1   논리1