안녕하세요, 얼마전에 질문글을 올렸었는데, 제가 너무 애매모호하게 글을 올렸던 것 같네요. 다시 정리해서 올립니다.
도로명 주소 검색 기능 구현 중입니다. 데이타는 총 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번길'로 검색하면 실행계획이 아래와 같습니다.
‘오리로’로 검색하면 실행계획이 아래와 같습니다. 검색 결과가 2400건이네요..
도로명주소 검색기능에 실제 사용될 프로시저는 아래와
같습니다. 검색어 파라미터값은 공백 기준으로 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 첨부로 올립니다^^
Comment 4
-
항해자™
2014.02.13 01:54
-
나뇽
2014.02.14 11:52
댓글 감사합니다. 웹사이트에서 우편번호 검색을 하기 위한 쿼리 맞습니다.
항해자님 말씀대로 UI를 변경하면 성능이 해결되겠지만, 지금 진행하고 있는 프로젝트에서 UI를 바꿀 수 없는 상황이라서요..
제가 짠 쿼리나 인덱스에서 속도를 개선할 수 있는 방법은 없을까요..?
-
꼬셔죠
2015.02.03 11:41
간만에 답변 써봅니다.
도로명 주소는 SQL 2012 Ent 이상에서 지원하는 column store index 가 갑입니다요.
2014의 Cluster column store index는 Deltal store가 32개 Segment로 나뉘어져(데이타량이 커서) 오히려 불필요하고
non-cluster column store index 쓰시는게 효율 대박이지요..
위 항해자님 말대로 검색 컬럼을 구분토록 하고 검색이 될 수 있는 칼럼을 non-cluster형 column store index를 쓴다면
매우 좋은 효율을 볼 수 있을 겝니다.
non-cluster column store index의 최대 단점인 데이터의 수정이 안되나 어차피 주소명 테이블은 변경될 일 거의 없다고 봐도 무방하고 변한다 해봤자 1년에 한번 관리자가 새로 갱신해주면 그만입니다.
-
꼬셔죠
2015.02.03 14:17
죈장. 검색하다가 제목에 꽂혀 답변했드니.. 작년꺼 답했넹.. 아... 배고파..
위 쿼리 내용을 봐서는 웹사이트 같은 곳에서 주소 검색을 하기 위한 쿼리 같군요,,,
제 생각에는 검색어 하나로 모든 컬럼이 검색되게 하지말고 유저가 선택해서 검색하게 하는게 좋을 것 같습니다,,,
기존 주소 검색과 도로명 검색을 나눌 수 있고, 빌딩 검색도 유저 선택적으로 검색하도록 ui를 변경하는 것을 추천합니다,,
또 빈칸 입력을 받지 않는게 좋을 것 같네요,,, 그러면 쿼리에서 "or" 구문이 줄어들고, 간결해 지겠죠??
인덱스도 상황에 따라 작성하면 될테니, 성능 문제가 해결될 듯 합니다,,
그러면 if 문을 사용해서 쿼리를 분기처리 가능한 수준이 될테니 동적쿼리를 사용하지 않아도 되겠네요,,,