Full-text Search

Full-text Search varchar(max) gibi büyük text içeren kolonlarda arama yapmak için kullanılır. Aynı işlemi LIKE ile de yapabilirsiniz fakat LIKE performansı bu tip büyük text verisi içeren kolonlarda çok yavaş çalışacaktır.

Bununla ilgili başıma gelen bir olayı anlatayım. Bir gün bir uygulama ekibi veritabanı sunucusu almak için bir girişimde bulunduğunu bunun için ne gibi özellikler olması gerektiğini bana danışmak için yanıma geldiler. Bende performansını kontrol edeyim daha sonra gerçekten ihtiyacınız varsa sunucu özellikleri için yardımcı olayım dedim.  Sunucudaki performans kriterlerini incelediğimde bütün sorguların varchar(max) kolonlara like şeklinde geldiğini gördüm ve cpu’ları sürekli %90’ın üstündeydi. Arama yapılan kolonlara full-text index koydum ve sorguyu da like yerine full-text arama fonksiyonu olan contains kullanarak yapmalarını istedim. Ve bu değişiklik sonucunda cpu kullanımı %10 civarına düştü ve uygulama performansı yaklaşık 20 kat arttı. Tabi bunun sonucunda sunucu almaktan vazgeçtiler. Gördüğünüz gibi full-text search performansı 20 kat, belki bazı uygulamarda daha fazla artırabilir.

Peki full-text search’ü nasıl gerçekleştiriyoruz?

İlk olarak aşağıdaki script yardımıyla test isimli veritabanında full text search testleri yapacağımız tabloyu oluşturup içine bir kayıt ekleyelim.

Not: Full Text index tanımlamak istediğiniz tabloda unique bir kolon olmalıdır.

USE [test]
GO
CREATE TABLE [dbo].[FullText_Deneme](
[ID] [int] NOT NULL,
[Dokuman] [varchar](max) NULL,
 CONSTRAINT [PK_FullText_Deneme] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO [dbo].[FullText_Deneme]
           ([ID]
           ,[Dokuman])
     VALUES
           (1,'SQL Server DBA olarak Nurullah ÇAKIR ı tavsiye eder misiniz?')
GO

Full-Text Catalog Oluşturmak:

Daha sonra aşağıdaki gibi veritabanının altından Storage->Full Text Catalogs kısmından New Full-Text Catalog diyoruz.

Açılan ekranda aşağıdaki gibi kataloğumuza bir isim ve bir owner veriyoruz(owner vermek için sağ taraftaki … ‘ya tıklayarak browse diyoruz ve bir user veya role seçiyoruz) ve ok diyoruz.

Sensitive ya da Insensitive seçeceğinize siz karar vermelisiniz. Sensitive seçerseniz ‘a’ = ‘á’ aynı anlama gelir.

Kataloğumuzu oluşturduktan sonra full text index’imizi oluşturmamız gerekiyor.

Full-Text Index Oluşturmak:

Makalenin başında oluşturduğumuz tablomuza sağ tıklayarak Full-Text index’i seçerek Define Full-Text Index…’e tıklıyoruz.

Karşımıza gelen ekranda next diyerek ilerliyoruz ve aşağıdaki gibi bir ekran geliyor. Bu ekranda tablodaki bir unique index’i seçmemiz gerekiyor. Verilere bu unique index üzerinden erişilecek. Next diyerek ilerliyoruz.

Bir sonraki ekranda tablomuzda char, varchar, nchar, nvarchar, text, ntext, image tiplerindeki kolonlar listelenir. Hangi kolona full text index koymak istiyorsak o kolonu seçiyoruz ve dilimiz türkçe olduğu için Turkish’i seçiyoruz. Type column sadece full text index koyacağımız kolon varbinary(MAX) ya da image ise aktif olacaktır. Bu tip resim dosyalarından arama yapılamayacağı için bu kolonu ifade eden referans verecek başka bir kolon’un Type Colum’dan seçilmesi gerekir. Next diyerek ilerliyoruz.

Bir sonraki ekranda tablodaki değişikliklerin full-text index’e  otomatik aktarılıp aktarılmayacağını seçiyoruz. Automatically seçerek bu işlemin otomatik yapılmasını tercih ediyoruz. Tercihimizi bu şekilde yaptığımız için index oluştuğundan otomatik olarak tablodaki veriler index’e aktarılacak. Bu işleme full population deniyor. Next diyerek ilerliyoruz.

Karşımıza gelen ekranda,

Select full-text catalog: kısmından biraz önce oluşturmuş olduğumu full text catalog’u seçiyoruz.

Select index filegroup: kısmından index’in veritabanınınş hangi filegroup’un içersine koyacağımızı belirliyoruz. Bizim veritabanımız test veritabanı olduğu için sadece PRIMARY filegroup var. Production veritabanlarında genelde index’ler için başka bir filegroup oluşturulmasını tavsiye ederim. “Veritabanı Oluşturmak Deyip Geçmeyin!” isimli makalemden büyük veri içerek veritabanı oluşturmanın püf noktalarını öğrenebilirsiniz.

Select full-text stoplist: stoplist arama yapılmayacak kelimelerin listesini içerir. Next demeden önce aşağıdaki şekilde Full Text Stoplists’e sağ tıklayarak New Full-Text Stoplist… diyoruz.

Karşımıza gelen ekranda Full-text stoplist name’den stop list’imize bir isim veriyoruz ve Owner’a da yandaki kutucuğu tıklayıp browse diyerek bir owner veriyoruz. Create an empty stoplist diyerek işlemi tamamlıyoruz.

Oluşan stop list’e sağ tıklayarak properties diyoruz.

Açılan ekranda Action kısmından Add stop word diyerek stop list’imize aramalarda çıkmasını istemediğimiz bir kelime yazıyoruz. Örneğimizin anlamlı olması açısından makalenin başında tablomuza eklediğimiz kaydın içinde geçen Server kelimesini stop word olarak ekledim. Server kelimesi bu stoplist’i kullanırsak aramalarda çıkmayacak.

Full-text language olarak da dilimiz türkçe olduğu için Turkish’i seçiyorum.

Tekrar full text index oluşturma ekranına geldiğimizde Select full-text stoplist kısmından biraz önce oluşturduğumuz stoplist’i görebilirsiniz. Göremiyorsanız önce back’e sonra tekrar next’e tıklayın. Stoplist’imizide seçtikten sonra next diyerek ilerliyoruz.

Gelen sonraki ekranlarda hiçbir değişiklik yapmadan next ve finish diyerek işlemimizi tamamlıyoruz.

Full-Text Index’ten Arama Yapmak:

Contains,ContainsTable,Freetext,FreetextTable fonksiyonlarını kullanarak arama yapılabilir.

Contains:

Kelimenin birebir aynısını full text index oluşturduğumuz kolonda arar. Aşağıdaki şekilde çalışır.

2 parametre alır. İlk parametre full text index’in olduğu kolonun adı. Diğer parametre ise arama yapılacak kelime ya da kelimeler.

Aşağıdaki script ile sadece “Nurullah” kelimesini arıyoruz. Tabloda bu kelime olduğu için sonuç dönecektir.

select * from dbo.FullText_Deneme where contains(Dokuman,'Nurullah')

Aşağıdaki script ile “Nurullah ÇAKIR” ‘ı Nurullah ve ÇAKIR’ın arasında bir boşluk olacak şekilde arıyoruz.

select * from dbo.FullText_Deneme where contains(Dokuman,'"Nurullah ÇAKIR"')

ContainsTable:

Aranan kelimenin rank’ını(derecesini) da gösterir. Aşağıdaki şekilde kullanabilirsiniz. Parametreleri tablo,full-text index’li kolon ve arama yapılacak kelimedir.

select * from dbo.FullText_Deneme fd
inner join  containstable(dbo.FullText_Deneme,Dokuman,'Nurullah') as ct
on fd.ID = ct.[key]

Freetext:

Contains fonksiyonu ile aynı şekilde çalışır. Contains’te aradığınız kelimenin aynısını bulurken FREETEXT fonksiyonunda farklı halleride sonuç olarak gelecektir. Örneğin tablodaki bir satırda “ÇAKIR gidiyordu NURULLAH” gibi bir ifade olsaydı aşağıdaki sorgu ile sonuç olarak karşımıza çıkardı.

select * from dbo.FullText_Deneme where freetext(Dokuman,'"Nurullah ÇAKIR"')

Yada “Nurullah” ile “Nurullahs” kelimelerini aynı kabul edecek ve sonuç olarak getirecekti.

FreetextTable:

ContainsTable gibi rank’ını(derecesini) da getirir. ContainsTable’dan farkı ise “ÇAKIR” ve “ÇAKIRs” kelimelerini aynı kabul edeceği için rank yüksek çıkabilir.

Bu fonksiyonların çok daha detayları var. Ben bu makalede daha fazla detaya girmeyeceğim.