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

Откуда:
Сообщений: 73
Всем привет.

Имеется следующая схема данных:
CREATE TABLE sellers (                           -- таблица продавцов/магазинов
    seller_id BIGINT IDENTITY (1, 1) NOT NULL,   -- ид продавца/магазина
    seller_name NVARCHAR(200) NOT NULL,          -- название продавца/магазина
    CONSTRAINT pk#seller_id PRIMARY KEY CLUSTERED (seller_id),
    CONSTRAINT uq#seller_name UNIQUE(seller_name)
)
CREATE TABLE goods (                             -- таблица товаров
    goods_id BIGINT IDENTITY (1, 1) NOT NULL,    -- ид товара
    goods_description NVARCHAR(500) NOT NULL,    -- описание товара
    seller_id BIGINT NOT NULL,                   -- ид продавца (внешний ключ)
    CONSTRAINT pk#goods_id PRIMARY KEY CLUSTERED (goods_id)
)
CREATE FULLTEXT CATALOG ftcGoods
	WITH ACCENT_SENSITIVITY = ON

CREATE FULLTEXT INDEX ON Goods (goods_description)
	KEY INDEX pk#goods_id ON (ftcGoods) 
	WITH (CHANGE_TRACKING AUTO)
По запросу пользователя, например, "Плюшевый мишка", программа в левом окне отображает список продавцов и в скобках количество релевантных товаров.
SELECT s.seller_name, COUNT (s.seller_name) AS [count]
FROM goods AS g
	INNER JOIN sellers AS s
		ON (g.seller_id = s.seller_id)
	INNER JOIN CONTAINSTABLE (goods, goods_description, N'"Плюшевый мишка"') AS K
		ON (g.goods_id = K.[KEY])
GROUP BY s.seller_name
ПродавецКоличество
Детский мир10
Всё для детей3
И уже по клику на конкретного продавца в правом списке отображается более детальная информация по товарам (другой запрос).


При большом объёме товаров (миллиарды), некоторые запросы могут выполняться 20 секунд и выше, что неприемлемо.
Собственно, как можно оптимизировать запрос/схему данных так, чтобы количество товаров выводилось очень быстро?
14 мар 17, 15:03    [20293816]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
voix_kas
При большом объёме товаров (миллиарды), некоторые запросы могут выполняться 20 секунд и выше, что неприемлемо.
Собственно, как можно оптимизировать запрос/схему данных так, чтобы количество товаров выводилось очень быстро?


Может вам пора смотреть в сторону другого механизма поиска.
14 мар 17, 15:25    [20293946]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
voix_kas,

У вас в goods могуть быть товары с одинаковым goods_description для разных seller_id. Так и задумано?
14 мар 17, 16:08    [20294210]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
voix_kas
Member

Откуда:
Сообщений: 73
a_voronin
Может вам пора смотреть в сторону другого механизма поиска.
Возможно. Однако хотелось бы понять, что возможности штатного средства используются на максимум.
invm
voix_kas, У вас в goods могуть быть товары с одинаковым goods_description для разных seller_id. Так и задумано?
Это всего лишь тестовый пример.
14 мар 17, 17:45    [20294688]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
voix_kas
Это всего лишь тестовый пример.
И как вам помогут рекомендации для тестового примера, если он действительности не соответствует?
14 мар 17, 17:57    [20294710]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
voix_kas
Member

Откуда:
Сообщений: 73
invm, у Вас есть предложение по оптимизации поискового запроса?
14 мар 17, 18:52    [20294909]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
voix_kas
invm, у Вас есть предложение по оптимизации поискового запроса?

У него есть сомнения, что рекомендации по оптимизации тестового примера помогут при реализации на действительности.
Ваш К.О.
А еще он пытался указать, что:
У вас товары и продавцы в одной таблице, что и приводит к тому, что получается медленно.
Приведите свои таблицы в 3НФ.
14 мар 17, 19:00    [20294927]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
voix_kas
Member

Откуда:
Сообщений: 73
Руслан Дамирович
А еще он пытался указать, что:
У вас товары и продавцы в одной таблице, что и приводит к тому, что получается медленно.
Приведите свои таблицы в 3НФ.
Видимо, пример плохой подобрал. Попробую по-другому.

В первой таблице собраны все продавцы/магазины. Каждый из них чем-то торгует, что-то предлагает. У одного продавца/магазина может быть более одного предложения, а может и не быть вовсе.
Собственно, во второй таблице собраны все их предложения (товары/услуги),
CREATE TABLE sellers (                           -- таблица продавцов/магазинов
    seller_id BIGINT IDENTITY (1, 1) NOT NULL,   -- ид продавца/магазина
    seller_name NVARCHAR(200) NOT NULL,          -- название продавца/магазина
    CONSTRAINT pk#seller_id PRIMARY KEY CLUSTERED (seller_id),
    CONSTRAINT uq#seller_name UNIQUE(seller_name)
)
CREATE TABLE offers (                            -- таблица предложений
    offer_id BIGINT IDENTITY (1, 1) NOT NULL,    -- ид предложения
    offer_description NVARCHAR(500) NOT NULL,    -- описание предложения
    seller_id BIGINT NOT NULL,                   -- ид продавца (внешний ключ)
    CONSTRAINT pk#offer_id PRIMARY KEY CLUSTERED (offer_id)
)
CREATE FULLTEXT CATALOG ftc_offers
	WITH ACCENT_SENSITIVITY = ON

CREATE FULLTEXT INDEX ON offers (offer_description)
	KEY INDEX pk#offer_id ON (ftc_offers) 
	WITH (CHANGE_TRACKING AUTO)
Необходимо найти количество предложений от каждого продавца, например, по слову "iPhone":
SELECT s.seller_name, COUNT (s.seller_name) AS [count]
FROM offers AS g
	INNER JOIN sellers AS s
		ON (g.seller_id = s.seller_id)
	INNER JOIN CONTAINSTABLE (offers, offer_description, N'"iPhone"') AS K
		ON (g.offer_id = K.[KEY])
GROUP BY s.seller_name
Не знаю, так стало яснее?
14 мар 17, 19:24    [20294997]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
-Гвоздь-
Guest
voix_kas,

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

При анализе такого запроса посмотрите на "Estimated" количество строк от полнотекстового поиска - там скорее всего - много, потому и план вероятно уходит в сканирования, плюс большая таблица на основе которой построен полнотекстовый индекс.
Если она достаточно часто обновляется, то индекс с опцией WITH (CHANGE_TRACKING AUTO) - будет часто (не могу сказать насколько часто) перестраиваться, если будете мониторить - можете поймать длительные блокировки, ожидание на перестроение индексов - возможно еще и поэтому запросы выполняются долго.

Я бы предложил переписать запрос

declare @t table (
    seller_id int not null primary key,
    [count] int not null
)

insert @t

SELECT g.seller_id, COUNT (*) AS [count]
FROM offers AS g
	INNER JOIN CONTAINSTABLE (offers, offer_description, N'"iPhone"') AS K
		ON (g.offer_id = K.[KEY])
GROUP BY g.seller_id



SELECT s.seller_name, g.[count]
FROM @t g
	INNER JOIN sellers AS s
		ON (g.seller_id = s.seller_id)

Этот шаблон брал из интернетов - суть отделить мух о котлет - таблица с полнотекстовым поиском отдельно - остальная логика отдельно


для решения потенциальных проблем с блокировками - ознакомьтесь со статьёй
[url=]https://sqldev.wordpress.com/2008/09/16/sql-server-2008-full-text-slowness/[/url]

Там рекомендуется TRACEFLAG - он может решить часть ваших проблем.

Ну а вообще полнотекстовый поиск от Мелкософта 2008 не впечатлил, возможно в поздних версиях он стал лучше.
Как вас уже и порекомендовали - посмотрите в сторону другого механизма - при наличии бюждета, времени, сил и средств.
15 мар 17, 10:13    [20296451]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация агрегативного запроса с использованием механизмов полнотекстового поиска  [new]
voix_kas
Member

Откуда:
Сообщений: 73
-Гвоздь-, спасибо, попробую.
16 мар 17, 15:26    [20302554]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить