더 효율적인건 없을까요 ^-^

클러스터드 인덱스가 ssno 에 있다고 치고 더

빠른 방법이나 좋은 예 있음 부탁드립니다.

compute 컬럼을 쓰는건 어떨까요?

인덱스 scan을 1회로 줄여봤습니다..

그럼..

< 이하 샘플 코드 >

use tempdb
go

create table tbluser(
  ssno varchar(13) not null
)
go

alter table tbluser with nocheck add
  constraint pk_tbluser primary key clustered (ssno)
go

insert into tbluser values ('5012121121212')
insert into tbluser values ('5512121121212')
insert into tbluser values ('6012121121212')
insert into tbluser values ('6512121121212')
insert into tbluser values ('7012121121212')
insert into tbluser values ('7512121121212')
insert into tbluser values ('8012121121212')
insert into tbluser values ('8512121121212')
insert into tbluser values ('9012121121212')
insert into tbluser values ('9512121121212')

insert into tbluser values ('5012122121212')
insert into tbluser values ('5512122121212')
insert into tbluser values ('6012122121212')
insert into tbluser values ('6512122121212')
insert into tbluser values ('7012122121212')
insert into tbluser values ('7512122121212')
insert into tbluser values ('8012122121212')
insert into tbluser values ('8512122121212')
insert into tbluser values ('9012122121212')
insert into tbluser values ('9512122121212')

select * from tbluser

-- given query
set statistics io on
set statistics profile on

select sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 18) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 24))
                   then 1 else 0 end) as [18-24]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 24) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 29))
                   then 1 else 0 end) as [25-29]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 29) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 34))
                   then 1 else 0 end) as [30-34]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 34) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 39))
                   then 1 else 0 end) as [35-39]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 39) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 49))
                   then 1 else 0 end) as [40-49]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) >= 50))
                   then 1 else 0 end) as [50]
    from tbluser
    where isnumeric(ssno) = 1 and substring(ssno, 7,1) = 1
union all
select sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 18) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 24))
                  then 1 else 0 end) as [18-24]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 24) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 29))
                   then 1 else 0 end) as [25-29]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 29) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 34))
                   then 1 else 0 end) as [30-34]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 34) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 39))
                   then 1 else 0 end) as [35-39]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 39) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 49))
                   then 1 else 0 end) as [40-49]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) >= 50))
                   then 1 else 0 end) as [50]
    from tbluser
    where isnumeric(ssno) = 1 and substring(ssno, 7,1) = 2

set statistics io off
set statistics profile off

-- tuning by me
set statistics io on
set statistics profile on

select substring(ssno, 7,1), sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) <= 18))
                   then 1 else 0 end) as [18-24]
          , sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 18) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 24))
                   then 1 else 0 end) as [18-24]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 24) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 29))
                   then 1 else 0 end) as [25-29]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 29) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 34))
                   then 1 else 0 end) as [30-34]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 34) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 39))
                   then 1 else 0 end) as [35-39]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) > 39) AND (year(getdate()) - cast('19'+ left(ssno,2) as int) <= 49))
                   then 1 else 0 end) as [40-49]
          ,sum(case when((year(getdate()) - cast('19'+ left(ssno,2) as int) >= 50))
                   then 1 else 0 end) as [50]
    from tbluser
    group by (substring(ssno, 7,1))

set statistics io off
set statistics profile off

drop table tbluser

나그네님 감사합니다. ^----^

또하나 재미 있는거 배웠습니다.

set statistics io on
set statistics profile on

나그네 tuned

아 통계정보를 볼때 Message 에

(11 row(s) affected)

'tblUser' 테이블. 스캔 수 1, 논리적 읽기 수 2221, 물리적 읽기 수 0, 미리 읽기 수 0.

(4 row(s) affected)

석이 generated
(2 row(s) affected)

'tblUser' 테이블. 스캔 수 2, 논리적 읽기 수 4442, 물리적 읽기 수 0, 미리 읽기 수 0.

(8 row(s) affected)

실행계획은 짧아 졌는데 시간은 동일하게 걸리는 군요

당연히 반 정도로 짧게 나와야 하는데 왜 비슷하게 나올까요?

이렇게 함수를 쓰면 인덱스를 못타는 걸로 들었는데

이런걸 대비해서 뭘 할 수 있을까요?

이런 정보가 나오는 군요

논리적 읽기수가 줄었다는 것은 그만큼 Server Resource의 사용이 줄었다는 것을 의미합니다.. 시간이 동일하게 나온다 하더라도 어느 정도 System Resource에 대해서는 어느 정도 효율적으로 된 것이겠죠..

인덱스를 타게 하기 위해서는 별도의 column을 만들어 둘 필요가 있습니다. 일반적으로 가입시 연령이라던지.. ^^*

아니면, 실행 시 연령이 필요하다면, 계산 Column을 추가해서 그 칼럼에다가 인덱스를 걸어두면 되겠죠.. (미확인 사항입니다.. 인덱스 걸어둘 수 있는지는 조사해봐야 겠지만요.. BOL 등에서.. ^^;;)

(추가 => 인덱스 걸 수 없다네요.. ^^;; 그럼, 칼럼을 만들어서 매년 업데이트 scheduling이라도.. ^^;;)

그럼..

여리님이 짜신거 ^-^

그냥 다른분들이 한번 보시라구...

set statistics io on
set statistics profile on
set statistics time on

Select sex,
 sum([18-24]) as [18-24], sum([25-29]) as [25-29],
 sum([30-34]) as [30-34] , sum([35-39]) as [35-39],
 sum([40-44]) as [40-44], sum([45-49]) as [45-49], sum([50]) as [50]
From
 (select case when substring(ssno, 7,1) = 1 then '남' else '여' end as Sex,
     case when year(getDate()) - cast('19' + left(ssno, 2) as int)  between 19 and 24 then 1 else 0 end as [18-24],
     case when year(getDate()) - cast('19' + left(ssno, 2) as int)  between 25 and 29 then 1 else 0 end as [25-29],
     case when year(getDate()) - cast('19' + left(ssno, 2) as int)  between 30 and 34 then 1 else 0 end as [30-34],
     case when year(getDate()) - cast('19' + left(ssno, 2) as int)  between 35 and 39 then 1 else 0 end as [35-39],
     case when year(getDate()) - cast('19' + left(ssno, 2) as int)  between 40 and 44 then 1 else 0 end as [40-44],
     case when year(getDate()) - cast('19' + left(ssno, 2) as int)  between 45 and 49 then 1 else 0 end as [45-49],
     case when year(getDate()) - cast('19' + left(ssno, 2) as int)  >= 50 then 1 else 0 end as [50]
 From  tbluser where  isnumeric(ssno) = 1) x
Group by sex
Order by 1

set statistics io off
set statistics profile off
set statistics time off

2탄

create view VIEW_STAT_for_Member_Execl_OLAP

as

-- 엑셀 피벗팅을 위한 기초데이터 작성
-- 작성자 김민석
-- 20050624
-- drop view VIEW_STAT_for_Member_Execl_OLAP


select                     'old' =
                        case
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) <= 12 then '12세이하'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) between 13 and 18  then '13-18'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) = 19  then '19'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) between 20 and 24  then '20-24'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) between 25 and 29  then '25-29'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) between 30 and 34  then '30-34'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) between 35 and 39  then '35-39'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) between 40 and 49  then '40-49'
                                when (year(getdate()) - cast('19' + left(ssno,2) as int)) > 50  then '50이상'
                                else '에러'
                        end

                , 'gender' =
                        case
                                when substring(ssno, 7,1) = 1 then '남'
                                when substring(ssno, 7,1) = 2 then '여'
                                when substring(ssno, 7,1) = 3 then '남'
                                when substring(ssno, 7,1) = 4 then '여'
                                else '미입력'
                        end

                , 'locale' =
                        case
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '02' then '서울'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '031' then '경기'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '032' then '인천'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '033' then '강원'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '041' then '충남'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '042' then '대전'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '043' then '충북'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '051' then '부산'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '052' then '울산'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '053' then '대구'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '054' then '경북'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '055' then '경남'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '061' then '전남'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '062' then '광주'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '063' then '전북'
                                when rtrim(ltrim(substring(tel,1,charindex('-',tel)-1))) = '064' then '제주'
                                else '미입력'
                                
                        end

                , 'job' =
                        case
                                when isnull(jobid,'99') = 1 then '사무직'
                                when isnull(jobid,'0') = 2 then '영업직'                        
                                when isnull(jobid,'0') = 3 then '기술직'
                                when isnull(jobid,'0') = 4 then '자영업'
                                when isnull(jobid,'0') = 5 then '대학(원)생'
                                when isnull(jobid,'0') = 6 then '초중고'
                                when isnull(jobid,'0') = 7 then '주부'
                                when isnull(jobid,'0') = 8 then '공무원'
                                when isnull(jobid,'0') = 9 then '교직자'
                                when isnull(jobid,'0') = 10 then '전문인'
                                when isnull(jobid,'0') = 11 then '의료인'
                                when isnull(jobid,'0') = 12 then '법조인'
                                when isnull(jobid,'0') = 13 then '종교인'
                                when isnull(jobid,'0') = 14 then '언론인'
                                when isnull(jobid,'0') = 15 then '농림축수산광업'
                                when isnull(jobid,'0') = 90 then '무직'
                                when isnull(jobid,'0') = 99 then '기타'
                                else        '기타'
                        end

                , 'vip' =
                        case
                                when isnull(jobid,'0') = 11 then 'vip'
                                when isnull(jobid,'0') = 12 then 'vip'
                                when isnull(jobid,'0') = 9 then 'vip'
                                else 'etc'
                        end

                , 'salary' =
                        case         
                                when isnull(salaryid, '1') = 1 then '1000이하'
                                when isnull(salaryid, '2') = 2 then '1000-1500'
                                when isnull(salaryid, '3') = 3 then '1500-2000'
                                when isnull(salaryid, '4') = 4 then '2000-3000'
                                when isnull(salaryid, '5') = 5 then '3000-4000'
                                when isnull(salaryid, '6') = 5 then '4000이상'
                                else '미입력'
                        end

                , 'ismarry' =
                        case
                                when len(weddingAnniversary) > 0 then '혼인'
                                else '미입력/미혼'
                        end

                , 'cPhone' =
                        case
                                when Mobile = '000-0000-0000' then '미입력/없음'
                                else '있음'
                        end
                                


        from tbluser a inner join tbluserdetail b
        on a.userNumber = b.userNumber

where                 isnumeric(ssno) = 1
                and isSendemail =1