안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번주 뉴스레터는 효과적인 SQL 작성, 즉 SQL을 더 빠르게 실행할 수 있는 작성방법에
대해 살펴보도록 하겠습니다.
효과적으로 SQL을 작성하게 되면, 응용프로그램의 성능을 향상시킬 수 있으므로,
최소의 비용으로 시스템의 안정성과 효율성을 높일 수 있게됩니다.
효과적인 SQL 작성 가이드
- 실제로 필요한 컬럼들만 가져오도록 SQL을 작성하십시오.
select * 문은 사용하지 마십시오. 컬럼 수가 많아질수록 CPU 사용률이 높아집니다.
- WHERE 절을 적절히 사용하여 필요한 데이터를 적절히 제한하도록 합니다.
- Host 변수를 사용할 경우 테이블 컬럼 Type과 길이와 동일하게 정의하여 사용하십시오.
만약 그렇지 않다면, DB2가 인덱스를 사용할 수 없어서 성능에 부정적인 영향을 미치게 됩니다.
예를 들어, CHAR(6)으로 정의된 컬럼을 CHAR(4)나 CHAR(7) 인 필드와 비교하게 되면
데이터 변환이 일어나고 DB2가 인덱스를 사용하는 대신 데이터 스캔을 하게되어 성능이 저하됩니다.
- SQL 문장에 Order by 절을 사용하게 되면 모든 cursor 프로세싱을 수행해야 하므로,
꼭 필요한 컬럼만 Order by 를 사용하십시오.
- Select 결과 리턴되는 행의 수가 한 건일 경우에는 SELECT INTO 절을 사용하는 것이
Cursor를 사용하는 것보다 성능에 좋습니다. 하지만, 결과 데이터를 변경해야 하는
경우에는 FOR UPDATE OF 를 이용하는 Cursor로 선언하는 것이 갱신되는 Row에
Exclusivce Lock을 걸어 데이터 무결성을 보장해주므로 더 좋습니다.
- 조회 전용일 경우에는 Select 문에 FOR FETCH ONLY 절을 사용합니다.
- DISTINCT의 사용을 피합니다. Distinct 를 사용하면 Sort가 발생하므로, 중복에
별 문제가 없다면 사용하지 않도록 합니다.
- JOIN 은 특별한 상황을 제외하면 5개 이내로 제한하는 것이 좋습니다.
만약 그 이상 테이블을 사용해야하면, Subquery 대신에 Join 을 사용하십시오.
Optimizer 가 Join을 사용할 때 데이터 접근 방법을 더 많이 가지고 있기 때문입니다.
그러나 예외도 있으므로 쿼리별로 테스트가 필요합니다.
- ORDER BY, GROUP BY, DISTINCT, UNION 을 사용할 경우 해당 컬럼에 Index 를 추가하는 것이 좋습니다.
- 대안이 있다면 SQL 조건 내에서 SUBSTR 함수의 사용을 피하는 것이 좋습니다.
예를 들어 다음과 같은 경우 substr 대신 like를 사용하십시오.
like를 사용하는 경우 index 를 사용하게 됩니다.
예) select custinfo from sales where substr(serial,1,4) = ‘1333’
select custinfo from sales where serial like ‘1333%’
|
- SQL의 기존 함수(AVG,COUNT,MAX,MIN,SUM)를 사용하십시오.
SQL의 기존 함수를 사용하는 것이 이런 함수를 응용프로그램으로 만들어 사용하는 것보다 더 효과적입니다.
- 대안이 있다면 SQL의 Scalar 함수의 사용을 피합니다.
데이터 형태의 변경, 문자열의 조작, 그리고 날짜/시간의 변경들을 위해 사용되는
SQL Scalar 함수(INTEGER, DECIMAL, HEX, SUBST등)는 사용을 피하십시오.
- 자주 갱신되지 않는 컬럼들은 테이블의 초입에 두고, 반면에 자주 갱신되는 컬럼들은
테이블 끝에 둡니다. 이것은 DB2 LOG에 어떻게 DB2가 변화한 것을 기록하는지를
연관지어서 효과적으로 고려한 것입니다.
- 일반적으로, VARCHAR 컬럼은 테이블 끝에 둡니다.
만약 자주 갱신되는 컬럼이 있다면 자주 갱신되는 컬럼을 VARCHAR 컬럼보다
우선적으로 테이블의 끝에 둡니다.
- <=, >= 보다는 BETWEEN을 사용합니다. BETWEEN은 Optimizer에게 더 효과적인
접근 경로를 선택하게 해줍니다.
- 배치 프로그램 수행시, 처리되는 input data의 순서를 Access 하는 테이블의
데이터 순서와 동일하게 맞추어 주십시오 이렇게 하면 DB2 가 sequential prefetch를
수행하기 때문에 성능을 높일 수 있습니다.
- multicolumn index의 경우 Index의 Matching 컬럼수가 많도록 쿼리를 작성합니다.
- Commit을 자주하면, 수행시간에 영향을 미칩니다. Commit을 적절히 조절하십시오.
- LIKE 대신 IN을 사용합니다. 만약 일어날 수 있는 특정 수만 알고 있다면, LIKE를 사용하는
것보다 IN에 목록을 나열하는 것이 더 효과적입니다.
예를 들어, 만약 Last Name이 M으로 시작하는 모든 근로자를 찾는다면,
LIKE ‘m%’ 대신에 BETWEEN ‘maaaaaaaaaaaaaa’ and ‘mzzzzzzzzzzzzzz’를 사용하십시오.
- NOT의 사용(EXISTS는 제외)을 피합니다. NOT은 단지 매우 복잡한 조건에서만 선택적으로 사용해야 합니다.
- 만약 응용프로그램 내의 컬럼 값을 이미 알고 있다면, SELECT 절에 컬럼의 사용을 피합니다.
|
예) select c1,c2,c3 into :c1, :c2,:c3 from t1 where c1 = :c1
|
- SELECT 문의 결과 중복된 행이 없다고 알고 있거나, 중복된 데이터가 있어도 된다면 ,
UNION 대신 UNION ALL을 사용합니다.
- 부정의 논리로 Subquery를 작성할 때, SQL문의 효율성을 높이기 위하여 가능하면
NOT IN 대신 NOT EXISTS를 사용합니다. NOT EXISTS를 사용할 때, DB2는 단지
존재하지 않는 것만 확인하지만 NOT IN조건을 포함하면, DB2는 완벽한 결과셋을
구성해서 조건을 비교하게 됩니다.
- JOIN은 Subquery 보다 더 효율적입니다.
예) select eno,ename from emp , proj
where workdept = deptno and empno = respemp (O)
select eno, ename from emp
where workdept = ( select deptno from proj
where respemp = emp.empno) (X)
|
XpertMon for DB2 UDB V2
DB 사업부
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail : hjlee@iteg.co.kr
URL : http://iteg.co.kr
|
|
|
