SQL Server'de tam metin sorgularının performansını geliştirme

Bu makalede, SQL Server'de tam metin koşullarını kullanan sorguların performansını artırmaya yönelik bir yöntem sağlanır.

Orijinal ürün sürümü: 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
Özgün KB numarası: 2549443

Özet

Bu makalede, tam metin arama koşullarını (ve gibi CONTAINSCONTAINSTABLE) kullanan ve verileri de filtreleyen Microsoft SQL Server sorgularının performansını iyileştirmeye yönelik bir yöntem açıklanır. Örneğin, bu yöntem aşağıdaki sorgunun performansını artırır:

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

Bu yöntem sorguyu, tablo şemasını ve tam metin dizinini, tam metin arama altyapısının sonuçları ilişkisel altyapıya gönderilmeden önce filtreleyebilecek şekilde tasarlamanıza olanak tanır. Bu nedenle ilişkisel altyapının büyük bir veri kümesini filtrelemesi gerekmez.

Daha fazla bilgi

Tam metin arama sorgusu oluşturduğunuzda, sorgunun performansını etkileyen temel faktör, kalan veriler ilişkisel altyapıya gönderilmeden önce tam metin arama altyapısının işlemesi gereken veri miktarıdır. SQL Server, daha sonra işlenmesi gereken satır sayısını azaltmak için satırları erken filtreleyerek sorgunun performansını geliştirebilirsiniz.

SQL Server 2008'den önce yayımlanan SQL Server sürümlerinde, tam metin arama altyapısı bir arama terimiyle eşleşen tüm satırları döndürür ve ilişkisel altyapı tüm filtreleri uygular. Bu davranışta yapılan iyileştirmeler SQL Server 2008, SQL Server 2008 R2 ve SQL Server 2012'de yapılmıştır. Ancak, tam metin arama dizinleri veritabanı dizinlerinden farklı düzende düzenleniyor olduğundan bu geliştirmeleri kullanmak zordur. Ayrıca, tam metin arama altyapısı ve ilişkisel altyapı farklı çalışır. Bu nedenle, bu makalede açıklanan yöntem, satırları erken filtrelemek ve daha sonra işlenmesi gereken satır sayısını azaltmak için Table-Valued İşlevini (TVF) kullanır.

Örneğin, aşağıdaki sorgu planı bir CONTAINS arama dizesiyle eşleşen 131051 satır döndürür. Ayrıca, plandaki bir join işleci dizin araması kullanarak ek filtreleme gerçekleştirir.

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

Ancak, sorgu koşul olarak tam metin benzersiz dizin anahtarı sütununu içeriyorsa, tam metin arama altyapısı sonuçları tam metin düzeyinde filtrelemek için koşulu kullanabilir. Bu durumda TVF, ek filtrelemenin uygulanması gerekmeden önce çok daha az miktarda veri döndürür. Örneğin, aşağıdaki sorgu c2 koşuluyla eşleşmesi gereken beş değer belirtir ve TVF yalnızca beş değerle eşleşen sonuçları döndürür:

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

Tam metin arama altyapısının benzersiz dizin anahtarı tarafından kullanılan değerleri aşağı gönderme özelliği aşağıdaki yöntemin temelini oluşturur.

Koşul bir DateTime veri türü sütunu içeriyorsa, benzersiz dizin anahtarı sütununa tarih bilgilerini ekleyebilirsiniz, böylece yalnızca bu koşulla eşleşen satırlar döndürülür. Bunu yapmak için tarih bilgilerini anahtar sütununa mantıksal olarak dahil etmeniz gerekir. Ancak, anahtar sütun veri türünü ve sorguyu kullanan uygulamaları da değiştirmeniz gerekebilir.

yöntemini uygulamak için, tam metin benzersiz anahtarının ID veri türünü olarak BIGINTdeğiştirin. Anahtar kimliğinin ilk 4 bayt değeri tarih sütunundaki yıl, ay ve tarih değerlerini yakalar ve son 4 bayt aynı kalır. Örneğin, anahtar kimliğinin ilk baytı yıla, sonraki bayt aya, son 2 bayt ise tarihe başvurabilir. Uygulamanın bu veri türü değişikliğine uyum sağlaması gerekir.

Ardından, bir aralık koşulunu anahtar kimliğindeki bir koşula çevirin. Örneğin, x < CDate < y aralık koşulu koşula (x*2^32 < ID < y*2^32) çevrilebilir. Çevrilen koşul, tam metin anahtarındaki bir koşul olduğundan, koşul tam metin Akış Table-Valued İşlevleri'ne (STVF) iletilir. Bu davranış, tarih aralığındaki aramaları etkili bir şekilde gerçekleştirir.