해외 호스팅 서버에 웹사이트 세팅하는 건으로 엄청 머리아프고 바빠 죽겠는데...

아는 형님이 자기네 회원이 서비스를 이용한 기록용 DB에서 일정한 조건을 걸어 필터링 된 자료를 얻고 싶다고 부탁이

들어왔어요...

일단 테이블 내용은 다음과 같아요...

시간, 전번, 출발지, 도착지, 요금.. 기타 등등...

근데 이게 약 100만건 정도 되요...

10년동안 모은거래요... 테이블 명은 vips_5

조건은 출발지에서 서울, 경기, 인천 지역에 해당하고 요금이 존재하며 전화번호가 휴대폰 번호인 레코드만 뽑고 싶데요..

다른건 문제가 안되는데 출발지, 도착지에 데이터 규칙이 굉장히 지 맘데로 들어가 있다는 것입니다.

어떤건 경기도, 충북 뭐 이런식이고 어떤건 강남, 인덕원 사거리 뭐 이런식이고...

그래서 이걸 어쩌지 하며 머리 싸메고 고민 하다가 생각해낸 것이

우체국 사이트 가면 우편번호용 주소자료 들어있는 엑셀 파일 있자나요..

시,도 / 시,군,구 / 읍,면,동 / 리 컬럼으로 나뉘어 지역명 정보가 있는... 이 자료에서

서울, 경기, 인천 지역명과 거기에 해당하는 시,군,구 명칭 그리고 거기에 또 해당되는 읍면동을

location이란 컬럼으로 UNION ALL 해서 하나의 컬럼으로 다 넣었어요..

경기도

성남시

안양시

...

분당구

수정구

권선구

...

야탑동

장지동

복정동

...

곤지암읍

도척면

진우리

...

이런식으로 도, 시, 구, 동, 읍, 면, 리 각 단계별 지역 명을 그냥 하나의 컬럼에 넣은 것이죠.. 서울, 경기, 인천에 해당하는 것만..

대충 한 1400여개 지명이 존재하더군요...

그리고는 이걸 location이란 테이블에 넣고 location이라는 컬럼으로 지정했어요.

제가 생각한 필터링 방법은 다음과 같았어요..

SP제작

일단 vips_5(서비스 이용 히스토리 테이블) 를 커서 선언 후 레코드 하나씩 가져옵니다.

출발지에 대한 컬럼을 @startP 라는 변수에 담아주고

다시 커서를 또하나 만들어서 location 테이블에 있는 레코드를 하나씩 가져오게 한 다음

location 컬럼의 값을 @location 이라는 변수에 담아서 CHARINDEX() 함수로 체크합니다.

@startP 라는 변수의 텍스트에 @location(지역명)이 존재하는지 체크하고 없으면 다음  location 레코드를 가져와서

다시 비교하고...

이렇게 1400여번 비교를 하는데 중간에 존재하는 지역명이 있으면 @LOC_CHK_BIT 변수의 값을 1로 바꿔주고 BREAK 해서

빠져 나갑니다.

그리고 지역명 범위에 해당하는 이 레코드는 INSERT 시켜 줍니다. 임시 테이블에

그리고 모든 서비스 이용기록 레코드가 필터링 되고 나면 그 동안 저장했던 임시테이블 내용을

결과모음용 테이블에 INSERT 시켜 주고 SP를 끝냅니다.

이걸 돌렸더니...30분동안 돌아가도 결과가 안나오더군요...

그도 그럴것이... HISTORY 레코드 1건당 최대 1400번의 비교가 발생하고 원하는 데이터면 임시 테이블에 INSERT 하는 작업이

100만건 반복되니... 대충 BREAK 문으로 중간에 빠져 나오는 걸 감안해 평균 비교 횟수로 계산 때려봐도 100만건 * 700번비교 하면..

그 어마어마한 비교 횟수와 적어도 80만건의 INSERT 발생

그리고 최종적으로 임시 테이블에 쌓인 것을 결과 저장용 테이블에 옮기는 작업까지 생각해보니.. 이거 답이 안나오더군요...

SP 제작은 초보라 성능 그 딴거 전혀 생각지도 못하고 만드는데 급급했구요...

제가 생각해낸 유일한 비교 후 필터링된 자료 구하기 방법은 이게 유일해서요...

혹시 좋은 방법이 있을까 하여 조언을 구해봅니다.

아래는 제가 만든 소스입니다...

필요하시면... 참고 부탁해요...

---------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_vips_FL]

-- Add the parameters for the stored procedure here

@TableName NVARCHAR(32)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- 변수 선언

DECLARE @Location NVARCHAR(16), @LOC_CHK_BIT BIT

, @strSQL NVARCHAR(4000), @strCUR NVARCHAR(4000), @insSQL NVARCHAR(4000)

, @DATE_T DATETIME, @TEL NVARCHAR(64), @STARTP NVARCHAR(510), @ENDP NVARCHAR(510), @CHARGE INT;

-- 임시테이블 생성 (원하는 레코드 발견 시 임시 저장용) --

CREATE TABLE #IMSI_TBL (

DATE_T datetime, TEL nvarchar(64), JISA nvarchar(64), STARTP nvarchar(510), ENDP nvarchar(510), 

CONF_WK_SABUN nvarchar(64), CHARGE INT, CONF_INFO nvarchar(510), CONF_STATUS nvarchar(32), CONF_CREASON nvarchar(510), 

IN_ID nvarchar(64), CONF_MEMO nvarchar(510), USE_COUNT INT, CANCEL_CODE nvarchar(32), CU_SMSYN nchar(2));

-----------------------------------------------------------------------------------------------------

-- VIPS 테이블 내용을 커서로 사용하기 위해 시작 --

Set @strCUR = N'DECLARE CUR_VIPS CURSOR FOR '

+ 'select DATE_T, TEL, STARTP, ENDP, CHARGE from ' + @TableName

+ ' where LEFT(TEL,2) = ''01'' and CHARGE > 0 and CU_SMSYN = ''Y'' and '

+ ' (LEN(STARTP) > 5 and LEFT(STARTP,3)<>''///'')';

EXEC sp_executesql @strCUR;

-- 커서오픈 (지역명 존재유무 체크할 레코드)

OPEN CUR_VIPS;

-- 첫행 가져오기

FETCH NEXT FROM CUR_VIPS INTO @DATE_T, @TEL, @STARTP, @ENDP, @CHARGE;

-- CUR_VIPS LOOP 시작 (마지막 레코드까지 반복)

While @@FETCH_STATUS = 0

BEGIN

-- 현재 레코드가 선택된 지역에 해당 하는지 여부 체크 BIT(0,1)

Set @LOC_CHK_BIT = 0;

------------------------------------------------------------------------------------------------

-- 커서 선언 : Location에 있는 지역 수 많큼 Loop 돌면서 STARTP 컬럼에 해당 문자열이 존재하는지 체크하여 존재하면 임시 테이블에 Insert

DECLARE CUR_LOC CURSOR FOR 

Select Location From location;

-- 커서 열고 (지역명 레코드 수 만큼 반복) --

OPEN CUR_LOC;

-- 첫행(테이블명)을 가져온다

FETCH NEXT FROM CUR_LOC INTO @Location;

IF @@FETCH_STATUS <> 0 

PRINT '         <<None>>' 

-- 마지막 행까지 반복 (지역 값이 STARTP 컬럼에 존재하는지 Loop 돌면서 체크)

While @@FETCH_STATUS = 0

BEGIN

IF CHARINDEX(@Location, @STARTP) > 0

Begin

Set @LOC_CHK_BIT = 1;

BREAK;

End

FETCH NEXT FROM CUR_LOC INTO @Location;

END

CLOSE CUR_LOC;

DEALLOCATE CUR_LOC;

--------------------------------------------------------------------------------------------------

IF @LOC_CHK_BIT > 0

Begin

-- 임시 테이블에 현재 레코드를 추가해준다.

Set @strSQL = N'INSERT INTO #IMSI_TBL SELECT * FROM ' + @TableName + ' WHERE TEL=''' + @TEL + ''' and STARTP=''' + @STARTP + ''' and ENDP=''' + @ENDP + ''' and CHARGE=''' + CAST(@CHARGE as nvarchar(16)) + '''';

EXEC sp_executesql @strSQL;

-- 임시 테이블에 레코드 추가된것 확인

--SELECT * FROM #IMSI_TBL;

End

ELSE

PRINT '제거대상 레코드는 SKIP';

FETCH NEXT FROM CUR_VIPS INTO @DATE_T, @TEL, @STARTP, @ENDP, @CHARGE;

-- CUR_VIPS LOOP 종료

END

------------------------------------------------------------------------------------------------------------

CLOSE CUR_VIPS;

DEALLOCATE CUR_VIPS;

INSERT INTO Result_TBL Select * from #IMSI_TBL

    -- Insert statements for procedure here

-- SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

PRINT '결과물 생성 완료 Result_TBL 조회'

Select * from Result_TBL

END

GO