[SQL 레벨업] 15강 반복계의 공포

15강 | 반복계의 공포

정리

• SQL은 반복문이 없다.
  • SQL은 처음부터 반복문을 지양하며 만든 언어이며, 그 이유는 최종 사용자의 생산성 때문이다.
• 반복계 코드
  • 테이블 단위를 레코드 단위로 쪼개어, 나눠진 레코드를 하나씩 반복문에 적용하는 방법
     

CREATE OR REPLACE PROCEDURE PROC_INSERT_VAR
IS
	/* 커서 선언 */
	CURSOR c_sales IS
		SELECT company, year, sale 
        	FROM Sales 
        	ORDER BY company, year;
	
	/* 레코드 타입 선언 */
	rec_sales c_sales %ROWTYPE;

	/* 카운터 */
	i_pre_sale INTEGER :=0;
	c_company CHAR(1) :='*';
	c_var CHAR(1) :='*';

BEGIN

OPEN c_sales;
	LOOP
	/* 레코드를 패치해서 변수에 대입 */
	fetch c_sales into rec_sales;
	/* 레코드가 없다면 반복을 종료 */
	exit when c_sales%notfound;

	IF (c_company = rec_sales.company) THEN
		/* 직전 레코드가 같은 회사의 레코드 일 때 */
		/* 직전 레코드와 매상을 비교 */
		IF (i_pre_sale < rec_sales.sale) THEN
			c_var :='+';
		ELSEIF(i_pre_sale > rec_sales.sale) THEN
			c_var :='-';
		ELSE
			c_var :='=';
		END IF;
	
	ELSE
		c_var :=NULL;
	
	END IF;
	
	/* 등록 대상이 테이블에 테이블을 등록 */
	INSERT INTO Sales2 (company, year, sale, var)
	VALUES (rec_sales.company, rec_sales.year, rec_sales.sale, c_var);
	
	c_company := rec_sales.company;
	i_pre_sale := rec_sales.sale;
	
	END LOOP;
	
	CLOSE c_sales;
	commit;

END;

반복계 코드 예시

Sales 테이블의 데이터를 기반으로 sales2 테이블에 데이터를 삽입하는 역할을 하는 프로시저

• 커서 선언
  • 커서를 선언하고, 이용하여 Sales 테이블의 데이터를 순회한다.
  • ‘SELECT company, year, sale FROM Sales ORDER BY company, year’ 구문을 통해 c_sales 커서를 초기화한다.,
• 레코드 타입 선언
  • c_sales 커서의 레코드 구조체를 나타내는 rec_sales 라는 레코드 타입을 선언한다.
• 변수 및 상수 선언
  • i_pre_sale : 이전 매상 값을 저장하는 변수
  • c_company : 이전 레코드의 회사를 나타내는 문자 변수
  • c_var : 레코드와 이전 레코드의 매상을 비교한 결과를 저장하는 문자 변수
• 커서 열기 (OPEN c_sales)
  • c_sales 커서를 열어서 데이터를 가져오기 위해 준비
• 루프 (LOOP)
  • c_sales 커서로부터 데이터를 한행씩 가져와서 rec_sales 레코드에 저장한다.
  • 이전 레코드와 현재 레코드의 회사를 비교하여 매상에 대한 비교 결과(c_var)를 설정합니다.
  • Sales2 테이블에 현재 레코드의 데이터를 삽입합니다.
• 커밋 (commit)
• 커서 닫기 (CLOSE c_sales)

• 포장계 코드
  • 테이블의 여러 행을 한번에 처리하는 방법

 

1. 반복계 단점

포장계에 비해 성능이 좋지 못하다.

 

 

• 성능시간
  • 반복계 성능 시간 = 처리횟수 * 한회에 걸리는 처리 시간
  • 포장계 = 대부분 완만한 커브를 그린다.

성능 저하의 주요 원인

 

1) SQL 실행의 오버헤드

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

- 후처리
5. 결과 집합을 네트워크로 전송

 

3번과 4번이 오버헤드가 가장 큰 영향을 미친다.

구문 파스는 데이터베이스가 SQL 받을 때 마다 실행되므로 여러번 반복되는 반복계에서는 오버헤드가 높아질 수 밖에 없다.

 

2) 병렬 분산이 힘들다.

반복계는 리소스 분산,병렬처리가 불가능 해 최적화가 힘들다.

DB 서버 저장소는 대부분 RAID 디스크로 구성되어 I/O 부하를 분산시킬 수 있으나 반복계 SQL 구문은 1회 구문이 접근하는 데이터양이 적기때문에 병렬화가 힘들다.

 

3) 데이터베이스 진화로 인한 혜택을 받을 수 없다.

요즘 데이터베이스는 많이 진화하여 대규모 데이터 처리를 위해 SQL 구문을 빠르게 처리하는 성능이 높아졌다. 그러나 반복계는 단순한 SQL 구문을 여러번 이용하는 것이기 때문에 성능 사용률이 떨어진다.

 

4) 튜닝 가능성이 거의 없다.

 

 

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

1) 다중화 처리로 성능을 선형에 가깝게 스케일 가능하다.

CPU, 디스크 등 리소스가 여유롭고 처리를 나눌 수 있는 키가 명확할 때 처리 다중화가 가능하다.

 

 

3. 반복계 장점

1) 실행 계획의 안정성

단순해서 실행 계획 변동 위험이 거의 없다.

 

2) 예상 처리 시간의 정밀도

처리 시간 = 한번 실행시간 * 실행 횟수

 

3) 트랜잭션의 정밀도를 미세하게 제어