오랫동안 내 정신을 갉아먹던 문제를 해결하였습니다.
기쁜 마음에.. 내용은 생략...결과 그림만 흔적으로 남겨 봅니다.
아래 그림은 로컬 디스크 WD 7200 rpm 2TB (WDC WD2003FZEX-00Z4SA0) 디스크에
단일 파일 그룹으로 구성한 Database 의 테이블 내에 초당 10만 쿼리를 기록(삽입)하는 그림입니다.
아래 그림은 해당 프로세스가 구동되는 시스템 사양입니다.
아래 그림은 해당 시스템 구동중 프로세스 사용 정보입니다.
초당 10만 쿼리면, 응답도 10만개 합이 초당 20만 로우정도를 실시간으로 DB에 삽입하는 결과를 말합니다.
한시간 기준으로 테이블에 삽입되는 로우는 7억2천만건 정도..
어제에 이어 오늘도 이 내용을 쓰게 되는군요..
어제 나름대로 MSSQL의 주석문 처리에 대하여 이해했다고 생각했는데.. ㅠㅠ
다음은 예시 그림..
<그림 1 - 어제 나름대로 이해한 바탕으로 주석 예시 만든것>
나의 머리를 복잡하게 만드는 아래 그림
<그림 2 - 또다시 의도하지 않는 결과를 토해내는 미친 sql>
아래는 상황을 좀더 자세하게 비교한 그림
<그림 3 - GO 문의 위치에 따라 다르게 동작하는 sql 엔진>
GO문이 특수하다는건 알겟지만.... 저런 짖거리를 하는 ms가 점점더 미워짐..
오늘도 하루가 날라가네 그려 ㅠㅠ;;;
주로 MSSQL 쪽 프로그래밍을 하다보니.. 여러가지 내부적인 것들을 건드리게 되는군요.
<그림 1 - 기본>
위 그림은 일반적인 블록 주석문과 라인 주석문에 대한 구문이고..
단순한 수행 쿼리입니다.
수행해 보면 결과가 정상적으로 1 이 나옵니다..
그렇다면 아래 2번 그림의 쿼리는 어떨까요?
<그림 2 - 먼가 다른 주석처리>
프로그래머 관점이라면 저기 붉은 사각박스의 /* 가 하나 더 들어갔다고 해서..
위 그림처럼 주석 처리가 되면 안되겠지요..
수행하면 당연히 에러를 토해냅니다.
메시지 113, 수준 15, 상태 1, 줄 5
주석 끝 표시('*/')가 없습니다.
사실 의도된 결과는 아래 그림처럼 나오길 바랬습니다.
<그림 3 - 내가 원했던 결과>
자 그렇다면 MSSQL에서는 그림 2번과 그 결과처럼 /*는 페어처리를 하도록 설계된 것인가?
그림 2번 내용을 조금 바꾸어 보도록 하죠.
<그림 4 - 이제서야 보이는 설계 의도>
<그림 5 - mssql 2008 ssms> <그림 6 - msvs 2008>
지금 까지 여러가지를 테스트해본 결과.. 쿼리를 고속으로 파싱해야 하는 sql 엔진 입장에서
내부적으로 다중 중첩된 블록 주석문을 처리하는것이 상당히 부담스러웠던거 같습니다.
첫번째 의견은 위와 같았는데.. 실제로 구현이나 파싱처리에서 보면 sql 처럼 처리하는게
로직적으로 더 부담이 되겠네요. 주석의 끝을 명시해야 하는것도 그렇고..
아마도 스크립트로 의도되지 않은 동작을 일으키지 못하도록 방어하는 입장에서 작성된거라 생각듭니다.
그래서 블록 주석문은 /*의 갯수가 나오면 기존 블록에 포함되던 말던.. 나온 갯수 만큼 */ 의 페어를
맞추어 주로록 의도적으로 설계된듯하네요..
어쨋든 저렇게 의도 되었다는 것을 알게는 되었지만..
일부 로직을 새로 짜야한다능.. ㅠㅠ
오늘의 팁하나..
MSSQL에서 tempdb에 임시테이블을 만들고 통계를 작성하면, 거기서 작성된 통계를
오리지날 테이블에 업데이트 시킬수가 없다.
콜레이션 문제가 발생하기 때문이다.. 끝~
안냐세염. 까막임돠...
회사에서 업무를 수행하다 보니 나오게된 결과물 입니다. 유틸리티성으로 만들게된 것인데
상업적 사용만 아니라면 공개해도 문제 없다는 답변을 받고 올려봅니다.
(상업적 사용이란, 회사에서 가공없이 업무에 사용하는것은 상관없습니다만, 재가공 하여 판매하는 것을 말함)
(개인적인 욕심은 혹시 사용해보시고, 문제점이나 추가 기능에 대한 요구등을 얻을 수 있을까 하는 목적도 ㅎㅎ;;;)
제목 그대로 테이블만 별도 파일로 떨구거나, 혹은 수행한 쿼리문의 결과를 파일로 저장하는 기능
그리고, 반대로 저장된 파일을 다시 테이블로 넣는 기능을 제공하는 유틸리티입니다.
(파일로 쓰거나, 테이블에 로딩하는 속도는 select into 와 비교했을 때 뒤지지 않습니다.)
1. 알려진 사용상의 단점
A. 위의 그림에서 보신바와 같이 테이블내에 혹은 수행된 쿼리 결과내에 여러개의 BLOB 형식의 데이터가 위치와 상관없이
삽입되어 있더라도 동작 수행에 문제는 없으며, 단 한 컬럼당 BLOB 데이터의 크기가 100MB 정도까지만 지원되도록
하는 제한 사항이 있습니다. (이는 일부러 제한을 둔것이 아니고, 아직까지 해결하지 못한 문제랍니다. -_-;;;)
B. 테이블을 백업하였을 경우는 테이블의 자료구조가 100% 정확하게 파일에 설정되지만, 쿼리의 수행결과를 파일로 저장할 경우는
select into 했을 때와 마찬가지로 호완가능한 구조로 저장되기 때문에, 테이블에 로딩했을 때 원본과 데이터 타입 일부가 다를 수
있습니다. 예를 들면 smallmony 타입을 쿼리 결과로 파일에 저장했을 경우 decimal 로 저장됩니다.
혹은 sysname 타입을 저장했을 경우는 nvarchar 로 저장되죠.
백업 기능을 이용 -BACKUP -TABLE sysindexes 로 저장한 것과
익스포트 기능을 이용 -EXPORT -QUERY "select * from sysindexes" 로 저장한 경우 테이블에 저장된 결과물은 동일하지만
테이블의 자료형이 일부 다를 수 있습니다.
반대로, -EXPORT 형태로 저장했을 경우는 해당 MSSQL 버전의 특정 자료형에 구속되지 않기 때문에 다른 버전의 MSSQL에도
테이블로 로딩할 때 별 문제가 없습니다. (2005에서 만든 파일을 2000에도 로딩시킬 수 있음)
C. 파일에 특정한 보안 로직이나 권한 제한등이 없기 때문에 외부로 유출되었을 경우 누구나 DB에 로딩 시킬 수 있습니다.
관리상의 주의가 필요합니다. (보완해야할 대표적인 기능중에 1번째)
D. 데이터파일에 대한 압축 기능이 제공되지 않습니다. (보완해야할 대표적인 기능중에 2번째)
(MSSQL 2008 버전 부터는 지원하다는데.. 아직 접해보지는 못했음)
2. 알려진 사용상의 장점
MSSQL에서는 DTS 및 패키기 매니저, BCP등을 지원하는데 이렇게 떨궈진 파일들은 실제로 DB에 넣어놓기 전까지는
내부 구조가 어떻게 생겨먹었는지, 무슨 이유로 만들어졌는지, 어떤 데이터가 들어있는지 아무도 모르죠..
본 프로그램을 이용해서 저장할 경우는
-PRINT 기능을 이용하여, DB에 로딩하지 않고서도 아래와 같이 필요한 정보를 볼 수 있습니다.
파일이 생성된 시각, 만들어질 당시 접속한 데이터베이스, 유저명(암호는 당연히 기록되지 않음), 수행한 쿼리 및 대상 테이블
각 컬럼의 구조 정보, 필요에 의해 기술한 코멘트, 그리고 첫번째 로우의 데이터 정보를 출력해줍니다.
(하기사 이것때문에 만든것입니다. 장점은 흠.. -_-;;; 이게 다군요..)
하나더 라고 한다면, 흠.. 쓸만할 만큼은 충분히 빠릅니다. GUI 툴처럼 복잡하지도 않고, BCP 처럼 다양한 옵션을 지원하진 않지만.
성능하나만은 탁월하게 빠르다고 말씀드릴 수 있겠네요. 파일로 떨구는거나 테이블에 밀어 넣는거나... ^^;;;
위의 그림에서 처럼, 생성시 넣어준 정보와 간단한 디스크립션, 컬럼 정보 및 저장된 내용중 첫번째 데이터를 출력해 줍니다.
바이너리 및 이미지 데이터는 출력시 64바이트를 넘어가면 64바이트까지면 화면상에 출력해줍니다.
특별하게 설치할건 없고, 실행파일 하나와 DLL 두개로 구성되어 있어서 그냥 압축 풀고 사용하시면 됩니다.
<테스트환경: Windows 2003 Server 32Bit, Windows XP Home/Pro 32Bit, Windows Vista Home 32Bit, Windows 2008 Server 64Bit>
사용상의 문제점이나 활용 방안에 대한 의견이 있으시면 개발하는데 많은 도움이 되겠습니다. ^^;
ODBC 프로그래밍을 하다보면, BLOB 타입 데이터를 처리할 때 자주 만나게 되는 에러이다.
예전에는 MS버그 려니, 하고 다음과 같은 방법으로 처리했었다.
1. 테이블을 설계할 때 BLOB 타입은 항상 마지막으로 몬다.
BLOB 타입이 여러개라면 모두 뒤쪽으로 몰아서 사용한다.
2. ODBC는 때려치고, ADO나 OLEDB를 사용한다.
(나는 ODBC를 꼭 써야하는 이유가 있으니 이 방법은 XXX 이다. -_-)
(사실 ADO는 쬐끔 써봤는데... OLEDB는 써본적이 한번도 없다..헐~)
위 두가지 방법이 인터넷 뒤져서 나온 해결법의 전부였다. MS포럼이나 KB 및 구글 뒤져바야
나오는게 그게 그거이다. MS에 문의해보니 안갈켜준다 -_-;;;
저 에러가 나오는 원인은 데이터 전송시 전송 효율을 올리기 위해서 미리 메모리를 할당해서 각
컬럼별로 바인딩 해놓고 패치하게 되는데, 크기 범위가 정해지지 않은 BLOB 데이터를 중간에
끼워넣게 되면 미리 준비된 메모리에 패치할 수 없기 때문이다.
(모든 프로토콜 분야에서 전송 성능은 상당히 중요한 이슈이다.)
이럴 경우 BLOB 영역은 0으로 리절브를 하게되고, 별도로 SQLGetData 를 이용해서 데이터를
가져오게 되는데....
이게 위치가 끝부분이 아니고, 중간부분에 가게되면 데이터 패치는 성공하지만, BLOB 데이터를
가져오려 하면 제목에 보이는 잘못된 인덱스 입니다 라는 에러가 발생한다.
한참 고민(3년간 사용하는 동안 -_-)하다가 문제의 원인을 추적해 보니, 패치하기 위하여 미리
메모리를 바인딩 해놓는 부분을 생략하고, 그 때 그 때 데이터를 SQLGetData 로 가져오는
방법을 생각해 봤다.
흠.. 잘나온다 -_-;;;
단, 미리 예비된 메모리에 덩어리로 패치하는게 아니기 때문에, 데이터 로우수가 많을 경우
위 방법을 사용하면 성능 저하가 발생할 수 있으므로, 사용상에 주의를 할 필요가 있다.
그나마 성능을 보전하기 위하여 머리를 굴려본걸 정리해 보면 다음과 같다.
1. 컬럼 정보를 읽어올 때 BLOB가 있는지 검사한다.
2. 모든 BLOB 컬럼이 뒤쪽에 몰려 있으면, 바인딩해서 리절브된 메모리에 패치하고, 나머지는 SQLGetData 로 긁어온다.
3. 중간에 BLOB 컬럼이 끼어있으면, 바인딩을 안하고 모든 컬럼을 SQLGetData 로 긁어온다.
이렇게 직접 구현하는 사람들이 많지 않겠지만, 그래도 혹시나 아직까지 쓰는 사람들이 있다면
조금이라도 도움이 되겠죠 ^^;;;
k4_freetds-stable.zip