SQL Server’da Fulltext Arama Üzerine Yeni Bir Yaklaşım – Ev Yapımı Fulltext

Merhabalar,

Uzun bir aradan sonra yine farklı bir çalışma ile birlikteyiz. Hızlıca konuya girmek istiyorum.

Bildiğiniz gibi SQL Server, Oracle, MySQL, PosgreSQL gibi ilişkisel veritabanları (RDMS), dikeyde çalışan yapılardır. Dikeyden kasıt verinin satırlar halinde tutuluyor olmasıdır. Bu sistemler dikeyde çalışmak için optimize edilmiştir. Yani eğer iyi konfigüre edilmiş ise satır sayısının büyüklüğü çok da önemli değildir.

İyi bir indexleme operasyonu ve binary tree mantığı ile sıralı olan yapılar üzerinde her seferinde ikiye bölmek suretiyle hızlı bir şekilde aranılan sonuca ulaşılır. Aslında index konusu başlı başına bir makale konusu olduğu için burada detaylı girmek istemiyorum. Ancak şu kadarını bilmek gerekir. Index olmayan 1 milyon satırlı bir yapıda teorik olarak 1 milyon kez işlem yapılırken index olan bir yapıda her seferinde 2’ye bölmek suretiyle, teorik olarak aşağıdaki gibi 20 adımda işlem tamamlanır.

1. adım 1.000.000/2=500.000

2. adım 500.000/2=250.000

3. adım 250.000/2=125.000

4. adım 125.000/2=64.000

5. adım 64.000/2=32.000

6. adım 32.000/2=16.000

7. adım 16.000/2=8.000

8. adım 8.000/2=4.000

9. adım 4.000/2=2.000

10. adım 2.000/2=1.000

11. adım 1.000/2=500

12. adım 500/2=250

13. adım 250/2=125

14. adım 125/2=64

15. adım 64/2=32

16. adım 32/2=16

17. adım 16/2=8

18. adım 8/2=4

19. adım 4/2=2

20. adım 2/2=1

1 milyon nireeee, 20 nire. 🙂

Gelin bu dediğimizin  bir de ispatını yapalım. SQL Server’da veriler 8 KBlık page ler halinde tutulur ve bir sorgu çalıştığında onun ne kadar page okuduğu işlemi genelde performans için bir kriterdir.

Örneğin elimizde 100 milyon satırlık ekteki gibi bir tablo var. Bir ilaç veritabanının simülasyonu ve tekil kare barkodlar tutuluyor.

Şimdi bir barcode verisini sorgumuza parametre olarak gönderelim.

Set Statistics IO on okunulan page sayısını gösterir

Set Statistics Time on ise işlemci kullanımını gösterir.

Tablomuzda index yok ve tüm verileri tarıyor. Sonuca bakıyoruz.

Aşağıda görüldüğü gibi sorgunun cevap döndürmesi toplamda 12 saniye sürüyor ve yaptığı okuma sayısı 425.050 page. Yani her biri 8 KB’dan 425.050*8/1024/1024=3.2 GB’lık veri okumuş.

Oysa gelen veri 1 KB bile değil sadece bir satırlık bir barkod bilgisi. Çok dramatik değil mi? 1KB veri için 3.2 GB okuma. Ferhat’ın Şirin için dağları delmesi gibi. 🙂

Şimdi tablomuza bir index koyalım.

Ve sorgumuzu tekrar çalıştıralım.

Aşağıdaki resimde görüldüğü gibi bu sorgu index olduğu zaman 5ms de gelmiş ve toplamda 8 tane page okumuş yani 8×8=64 KB lık bir okuma yapmış. Gayet güzel bir rakam. Ferhat evine şebeke suyu çektiriyor. 🙂

İlk haliyle karşılaştırınca 425.050/8=53.131 kat daha hızlı performans. Sanırım başka herhangi bir donanımla sağlanamayacak bir performans artışı. DB sunucum yavaş deyip yeni sunucu alanlara selam olsun. 🙂

Şimdi burada index konusunu hızlıca hatırladık ve başta dediğimiz gibi bu şekilde doğru indexleme yapılırsa SQL Server için satır sayısının fazla olması çok da önemli değil. Zira satır sayım 1 milyon değil de 2 milyon olsaydı işlem sayım iki katına çıkmayacak 20+1 yani 21 olacaktı.

4 milyon için 22

8 milyon için 23

16 milyon için 24

32 milyon için 25

64 milyon için 26

128 milyon için 27

256 milyon için 28

512 milyon için 29

1 milyar için 30

İşlemde işi bitirecek. Veri boyutu 1.000 kat arttı ve 1 milyar oldu ama işlem sayısı 20 den 29’a çıktı ve 1.5 kat arttı. Teoride bile olsa gerçek rakamlar da buna çok yakın.

Dediğim gibi dikeyde durum bu. Peki yatayda işler nasıl?

“Abi dikeyi anladık da yatay ne?” dediğinizi duyar gibiyim.

Şöyle düşünün elinizde bir kitap veritabanı var. Her bir satırda kitabın pdf inin text hali var. Yani ortalama her bir kitap 20-30 bin kelime. Bu verinin içinde herhangi bir kelimeyi arama bilindik yöntemlerle yapılamaz ve bilindik yöntemlerle de indexlenemez.

Hadi gelin deneyelim.

Elimizde 1000 satırlık bir e-book veritabanı var. Gördüğünüz gibi her satırın text bilgisi bir kolonda tutuluyor.

Şimdi bilindik yöntemler ile içerisinde “Bilim” kelimesi geçen kitapları bulmaya çalışalım.

Toplamda yaklaşık 9 saniye sürdü ve 166.796 page okudu. Yani 166.796*8/1024/1024 =1.2 GB veri okudu. Ah Ferhat ahh!!!! :)))

Üstelik bu kelimenin kitabın neresinde geçtiğini de bilmiyoruz. Hadi bir de içinde Bilim ve Teknik kelimelerinin geçtiklerine bakalım. Yani hem bilim geçecek hem teknik ama aynı yerde geçmesi önemli değil.

Görüldüğü gibi süre azalmadı aksine arttı. Ayrıca okunan page sayısı da arttı.

Peki ne yapacağız?

Yöntem 1:SQL Server’ın fulltext engine altyapısını kullanabiliriz.

Şimdi bu konuya burada girmeyeceğim zira burada kendi fulltext algoritmamızı yazacağız. Yine de merak edenler buradan izleyebilir.

Yöntem 2:Yatayı dikeye çevirmek. Yani tren gibi yan yana dizilen kelimeleri parçalayıp satır satır bir tabloda tutmak. Esasen SQL Fulltext de böyle çalışıyor. Ama arka planda olanları biz görmüyoruz tabi.

Bunun için bir tablo oluşturacağız.

Sonra bu tablonun içine bütün kitapların textini kelimelere parçalayıp atmasını sağlayacağız.

Kelimelere parçalamak için bir fonksiyona ihtiyacımız var. Adı dbo.splitWithSeq() bir string ifadeyi bir ayıraca göre parçalayıp sıra numarası ile birlikte tablo döndürüyor. Burada biz kelimeleri parçalayacağımız için ayracımız ‘ ‘ yani boşluk.

Bu fonksiyonun scripti aşağıda.

create FUNCTION [dbo].[SplitWithSeq](@String varchar(MAX), @Delimiter char(1))    

returns @temptable TABLE (items varchar(MAX), seq int identity(1,1))    

as    

begin    

       INSERT INTO @temptable (items) SELECT VALUE FROM string_split(@String,@Delimiter)

return    

end

Şimdi de kelimelere parçalayacak procedure ümüzü paylaşayım. Bu da her seferinde indexlenmeyen 10 kaydı kelimelerine ayırıp içeri atıyor. Burada 10 rakamı size kalmış ama biraz uzun süreceği için ben her seferinde 10’ar 10’ar çalıştırmayı uygun gördüm.

Şimdi procedure ümüzü çalıştıralım.

create PROC [dbo].[FILLWORDS]

AS

–TRUNCATE TABLE BOOK_WORDS

DECLARE @ID AS INT

DECLARE CRS CURSOR FOR SELECT TOP 10 ID FROM BOOKS WHERE ID NOT IN (SELECT BOOKID FROM BOOK_WORDS)

OPEN CRS

FETCH NEXT FROM CRS INTO @ID

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO BOOK_WORDS

 select    B.ID AS BOOKID,

 LTRIM(RTRIM(BB.items)) as WORD,BB.seq AS SEQID

  from BOOKS B

 CROSS APPLY    dbo.SplitWithSeq(LTRIM(RTRIM(FULLTEXT)),’ ‘)  BB

 WHERE B.ID =@ID  AND

   LEN(BB.items)<50

 UPDATE BOOK_WORDS SET WORD=LTRIM(RTRIM(WORD)) WHERE BOOKID=@ID

 UPDATE BOOK_WORDS SET WORD=REPLACE(WORD,’.’,”) WHERE BOOKID=@ID

 UPDATE BOOK_WORDS SET WORD=REPLACE(WORD,’,’,”) WHERE BOOKID=@ID

 UPDATE BOOK_WORDS SET WORD=REPLACE(WORD,’!’,”) WHERE BOOKID=@ID

 UPDATE BOOK_WORDS SET WORD=REPLACE(WORD,’?’,”) WHERE BOOKID=@ID

 UPDATE BOOK_WORDS SET WORD=REPLACE(WORD,’;’,”) WHERE BOOKID=@ID

UPDATE BOOK_WORDS SET WORD=REPLACE(WORD,'”‘,”) WHERE BOOKID=@ID

UPDATE BOOK_WORDS SET WORD=REPLACE(WORD,’• ‘,”) WHERE BOOKID=@ID

FETCH NEXT FROM CRS INTO @ID

END

CLOSE CRS

DEALLOCATE CRS

10 kitap ben de yaklaşık 2 dakika sürdü. Bunu 100 kez çalıştırmak gerekiyor 1.000 satır için.

Bunun için exec [FILLWORDS] komutunu alt alta yapıştırabilirsiniz. Ya da 10 rakamını biraz arttırabilirsiniz.

Sonuç itibariyle bir kez yapılacak bir işlem ve burada yeni eklenen kayıtlar için de bu işlem bir job’ a bağlanılabilir.

Şimdi gelelim bu tabloda kelimelere index eklemeye. Aşağıdaki script ile word kolonuna içerisinde bookID ve SeqID alanlarını da içerecek bir index ekliyoruz.

CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[BOOK_WORDS]

(

       [WORD] ASC

)

INCLUDE ([BOOKID],

       [SEQID])

GO

Veeee geldik en vurucu yere. Artık kitap datası üzerinde arama yapacağız. İşte bunun için Search isimli çok özel bir procedure yazdım. Ah Ferhat ahhhh! :)))

create PROC [dbo].[SEARCH](@STRINGID AS VARCHAR(1000))

AS

BEGIN

DECLARE @SQL AS NVARCHAR(MAX)=”

DECLARE @STR AS VARCHAR(100)

DECLARE @I AS INT=1

DECLARE CRS CURSOR FOR SELECT items FROM DBO.SPlitWithSeq(@STRINGID,’ ‘)

OPEN CRS

FETCH NEXT FROM CRS INTO @STR

WHILE @@FETCH_STATUS=0

BEGIN

       IF @I=1

       BEGIN

             SET @SQL=’SELECT ID,FULLTEXT FROM BOOKS WHERE ID IN (SELECT BOOKID FROM BOOK_WORDS WHERE  WORD LIKE ”’+@STR+’%”’

       END

       ELSE

       BEGIN

             SET @SQL=@SQL+’ AND BOOKID IN (SELECT BOOKID FROM BOOK_WORDS WHERE WORD LIKE ”’+@STR+’%”)’

       END

       SET @I=@I+1

       FETCH NEXT FROM CRS INTO @STR

END

 SET @SQL=@SQL+’)’

CLOSE CRS

DEALLOCATE CRS

EXEC SP_EXECUTESQL     @SQL

END

Çok özel bir procedure çünkü içine birden fazla kelime alıyor ve dinamik bir şekilde arıyor. Üstelik hangi sırada yazarsanız yazın arayabiliyor.

Veee sonuç

CPU süresi 85 ms. Öncekini hatırlayalım 9 sn idi.

Ayrıca okunan page sayısı 25 =25*8/1024=2 MB. Önceki 166.796=166.796*8/1024/1024=1.2 GB idi.

Şimdi de Bilim Teknik kelimesini arayalım.

CPU süresi 112 ms. Öncekini hatırlayalım 9 sn idi.

Şimdi de daha farklı bir dataset üzerinde arama yapalım. 4 milyon satırlık bir e ticaret datası üzerinde ‘prima bebek bezi 72’ aramasını yapalım. Standard sql bilgimiz ile bu arama aşağıdaki gibi yapılır.

Süre 9 sn, okunan page 57.914=57.914*8/1024/1024=500 MB’lık okuma.

Şimdi bizim sistemi uygulayalım.

Gördüğünüz gibi sonuç farketmedi.

Şimdi de performansa bir bakalım.

Toplam okuma 152+56=208= yaklaşık 1 MB

Toplam süre ise 202 ms.

Daha da hızlandırılabilinir mi? Kesinlikle!

Bu makaleyi okuyup çeşitli text search platformları kullanan arkadaşlar olacaktır. Burada amacın onlarla yarıştırma ya da karşılaştırma olmadığını tamamen bu işlerin gerçekte temel anlamda nasıl döndüğünü göstermektir. Ayrıca kendi yazdığınız bir algoritmayı istediğiniz gibi evirip çevirebilirsiniz de.

Umarım full text ve text searching olayına farklı bir bakış açısı kazandırmışımdır. Başka bir makalede görüşmek dileğiyle.

Sağlıcakla…