14강 - 반복문 의존증
SQL은 반복문을 제외하고 만들어진 언어이다. “그게 편하니까”
15강 - 반복계의 공포
성능적으로 떨어진다
반복계의 첫 번째 단점은 성능이다. 반복계로 구현한 코드는 포장계로 구현한 코드에 성능적으로 이길 수 없다.
반복계와 포장계의 처리 시간
반복계의 처리 시간 = 처리 횟수 * 한 회에 걸리는 처리 시간 으로, 처리 횟수에 비례한다.
반복문을 사용하지 않을 경우, 즉 포장계의 경우 인덱스를 사용한 접근이고 실행 계획에 변동이 없다면 대부분 완만한 커브형이다.
SQL 실행의 오버헤드
•
전처리
1.
SQL 구문을 네트워크로 전송
2.
데이터베이스 연결
3.
SQL 구문 파스
4.
SQL 구문의 실행 계획 생성 또는 평가
•
후처리
5.
결과 집합을 네트워크로 전송
오버헤드 중 가장 영향이 큰 것은 3과 4이다. 특히 3. SQL 구문 파스가 가장 까다롭다. 파스는 DBMS마다 하는 방법도 미묘하게 다르고 종류에 따라 느린 부분은 0.1초 ~ 1초 정도 걸린다. 다른 오버헤드는 밀리 초 단위로 영향을 미치는 것에 비해 굉장히 큰 오버헤드이다. 그리고 파스는 데이터베이스가 SQL을 받을 때마다 실행되므로 작은 SQL을 여러 번 반복하는 반복계에서는 더욱 오버헤드가 커진다.
병렬 분산이 힘들다
반복계는 반복 1회마다의 처리를 단순화한 것으로, 리소스를 분산해서 병렬 처리하는 최적화가 불가하다. 즉, 리소스 사용 효율이 나쁘다.
데이터베이스의 진화로 인한 혜택을 받을 수 없다
DBMS는 대규모 데이터를 다루는 복잡한 SQL 구문 처리를 빠르게 하려고 연구한다. 단순하고 가벼운 SQL 구문 처리를 빠르게 하려고 하는게 아니기 때문에, 반복계는 미들웨어 또는 하드웨어의 진화에 따른 혜택을 거의 받을 수 없다.
포장계의 SQL 구문은 튜닝 가능성이 높지만, 반복계는 느린데다가 느린 구문을 튜닝할 가능성도 거의 없다.
반복계를 빠르게 만드는 방법
반복계를 포장계로 다시 작성
즉, 애플리케이션 수정이다. 실제 컷오버(새로운 시스템 가동) 직전의 성능 검증 단계에서 수정하기는 힘들다.
각각의 SQL을 빠르게 수정
반복계에서 사용하는 SQL 구문은 너무 단순해서 튜닝이 힘들다.
다중화 처리
CPU, 디스크와 같은 리소스에 여유가 있고, 처리를 나눌 수 있는 키가 명확하게 정해져 있다면, 처리를 다중화해서 성능을 선형에 가깝게 스케일할 수 있다.
반대로 데이터를 분할할 수 있는 명확한 키가 없거나, 순서가 중요한 처리, 병렬화했을 때 물리 리소스가 부족하다면 이러한 방법은 사용할 수 없다.
반복계로 만든 애플리케이션이 느리다면 대대적인 애플리케이션 수정을 각오해야 한다. 수백 개 정도의 반복은 큰 성능 차이가 없겠지만, 수백만 번의 반복을 한다면 주의해야 한다.
반복계의 장점
실행 계획의 안정성
즉, 해당 실행 계획에 변동 위험이 거의 없다. 옵티마이저가 완벽하지 않은 현재 시점에서 안정적인 성능 확보라는 큰 장점을 가진다.
반면, 포장계는 SQL 구문이 복잡한 만큼 실행 계획의 변동 가능성이 굉장히 크다.
예상 처리 시간의 정밀도
실행 계획이 단순하고 성능이 안정적임에 따라, 예상 처리 시간의 정밀도 또한 높아진다.
[처리 시간] = [한 번의 실행 시간] X [실행 횟수]
Shell
복사
트랙잭션 제어가 편리
트랜잭션의 정밀도를 미세하게 제어할 수 있다. 중간에 오류가 발생하거나 특정 이유로 배치를 잠시 중단할 경우, 중간에 커밋을 했으므로 해당 기점 근처에서 다시 처리를 실행할 수 있다.
포장계의 경우, 갱신 처리 중간에 오류가 발생하면 처리를 처음부터 다시 실행해야 한다.
16강 - SQL에서는 반복을 어떻게 표현할까?
포장계 사용법을 알아보자
반복 대신 CASE 식과 윈도우 함수 이용
절차 지향형 언어에서 반복의 내부에 대부분 IF 조건문을 세트로 사용하는 것처럼, SQL에서도 CASE 식과 윈도우 함수를 함께 사용하는 세트이다.
INSERT INTO "05_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 '-'
END AS var
FROM "05_Sales";
SQL
복사
•
SIGN 함수: 숫자 자료형을 매개변수로 받아 결과값의 부호에 따라 -1, 0, 1을 리턴한다.
•
PARTITION BY company: 회사별로 데이터를 분할한다.
•
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING: 현재 행(레코드)의 1개 이전부터 1개 이전까지의 행, 즉 바로 직전 행만을 대상으로 한다.
ROW BETWEEN의 작동
윈도우 함수로 직전 회사명과 직전 매상 검색
최대 반복 횟수가 정해진 경우
우편번호 테이블
•
가까운 우편번호를 구해보자.
위 테이블의 경우, 최대 반복(또는 분기) 횟수가 7회로 정해져있다.
쿼리
7회 반복 대신 7회 CASE 식 분기를 사용한다. 하지만 순위의 최솟값을 서브쿼리에서 찾으면서 테이블 풀스캔이 2회 발생하므로 윈도우 함수를 이용해 이를 줄여보자.
•
윈도우 함수를 사용한 스캔 횟수 감소
쿼리
윈도우 함수를 사용하면서 테이블 접근이 1회로 감소하는 대신 정렬 비용이 추가된다. 테이블 크기가 크다면 테이블 풀 스캔을 줄이는 것의 효과가 더 크다.
반복 횟수가 정해지지 않은 경우
•
우편번호 이력 테이블
쿼리
가장 오래 전에 살았던 주소는 ‘4130001’이다.
•
우편번호를 키로 삼아 데이터를 연결한 것을 포인터 체인이라 하고, 포인터 체인을 사용하는 테이블을 인접 리스트 모델이라고 한다.
•
가장 오래된 주소를 검색
쿼리
◦
책에서는 인덱스 idx_new_pcode를 사용해 Nested Loops를 사용한다고 했지만, 실행 계획을 살펴본 결과 Hash Join을 하는 것 같다.
중첩 집합 모델
SQL에서 계층 구조를 나타내는 방법
1.
인접 리스트 모델
2.
중첩 집합 모델
3.
경로 열거 모델
갱신이 거의 발생하지 않고 계층 구조가 많을 때 자주 쓰이는 방법이다.
중첩 집합 모델
쿼리
각 레코드의 데이터를 집합으로 보고, 계층 구조를 집합의 중첩 관계로 나타낸다.
•
노드의 좌표 연산
추가되는 노드의 왼쪽 끝 좌표 = (plft * 2 + prgt) / 3
추가되는 노드의 오른쪽 끝 좌표 = (plft + prgt * 2) / 3
SQL
복사
•
가장 외부에 있는 원 찾기
SELECT name, pcode
FROM "05_PostalHistory2" PH1
WHERE name = 'A'
AND NOT EXISTS (SELECT *
FROM "05_PostalHistory2" PH2
WHERE PH2.name = 'A'
AND PH1.lft > PH2.lft);
SQL
복사
외측 테이블(PH1)과 내측 테이블(PH2)을 한 번만 Nested Loops로 결합한다.
책에서는 레코드 수가 적어 테이블 풀스캔이 수행됐다고 하지만, 직접 실행한 결과 인덱스를 사용해 스캔하는 것 같다.
•
중첩 집합의 코드가 재귀보다 빠르다고 단순히 판단할 순 없다. 하지만 이런 엔티티 구조로 해결할 수 있다는 점을 알아두자.
17강 - 바이어스의 공죄
그렇다고 반복계가 무조건 나쁘다는 건 아니다. 하지만 SQL은 집합 지향형 언어이므로 의식적으로 절차 지향형적 사고에서 벗어나야 한다.
실행 계획을 살펴보면 SQL도 결국 내부적으로는 절차 지향적으로 하나의 레코드씩 처리한다는 것을 알 수 있다.