안녕하세요, 얼마전에 질문글을 올렸었는데, 제가 너무 애매모호하게 글을 올렸던 것 같네요.  다시 정리해서 올립니다.

도로명 주소 검색 기능 구현 중입니다. 데이타는 총 6백만건 정도 되구요.

인덱스에 대한 개념도 잘 모르는 상태에서 인덱스 만들고, 쿼리를 짰습니다.

select bld_mng_no, zipcode, sido_name, sigungu_name, eupmyeon_name,

               road_name, bld_no1, bld_no2, dong_name,

               sigungu_bld_name, jibun_no1, jibun_no2, ismountain

from NewPostZips

where road_name like '검색어%' or

sido_name ='검색어' or

sigungu_bld_name like '검색어%' or

dong_name like '검색어%' or

sigungu_name like ' 검색어%' or

eupmyeon_name like '검색어%'

'조마루로97번길'로 검색하면 실행계획이 아래와 같습니다.

인덱스탐.jpg

오리로로 검색하면 실행계획이 아래와 같습니다. 검색 결과가 2400건이네요..

인덱스안타요.jpg

 

도로명주소 검색기능에 실제 사용될 프로시저는 아래와 같습니다. 검색어 파라미터값은 공백 기준으로 split시켜 where절에 추가시킵니다. 검색어에 도로명, 건물명, , //, /, /면 등 다양한 값이 들어올 수 있어 where절에 추가시킨 컬럼이 많아요. 검색결과가 많으면 속도가 너무 느려 일단 COUNT를 하여 1,000건이 넘으면 검색이 안되도록 하였습니다. 그런데, COUNT만 하는데도 오래걸리는건 9초정도 걸리더라구요..

ALTER PROCEDURE [dbo].[USP_NewPostZip_LIST4]

        @searchValue1  varchar(100),          -- 검색어

        @searchValue2  varchar(50),           -- 지번앞자리

        @searchValue3  varchar(50),           -- 지번뒷자리

        @searchValue4  varchar(50),           -- 우편번호

        @Rtn                   int     Output         -- 리턴코드

AS

SET NOCOUNT ON

BEGIN

        Declare

        @Str           nvarchar(4000),

        @whereSql      nvarchar(4000),

        @Sql           nvarchar(4000)

        SET @whereSql = ' WHERE 1=1 '

        --지번 

        If (len(@searchValue2) > 0  and len(@searchValue3) > 0)

               BEGIN

                       SET @whereSql = @whereSql + ' and ((bld_no1= '''+@searchValue2 + ''' and bld_no2= '''+@searchValue3 + ''') or

                       (jibun_no1= '''+@searchValue2 + ''' and jibun_no2= '''+@searchValue3 + ''' )) '

               END

          

        --우편번호

        If len(@searchValue4) > 0

               BEGIN

                       SET @whereSql = @whereSql + ' and (zipcode = '''+@searchValue4 + ''') '

               END    

        -- 검색어

        If len(@searchValue1) > 0

               BEGIN

                       Declare @tmpTbl Table(T_STR varchar(100))

        

                       INSERT INTO @tmpTbl(T_STR)

                       SELECT * from FN_SPLIT(@searchValue1, ' ')

                      

                       Declare cur CURSOR FOR

                       SELECT T_STR FROM @tmpTbl

                       OPEN cur

                       Declare @T_STR varchar(100)

                       FETCH NEXT FROM cur INTO @T_Str

                       WHILE @@FETCH_STATUS = 0

                       BEGIN

                              SET @whereSql = @whereSql + ' AND (road_name like '''+ @T_Str + '%'' or

                              sigungu_bld_name like '''+ @T_Str + '%'' or

                              dong_name like '''+ @T_Str + '%'' or

                              sigungu_name like '''+@T_Str+'%'' or

                              sido_name = '''+@T_Str+''' or

                              eupmyeon_name like '''+@T_Str+'%'') '

                              FETCH NEXT FROM cur INTO @T_Str      

                       END

                       CLOSE cur

                       DEALLOCATE cur

               END

        Declare @totalCount int

        -- 전체레코드수

        SET @Sql = 'SELECT @totalCount = COUNT(bld_mng_no) '

                        + ' FROM newpostzips '

                        + @whereSql

        EXEC sp_executesql @Sql, N'@totalCount int OUTPUT', @totalCount = @totalCount OUTPUT             

        print @totalCount

 

        IF @totalCount > 1000

               BEGIN

                       SET @Rtn = 99

                       Return;

               END

        Else

               BEGIN

                       SET @Rtn = 1

               END

        SET @Sql = ' SELECT bld_mng_no, zipcode, sido_name, sigungu_name, eupmyeon_name, '

               + ' road_name, bld_no1, bld_no2, dong_name, '

               + ' sigungu_bld_name, jibun_no1, jibun_no2, ismountain '

               + ' FROM newpostzips '

               + @whereSql

               + ' ORDER BY sido_name, sigungu_name, eupmyeon_name, road_name, bld_no1, bld_no2 '

        EXEC sp_executesql @Sql

END

워낙 데이터도 많고, where절에 추가시킨 컬럼이 많아서 속도가 잘 안나오는데.. 좀 더 개선할 수 있는 방법이 있을까요?

이렇게 대용량 데이터는 다뤄본 적이 없어, 조언 좀 구하려고 글 올립니다.

프로시저 실제실행계획 xml 첨부로 올립니다^^

No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 2196
7610 mssql 2008R2를 사용합니다. 프로시져를 돌리면 오류에 대한 문의 [1] 김영선_281276 2014.02.19 16146
7609 쿼리 질문드립니다. [1] 람브르기니 2014.02.19 2220
7608 bcp를 이용하여 텍스트파일을 DB에 넣으려고 합니다. [1] 잠스 2014.02.19 4810
7607 동적쿼리에서 파라미터로 받아온 값이 인식을 안합니다. [1] 풀내기vber 2014.02.18 4270
7606 네트워크 설정 anonymous 2014.02.18 13811
7605 작업 모니터의 개요에서 대기중인 태스크 막새바람 2014.02.17 10547
7604 SQL2000 - > 2008 마이그레이션 문의 [8] 텅신보안 2014.02.17 3471
7603 성능모니터(Perfmon) 관련 문의 [1] 메칸더 2014.02.17 3906
7602 임시테이블 사용법 문의.. [1] 킹멋쟁 2014.02.17 3215
7601 데이터베이스 단위 감사 문의 더메이드 2014.02.14 2893
7600 DB 메모리 모니터링 방법 및 릴리즈 시킬수 있을까요? [5] 언제쯤 2014.02.14 5368
7599 쿼리 업데이트 조언좀 부탁드립니다 [1] 김세일_280618 2014.02.14 4501
7598 두가지 조건을 줘서 쿼리를 짜고 있습니다... [2] 사이드사이드 2014.02.13 4573
7597 DB 미러링 구성시에 방화벽 해제 안하고 구성하시는분 문의좀 드릴게요. [3] SE왕초보 2014.02.13 5766
7596 인덱스 관련 문의 [2] 냥냥 2014.02.13 4450
7595 SQLCMD를 배치파일로 실행하는 방법이 있을까요? [2] giant123 2014.02.13 13495
7594 ms sql 2012 Always on 구성 NiceHee 2014.02.12 5299
» 도로명주소 검색 쿼리 조언 좀 해주세요. [4] 나뇽 2014.02.12 15104
7592 UNION ALL과 WHILE문을 같이 쓰는 방법은? [2] giant123 2014.02.12 5177
7591 기존 컬럼에 identity 속성을 추가하는 법. [2] 흑흑 2014.02.12 9072





XE Login