2006-09-20

OTL Tips

OTL은 ODBC를 잘 래핑한 오픈소스 DB 쿼리 라이브러리이다. 헤더 파일 하나만 있으면, 굳이 ODBC 래퍼 클래스를 만들지 않아도 충분히 안심하고 쓸 수 있으며, MYSQL, MSSQL, ORACLE 모두에 사용될 수 있다. 꽤 여러 곳에서 사용되고 있고, 평판도 좋다고 알려져 있어서 써봤는데, 대략 반년동안 사용해본 경험으로는, 차라리 OLE DB 를 쓰거나, ODBC 래핑 라이브러리를 어딘가에서 갖다 쓰는 게 더 개발기간을 단축시킬 수 있다고 판단된다. OTL 만의 미묘한 버그스러움이 너무 많기에... '''비추천'''이었다. 그러나 최근에 legacy code를 손보면서 raw ODBC 코드를 만지다가 결국 포기하고 다시 OTL을 도입한 적도 있어서, 뭐라고 표현하기는 난감하지만 하나 확실한 건, ODBC를 직접 만지는 것보다는 백만배 정도 나으므로, 한번 써보기를 권한다.

data binding

OTL type 과 ODBC type 과의 대응표를 잘못 기억했다가는 마감 시간에 낭패를 보는 경우가 있다. 특히 헷갈리는 타입들을 적어본다.

ODBC	OTL
bit	int
tinyint	int
smallint	short
datetime/smalldatetime	char[N] (only for explicit-binding select)

implicit_select 에서는 datetime -> string 바인딩은 지원되지 않으므로, convert() 등으로 변환해서 string 으로 받아야 한다. 그런데, explicit binding select 에서는 웬지 지원이 된다. 이유는....모른다...

timestamp 타입을 받으려면 아무래도 otl_datetime 을 사용하는 편이 좋을 것 같다. 그리고, built-in type 으로 바인딩을 하는 것보다는, otl_value<T>를 쓰면 NULL 체크도 되니깐 추천.

output parameter + implicit_select

잘 되던 프로시저의 output 파라미터의 값이 이상해진다든지 꼬일 경우, 새로 추가된 로직에 select 가 들어가있는지를 확인할 것. (ODBC의 한계는 아닌 듯한데) OTL에서의 프로시저는 output 파라미터만을 가지든, implicit_select 만을 하든, 둘 중 하나만 해야 한다. 아래와 같이 둘 다 할 경우 바보가 된다.

CREATE procedure test_proc
@param int output
AS
....
SELECT something FROM tbl
IF @@error <> 0 OR @@rowcount <> N
....

위와 같은 코드들은 assignment select 로 해결하면 된다.

otl_implicit_select

output parameter 가 아닌, 내부에서 select 를 하는 프로시저의 경우 이 옵션을 명시해줘야 한다. 주의할 점은, datetime 이나 smalldatetime 을 내부에서 select 하고, OTL 에서 스트링으로 받으면 incompatible datatype 예외가 던져진다. 이때에는 varchar(N)으로 cast/convert 하는 것을 권장한다.

SET NOCOUNT ON/OFF

OTL/ODBC 의 가장 극악한 버그(?) 중의 하나이다.

OTL 은 @@rowcount 와 다른 output parameter 또는 select 결과를 구분할 수 없다고 한다. 그래서, SET NOCOUNT 를 설정해야만 프로시저에서 값을 제대로 읽어올 수 있다. (이 문제 때문에 얼마나 많은 시간을 날려버렸던가!! 만약 OTL 에서 프로시저를 제대로 실행할 수 없다면 90% 는 이 문제라고 보면 된다.)

주의할 점은, 프로시저 내부에서 다른 프로시저를 호출할 때 하위 프로시저에서 SET NOCOUNT OFF 를 해 버리면 그 뒤의 결과값 역시 꼬여버리게 된다는 점이다. 따라서, OTL 에서 직접 호출하는 프로시저의 시작과 끝에만 SET NOCOUNT 를 설정하고, 다른 프로시저에서는 절대 사용하지 말 것을 권장한다.

Multiple ResultSet

OTL/ODBC 에서는 공식 지원하지 않는다. 실제로 해보면 첫번째 result set 은 잘 받지만 두번째 이후부터는 eof() 가 되어 버린다. 결국, 해킹을 하거나 포기하는 수 밖에 없는데... 그래도 강제 실행을 시도해보겠다면 대략 다음과 같이 해 보기를... (내부 구조가 복잡해서 결국 포기했지만...)

otl_stream stmt(1,
"{CALL get_multi_resultset(:p<int,in>)}",
conn,
otl_implicit_select);
stmt <<(int)1;
otl_cur & s = (**stmt.ss).cursor_struct;
SQLRETURN result = SQLMoreResults( s.cda );

Binding varchar

문자열 바인딩시 NULL 을 위한 공간을 2바이트씩 예비해두어야 한다. 예를 들면, otl_statement 로 바인딩시 VARCHAR(10) 컬럼의 값을 읽거나 쓰려면 :XXX 를 사용해야 한다는 뜻이다.

Constant SQL Statements

dynamic binding 이 필요없을 경우 otl_cursor::direct_exec() 를 사용해야 한다. '''반대로 말하면 otl_stream 에 static sql 을 사용하지 말라!''' rpc 를 알고 싶다면 otl_cursor::direct_exec() 의 리턴값을 사용하면 된다.

otl_connect::auto_commit_on()

otl_cursor::direct_exec() 를 이용해서 insert/delete 를 호출할 경우, 암시적인 트랜잭션이 발생해서 관련된 테이블이나 row 에 IX 를 걸게 된다. 따라서 다른 커넥션(otl_connect)에서 관련 테이블에 쿼리를 하게 되면 블로킹되는 불상사가 발생할 수 있다. 또는 아예 처음부터 커넥션 자체에 auto_commit_on()를 설정해서, 매 쿼리 실행마다 자동적으로 commit 를 하도록 하면 좋다.

otl_stmt::set_commit()도 비슷한 역할을 하는 게 아닐까 추측한다. 그런데, 이 옵션을 켰을 때 동일 커넥션에 대해서 여러번 쿼리를 하는 경우 이미 사용중이라는 에러 메시지가 나온다. 도대체.... ㅠㅠ

ODBC Connection Pooling 기능 추가하기

OTL을 ThreadPooling 과 함께 사용하기 위해서는 Connection Pooling 이 필수적이다. (그렇지 않으면 Thread Local Storage를 사용해야 하는데, 이는 권장하지 않는다고 빌게이츠가 말했다 ㅎㅎ)

테스트삼아, 단순한 SELECT COUNT(*) FROM XXX를 ThreadPooling 을 이용해서 로컬랜에 설치된 MSSQL 서버에 N번 쿼리를 큐잉하고 GetTickCount()로 시간을 체크해보았다. 이때, 풀링을 사용할 경우 평균 쿼리 시간이 5ms 정도 낮게 나왔으며 최소 쿼리 시간이 0 인 횟수가 꽤 많이 나왔다. 풀링을 하지 않을 경우에는 99% 의 확률로 10ms 이상의 시간이 소모되었다.

주의사항 i. shared environment 생성 전, 즉 rlogin() 전에 풀링을 설정해야 한다!! i. env 즉 프로세스당 풀링 대신 시스템 전체에서 연결을 풀링하고 싶다면 SQL_CP_ONE_PER_DRIVER(?)를 사용하면 된다.

inline
bool SQL_OK ( SQLRETURN nResult )
{
return nResult == SQL_SUCCESS || nResult == SQL_SUCCESS_WITH_INFO;
}

void EnableConnectionPooling()
{
// enable connection pooling per environment
SQLRETURN ret = SQLSetEnvAttr(SQL_NULL_HANDLE,
SQL_ATTR_CONNECTION_POOLING,
(LPVOID)SQL_CP_ONE_PER_HENV,
0);
BOOST_REQUIRE( SQL_OK(ret) );
}

void DisableConnectionPooling()
{
SQLRETURN ret = SQLSetEnvAttr(SQL_NULL_HANDLE,
SQL_ATTR_CONNECTION_POOLING,
(LPVOID)SQL_CP_OFF,
0);
BOOST_REQUIRE( SQL_OK(ret) );
}

bool IsConnectionPooling()
{
DWORD value;
SQLRETURN ret = SQLGetEnvAttr(SQL_NULL_HANDLE,
SQL_ATTR_CONNECTION_POOLING,
&value,
0,
NULL);
BOOST_REQUIRE( SQL_OK(ret) );

if ( value == SQL_CP_ONE_PER_HENV || value == SQL_CP_ONE_PER_DRIVER )
{
return true;
}
else if ( value == SQL_CP_OFF )
{
return false;
}
else
{
BOOST_ERROR( "error" );
}
return false;
}

precreated handle 사용하기

Connection Pooling 을 사용할 때, otl_connect::rlogin()을 쓰게 되면 매번마다 id/passwd 를 파싱하고 environment handle 을 생성하는 등 불필요한 부하가 있다. 따라서 미리 shared environment handle 을 하나 할당하고, hdbc handle 도 만들어서 객체를 생성하면 비교적 나을 것이다.

스트림 풀링

스트림의 풀이 otl_connect 객체 내부에 존재하므로, 이 객체를 항상적으로 유지해야만 풀링의 효과를 볼 수 있다.

테스트 결과

1000개의 단순쿼리({{{SELECT COUNT(*) FROM XXX}}})를 ThreadPooling을 이용해서 처리, 그 시간을 살펴보았다. 첫번째를 제외하고는 모든 상황에서

NO_POOLING : 매 작업마다 재연결 시도
POOLING : 단지 ODBC Connection Pooling 옵션만 설정하고 위와 같음
ODBC_POOLING : 미리 env 1개를 생성하고 매 작업마다 hdbc handle 만 생성해서 쿼리하기
OTL_POOLING : 아예 otl_connect 객체를 자체 풀링시킴
OTL_POOLING_NO_STREAM_POOLING : 위와 같지만 ODBC 풀링과 스트림 풀링을 끈 상태

Query Type	maxTick	avgTick	#threads
NO_POOLING	47	12	4
POOLING	47	10	4
ODBC_POOLING	78	3	4
OTL_POOLING_NO_STREAM_POOLING	78	2	4
OTL_POOLING	32	0	2

결론적으로 otl_connect 객체를 자체적으로 풀링하는 방법이 가장 빨랐다. 그러나, 스트림 풀링을 끄면 2위인 ODBC_POOLING과 비슷한 걸로 봐서 스트림 풀링의 비용이 꽤 높다는 것을 알 수 있다. (쿼리 자체를 좀더 복잡하게 해서 테스트해야 나을 거 같다.)