데이터베이스 개발자 질문과 답변 게시판

데이터베이스 개발/운영 관련 질문과 답변을 올리는 게시판입니다. 궁금하신 내용을 이곳에서 문의하시면 SQLER 분들의 답변을 받으실 수 있습니다. 문의를 하실때에는 최근 작업하신 특이 사항이나, 장애 발생 전 상황을 상세히 올려 주시면 답글을 적어주시는 SQLER분들의 답변이 더 정확할 수 있으니 도움 되시길 바랍니다. 쿼리 문의일 경우, 실제 문제가 재현되는 테이블생성, 샘플데이터 생성 쿼리를 함께 올려 주시면 더 빠르고 정확한 쿼리 문의 응답이 가능합니다.

쿼리 도움 요청드려요.

수수리 2017.08.02 00:32 Views : 4215

안녕하세요.

 

 

아는대로 이런 저런 방법 다 해봤는데 초보다 보니 한계가 있어

처리 속도가 너무 느려서 도움을 요청합니다.

 

mssql 2008 R2  +  vb 6.0으로 작업중입니다.

-----------------------------------------------------------------------------------------

[원하는 데이터]

 

모든 호실의 사용기간 ( 2017-04-01 ~ 2017-04-30 )Elgum 사용량 합 및 계량기값

 

조건1 : Dailytable_local.FLOOR =  a2mst.a202 , 

             Dailytable_local.HOSILNO =  substring(a2mst.a203,1,4) 

 

조건2 : 호실별 사용한 날짜의 사용량 합을 구한다.

             , 공동 사용시 해당 일의 사용량은 사용 인원으로 나누어 계산한다.

             (a203 필드의 뒤 "-1" , "-2"로 공동사용 인원수를 체크한다.) 

 

조건3: a292 값이 0 인 사람은 사용하지 않은것으로 간주하고 공동 사용자에게 부담 시킨다.(1 인 사람만 계산한다)

            호실별로 최소 1인은 사용을 했음.

 

ex)  floor =  '03' , hosilno = '0301' , 사용기간 : 2017-04-01 ~ 2017-04-30

0301호실은 2명 사용중임

(1) 0301-1 A회사 a292 = 0 이므로 사용량 없음

(2) 0301-1 이영식 2017-04-06 ~ 2017-04-30 까지 사용량의 1/2

(3) 0301-2 B회사 2017-04-01 ~2017-04-05까지 사용량 전체    +  2017-04-06~2017-04-20 까지의 1/2

(4) 0301-2 이지웅 2017-04-06 ~ 2017-04-30 까지 사용량의 1/2

 

 

이것저것 해보다가 결국  하루씩 체크해서 사용량 계산하여 합산하는 방향으로 가려고 하는데 속도가 너무 느립니다.

 

 

 

테이블1 :   Dailytable_local     일자별 검침 계량기 값

모든 호실의 일자별 데이터가 저장되어 있습니다.

DATE

FLOOR

HOSILNO

ELGUM

WAGUM

HIGUM

HOGUM

GAGUM

2017-03-31

03

0301

246.9

6.9

0.33

0.1

0

2017-04-01

03

0301

250.9

7

0.33

0.3

0

2017-04-02

03

0301

252.7

7.1

0.33

0.4

0

2017-04-03

03

0301

254.4

7.2

0.33

0.5

0

2017-04-04

03

0301

257

7.3

0.33

0.6

0

2017-04-05

03

0301

259.4

7.5

0.33

0.7

0

2017-04-06

03

0301

261.3

7.6

0.33

0.8

0

2017-04-07

03

0301

263.3

7.7

0.33

0.9

0

2017-04-26

03

0301

306.7

9.1

0.33

2.5

0

2017-04-27

03

0301

308.9

9.2

0.33

2.5

0

2017-04-28

03

0301

311.1

9.3

0.33

2.6

0

2017-04-29

03

0301

313.5

9.3

0.33

2.7

0

2017-04-30

03

0301

317.2

9.4

0.33

2.7

0

 

테이블2 : a2mst   호실 사용 현황

a202

a203

a205

a206

a207

a292

03

0301-1

A회사

1900-01-01

2017-04-05

0

03

0301-1

이영식

2017-04-06

9999-12-31

1

03

0301-2

B회사

1900-01-01

2017-04-20

1

03

0301-2

이지웅

2017-04-21

9999-12-31

1

03

0302-1

C회사

1900-01-01

2016-12-29

1

03

0302-1

안광효

2016-12-30

9999-12-31

1

03

0302-2

D회사

1900-01-01

2016-12-29

1

03

0302-2

최재혁

2016-12-30

9999-12-31

1

03

0303-1

E회사

1900-01-01

2016-12-27

1

03

0303-1

윤국성

2016-12-28

9999-12-31

1

...

...

...

...

...

...

03

0309-1

F회사

1900-01-01

2016-12-27

1

 

'------------------------------- 아래는 VB 코드 입니다 --------------------------------

Dim c%

Dim Td as integer

Dim SD as string , ED as String , Sdate as String

Dim cmd as Sting , Dcmd as String

Dim DbField  as String

Dim FLoor as String , Hosilno as String

 

Floor = "03"

Hosilno = "0301"

DbField = "Elgum"

Sdate = "2017-04-01"            '시작일

Td = 30

                                  For c% = 1 To Td    '총 일수 만큼 하루씩 돌린다.
                             
                                            '>>>>>>>>>>>>>>>>>>>>>>>
검침 수량 계산 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                            SD = Format(DateAdd("D", -1 + c%, SDate), "yyyy-mm-dd")
                                            ED = Format(DateAdd("D", c%, SDate), "yyyy-mm-dd")
        
                                            If c% = 1 Then
                                                cmd = "select sum(gap) from ("
                                            End If
                                            cmd = cmd & Chr(13) & " select "
                                            cmd = cmd & Chr(13) & " ("
                                            cmd = cmd & Chr(13) & "   isnull((select " & DbField & " from DailyTable_Local "
                                            cmd = cmd & Chr(13) & "   where date = '" & ED & "' and floor = '" & Floor & "' and HOSILNO = '" & HOSILNO  & "'),0) "
                                            cmd = cmd & Chr(13) & " - "
                                            cmd = cmd & Chr(13) & "   isnull((select " & DbField & " from DailyTable_Local "
                                            cmd = cmd & Chr(13) & "   where date = '" & SD & "' and floor = '" & Floor & "' and HOSILNO = '" & HOSILNO  & "'),0) "
                                            cmd = cmd & Chr(13) & " ) "
                                            cmd = cmd & Chr(13) & " / "   '
나누기
                                                '
나눌값 (해당일자의 호실수)###################
                                                cmd = cmd & Chr(13) & " case when  "

                                                Dcmd = Chr(13) & " (Select COUNT(a202) from a2mst where "
                                                Dcmd = Dcmd & Chr(13) & " a202 = '" & Floor & "' "
                                                Dcmd = Dcmd & Chr(13) & " And substring(a203,1,4) = '" & HOSILNO  & "'"
                                                Dcmd = Dcmd & Chr(13) & " AND a292 = 1 "    '
공실여부(0:공실 , 1:입주)
                                                Dcmd = Dcmd & Chr(13) & " And  (   (a206 <= '" & SD & "'  And a207 >= '" & SD & "' )"
                                                Dcmd = Dcmd & Chr(13) & " or (a206 >= '" & SD & "'  And a206 <= '" & ED & "' and a207 >= '" & ED & "' )   )"
                                                Dcmd = Dcmd & Chr(13) & " )"

                                                cmd = cmd & Dcmd & Chr(13) & " = 0 then 1 else" & Dcmd & " end "
                                                '#############################################
                                           
                                            cmd = cmd & Chr(13) & " as gap"
                                           
                                            If c% = Td Then '
마지막일때
                                                cmd = cmd & Chr(13) & " ) as T1"
                                            Else
                                                cmd = cmd & Chr(13) & " union all "   '
계속 더해준다
                                            End If
                                            
                                   Next c%

 

'-------------------  실행시 작성된 쿼리문 입니다. -------------------------------

 


select sum(gap) from (
 select
    (
     isnull((select (elgum) from DailyTable_Local
     where date = '2017-04-01' and floor = '03' and HOSILNO = '0301'),0)
   -
     isnull((select all(Elgum) from DailyTable_Local
     where date = '2017-03-31' and floor = '03' and HOSILNO = '0301'),0)
     )
   /
   case when 
      (Select COUNT(a202) from a2mst
        where a202 = '03'
        And substring(a203,1,4) = '0301'
        AND a292 = 1
        And  (   (a206 <= '2017-03-31'  And a207 >= '2017-03-31' )
        or (a206 >= '2017-03-31'  And a206 <= '2017-04-01' and a207 >= '2017-04-01' )   )
      )
     = 0
    then 1
    else
     (Select COUNT(a202) from a2mst where
      a202 = '03'
      And substring(a203,1,4) = '0301'
       AND a292 = 1
       And  (   (a206 <= '2017-03-31'  And a207 >= '2017-03-31' )
       or (a206 >= '2017-03-31'  And a206 <= '2017-04-01' and a207 >= '2017-04-01' )   )
     )
     end
 as gap
 union all
  select
    (
     isnull((select (elgum) from DailyTable_Local
     where date = '2017-04-02' and floor = '03' and HOSILNO = '0301'),0)
   -
     isnull((select all(Elgum) from DailyTable_Local
     where date = '2017-04-01' and floor = '03' and HOSILNO = '0301'),0)
     )
   /
   case when 
      (Select COUNT(a202) from a2mst
        where a202 = '03'
        And substring(a203,1,4) = '0301'
        AND a292 = 1
        And  (   (a206 <= '2017-04-01'  And a207 >= '2017-04-01' )
        or (a206 >= '2017-04-01'  And a206 <= '2017-04-02' and a207 >= '2017-04-02' )   )
      )
     = 0
    then 1
    else
     (Select COUNT(a202) from a2mst where
      a202 = '03'
      And substring(a203,1,4) = '0301'
       AND a292 = 1
       And  (   (a206 <= '2017-03-31'  And a207 >= '2017-04-01' )
       or (a206 >= '2017-04-01'  And a206 <= '2017-04-02' and a207 >= '2017-04-02' )   )
     )
     end
 as gap
 

 
 .....
중략
 
 
 union all
 select
    (
     isnull((select (elgum) from DailyTable_Local
     where date = '2017-04-02' and floor = '03' and HOSILNO = '0301'),0)
   -
     isnull((select all(Elgum) from DailyTable_Local
     where date = '2017-04-01' and floor = '03' and HOSILNO = '0301'),0)
     )
   /
   case when 
      (Select COUNT(a202) from a2mst
        where a202 = '03'
        And substring(a203,1,4) = '0301'
        AND a292 = 1
        And  (   (a206 <= '2017-04-29'  And a207 >= '2017-04-29' )
        or (a206 >= '2017-04-29'  And a206 <= '2017-04-30' and a207 >= '2017-04-30' )   )
      )
     = 0
    then 1
    else
     (Select COUNT(a202) from a2mst where
      a202 = '03'
      And substring(a203,1,4) = '0301'
       AND a292 = 1
       And  (   (a206 <= '2017-04-29'  And a207 >= '2017-04-29' )
       or (a206 >= '2017-04-29'  And a206 <= '2017-04-30' and a207 >= '2017-04-30' )   )
     )
     end
 as gap
 ) as T1

 


이런식으로 3천개이상 호실 및 검침 5개 필드 모두를 계산 해야 하는데  한 건당 1초 정도 걸리는것 같습니다.


어떻게 해야 빨라질까요?



No. Subject Author Date Views
» 쿼리 도움 요청드려요. [2] 수수리 2017.08.02 4215
9930 차등백업이나 트랜잭션 로그백업에 사용할 풀백업본 질문입니다. [2] tempest 2017.07.31 5593
9929 비클러스터 인덱스를 잡았는데 클러스터 인덱스를 scan하는 이유는 왜그런가요? [3] 바다낚시꾼 2017.07.26 2495
9928 프로시저 실행 중 table lock 문의 [2] 톡히슈터 2017.07.19 2761
9927 MSSQL 2012 행을 열로 변경 [3] l1j2e3 2017.07.17 2954
9926 my sql 정말 쉬운 질문입니다..(DEC관련..) 유스탁키오관 2017.07.15 2088
9925 로그인 계정에 대한 질문 입니다. icham 2017.07.14 2573
9924 쿼리 질문드립니다 ㅠ [3] 브라미 2017.07.14 2405
9923 DELETE 관련 질문입니다 [2] 코코 2017.07.14 2089
9922 함수 먹힌 column 에 인덱스 추가하는방법? [2] 노잼호 2017.07.13 2615
9921 like에 대해서..? [4] 문상연 2017.07.13 2507
9920 postgresSQL 다중 행 쿼리질문(내용수정) [3] 궁금해서알고싶어요 2017.07.11 1953
9919 프로시저내 트랜젝션 문의드립니다. [2] 톡히슈터 2017.07.10 2523
9918 2005에서 2008로 업그레이드 하려고 합니다. 엘제이 2017.07.10 2587
9917 뜬금없이MSSQL이실행중이나 실행이 안됩니다. [1] 김영환_281520 2017.07.10 4237
9916 SQL 쿼리 질문(동적쿼리) [2] SQL입문도못한자 2017.07.07 2245
9915 SQL Server 에이전트 [2] 기타치는퐝동 2017.07.07 3254
9914 쿼리 질문입니다 [1] 문상연 2017.07.06 3877
9913 mssql sysadmin 권한 관련 확인요청 드립니다. 제비 2017.07.06 3964
9912 쿼리 질문입니다! [2] 파란약 2017.07.05 3135





XE Login