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

도로명 주소 검색 기능 구현 중입니다. 데이타는 총 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 SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 42818
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 22720
7603 성능모니터(Perfmon) 관련 문의 [1] 메칸더 2014.02.17 3949
7602 임시테이블 사용법 문의.. [1] 킹멋쟁 2014.02.17 3281
7601 데이터베이스 단위 감사 문의 더메이드 2014.02.14 2924
7600 DB 메모리 모니터링 방법 및 릴리즈 시킬수 있을까요? [5] 언제쯤 2014.02.14 5406
7599 쿼리 업데이트 조언좀 부탁드립니다 [1] 김세일_280618 2014.02.14 4525
7598 두가지 조건을 줘서 쿼리를 짜고 있습니다... [2] 사이드사이드 2014.02.13 4595
7597 DB 미러링 구성시에 방화벽 해제 안하고 구성하시는분 문의좀 드릴게요. [3] SE왕초보 2014.02.13 5788
7596 인덱스 관련 문의 [2] 냥냥 2014.02.13 4477
7595 SQLCMD를 배치파일로 실행하는 방법이 있을까요? [2] giant123 2014.02.13 13720
7594 ms sql 2012 Always on 구성 NiceHee 2014.02.12 5333
» 도로명주소 검색 쿼리 조언 좀 해주세요. [4] 나뇽 2014.02.12 15517
7592 UNION ALL과 WHILE문을 같이 쓰는 방법은? [2] giant123 2014.02.12 5264
7591 기존 컬럼에 identity 속성을 추가하는 법. [2] 흑흑 2014.02.12 9494
7590 문자열 날짜 변경부분 질문드립니다. [2] 호야~♥ 2014.02.12 5415
7589 실행계획 좀 살펴주세요. [6] ssunsori 2014.02.12 3847
7588 SQLCMD 파일로 출력할 때 옵션이 궁금합니다. [1] giant123 2014.02.12 6825
7587 커져만가는 tempdb 용량 [1] 초심 2014.02.12 3757
7586 between쿼리문 활용 문의 드려요 [5] 야구소년 2014.02.12 5157
7585 User 가 어떤 role을 가지고 있고 그 role이 어떤 권한을 후룩후룩 2014.02.11 3414
7584 이런 쿼리는 어떻게 하나요? [1] 인생은 열심히 2014.02.10 4207





XE Login