SQL Server 전체 텍스트 쿼리의 성능 향상

  • 아티클

이 문서에서는 SQL Server 전체 텍스트 조건자를 사용하는 쿼리의 성능을 향상시키는 방법을 제공합니다.

원래 제품 버전: SQL Server 2008 Developer, SQL Server 2008 Enterprise, SQL Server 2008 R2 Datacenter, SQL Server 2008 R2 Developer, SQL Server 2008 R2 Enterprise, SQL Server 2008 R2 Standard, SQL Server 2012 Developer, SQL Server 2012 Standard, SQL Server 2012 Web, SQL Server 2012 Enterprise
원래 KB 번호: 2549443

요약

이 문서에서는 전체 텍스트 검색 조건자(예: CONTAINS 및 CONTAINSTABLE)를 사용하고 데이터를 필터링하는 Microsoft SQL Server 쿼리의 성능을 향상시키는 방법을 설명합니다. 예를 들어 이 메서드는 다음 쿼리의 성능을 향상시킵니다.

SELECT * FROM dbo.ftTest WHERE CONTAINS(TextData, '"keyword"') AND CDate > @date

이 메서드를 사용하면 전체 텍스트 검색 엔진이 관계형 엔진으로 전송되기 전에 결과를 필터링하는 방식으로 쿼리, 테이블 스키마 및 전체 텍스트 인덱스를 디자인할 수 있습니다. 따라서 관계형 엔진은 큰 데이터 세트를 필터링할 필요가 없습니다.

추가 정보

전체 텍스트 검색 쿼리를 만들 때 쿼리 성능에 영향을 주는 원칙 요소는 전체 텍스트 검색 엔진이 나머지 데이터를 관계형 엔진으로 전송하기 전에 처리해야 하는 데이터의 수량입니다. SQL Server 행을 일찍 필터링하여 나중에 처리해야 하는 행 수를 줄여 쿼리 성능을 향상시킬 수 있습니다.

SQL Server 2008년 이전에 릴리스된 SQL Server 버전에서 전체 텍스트 검색 엔진은 검색 용어와 일치하는 모든 행을 반환한 다음 관계형 엔진이 필터를 적용합니다. 이 동작은 2008년 SQL Server, SQL Server 2008 R2 및 2012년 SQL Server 개선되었습니다. 그러나 전체 텍스트 검색 인덱스는 데이터베이스 인덱스와 다르게 구성되므로 이러한 개선 사항을 사용하기가 어렵습니다. 또한 전체 텍스트 검색 엔진과 관계형 엔진은 다르게 작동합니다. 따라서 이 문서에서 설명하는 메서드는 TVF(Table-Valued 함수)를 사용하여 행을 조기에 필터링하고 나중에 처리해야 하는 행 수를 줄입니다.

예를 들어 다음 쿼리 계획은 검색 문자열과 일치하는 131051 행을 CONTAINS 반환합니다. 또한 계획의 조인 연산자는 인덱스 검색을 사용하여 추가 필터링을 수행합니다.

Rows StmtText
-------------------- ----------------------------------------------------------------------------------------
1167 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate> '08/05/2019'

1167 |--Merge Join(Left Semi Join, MERGE:([FTSdb].[dbo].[fttest].[ID])=(FulltextMatch.[docid]), RESIDUA
5858 |--Sort(ORDER BY:([FTSdb].[dbo].[fttest].[ID] ASC))
5858 | |--Clustered Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[clidx1]), SEEK:([FTSdb].[
131051 |--Table-valued function

그러나 쿼리에 전체 텍스트 고유 인덱스 키 열이 조건자로 포함된 경우 전체 텍스트 검색 엔진은 조건자를 사용하여 전체 텍스트 수준에서 결과를 필터링할 수 있습니다. 이 경우 TVF는 추가 필터링을 적용하기 전에 훨씬 적은 양의 데이터를 반환합니다. 예를 들어 다음 쿼리는 c2 조건과 일치해야 하는 5개의 값을 지정하고 TVF는 5개 값과 일치하는 결과만 반환합니다.

Rows StmtText
-------------------------------------------------------------------------------------------------------------------------------------------
5 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate > '08/05/2019' AND ID IN ( 654051, 644051, 649106, 465, 105)

5 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([FTSdb].[dbo].[fttest].[ID]))
5 |--Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[idx1]), SEEK:([FTSdb].[dbo].[fttest].[ID]=(105) OR ...
5 |--Table-valued function

고유 인덱스 키에서 사용하는 값을 푸시다운하는 전체 텍스트 검색 엔진의 기능은 다음 메서드의 기초입니다.

조건자가 데이터 형식 열을 포함하는 DateTime 경우 이 조건자와 일치하는 행만 반환되도록 고유 인덱스 키 열에 날짜 정보를 포함할 수 있습니다. 이렇게 하려면 키 열에 날짜 정보를 논리적으로 통합해야 합니다. 그러나 쿼리를 사용하는 키 열 데이터 형식 및 애플리케이션을 변경해야 할 수도 있습니다.

메서드를 구현하려면 전체 텍스트 고유 키 ID 의 데이터 형식을 로 변경합니다 BIGINT. 키 ID의 처음 4바이트는 날짜 열에서 연도, 월 및 날짜 값을 캡처하고 마지막 4바이트는 동일하게 유지됩니다. 예를 들어 키 ID의 첫 번째 바이트는 연도를 참조할 수 있고, 다음 바이트는 월을 참조할 수 있으며, 마지막 2바이트는 날짜를 참조할 수 있습니다. 애플리케이션은 이 데이터 형식 변경을 수용해야 합니다.

그런 다음 범위 조건자를 키 ID의 조건자로 변환합니다. 예를 들어 범위 조건자를 x < CDate < y 조건자로 변환할 (x*2^32 < ID < y*2^32) 수 있습니다. 번역된 조건자는 전체 텍스트 키에 대한 조건자이므로 조건자는 전체 텍스트 스트리밍 Table-Valued 함수(STVF)로 푸시다운됩니다. 이 동작은 날짜 범위 내에서 검색을 효과적으로 수행합니다.