Framework - 커서(Anonymous)

public void testStatic() throws Exception {     DBManager dbm = new MSSQLPoolDBManager("com.microsoft.jdbc.sqlserver.SQLServerDriver",            "jdbc:microsoft:sqlserver://novision:1433;DatabaseName=test", "bleu", "bleu") ;     Connection conn = null;     ResultSet rs = null;     PreparedStatement pstmt = null;     try {       conn = dbm.getConnection();        pstmt = conn.prepareStatement(getProcSQL());       rs = pstmt.executeQuery();        populate(rs);      } catch (SQLException ex) {       throw ex;     } finally {       closeWithoutException(rs);       closeWithoutException(pstmt);       dbm.freeConnection(conn);      }   }   private String getProcSQL() {     return "select custNo, fixLength from customer_tblc";   }   private void populate(ResultSet rows) throws SQLException {     int i = 0;     while (rows.next() && i++ < 10) {       System.out.print(rows.getString(1));     }   }

위 코드가 customer_tblc에서 10건을 읽어서 화면에 쓰는 프로그램이 아니라는 얘기는 이미 한적이 있다. 위 프로그램은 실제로는 customer_tblc를 모두 읽어서 client에게 Network를 통해 모두 전송해주고 client는 단지 10건만 필요한데도 불구하고 모두 받아서 버려야 하는 바보같은 코드이다.

만약 sensitive 방식을 사용하면 이번에는 customer_tblc를 모두 읽어서 데이타 베이스의 temporary space에 Set 형태로 저장해서 요청시마다 한건씩 건네주는 Static 커서를 사용한다. 나중에 다시 이야기 하겠지만 Static 커서는 특정 상황에 따라 유용성이 있을 수도 있지만 위의 상황에는 적합하지 않다. 

원하는 것은 화면에 찍을 "일부"만 읽어서 "일부"만 전송이 되는 방식이다. 이렇게 하기 위해서는 전혀 생뚱맞을지 모르지만 위 코드의 경우 JDBC 문자열을 바꿔야 한다.

jdbc:microsoft:sqlserver://novision:1433;DatabaseName=test;selectMethod=cursor 와 같이 JDBC URL에 selectMethod=cursor 이란 문자열을 추가해야 한다. 이 방식의 커서에 대한 명칭은 디비마다 제멋대로 이므로 이 글에서는 Anonymous 커서라고 부르자. 원칙적으로 selectMethod=cursor는 트랜잭션에 관련한 프로퍼티이지만 row level 락의 문제로 커서에도 영향을 준다.

Anonymous 커서 동작은 위와 같다.(오라클의 아키텍쳐 이름이지만 MSSQL도 동작방식은 같다. 다른 DB는 아키텍쳐 동작에 관한 문서가 없다시피 해서 단지 추측만 할 수 있다.)

1. 쿼리를 실행한다.
2. 쿼리의 결과에 관계하는 "일부 블럭"이 Buffer Cache 메모리에 올라간다.(MSSQL은 Page)
   FetchSize만큼의 Row만 Set형태로 임시 공간에 PreLoad하고 커서는 첫번째 row를 가르킨다.
3. Client가 request fetch를 한다.
4. Server는 현재 Temporary에 있는 일부 Set의 현재 row를 리턴한다.
5. Client는 confirm message를 발송한다.
3-5 동작을 반복한다. 만약 fetchSize 이상의 row를 요청하면 2번이 한번 더 실행된다. 즉 만약 FetchSize가 10이라면 2, (3, 4, 5) * 10, 2, (3, 4, 5) * 10, 2 ... 와 같은 방식으로 실행된다.
6. 커넥션을 닫기 전에 임시 저장공간의 Set과 SQL AREA의 cursor 정보를 정리한다.


단지 selectMethod=cursor만 추가하여 첫번째의 글과 같은 방식으로 테스트를 해보면
connection : 2360
execute : 78 (2438 - 2360)
populate : 31 (2469 - 2438)
close : 0 (2469 - 2469)
가 나온다.

이전의 forward only cursor의 실행결과는 아래와 같았다.
connection : 2375
execute : 47 (2422-2375)
populate : 31 (2453-2422)
close : 6616 (9069-2453) <- 모든 데이타를 읽고 전송하느라 close 하는데에 오래 걸린다.


연결하는데 만드는데 2.3초가 걸렸지만 풀링을 만드는건 단 한번만 하는 동작이므로 역시 이는 신경쓸 필요 없다. 주의해서 볼것은 실행이 0.078초 그리고 close까지 완료하는데 총 0.1초정도 걸렸다. 데이타량도 약 58만건으로 변동이 없고 갑자기 DB가 눈부시게 빨라져서도 아니고 단지 부분범위 처리 Select를 했기 때문이다. 그리고 일부만 Client에게 전송하였다. 단지 커서만 바꿨을 뿐인데 처음의 default cursor와(forward only) 7초와 0.1초의 약 70배 차이가 발생한다. 이는 58만건일때의 차이이므로 만약 데이타가 더 많다면 그 차이가 더 벌어진다. 

네트워크 패킷 캡쳐 화면을 보면 이 사실은 더욱 명확해진다. 이전의 forwardOnly는 모든 row의 데이타를 받았기 때문에 패킷의 수가 아주 많았지만 Anonymous 방식은 단지 수백개의 패킷만을 받았다. 3개식 순서대로 나타나는 것은 fetch와 send가 동기적임을 뜻한다. request Fetch by Client -> Send Packet By Server -> confirm message by Client 이 3가지 동작이 반복해서 일어난다.

--
잠깐 상관없는 얘기를 하자면 성능과 관련해서 테스트를 할때는 항상 총알을 2발이상 쏴야 한다. 암살자가 저격을 할때 영화나 만화에서처럼 이마를 겨냥해서 딱 한방을 쏘고 폼나게 돌아서는 짓은 아주 바보같은 짓이다. 저격 포인트라는 것 자체가 저격하기 쉽지 않다는 걸 뜻하고 정말 한방에 목표를 죽이는게 쉽지 않기 때문에 총은 2발 이상을 쏘는게 좋다. 즉사가 아니더라도 과도한 피출혈로도 죽일 수 있기 때문에 가능하면 여러발을 쏘는게 좋다. 테스트를 할 때도 마찬가지이다. 위 테스트들은 한번 한게 아니라 기관총을 쏘듯 수천번을 테스트 한 결과이고 평균치는 거의 변함이 없다.
--


마땅한 명칭이 없어서 Anonymous라고 지칭했지만 명칭만 제멋대로 인게 아니라 이 커서를 사용하기 위한 방법도 제 멋대로다. selectMethod=cursor는 마법의 지팡이가 아니기 때문에 JDBC URL에 단순히 이 문장을 써준다고 빨라지는 것은 아니다. 많은 상황에서의 단지 하나의 예일 뿐이다. (예컨데 ResultSet.TYPE_SCROLL_SENSITIVE 방식을 사용하게 되면 Static 커서를 사용하게 되므로 영향을 미치지 않는다. )

MSSQL2000은 연결 문자열을 바꿔야 하지만 이를테면 오라클은 이 글의 Anonymous 방식이 default이다. 어떤 DB는 SQL을 바꿔야 하고 또 어떤 드라이버를 사용하면 안되고 다른 드라이버를 사용해야 할때도 있었다. MSSQL도 2005버전이상이 되면 selectMethod=cursor로 되지 않는다. DB마다 버전마다 드라이버마다 다르기 때문에 매 프로젝트마다 이를 테스트 해보고 방법을 찾아야 했다. 그리고 일부 디비는 이런 처리방식이 애초에 존재하지 않는 경우도 있었다. (일단 DB가 부분범위 처리를 지원하지 않으면 안되고 드라이버에서 부분범위 처리를 구현했어야 한다.)

위 방식의 장점은
1. 원하는 만큼만 select를 해서 원하는 만큼만 전송이 이루어지기 때문에 상대적으로 매우 빠르게 동작한다.
실제 필요한 부분이 쿼리셋의 단지 부분일 뿐이라면 그  결과에 대해 모두 알 필요는 없다는 것이 위 방식의 핵심이고 Anonymous cursor를 사용하는 이유가 된다.



단점은

1. 매 fetch시마다 3번의 Network 통신이 발생한다.
이 문제의 해결은 다음글에 적기로 하자.



2. 항상 쓸 수 있는 방식이 아니다. 쿼리의 종류에 영향을 받는다.

항상 쓸수 있는 방식이 아니라는 얘기는 이렇다. 이를테면 부서별 사원수를 알고 싶다고 하자.

select deptno, count(*) from emp group by deptno 와 같은 쿼리는
흔히 전체범위 쿼리라고 불리는데 전체를 읽지 않고서는 제대로 된 결과를 얻을 수 없다. 그래서 위와 같은 쿼리는 사용자의 선택에 상관없이 Static 방식의 커서가 사용된다. 그러나 조금 생각을 달리 해볼수도 있다.

이를테면 부서가 아주 세분화되서 수천개에 이르고 역시 화면에 보고자 하는 것은 그 일부분이라고 하자. 그럴경우 내가 보고자 하는 부분은 일부 부서이지만 쿼리는 전체 부서를 대상으로 실행이 된다. (전체범위 쿼리의 대표적인 예는 group by, order by, 집계함수 등이 있다.)

그럴경우
select deptno, (select count(*) from emp where deptno = x1.deptno ) from dept x1
와 같이 부분범위 처리가 가능한 쿼리로 바꾸고 Anonymous 커서 방식을 사용할 수도 있다. 요는 응용의 문제이므로 이 쿼리는 전체범위 처리이므로 어쩔수없어 라고 단념할 필요는 없다.

같은 쿼리지만 전체범위용 쿼리와 부분범위용 쿼리로 자유자재로 바꿀수 있는 것은 개발자로서 아주 중요한 능력이다. 이를테면 성적테이블을 대상으로 해서 79점 이하의 점수가 없는 학생의 리스트를 얻고자 한다고 할때

전체 범위 처리용 쿼리는

select 학생번호
from 성적테이블
group by 학생번호
having decode(sign(점수 - 80), -1, -1, 0) >= 0
....와 같이 작성해야 하지만


부분 범위 처리용 쿼리는
select 학생번호
from 학생테이블 x1
where not exists(select 1 from 성적태이블 where rowunum = 1 and 학생번호 = x1.학생번호 and 점수 < 80)
.....와 같이 작성해야 한다.

블랙박스 주장하는 사람에게는 애석하겠지만 이와 같이 커서방식과 쿼리는 서로 영향을 받게 된다. 달리 말해서 개발자는 자신이 실행하는 쿼리의 형태를 알아야 하고 이에 따라 적절한 커서 방식을 선택해야 한다. (위 2개의 쿼리는 모델의 mandatory의 여부에 따라 미묘하게 결과가 달라질 수 있다.)




아마도 여기서
"커서방식에 따라 많은 성능차이가 발생하기 때문에 DB, 버전, 드라이버마다 틀린 위 방법을 각자 찾아서 알아서 사용해라"
라고 끝이라고 생각했을지 모르지만 진정 흥미로운 부분은 다음 글 부터이다.