Допустим, что вы тоже совершили ошибку и вместо того, чтобы использовать, скажем, Lucene или Solr стали пользоваться полнотекстовым поиском в SQL Server 2008. Тогда, возможно, вам пригодится небольшой хинт, который мы сейчас имплементируем на работе и о котором я хочу рассказать.

Для примера рассмотрим такую ситуацию: где-то в нашей системе у нас есть документы, состоящие из набора некоторых свойств, каждое из которых имеет значение. Для простоты – текстовое.
Получается табличка примерно такого вида:

Понятно, что в одном документе одно свойство может иметь только одно значение, то есть, комбинация DocumentId и PropertyId уникальна.

Мы хотим осуществлять полнотекстовый поиск в значениях заданных свойств этих документов, соответственно, получая список документов “наружу”. Что дальше делать с этим списком – задача десятая, но для забивания гвоздя в крышку допустим, что кроме полнотекстового поиска мы хотим делать ещё какую-то фильтрацию документов по каким-то иным критериям, или группировку по автору, или ещё что-то в том же запросе делать. Трогать этот аспект не будем, просто будем иметь в виду и пометим звёздочкой: *.

Для примера, нам нужно найти все документа, которые содержат @criteria1 в свойстве @propId1 и @criteria2 в свойстве @propId2.
Запрос в этом случае будет содержать примерно такое (точно я сказать не могу, так как у меня сейчас нет возможности полнотекстового поиска под рукой, но смысл сохраняется: нам нужно связывать таблицы Documents и Values и осуществлять полнотекстовый поиск с учётом PropertyId. Будем считать это псевдокодом):

В других случаях нам могут понадобиться выражения типа “NOT contains”, их связки не только по AND, но и по OR, но идея та же.

Кроме того, что полнотекстовый поиск в SQL Server 2008 штука довольно тормозная (результаты тестирования говорят, что в “Динали” он в сотни раз быстрее, но использовать “Динали” в production сейчас рискнут даже не все те, кто имеют на это право), тут есть и другие очевидные проблемы: дополнительные join’ы к таблице Values и необходимость лазать в кластерный индекс Values для того, чтобы получить значения PropertyId и DocumentId.
Последнее – самое неприятное, особенно в случае негативного условия (NOT contains), которое с большой вероятностью будет возвращать много-много результатов из полнотекстового поиска (если не вообще все), что превращает лазание в кластерный индекс в банальный scan, увеличивает количество read’ов и вообще приносит много хлопот.

Проблемы бы не было, если бы могли попросить полнотекстовый поиск вернуть нам DocumentId и PropertyId для каждого найденного значения, но, к сожалению, SQL Server не позволяет “привязывать” к документу полнотекстового поиска никаких метаданных, и всё, что он может нам вернуть – это некий ключ [Key], которым в нашем случае является поле Values.Id.
Композитного ключа (связки DocumentId и PropertyId) полнотекстовый поиск тоже не поддерживает, так как Key должен быть одной уникальной колонкой.

Решить проблему можно самим создав такой композитный ключ, который хранился бы в одном поле, был бы уникальным, но при этом содержал бы оба значения DocumentId и PropertyId.
Самый эффективный способ это сделать, пожалуй, это объявить ещё одну колонку типа bigint (справка: bigint содержит 8 байт информации, int – 4 байта), 4 байта в котором будет занимать DocumentId, а другие 4 байта – PropertyId.
Примерно так:

Если теперь наложить уникальный индекс на поле DataId (а оно уникально потому, что связка DocumentId и PropertyId уникальна), то можно построить полнотекстовый индекс на таблице Values с этим полем в качестве ключа.

Теперь мы можем прямо из ключа результата полнотекстового поиска получать оба интересующие нас значения DocumentId и PropertyId, что избавляет нас от необходимости делать лишние join’ы к таблице Values. Мы можем переписать запрос так, чтобы вообще не обращаться к этой таблице:

Лучше, но всё ещё не идеально.
Дело в том, что ContainsTable всё ещё возвращает нам полный набор ключей, удовлетворяющих критерию полнотекстового поиска, а уже после этого мы фильтруем этот набор ключей, производя над каждым из них нехитрую математическую операцию.
Это отлично видно на плане исполнения запроса (который я тут, к сожалению, показать не могу, так как пишу всё это без имеющейся в наличии базы), там из кубика полнотекстового поиска выходит жирная стрелка с немереным количеством результатов и идёт к кубику под названием “фильтр”, из которого стрелка выходит уже гораздо тоньше.

Это можно исправить “инвертировав” операцию с ключом:

Константа 4294967296 здесь – это тот же самый 0x100000000, но SQL Server почему-то отказывается принимать его здесь в HEX-виде, несмотря на то, что он это успешно делал в случае формулы для вычисляемой колонки. Не знаю, что это за баг, но в данном случае требуется либо приведение всего к HEX, либо приведение константы к DEC, что и было сделано.

Операция получения значений из полнотекстового поиска теперь сводится фактически не к поиску всего, что удовлетворяет условию с последующей фильтрацией ключа, а, наоборот, к поиску по ключу и последующей проверкой на удовлетворение условию! Взятие по ключу из хранилища вида key-value – самая быстрая операция, которую здесь только можно придумать.

Кубик фильтрации из плана исполнения запроса таким образом исчезает, а из кубика полнотекстового поиска выходит красивенькая тоненькая стрелочка, символизирующая то, что полнотекстовый поиск возвращает нам только то, что нам нужно и ничего более.

Итак, что мы получили и “какой кровью”:

1. Мы создали одно дополнительное вычисляемое поле (DataId) в таблице Values.
   Это никак не затрагивает ни приложение, которое работает с таблицей, ни любые другие операции, которые мы производим с этой таблицей в базе данных.
2. Мы можем вообще выкинуть поле Id из таблицы Values за ненадобностью.
3. Мы создали уникальный индекс на поле DataId и построили “вокруг него” полнотекстовый поиск.
   Тоже не имеет никаких побочных эффектов.
4. Мы переписали запрос так, что избавились не только от “лишних” join’ов и лазания в кластерный индекс таблицы Values, но и вообще от необходимости её трогать.
   Фактически мы получили возможность динамически формировать такие запросы, набирая критерии для полнотекстового поиска в значениях свойств соответствии с требованиями без столь существенной деградации производительности.
5. Мы превратили “Динали”, скорость полнотекстового поиска которого в сотню раз выше, в своего союзника.
   В будущем, когда свершится переезд на “Динали”, то, что мы сделали, будет работать ещё лучше и быстрее. “Динали” не помог бы в случае начального варианта, так как лазать по таблицам ему бы пришлось точно так же.
6. Запрос стал попроще
   Он стал не только проще читаться (что, конечно, субъективно), но и проще анализироваться SQL Server’ом для подготовки плана исполнения. А ведь в случае большого количества критериев это может сыграть определённую роль. Плюс тут я отошлю вас к звёздочке (*), которую мы решили не трогать в начале этого постинга.

Надеюсь, кому-то будет полезно