성능 최적화 ¶

저장 프로시저와 저장 함수는 사용자가 직접 작성한 코드이므로, 성능 최적화가 필요할 수 있다. 이 장에서는 저장 프로시저와 저장 함수의 성능을 최적화하기 위한 다양한 가이드를 제공한다.

SQL 질의 최적화 ¶

저장 프로시저와 저장 함수의 가장 흔한 성능 오버헤드는 비효율적인 SQL 구문 수행에서 발생할 수 있다. 내부 루틴에서 사용되는 SQL 문에 대해서 충분히 튜닝하여 다음의 비효율적인 문제를 방지해야 한다.

테이블 풀 스캔: 인덱스를 사용하지 않고 전체 테이블을 스캔하는 경우 성능이 저하될 수 있다.
통신 오버헤드: CUBRID 데이터베이스 서버와 CUBRID PL 서버 간의 통신 오버헤드를 줄이기 위해서는 최소한의 개수의 레코드를 반환하도록 쿼리를 작성해야 한다.
비효율적인 반복문 내의 질의 호출: 저장 프로시저와 저장 함수 내부에서 반복문을 사용할 때, 반복문 내에서 다수의 질의를 호출하지 않고 한번의 질의로 데이터를 가져오는 것을 권장한다.

저장 프로시저와 저장함수에서 사용된 비효율적인 질의는 조인 방식 문서를 참조하여 질의 최적화하기를 권장한다.

질의에서의 저장 함수 호출 최적화 ¶

질의에서 실행하는 저장 함수의 불필요한 반복 호출은 성능을 저하시킬 수 있다. 따라서 저장 함수 호출을 최적화하기 위해 다음과 같은 방법을 고려해야 한다.

불필요한 반복 호출 최소화: 저장 함수의 호출 횟수를 줄이는 것이 성능을 향상시키는 가장 좋은 방법이다.
- 인덱스를 활용하여 저장 함수를 호출하는 레코드의 수를 줄인다.
- 동일한 인수에 대해 반복 호출되지 않도록, 중복되는 데이터를 그룹화 하여 한번의 호출로 처리할 수 있도록 한다.
- 만약 함수의 로직이 결정적이라면 결정적 함수 사용를 활용한 상관 부질의 결과 캐시를 사용하여 성능을 향상시킬 수 있다.
함수의 인수와 반환 값의 크기를 최소화: 저장 함수의 인수와 반환 값을 필요한 값만 반환하도록 설계하여 불필요하게 큰 데이터를 반환하지 않게 한다.

CUBRID에서 기본적으로 제공하는 내장 함수는 (연산자와 함수) CUBRID의 쿼리 실행 동작에 맞춰 최적화되어 효율적으로 동작한다. 반면, 사용자가 작성하는 저장 함수는 그 내부 루틴을 모르는 블랙박스 이므로 내장 함수에 비해 성능이 떨어질 수 있다. 따라서 내장 함수의 단순한 조합으로 구현이 가능한 저장 함수보다 내장 함수를 사용하는 것을 것을 권장한다.

아래는 CONCAT 내장 함수와 동일한 기능을 수행하는 저장 함수의 예시이다. 사용자 저장 함수인 my_concat()보다 내장 함수인 concat()사용 시 성능 이점이 있다.

CREATE OR REPLACE FUNCTION my_concat (a STRING, b STRING) RETURN STRING AS
BEGIN
  RETURN a || b;
END;

SELECT COUNT(*) FROM (SELECT /*+ NO_MERGE */ concat (name, event) FROM athlete);

SELECT COUNT(*) FROM (SELECT /*+ NO_MERGE */ my_concat (name, event) FROM athlete);

-- concat 함수 사용
              count(*)
======================
                  6677

1 row selected. (0.019853 sec) Committed. (0.000000 sec)


-- my_concat 함수 사용
              count(*)
======================
                  6677

1 row selected. (0.302333 sec) Committed. (0.000000 sec)

결정적 함수 사용 ¶

결정적 함수는 동일한 인수에 대해 항상 동일한 결과를 반환하는 함수를 의미한다. 저장 함수가 결정적 함수이면, 저장 함수의 결과를 재사용하여 성능을 향상시킬 수 있다.

저장 함수를 결정적 함수로 만들기 위해 생성 시 CREATE FUNCTION 구문에서 DETERMINISTIC 속성을 지정할 수 있다. 자세한 내용은 CREATE FUNCTION를 참고한다.
DETERMINISTIC 속성을 지정하면 저장 함수는 상관 부질의 결과 캐시의 최적화에 사용될 수 있으며 동일한 인수에 대해 항상 동일한 결과를 반환한다. 상관 부질의 캐시 동작 방식에 대한 자세한 내용은 서브 쿼리 캐시 (correlated)을 참고한다.

다음은 DETERMINISTIC으로 결정적 함수로 생성한 저장 함수의 예시이다. 이 예시에서는 상관 부질의를 사용할 때 결과를 캐시하여 성능을 최적화하는 과정을 보여준다.

CREATE TABLE dummy_tbl (col1 INTEGER);
INSERT INTO dummy_tbl VALUES (1), (2), (1), (2);

CREATE OR REPLACE FUNCTION pl_csql_not_deterministic (n INTEGER) RETURN INTEGER AS
BEGIN
  return n + 1;
END;

CREATE OR REPLACE FUNCTION pl_csql_deterministic (n INTEGER) RETURN INTEGER DETERMINISTIC AS
BEGIN
  return n + 1;
END;

SELECT sp_name, owner, sp_type, is_deterministic from db_stored_procedure;

   sp_name                      owner           sp_type               is_deterministic
========================================================================================
   'pl_csql_not_deterministic'  'DBA'           'FUNCTION'            'NO'
   'pl_csql_deterministic'      'DBA'           'FUNCTION'            'YES'

위 예시에서 pl_csql_not_deterministic 함수는 NOT DETERMINISTIC이므로 상관 부질의에서 쿼리 캐시를 사용하지 않는다. 반면, pl_csql_deterministic 함수는 DETERMINISTIC 키워드가 지정되어 있으므로 상관 부질의에서 쿼리 캐시를 사용하여 성능을 최적화할 수 있다.

;trace on
SELECT (SELECT pl_csql_not_deterministic (t1.col1) FROM dual) AS results FROM dummy_tbl t1;

     results
=============
           2
           3
           2
           3

=== Auto Trace ===
   ...
   Trace Statistics:
     SELECT (time: 4, fetch: 11, fetch_time: 0, ioread: 0)
       FUNC (time: 4, fetch: 2, ioread: 0, calls: 4)
       SCAN (table: dba.dummy_tbl), (heap time: 0, fetch: 5, ioread: 0, readrows: 4, rows: 4)
       SUBQUERY (correlated)
         SELECT (time: 4, fetch: 6, fetch_time: 0, ioread: 0)
           FUNC (time: 4, fetch: 2, ioread: 0, calls: 4)
           SCAN (table: dual), (heap time: 0, fetch: 4, ioread: 0, readrows: 4, rows: 4)

pl_csql_not_deterministic 함수는 NOT DETERMINISTIC 이므로 상관 부질의 결과를 캐시하지 않는다.

;trace on
SELECT (SELECT pl_csql_deterministic (t1.col1) FROM dual) AS results FROM dummy_tbl t1;

     results
=============
           2
           3
           2
           3

=== Auto Trace ===
   ...
   Trace Statistics:
     SELECT (time: 2, fetch: 9, fetch_time: 0, ioread: 0)
       FUNC (time: 2, fetch: 2, ioread: 0, calls: 2)
       SCAN (table: dba.dummy_tbl), (heap time: 0, fetch: 5, ioread: 0, readrows: 4, rows: 4)
       SUBQUERY (correlated)
         SELECT (time: 2, fetch: 4, fetch_time: 0, ioread: 0)
           FUNC (time: 2, fetch: 2, ioread: 0, calls: 2)
           SCAN (table: dual), (heap time: 0, fetch: 2, ioread: 0, readrows: 2, rows: 2)
           SUBQUERY_CACHE (hit: 2, miss: 2, size: 150808, status: enabled)

pl_csql_deterministic 함수의 Trace 결과에서는 SUBQUERY_CACHE 항목이 표시되며(hit: 2, miss: 2, size: 150808, status: enabled), 상단의 SCAN (table: dual) 에서 읽은 레코드 수(readrows)가 NOT DETERMINISTIC 예시와 비교해 감소한 것을 확인할 수 있다.

Warning

저장 프로시저에서는 DETERMINISTIC 속성을 지원하지 않는다.
결정적이지 않은 결과를 반환하는 내부 구현에서 DETERMINISTIC 속성을 사용하는 경우에는 기대한 결과를 반환하지 않을 수 있다.

CREATE TABLE test_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO test_table (name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('Alice'),
('Bob');

CREATE SERIAL my_serial;
CREATE OR REPLACE FUNCTION cnt_name (name VARCHAR) RETURN VARCHAR DETERMINISTIC AS BEGIN RETURN name || my_serial.NEXT_VALUE; END;

SELECT
id,
name,
  (SELECT cnt_name(name) FROM DUAL) AS result
FROM test_table;

        id  name                  result
=========================================================
        1  'Alice'               'Alice1'
        2  'Bob'                 'Bob2'
        3  'Charlie'             'Charlie3'
        4  'Alice'               'Alice1'
        5  'Bob'                 'Bob2

위 예시에서 cnt_name 함수 내부의 my_serial.NEXT_VALUE는 결정적이지 않은 결과를 반환하므로 상관 부질의 캐시에 의해 기대하지 않은 결과를 반환한다. 저장 함수의 구현을 고려하여 DETERMINISTIC 속성을 지정 할 것을 권장한다.

성능 최적화¶

SQL 질의 최적화¶

질의에서의 저장 함수 호출 최적화¶

내장 함수 사용¶

결정적 함수 사용¶

성능 최적화 ¶

SQL 질의 최적화 ¶

질의에서의 저장 함수 호출 최적화 ¶

내장 함수 사용 ¶

결정적 함수 사용 ¶