SQL 퍼포먼스 0 6 1,102

by 김선우 SQL Server [2018.02.15 14:56:59]


안녕하세요

새해 복 많이들 받으세요.

 

다음과 같은 쿼리를 실행하는데 테이블에는 약4억건 정도 들어 있습니다.

SELECT
  AVG([oneday_use_cnt])
 ,AVG([oneday_avg_price])
FROM
(
  SELECT
    [usedate]
   ,COUNT(DISTINCT([idi])) AS [oneday_use_cnt]
   ,SUM([use_price]) / COUNT(DISTINCT([idi])) AS [oneday_avg_price]
  FROM
  (
      SELECT
        A.*
      FROM [s_train_tbl] A INNER JOIN [station_mst] B
      ON A.[station_1] = B.[station_name]
      WHERE
        A.[usedate] BETWEEN '2017-12-01' AND '2018-01-10'
        AND
        A.[process_name] = 'OUT'
        AND
        A.[process_flg] <> '06'
        AND
        A.[use_price] <> 0
      UNION
      SELECT
        A.*
      FROM [s_train_tbl] A INNER JOIN [station_mst] B
      ON A.[station_2] = B.[station_name]
      WHERE
        A.[usedate] BETWEEN '2017-12-01' AND '2018-01-10'
        AND
        A.[process_name] = 'OUT'
        AND
        A.[process_flg] <> '06'
        AND
        A.[use_price] <> 0
  ) C
  GROUP BY
    [usedate]
) D

 

상기 SQL을 SQL Server2012에서 실행했을 때 18분정도 걸리네요.

s_train_tbl에는 usedate에 인덱스가 생성되어 있는 상태입니다.

 

성능개선의 방향이 있는지요?

 

 

by 마농 [2018.02.19 08:19:16]

B 와의 조인이 꼭 필요한 것인지 의문입니다.
B 의 역할이 뭔가요?
B 와의 조인을 제거하고 A만 조회했을 때 결과가 달라지나요?
B 와의 조인이 필요 없다면?
 - 조인을 제거하고 Union 없이 A 한번만 읽으면 됩니다.
B 와의 조인이 필요 하다면?
 - Union 을 제거하고 IN 조건으로 A 한번만 읽으면 됩니다.
 - B.[station_name] IN (A.[station_1], A.[station_2])


by 우리집아찌 [2018.02.19 09:53:57]

마농님이 다 올려주셔서..

일단 s_train_tbl 을 한번만 읽는것이 제일 중요해보이네요.

파티션 테이블 사용하시면 훨신 효율이 좋아질듯합니다.

 

 

 

 


by 김선우 [2018.02.19 11:22:49]

마농님 아찌님 대단히 감사합니다.

B와의 조인이 필요합니다. B.[station_name] IN (A.[station_1],A.[station_2])과 같이 where에 설정하면

레코드가 중복되어 되돌아 오기에 SUM([use_price])가 영향을 받습니다.

      SELECT
        A.*
      FROM [s_train_tbl] A, [station_mst] B
      WHERE
        A.[usedate] BETWEEN '2017-12-01' AND '2018-01-10'
        AND
        A.[process_name] = 'OUT'
        AND
        A.[process_flg] <> '06'
        AND
        A.[use_price] <> 0

        AND
        B.[station_name] IN (A.[station_1], A.[station_2])

따라서 join후에 union으로 해당 레코드의 중복제거를 하고 있습니다.

왜냐하면 station_1와 station_2에는 B.[station_name] 에 동시에 존재하는 레코드가 있을수 있기에요


by 마농 [2018.02.19 11:38:05]

조인이 왜 필요한지? 납득할 만한 설명 가능한가요?
b.station_name 에 존재하지 않는 값이 a.station_1, a.station_2 에 입력 되는 경우가 없다면?
조인은 불필요합니다.


by 마농 [2018.02.19 13:28:08]
-- 조인이 필요 없다면?
SELECT AVG(oneday_use_cnt  ) avg_oneday_use_cnt
     , AVG(oneday_avg_price) avg_oneday_avg_price
  FROM (SELECT a.usedate
             , COUNT(DISTINCT a.idi) AS oneday_use_cnt
             , SUM(a.use_price)
             / COUNT(DISTINCT a.idi) AS oneday_avg_price
          FROM s_train_tbl a
         WHERE a.usedate BETWEEN '2017-12-01' AND '2018-01-10'
           AND a.process_name = 'OUT'
           AND a.process_flg <> '06'
           AND a.use_price   <> 0
           AND ( a.station_1 IS NOT NULL OR
                 a.station_2 IS NOT NULL )
         GROUP BY a.usedate
        ) d
;
-- 조인이 필요 하다면?
-- 1. Join & Distinct --
SELECT AVG(oneday_use_cnt  ) avg_oneday_use_cnt
     , AVG(oneday_avg_price) avg_oneday_avg_price
  FROM (SELECT a.usedate
             , COUNT(DISTINCT a.idi) AS oneday_use_cnt
             , SUM(a.use_price)
             / COUNT(DISTINCT a.idi) AS oneday_avg_price
          FROM (SELECT DISTINCT a.pk
                     , a.usedate
                     , a.idi
                     , a.use_price
                  FROM s_train_tbl a
                 INNER JOIN station_mst b
                    ON b.station_name IN (a.station_1, a.station_2)
                 WHERE a.usedate BETWEEN '2017-12-01' AND '2018-01-10'
                   AND a.process_name = 'OUT'
                   AND a.process_flg <> '06'
                   AND a.use_price   <> 0
                ) a
         GROUP BY a.usedate
        ) d
;
-- 2. Outer Join & Is Not Null --
SELECT AVG(oneday_use_cnt  ) avg_oneday_use_cnt
     , AVG(oneday_avg_price) avg_oneday_avg_price
  FROM (SELECT a.usedate
             , COUNT(DISTINCT a.idi) AS oneday_use_cnt
             , SUM(a.use_price)
             / COUNT(DISTINCT a.idi) AS oneday_avg_price
          FROM s_train_tbl a
          LEFT OUTER JOIN station_mst b
            ON a.station_1 = b.station_name
          LEFT OUTER JOIN station_mst c
            ON a.station_2 = c.station_name
         WHERE a.usedate BETWEEN '2017-12-01' AND '2018-01-10'
           AND a.process_name = 'OUT'
           AND a.process_flg <> '06'
           AND a.use_price   <> 0
           AND ( b.station_name IS NOT NULL OR
                 c.station_name IS NOT NULL )
         GROUP BY a.usedate
        ) d
;
-- 3. Exists --
SELECT AVG(oneday_use_cnt  ) avg_oneday_use_cnt
     , AVG(oneday_avg_price) avg_oneday_avg_price
  FROM (SELECT a.usedate
             , COUNT(DISTINCT a.idi) AS oneday_use_cnt
             , SUM(a.use_price)
             / COUNT(DISTINCT a.idi) AS oneday_avg_price
          FROM s_train_tbl a
         WHERE a.usedate BETWEEN '2017-12-01' AND '2018-01-10'
           AND a.process_name = 'OUT'
           AND a.process_flg <> '06'
           AND a.use_price   <> 0
           AND EXISTS (SELECT 0
                         FROM station_mst b
                        WHERE b.station_name IN (a.station_1, a.station_2)
                       )
         GROUP BY a.usedate
        ) d
;

 


by 김선우 [2018.02.20 13:51:12]

마농님 대단히 감사합니다.

시간을 할애해 주셔서 이렇게 까지 답변을 주시다니 뭐라 감사의 말씀을 드려야할지..

조인은 필요하고 3번의 Exists를 이용한 걸로 실행한 결과 약3분(2분59초)만에 동일한 결과가 돌아오는 것을 확인 하였습니다.

대단히 감사합니다.

아참 그리고 조인이 필요한 이유는 station_1와 station_2에는 station_name에 없는 값이 들어올수 있었기에 그렇습니다.

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입