Операторы contains и freetext позволяют быстро найти все строки таблицы, содержащие определенные слова или их словоформы, в различных комбинациях. Предположим, что такую задачу требуется решить не для одной фразы, а для каждой строки в дополнительной таблице. Здесь мы наталкиваемся на ограничение MS SQL. Оказывается нельзя использовать полнотекстовые операторы в сочетании с оператором cross apply. Существующие методы, позволяющие обойти это препятствие, не достаточно эффективны. Рассмотрим такой пример:
Мы создали таблицу и полнотекстовый индекс на ее столбце с автоматическим отслеживанием изменений. Наполним таблицу тестовыми данными:
В таблицу dbo.Documents вставим для разнообразия дополнительные строки, например, перечень имен из словаря:
После окончания обновления полнотекстового индекса создадим таблицу со словами:
Если мы захотим найти все строки в dbo.Documents, содержащие слово "мир", то достаточно выполнить запрос:
Вместо этого нужно получить набор строк, у которого в первом столбце хранилось бы слово из dbo.Ptr, а во втором - идентификатор строки из dbo.Documents, содержащей слово из dbo.Ptr. Хотелось бы получить такой набор одним запросом. Но в таблице dbo.Ptr может быть много строк, перебор их в цикле может быть долгим и неудобным. Если запустить такой запрос:
, то получим синтаксическую ошибку:
Сообщение 102, уровень 15, состояние 1, строка 8
Неправильный синтаксис около конструкции "ptr".
Неудача будет и при использовании инлайновой табличной функции:
При попытке выполнить запрос, написанный выше, получаем ошибку:
Сообщение 4129, уровень 16, состояние 1, строка 1
Встроенная функция "dbo.GetTextData" не может принимать взаимосвязанные параметры или вложенные запросы, так как использует полнотекстовый оператор.
if
object_id ( N'dbo.Documents', N'U' ) is null
begin
create table dbo.Documents
(
iDocId int identity ( 1, 1 ) not null,
vcContent varchar ( max ) not null,
constraint
PK_DOcuments_iDocId primary
key clustered ( iDocId asc ) on [PRIMARY],
) on [PRIMARY]
end
go
if
not exists
(
select *
from sys.fulltext_catalogs
where name = N'docs'
)
begin
create fulltext catalog docs
on filegroup [PRIMARY]
with
accent_sensitivity =
off
authorization
dbo
end
go
if
not exists
(
select *
from sys.fulltext_indexes
where object_id = object_id ( N'dbo.Documents', N'U' )
)
begin
create fulltext index on dbo.Documents
(
vcContent language
russian )
key
index PK_DOcuments_iDocId
on Docs
with
change_tracking
= auto,
stoplist =
system
end
goМы создали таблицу и полнотекстовый индекс на ее столбце с автоматическим отслеживанием изменений. Наполним таблицу тестовыми данными:
set
nocount on
insert
into dbo.Documents ( vcContent )
values
( 'Сужба поиска'
),
( 'служба безопасности'
),
( 'службы охраны'
),
( 'Проверка
готовноти' ),
( 'Готовность
в препятствиям' ),
( 'Проверка
работоспособности' ),
( 'многочисленные
проверки' ),
( 'поставленный
диагноз' ),
( 'неверные
диагнозы' ),
( 'неверный
подход' )
go 200000
while
fulltextcatalogproperty ( 'docs', 'PopulateStatus' ) <> 0
begin
waitfor delay '00:00:03'
end
goВ таблицу dbo.Documents вставим для разнообразия дополнительные строки, например, перечень имен из словаря:
После окончания обновления полнотекстового индекса создадим таблицу со словами:
if
object_id ( N'dbo.Ptr', N'U' ) is null
begin
create table Ptr
(
vcPattern
varchar ( max ) not null
) on [PRIMARY]
end
go
insert
into dbo.Ptr ( vcPattern )
values
( 'личность'
),
( 'мир'
),
( 'имён'
),
( 'служба'
)
goЕсли мы захотим найти все строки в dbo.Documents, содержащие слово "мир", то достаточно выполнить запрос:
select
iDocId
from
dbo.Documents
where contains ( vcContent, 'мир' )Вместо этого нужно получить набор строк, у которого в первом столбце хранилось бы слово из dbo.Ptr, а во втором - идентификатор строки из dbo.Documents, содержащей слово из dbo.Ptr. Хотелось бы получить такой набор одним запросом. Но в таблице dbo.Ptr может быть много строк, перебор их в цикле может быть долгим и неудобным. Если запустить такой запрос:
select
*
from
dbo.Ptr ptr
cross
apply
(
select
iDocId
from
dbo.Documents doc
where
contains ( doc.vcContent, ptr.vcPattern )
) doc, то получим синтаксическую ошибку:
Сообщение 102, уровень 15, состояние 1, строка 8
Неправильный синтаксис около конструкции "ptr".
Неудача будет и при использовании инлайновой табличной функции:
create
function dbo.GetTextData ( @vcWord varchar ( 100 ) )
returns table
as
return
(
select *
from dbo.Documents
where contains ( vcContent, @vcWord )
)
go
select
ptr.vcPattern, wr.iDocId
from
Ptr ptr
cross
apply
dbo.GetTextData ( ptr.vcPattern ) wr
goПри попытке выполнить запрос, написанный выше, получаем ошибку:
Сообщение 4129, уровень 16, состояние 1, строка 1
Встроенная функция "dbo.GetTextData" не может принимать взаимосвязанные параметры или вложенные запросы, так как использует полнотекстовый оператор.
Можно выполнить конкатенацию строк из dbo.Ptr с разделителем or и подставить ее как аргумент полнотекстового оператора. Однако это может быть проблематично, если строк в dbo.Ptr много, к тому теряется информация о том какой строке из dbo.Ptr соответствует найденный идентификатор iDocId. Остается только написать табличную мультистейтмент функцию:
create
function dbo.GetDocData ( @vcWord varchar ( 100 ) )
returns @res table ( iDocId int )
as
begin
insert into @res ( iDocId )
select
iDocId
from
dbo.Documents
where contains ( vcContent, @vcWord )
return
end
go
Теперь можно написать такой запрос:
select
*
from
#Ptr ptr
cross
apply
dbo.GetDocData ( ptr.vcPattern )
Хочу предложить более удобный и менее ресурсоемкий вариант для решения этой задачи. Поможет clr-сборка. Вот ее код:
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
using
System.Collections;
public
partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow", DataAccess=DataAccessKind.Read)]
public static IEnumerable
GetPtr(SqlString vcContent, SqlString vcTableName, SqlString
vcColumn, SqlString vcIdCol)
{
ArrayList
arr = new ArrayList();
string
sql = @"select " +
vcIdCol.ToString() + @" from " +
vcTableName.ToString() + @" where freetext (
" + vcColumn.ToString() + @",
@vcContent )";
using(SqlConnection cn = new
SqlConnection("context
connection = true"))
{
cn.Open();
using
(SqlCommand cmd = new
SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@vcContent", vcContent);
using
(SqlDataReader dr = cmd.ExecuteReader())
{
while
(dr.Read())
{
object[] wr = new object[1];
wr[0] = (int)dr[0];
arr.Add(wr);
}
}
}
}
return
arr;
}
private static void FillRow(object obj, out int iDocId)
{
object[]
row = (object[])obj;
iDocId = (int)row[0];
}
};
Теперь развернем сборку и создадим функцию на сервере:
if
not exists
(
select *
from sys.assemblies
where name = N'TxtSearch'
)
begin
create assembly TxtSearch
authorization
dbo
from
'C:\В\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
with
permission_set =
safe
end
go
if
object_id ( N'SearchWords', N'FT' ) is null
begin
create function dbo.SearchWords ( @vcContext nvarchar ( max ), @vcTableName nvarchar ( 200 ), @vcColumn nvarchar ( 200 ), @vcIdCol nvarchar ( 200 ) )
returns
table
(
iDocId
int
)
as external name TxtSearch.UserDefinedFunctions.GetPtr
end
go
alter
function dbo.SearchWords ( @vcContext nvarchar ( max ), @vcTableName nvarchar ( 200 ), @vcColumn nvarchar ( 200 ), @vcIdCol nvarchar ( 200 ) )
returns table
(
iDocId
int
)
as
external name TxtSearch.UserDefinedFunctions.GetPtr
go
Для решения задачи можно использовать запрос:
select
*
from
Ptr ptr
cross
apply
dbo.SearchWords ( ptr.vcPattern, 'dbo.Documents', N'vcContent', N'iDocId' ) wr
Функция dbo.SearchWord более удобная, так как не надо писать отдельную функцию на каждую таблицу. Более того, можно добавить параметры и реализовать в коде clr-сборки дополнительную логику, с помощью которой можно было бы, например, учитывать расстояния между словами, использовать веса. По аналогии можно реализовать в том же классе функции для задач семантического поиска, чтобы одним запросом для набора фраз найти подходящие по смыслу документы, или для каждого из документов найти семантически значимые фразы.
Изучим скорость работы запросов. Сначала каждая из функций, dbo.SearchWords, dbo.GetDocData, показали одно время. Посмотрим как они работают если запросы запускаются одновременно в нескольких соединениях. Сначала запустим одновременно 5 запросов, с использованием dbo.SearchWords:
waitfor
time '20:35:00'
select
getdate ()
select
*
from
Ptr ptr
cross
apply
dbo.SearchWords ( ptr.vcPattern, 'dbo.Documents', 'vcContent', 'iDocId' ) wr
select getdate ()
На моем ноутбуке время выполнения составило: 1) 1 мин. 58 сек.; 2) 1 мин. 40 сек.; 3) 1 мин. 41 сек.; 4) 1 мин. 58 сек.; 5) 1 мин. 59 сек.
Запустив одновременно 5 аналогичных запросов с использованием функции dbo.GetDocData, получаем такие результаты: 1) 2 мин. 56 сек.; 2) 2 мин. 17 сек.; 3) 2 мин. 18 сек.; 4) 2 мин. 57 сек.; 5) 2 мин. 57 сек.
Запрос с функцией dbo.GetDocData работал в среднем на 42% дольше чем с функцией dbo.SearchWords. Изучим причины замедления. Функция dbo.GetDocData использует табличную переменную. Табличные переменные это те же временные таблицы (только без статистик), и хранятся они на жестком диске, в базе данных tempdb !!!!!!!
Параллельно с запуском пяти запросов с использованием функции dbo.GetDocData выполним несколько раз такой запрос:
select
tab.name, tab.create_date, part.rows
from
tempdb.sys.tables tab with ( nolock )
inner
join
tempdb.sys.partitions part with ( nolock ) on tab.object_id = part.object_id
order by tab.create_date desc
Мы увидим появление новых таблиц в tempb, в которых постоянно возрастает количество строк, из-за работы функции dbo.GetDocData:
Если также запускать запрос для выявления типов ожиданий в работающих соединениях, то мы обнаружим наличие ожиданий PAGELATCH_UP, PAGELATCH_SH, LOGBUFFER:
select
cmd, *
from
sys.sysprocesses
where spid in ( 59, 60, 61, 62, 63 )
select
*
from
sys.dm_os_waiting_tasks
where session_id in ( 59, 60, 61, 62, 63 )
Это свидетельствует о серьезной дисковой активности, транзакционном логировании, что вызывает общее замедление. Также при создании и наполнении временных таблиц происходит конкуренция при обращении к картам GAM, SGAM, с помощью которых находятся свободные экстенты. Никаких ожиданий подобного рода не наблюдается при работе функции dbo.SearchWords. Если включить Performance Monitor и проследить за счетчиком "% активности диска при записи", то максимальное среднее значение этого счетчика не превышает 64 при работе dbo.SearchWords, в то время как для dbo.GetDocData среднее значение счетчика возрастает до 120!
dbo.SearchWords:
dbo.GetDocData:
В работе dbo.SearchWords пока виден только один потенциальный недостаток: поскольку код запускается через clr, то при его работе не используется принцип кооперации планировщика непривилегированного режима сервера. Используется принцип вытеснения операционной системы. В частности поток, управляющий работой кода сборки не может обслуживать другие запросы. Однако предельный объем потоков для СУБД достаточно велик, и мало шансов что подобное ограничение причинит неудобства на практике.
