Wierzyć się nie chce, że przez ładnych kilka lat całkiem poważny moim zdaniem błąd w wyszukiwaniu pełnotekstowym SQL Servera nie został przez nikogo dostrzeżony, a raczej zgłoszony do Microsoftu – bo być może ktoś zauważył, że Full-Text Search zwraca czasem różne zestawy wyników, ale nie miał możliwości, czasu lub chęci by problem eskalować. My zadaliśmy sobie ten trud, bo wyszukiwarka jest bardzo ważnym elementem wszystkich naszych witryn i powinna działać dobrze. Okazało się, że faktycznie obsługa polskiego języka w FTS jest zrobiona po prostu źle.
Czasy, gdy do wyszukiwania w bazach danych używało się predykatu LIKE chyba już minęły – tam, gdzie ruch w wyszukiwarce liczy się w zapytaniach na sekundę, a tabele zawierają miliony rekordów zwykły LIKE po prostu się nie sprawdza. Również sama precyzja wyników nie jest w takim scenariuszu zadowalająca. Po to wymyślono wyszukiwanie pełnotekstowe, by całą treść zaindeksować, a wyszukiwanie przebiegało błyskawicznie i na dodatek bardziej „inteligentnie”. W MySQL, z którego korzystaliśmy w dobrychprogramach przez całe lata wyszukiwania pełnotekstowego najpierw nie było, a później jak już było, to prezentowało się bardzo blado. Wraz z niedawną migracją na ASP.NET nie było już jednak żadnych przeszkód, by zaprząc do pracy wbudowany w SQL Server i rozwijany od dawna Full-Text Search, a od kilkunastu tygodni bardziej intenstywnie pracujemy nad tym, by wyniki były bardziej adekwatne do oczekiwań pytających.
Full-Text Search w SQL Server 2008 (skupmy się na tej wersji, bo tej właśnie używamy) oferuje dwa predykaty służące do odpytywania indeksu FTS. Pierwszy z nich, CONTAINS, bardzo przypomina LIKE, bo wykonuje dopasowanie wyrazu lub jego prefiksu (lub kilku wyrazów/prefiksów). Tak na marginesie, od lat klienci na całym świecie żądają od Microsoftu wprowadzenia możliwości wyszukiwania po sufiksach (czyli wpisując „office” dostajemy też „openoffice”), ale funkcji tej nadal nie ma i nie wiadomo, czy będzie. Obejściem problemu jest trzymanie w oddzielnej kolumnie tego samego tekstu, tyle że pisanego od prawej do lewej (pomaga w tym wbudowana funkcja REVERSE), dzięki czemu szukanie po prefiksie daje nam efekt jakbyśmy szukali po sufiksie. Generalnie CONTAINS dość dobrze sprawdza się do dokładnego wyszukiwania bardzo konkretnych wyrazów czy krótkich fraz. Do wyszukiwania kontekstowego, w obszernych tekstach bardziej nadaje się predykat FREETEXT. Szuka on wystąpień całych wyrazów, a ponadto umożliwia szukanie form fleksyjnych danego wyrazu czy nawet jego synonimów (jeśli wcześniej skonfigurujemy tezaurus). Szukamy frazy „zarządzanie siecią” i dostajemy dokument „o zarządzaniu sieciami” – całkiem sensowne, ani LIKE ani CONTAINS nie zwróci nam takich wyników i to w tak szybkim czasie.
Niestety gdy zaczęliśmy implementować FREETEXT pojawiły się pewne problemy. SQL Server raz zwracał pełen zestaw wyników, czyli wystąpienia danego słowa i jego wszystkich form fleksyjnych, a raz po prostu ignorował formy fleksyjne i szukał tylko ściśle tej formy, jaka została wpisana w wyszukiwarce. Trwające kilka dni intensywne próby debugowania problemów spełzły na niczym, a że akurat była okazja, zgłosiliśmy problem bezpośrednio do pomocy technicznej Microsoftu. Problem szybko został eskalowany do grupy produktowej SQL Servera i przez trzy tygodnie kilku inżynierów dość intensywnie pracowało (także zdalnie na naszych serwerach) nad ustaleniem przyczyny.
Dopiero dzisiaj otrzymałem e-mail, w którym ostatecznie uznano problem za zbadany – to znaczy udało się opracować obiektywną metodę jego reprodukcji. Za przyczynę wstępnie uznano polski word-breaker, komponent odpowiedzialny za przetwarzanie i przygotowywanie wpisanej frazy do wyszukiwania. Dzięki poleceniu sys.dm_fts_parser udało się ustalić, że czasami word-breaker po prostu nie generuje form fleksyjnych i dlatego nie są one wyszukiwane w indeksie. Co ciekawe, obsługa języka polskiego w Full-Text Search nie jest realizowana przez Microsoft – wraz z SQL Serverem dostarczane są biblioteki tworzone przez polską firmę TiP i trzeba je ręcznie włączyć w rejestrze postępując według instrukcji na stronach MSDN. Podobnie jest z językiem duńskim i tureckim, resztę języków robi już bezpośrednio Microsoft.
Jak zreprodukować problem? Trzeba utworzyć sobie bazę danych z tabelą i jakimś przykładowym tekstem (trzeba oczywiście zadbać o umieszczenie odpowiednich form fleksyjnych - dla przykładu niech będzie to wyraz "system" i formy "systemowi", "systemów" itp.), a następnie załączyć na tej tabeli FTS z polskim językiem na domyślnych ustawieniach. Przed testem należy zrestartować SQL Server i na takim czystym środowisku odpalić polecenie:
USE Baza
GO
DECLARE @i int
SET @i=1
WHILE @i < 10000
BEGIN
SELECT * FROM dbo.Tabela WHERE FREETEXT(Kolumna, 'system')
SET @i=@i+1
END
GO
Czasami już wtedy widać rezultaty w postaci nieprawidłowych wyników - zwracane są tylko wiersze zawierające "system", w wynikach nie ma wyrazów "systemowi", "systemów" itp. Jeśli wszystko jest OK, to w trakcie wykonywania tej pętli w drugim oknie edytora należy wykonać to samo zapytanie, tyle że już bez instrukcji WHILE. W tym momencie wyniki zwracane w jednym lub drugim oknie na pewno już będą nieprawidłowe. Stan taki utrzymuje się jakiś czas, by po chwili znów zwracane wyniki były poprawne. Znowu jednak wystarczy kilka kolejnych zapytań, by otrzymywane rezultaty były niewłaściwe.
Brzmi dość magicznie i trochę to trwało, zanim inżynierowie z pomocy technicznej uwierzyli :-) Zobaczyć znaczy jednak uwierzyć i teraz czekam na kontakt, co dalej – błąd został potwierdzony, tylko rozwiązania póki co brak... Miejmy nadzieję, że uda się coś na ten problem poradzić. Jak tylko otrzymam jakieś konkretne informacje od Microsoftu, dam oczywiście znać :-)