SQL 레벨업 - 5장

14강 반복문 의존증

반복문 의존증 - 문제를 작게 나우어 레코드라는 단위에 이를 때까지 자르고 그러한 레코느에 반복문을 적용해서 문제를 해결하려는 태도

SQL 은 반복문이 없음. 

 

내부적으로는 반복문 사용

하나의 레코드마다 작은 SQL을 사용해 접근함. 비즈니스 로직은 호스트 언어(절차형 언어)에서 반복 처리를 구현하는 것이 일반적임. 나머지는 c#이나 자바에서 구현하면 됨.

 

15강 반복계의 공포

1. 반복계의 단점

같은 기능을 구현한다고 가정하면, 반복계로 구현한 코드는 포장계로 구현한 코드에 성능적으로 이길 수가 없음. 

처리하는 레코드 수가 적을 때는 반복계와 포장계에 큰 차이가 없음.하지만 처리하는 레코드 수가 많아지면 차이가 점점 벌어짐.

 

반복계의 처리기간=  <처리횟수>*<한 회에 걸리는 처리 시간>

- SQL 실행의 오버 헤드

  SQL 처리 외에 다양한 처리 

 

  * 전처리

1. SQL 구문을 네트워크로 전송  
2. 데이터베이스 연결
3. SQL 구문 파스
4. SQL 구문의 실행 계획 생성 또는 평가

 

*후처리

5. 결과 집합을 네트워크로 전송

 

1, 5 번은 SQL을 실행하는 애플리케이션과 데이터베이스가 물리적으로 같은 본체에 있다면 발생하기 않음.

일정 규모 이상의 시스템에서는 애플리케이션 서버와 데이터베이스 서버를 물리적으로 분리해서 사용하르모 SQL 구문, 결과 집합을 네트워크로 전송. 일반적으로 같은 LAN위에 있어서 오버헤드가 일어나지 않음

 

2는 SQL구문을 실행하기 위한 작업 . 오버헤드를 감소시키는 커넥션 풀 기술을 사용하여 오버헤드가 일어나지 않음

 

3, 4 번은  오버헤드 영향이 가장 크다. SQL 파스에서 파스는 종류에 따라 다르지만 0.1초 ~ 1초 정도 걸린다. 다른 오버헤드들은 밀리초인 반면에 굉장히 큰 오버헤드이다.

- 병렬 분산이 힘들다

반복계는 반복 1회마다 처리를 단순화

따라서 리소스를 분산해 병렬 처리하는 최적화가 안된다. CPU의 멀티 코어로 분산 처리를 할 수 없고, 저장소의 분산 효율이 낮다. 데이터베이스 서버 저장소는 대부분 RAID로 구성되어 I/O 부하를 분산할 수 있지만, 반복계는 SQL 구문이 대부분 단순해서 1회의 SQL 구문이 접근하는 데이터 양이 적다. 따라서 I/O를 병렬화 하기 힘들다.

 

-데이터 베이스의 진화로 인한 혜택을 받을 수 없다

반복계는 단지 느리기만 한 것이 아니라 느린 구문을 튜닝할 수 있는 가능성도 거의 없다 라는게 가장 크고 무서운 단점

 

2. 반복계를 빠르게 만드는 방법

-반복계를 포장계로 다시 작성 : 애플리케이션 수정

-각각의 SQL을 빠르게 수정 

-다중화 처리

 

3. 반복계의 장점

-실행 계획의 안정성

-예상 처리 시간의 정밀도 <처리시간> = <한 번의 실행 시간> X <실행 횟수>

-트랜잭션 제어가 편리

16강 SQL에서는 반복을 어떻게 표현할까?

1. 포인트는 CASE 식과 윈도우 함수

CASE식은 절차 지향형 언어에서 말하는 IF-THEN-ELSE 구문에 대응하는 기능

INSERT INTO Sales2
SELECT company, year, sale,
	CASE SIGN(sale - MAX(sale)
    		OVER(PARTITION BY company
            		ORDER BY year
                    ROWS BETWEEN 1 PRECEDING
                    	AND 1 PRECEDING) )
   WHEN 0 THEN '='
   WHEN 1 THEN '+'
   WHEN -1 THEN '-'
   ELSE NULL END AS var
FROM Sales;

SIGN 함수 : 숫자 자료형을 매개변수로 받아 음수라면 -1, 양수라면 1,0 이라면 0을 리턴하는 함수

 

2.최대 반복 횟수가 정해진 경우

CASE 식과 윈도우 함수 사용

 

우편번호 찾기.

-> 일단 우편번호 '4130033'이 테이블에 있는지를 찾기

-> 그런데 테이블에 없다면 이어서 '413003*'가 있는지를 찾기.

-> 마찬가지로 없다면 이어서 '41300**'가 있는지를 찾기

-> 3개의 우편번호가 일치하므로, 이것을 출력

 

--> 테이블의 레코드 수가 많아질수록 성능 측면에서 점점 악화 됨. 스캔횟수를 줄이려면 윈도우 함수를 사용해야 함

SELECT pcode,
       district_name
FROM (SELECT pcode,
             district_name,
             CASE WHEN pcode = '4130033' THEN 0
                  WHEN pcode LIKE '413003%' THEN 1
                  WHEN pcode LIKE '41300%' THEN 2
                  WHEN pcode LIKE '4130%' THEN 3
                  WHEN pcode LIKE '413%' THEN 4
                  WHEN pcode LIKE '41%' THEN 5
                  WHEN pcode LIKE '4%' THEN 6
                  ELSE NULL END AS hit_code,
             MIN(CASE WHEN pcode='4130033' THEN 0
                      WHEN pcode LIKE '413003%' THEN 1
                      WHEN pcode LIKE '41300%' THEN 2
                      WHEN pcode LIKE '4130%' THEN 3
                      WHEN pcode LIKE '413%' THEN 4
                      WHEN pcode LIKE '41%' THEN 5
                      WHEN pcode LIKE '4%' THEN 6
                      ELSE NULL END)
             OVER(ORDER BY CASE WHEN pcode = '4130033' THEN 0
                                WHEN pcode LIKE '413003%' THEN 1
                                WHEN pcode LIKE '41300%' THEN 2
                                WHEN pcode LIKE '4130%' THEN 3
                                WHEN pcode LIKE '413%' THEN 4
                                WHEN pcode LIKE '41%' THEN 5
                                WHEN pcode LIKE '4%' THEN 6
                                ELSE NULL END) AS min_code
             FROM PostalCode) Foo
 WHERE hit_code = min_code;

3. 반복 횟수가 정해지지 않은 경우

- 인접 리스트 모델과 재귀 쿼리

포인트 체인: 어떤 값을 키로 삼아 데이터를 줄줄이 연결한 것 , 계층 구조를 표현하는 고전적인 방법

인접리스트 모델 : 포인터 체인을 사용하는 PostalHistory 같은 테이블 형식

ITH RECURSIVE Explosion (name, pcdoe, new_pcode, depth)
AS
(SELECT name, pcode, new_pcode, 1
	FROM PostalHistory
  WHERE name = 'A'
  	AND new_pcode IS NULL 
UNION
SELECT Child.name, Child.pcode, Child.new_pcode, depth + 1
	FROM Explosion AS Parent, PostalHistory AS Child
  WHERE Parent.pcode = Child.new_pcode
  	AND Parent.name = Child.name)

SELECT name, pcode, new_pcode
	FROM Explosion
  WHERE depth = (SELECT MAX(depth) FROM Explostion);

- 중첩 집합 모델

SQL 에서 계층 구조를 나타내는 방법

 1. 인접 리스트 모델 : RDB가 탄생하기 이전부터 계층 구조를 표현하는 전통적인 방법으로 사용

 2. 중첩 집합 모델 : 각 레코드의 데이터를 집합으로 보고 계층 구조를 집합의 중첩 관계로 나타냄

 3. 경로 열거 모델 : 갱신이 거의 발생하지 않은 경우에 유용하게 쓰임