SQL의 반복문
SQL은 일부러 반복문을 언어 설계에서 제외했습니다.
일반적으로 SQL에서 반복문이 지원되지 않아, 다음과 같은 방식으로 프로그래밍을 하는 경우가 많습니다.
•
레코드에 하나씩 접근하는 SELECT 구문을 반복해서 사용합니다.
•
호스트 언어에서 반복문을 처리한 뒤 테이블을 갱신합니다.
그러나, 반복문이 없을 경우 생기는 장점들이 많습니다.
반복계의 단점
1. 성능
반복계로 구현한 코드는 포장계(반복을 사용하지 않은)로 구현한 코드에 비해 성능적으로 완벽하게 뒤쳐집니다.
레코드 수가 적을 때에는 반복계가 빠른 경우도 있지만, 레코드 수가 많아질수록 성능 차이가 더 크게 벌어집니다.
SQL 실행의 오버헤드
•
전처리
1.
SQL 구문을 네트워크로 전송
2.
데이터베이스 연결
3.
SQL 구문 파싱
4.
SQL 구문의 실행 계획 생성 / 평가
•
후처리
1.
결과 집합을 네트워크로 전송
1번과 5번의 과정은 내부의 동일 LAN 위에 있으므로 전송 속도 자체는 거의 밀리초입니다.
→ 오버헤드가 딱히 발생하지 않습니다.
2번의 경우 커넥션 풀이라는 기술을 사용하여 거의 문제가 되지 않습니다.
문제가 되는 경우는 3번과 4번 과정입니다.
파싱은 SQL을 받을 때마다 실행되므로 작은 SQL을 여러 번 반복하는 반복계에서는 오버헤드가 높아질 수밖에 없습니다.
2. 병렬 분산의 어려움
반복계는 반복 1회마다의 처리를 굉장히 단순화합니다. 따라서 리소스를 분산해서 병렬 처리하는 최적화가 되지 않습니다. 데이터베이스는 대부분 RAID 디스크로 구성되어 I/O 부하를 분산화할 수 있게 되어 있지만, 반복계에서 실행하는 SQL 구문은 너무 단순해 1회의 접근하는 데이터양이 적습니다.
I/O를 병렬화하기 힘듭니다.
3. 데이터베이스 업그레이드
DBMS의 버전이 오를수록 옵티마이저는 보다 효율적으로 실행 계획을 세우며, 데이터에 고속으로 접근할 수 있는 아키텍처를 구현합니다.
그러나, 이러한 업데이트의 중심은 대규모 데이터를 다루는 복잡한 SQL구문을 빠르게 만들기 위해서입니다.
반복계는 미들웨어의 진화의 혜택을 거의 받을 수 없습니다.
포장계가 반복계보다 성능이 좋다는 가정은 포장계의 SQL이 충분히 튜닝되어 있다는 가정이 있어야 합니다.
일반적으로 굉장히 단순한 반복계의 SQL은 튜닝 가능성이 거의 없지만, 포장계의 SQL은 매우 복잡하기 때문에 튜닝의 가능성이 매우 큽니다. (이것은 포장계의 단점이기도 함 - 복잡하니까)
반복계의 장점
1. 실행 계획의 안정성
반복문의 SQL 구문이 지나치게 단순하기 때문에 실행 계획도 매우 간단합니다.
따라서 실행 계획이 거의 변하지 않는 것을 알 수 있습니다.
실제로 운영 중에 갑작스럽게 실행 계획이 변경되어 느려지는 현상이 발생하지 않습니다.
•
이는 포장계의 단점이기도 합니다.
2. 트랜잭션 제어의 편리함
트랜잭션의 정밀도를 세밀하게 제어할 수 있습니다.
반복문에서 갱신 처리를 수행하고, 특정 반복 횟수마다 커밋한다고 가정했을 때, 중간에 오류가 발생하더라도 중간 커밋을 활용하여 해당 지점 근처부터 다시 처리할 수 있습니다.
SQL에서의 반복 표현
SQL에서 반복을 대신하는 수단은 CASE 식과 윈도우 함수이다.
•
정확하게 말하면 CASE 식은 절차 지향형 언어에서 말하는 IF-THEN-ELSE 구문에 대응하는 기능입니다.
•
SQL에서 CASE 식과 윈도우 함수를 함께 사용하면 다음과 같습니다.
•
작년도와 매출액을 비교해 +, -, = 을 표시하는 SQL
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;
SQL
복사
SIGN 함수 : 음수일 경우 -1, 0일 경우 0을 반환
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING: 현재 레코드에서 1개 이전부터 1개 이전까지의 범위를 지정합니다.
같은 회사의 직전 매상을 리턴하고, 차이를 계산한 뒤 SIGN 함수를 사용하여 출력합니다.
반복 횟수가 정해지지 않은 경우
•
인접 리스트 모델과 재귀 쿼리
이사를 갈 때마다 필드를 추가해야 하는 경우가 있다고 가정합니다.
가장 오래된 주소를 찾고 싶습니다.
우편번호를 키로 사용하여 이전 주소 데이터를 연결하여 저장합니다.
•
이를 포인터 체인이라고 합니다.
•
포인터 체인을 사용하는 테이블 형식을 '인접 리스트 모델'이라고 합니다.
SQL에서 계층 구조를 찾는 방법 중 하나는 재귀 공통 테이블 식을 사용하는 방법입니다.
WITH 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 구문
SELECT name, pcode, new_pcode
FROM Explosion
WHERE depth = (SELECT MAX(depth)
FROM Explostion)
;
SQL
복사
RECURSIVE는 재귀라는 것을 알고 있지만, 이해하기 어려워서 다시 공부했습니다
WITH RECURSIVE [VIEWNAME]
AS
초기식
UNION
SELECT ~ FROM ~
WHERE RECURSIVE 종료 조건
SQL
복사
즉, Parent의 자식이 있는지 검사하여 있다면 depth를 +1 증가시킵니다.
depth의 최댓값을 구하면, 가장 오래 전에 살았던 곳입니다.
실행 계획 (PostgreSQL)
'Recursive Union'은 재귀 연산을 의미하며, 여러 번의 이동에도 대응할 수 있어 매우 유연한 쿼리입니다.
'WorkTable'은 Explosion 뷰에 여러 번 접근하기 때문에 임시 테이블로 생성됩니다.
이렇게 생성된 임시 테이블과 원래의 PostalHistory 테이블은 인덱스를 사용하여 Nested Loops(이중 for문)를 수행하므로 꽤 효율적인 계획입니다.
RDB에서 고성능을 실현하고 싶다면, 절차 지향적인 바이어스를 제거하고 자유로워져야 합니다. 동시에 반복계와 포장계의 장점과 단점을 고려하고, 어느 것을 채택할지 냉정하게 판단해야 합니다. SQL이 가진 강력한 도구와 튜닝 방법을 활용하려면 반드시 집합 지향적인 사고방식을 가져와야 합니다.