Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите оптимизировать запрос  [new]
=Сергей=
Member

Откуда:
Сообщений: 334
Для реализации нечеткого поиска использую индексацию 3-грам. Табличная функция разбиения слов на 3-грамы реализована посредством внешней CLR-библиотеки. Работа поиска нравилась, покуда количество записей превысил миллион строк.
Вот моя реализация, покритикуйте плиз:

--табличная CLR-функция StringToTriGrams осуществляет замену небуквеных символов на пробелы и удаляя их повторы, а потом разбиение строки на 3-граммы и считает количество 3-грамм
select * from [dbo].[StringToTriGrams]('"Гоголь-моголь".')

TriGram TriGramCount
------- ------------
 го     1
гог     1
ого     2
гол     2
оль     2
ль      2
ь м     1
 мо     1
мог     1


Таблица [TriGrams] и индексы для хранения словаря 3-грамм
create table [dbo].[T_TriGrams](
	[id] int identity(1,1),
	[TriGram] nchar(3) not null
)
create unique clustered index [idx_id] on [T_TriGrams]([id])
create unique nonclustered index [idx_TriGram] on [dbo].[T_TriGrams]([TriGram])

-- тригер, для обеспечения уникальности записей в T_TriGrams
create trigger [dbo].[Tr_TriGram_Add]
   on [dbo].[T_TriGrams] instead of insert
as 
begin
	set nocount on
	insert into [T_TriGrams]([TriGram])
		select [TriGram] from inserted A
			where not exists(select * from [T_TriGrams] where [TriGram] = A.[TriGram])
end


Таблица [T_IdxInfo] и индексы для хранения соответствий идентификаторов 3-грамм номеру строки индексируемой таблицы
create table [dbo].[T_IdxInfo](
[id] int identity(1,1),
[RowID] int not null, --идентификатор индексируемой строки в таблице
[OrderID] smallint not null, --порядок 3-граммы в индексируемой строке (пока не применяется)
[TriGramID] int not null, --идентификатор 3-граммы из словаря T_TriGrams
[TriGramCount] smallint not null --количество указанной 3-граммы в индексируемой строке
)
create unique clustered index [idx_id] on [T_TriGrams]([id])
create nonclustered index [idx_TriGramID] on [dbo].[T_IdxInfo]([TriGramID])
create unique nonclustered index [idx_Main] on [dbo].[T_IdxInfo]([RowID])

-- тригер, для обеспечения уникальности записей в T_IdxInfo
create trigger [dbo].[Tr_IdxInfo_Add]
   on [dbo].[T_IdxInfo] instead of insert
as 
begin
	set nocount on
	insert into [T_IdxInfo]([RowID],[OrderID],[TriGramID],[TriGramCount])
		select [RowID],[OrderID],[TriGramID],[TriGramCount] from inserted A
			where not exists(select * from [T_IdxInfo] where [RowID] = A.[RowID] and [OrderID] = A.[OrderID])
end


Идентификаторы строк, удовлетворяющие поиску вытаскиваются следующим запросом:
declare @Text nvarchar(3000)
declare @RetSubstrs table ([TriGram] char(3), [TriGramCount] int)
set @Text = N'Нечеткий поиск - это здорово'

select top 100
	 [RowID]
	,count(*)[cnt]
from [dbo].[StringToTriGrams](@Text) A
join [T_TriGrams] B on A.[TriGram] = B.[TriGram]
join [T_IdxInfo] C on B.[id] = C.[TriGramID]
group by [RowID]
order by [cnt] desc


Как уже говорил, производительность резко снизилась, когда количество проиндексированных строк превысило миллион.
Покритикуйте плиз.

Сообщение было отредактировано: 16 сен 15, 18:36
16 сен 15, 10:43    [18154832]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать N-gram поиск  [new]
=Сергей=
Member

Откуда:
Сообщений: 334
=Сергей=,
Тригер на вставку в T_IdxInfo приведён с ошибкой, он вообще не нужен и выключен.
16 сен 15, 10:47    [18154847]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
=Сергей=
Member

Откуда:
Сообщений: 334
=Сергей=,
Посмотрел план выполнения, используются индексы.
16 сен 15, 20:33    [18158032]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
=Сергей=
Посмотрел план выполнения, используются индексы.

И что же вам тогда не нравится ?
16 сен 15, 20:34    [18158036]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
пора обновлять статистику
Guest
=Сергей=,

clr ф-я согласно плану сколько раз вызывается?
16 сен 15, 20:46    [18158057]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
=Сергей=
Покритикуйте плиз.


Критикую.

1) Нефиг совать под группировку лишний join. Таблица T_TriGrams ваще лишняя.
Разница в размере int и nchar(3) = 2 байта и не стоит оно того, чтобы городить огород.

вполне достаточно
select top(100) C.[RowID], count(*) [cnt] 
    from [dbo].[StringToTriGrams](@Text) A
    left outer join [T_IdxInfo] C on A.[TriGram] = С.[TriGram]
 group by C.[RowID]
order by [cnt] desc


2) Индексы проектировал идиот

create index [idx_TriGram] on [dbo].[T_IdxInfo]([TriGram], [RowID])
17 сен 15, 06:22    [18158850]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
create UNIQUE index [idx_TriGram] on [dbo].[T_IdxInfo]([TriGram], [RowID])
17 сен 15, 09:29    [18159129]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
=Сергей=
Member

Откуда:
Сообщений: 334
aleks2,
большое спасибо! я переделаю и отпишусь о результатах
17 сен 15, 11:19    [18159652]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить