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

Откуда:
Сообщений: 21
Уважаемые гуру SQL программирования, помогите оптимизировать или переписать запрос к БД.

Собственно имеем:
1. Таблицу индексов:
CREATE TABLE [Index-01]
(
Document varchar(255),
Word varchar(255),
Section int,
Paragraph int,
Indx int
)

2. Поисковый запрос:
Тест* или проверка поиска
3. Ну и собственно сам запрос на поиск в таблице индексов по поисковому запросу:
SELECT * FROM [Index-01] 
WHERE Word='поиска' 
and Document in (select document from [index-01] where word like 'тест%'
and section in (select section from [index-01] where word like 'тест%'
and Paragraph in (select Paragraph from [index-01] where word like 'тест%'
and Document in (select document from [index-01] where word = 'проверка'
and section in (select section from [index-01] where word = 'проверка'
and Paragraph in (select Paragraph from [index-01] where word = 'проверка'

Впринципе поиск работает и находит так как надо (слова должны встретиться в одном документе, в одном разделе и в одном параграфе(абзаце)).
Но смущает
IN SELECT
и сразу понятно что очень не оптимизирован запрос, а также не учитывается порядок следования слов.
14 мар 17, 06:25    [20291942]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Добавьте индекс по полю Word, а также убирите вот это
Document in (select document from [index-01] where word like 'тест%'

и ему подобное.
Смотрите что вы делаете, сначала вы выбираете все данные (кстати * это плохо) из таблицы где Word = 'Поиск', а дальше вы по 3 раза читаете одни и теже данные, для начала можно было бы сделать так и разницы бы не было
and Document in (select document from [index-01] where word like 'тест%' or  word = 'проверка'
and section in (select section from [index-01] where word like 'тест%' or  word = 'проверка'
and Paragraph in (select Paragraph from [index-01] where word like 'тест%' or  word = 'проверка'

то здесь вы читаете [index-01] 3 раза, а если учесть что у вас нет индекса то совсем беда.
Я бы построил временную таблицу
select document, section, Paragraph from [index-01] where word like 'тест%' or  word = 'проверка'

И уже дальше работал бы с ней.
14 мар 17, 07:21    [20291965]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Я не внимательно смотрел, у вас же там and между всеми условиями, тогда я не могу понять замеч это и как это должно работать
Document in (select document from [index-01] where word like 'тест%'
and Document in (select document from [index-01] where word = 'проверка'
т.е. у вас Маша дожна быть одновременно и Петей.
14 мар 17, 07:44    [20291972]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
aleksrov
Добавьте индекс по полю Word, а также убирите вот это
Document in (select document from [index-01] where word like 'тест%'

По поводу индекса полностью согласен, а вот конструкция
where word like 'тест%' or  word = 'проверка'
немного смущает, ведь по логике получается либо Word начинается со слова "Тест", либо содержит "Проверка", что должно отобрать несколько документов где встречается одно слово при этом не встречается другое, а мне надо чтобы они оба находились в одном документе, разделе, параграфе.
И да немного ошибся со скобками в конце IN SELECT:
SELECT * FROM [Index-01] 
WHERE Word='поиска' 
and Document in (select document from [index-01] where word like 'тест%')
and section in (select section from [index-01] where word like 'тест%')
and Paragraph in (select Paragraph from [index-01] where word like 'тест%')
and Document in (select document from [index-01] where word = 'проверка')
and section in (select section from [index-01] where word = 'проверка')
and Paragraph in (select Paragraph from [index-01] where word = 'проверка')

Здесь получается слово "поиска" должно содержаться в тех же документах, разделах, параграфах, что и остальные слова.
aleksrov
Я бы построил временную таблицу
И уже дальше работал бы с ней.

Понимаю что можно и даже нужно использовать промежуточные таблицы. Но ума не приложу как сделать проще
14 мар 17, 07:52    [20291979]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Нектотам
Guest
RAnthon,
SELECT * FROM [Index-01] i1
WHERE 
	Word='поиска'
	and exists (select * from [index-01] i2 
		where word like 'тест%' 
		and (i1.Document = i2.Document and i1.section = i2.section and i1.Paragraph = i2.Paragraph)
	) 
	and exists (select * from [index-01] i2 
		where word = 'проверка'
		and (i1.Document = i2.Document and i1.section = i2.section and i1.Paragraph = i2.Paragraph)
	) 


индексы:
Word, Document, section, Paragraph
Document, section, Paragraph
14 мар 17, 08:48    [20292050]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Нектотам
Guest
Нектотам,

Ошибся, эти мой запрос не эквивалентен. :(
14 мар 17, 08:53    [20292058]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
Нектотам
RAnthon,
SELECT * FROM [Index-01] i1
WHERE 
	Word='поиска'
	and exists (select * from [index-01] i2 
		where word like 'тест%' 
		and (i1.Document = i2.Document and i1.section = i2.section and i1.Paragraph = i2.Paragraph)
	) 
	and exists (select * from [index-01] i2 
		where word = 'проверка'
		and (i1.Document = i2.Document and i1.section = i2.section and i1.Paragraph = i2.Paragraph)
	) 


индексы:
Word, Document, section, Paragraph
Document, section, Paragraph


А вот ваш вариант намного привлекательнее! Работает и судя по плану выполнения быстрее.
14 мар 17, 09:40    [20292189]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
buven
Member

Откуда:
Сообщений: 792
Вы уж вот с этим определитесь...

RAnthon
2. Поисковый запрос:
Тест* или проверка поиска


RAnthon
... ведь по логике получается либо Word начинается со слова "Тест", либо содержит "Проверка", что должно отобрать несколько документов где встречается одно слово при этом не встречается другое, а мне надо чтобы они оба находились в одном документе, разделе, параграфе.


Дайте примерное наполнение и нужный результат выборки - дело веселее пойдет.
14 мар 17, 10:55    [20292420]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
Извините, надо было пояснить для начала по поисковому запросу, тут слово или вообще не учитывается, грубо говоря игнорируется, так как его можно отнести к стоп-словам. Условие содержит, а точнее начинается формируется для слова Тест так как оно оканчивается на "*" для других слов проверка и поиска накладывается точное соответсвие

buven
Вы уж вот с этим определитесь...

RAnthon
2. Поисковый запрос:
Тест* или проверка поиска


RAnthon
... ведь по логике получается либо Word начинается со слова "Тест", либо содержит "Проверка", что должно отобрать несколько документов где встречается одно слово при этом не встречается другое, а мне надо чтобы они оба находились в одном документе, разделе, параграфе.


Дайте примерное наполнение и нужный результат выборки - дело веселее пойдет.
14 мар 17, 11:14    [20292511]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
invm
Member

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

https://msdn.microsoft.com/ru-ru/library/ms142571.aspx
14 мар 17, 11:27    [20292590]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
Насколько я понимаю полнотекстовый поиск нужен там где текст хранится в БД, тогда да идеальное решение, но у меня другая задача
invm
RAnthon,

https://msdn.microsoft.com/ru-ru/library/ms142571.aspx
14 мар 17, 11:39    [20292670]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
RAnthon
Уважаемые гуру SQL программирования, помогите оптимизировать или переписать запрос к БД.

Собственно имеем:
1. Таблицу индексов:
CREATE TABLE [Index-01]
(
Document varchar(255),
Word varchar(255),
Section int,
Paragraph int,
Indx int
)

2. Поисковый запрос:
Тест* или проверка поиска
3. Ну и собственно сам запрос на поиск в таблице индексов по поисковому запросу:
SELECT * FROM [Index-01] 
WHERE Word='поиска' 
and Document in (select document from [index-01] where word like 'тест%'
and section in (select section from [index-01] where word like 'тест%'
and Paragraph in (select Paragraph from [index-01] where word like 'тест%'
and Document in (select document from [index-01] where word = 'проверка'
and section in (select section from [index-01] where word = 'проверка'
and Paragraph in (select Paragraph from [index-01] where word = 'проверка'

Впринципе поиск работает и находит так как надо (слова должны встретиться в одном документе, в одном разделе и в одном параграфе(абзаце)).
Но смущает
IN SELECT
и сразу понятно что очень не оптимизирован запрос, а также не учитывается порядок следования слов.


1. Работает - не трогай.
2. Если очень хочется - in (select замени на exists( ... ). Хотя в твоем контексте разница невелика.
3. Если хочешь "учет следования" - ну дык и пиши с учетом следования. Где у тя "порядок"?
14 мар 17, 12:30    [20292895]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
aleks2
1. Работает - не трогай.
2. Если очень хочется - in (select замени на exists( ... ). Хотя в твоем контексте разница невелика.
3. Если хочешь "учет следования" - ну дык и пиши с учетом следования. Где у тя "порядок"?


Так исходя из запроса получается в моем случае опорным словом является последнее слово в запросе, потом зная его координаты (Section, Paragraph) мы проверяем есть ли следующие слова с такими же координатами. Как тут учесть следование?

И да я немного переписал запрос и убрал ненужную координату Section
SELECT DocumentName ,Paragraph ,Indx FROM [Index-01] i1 
WHERE Word = 'поиска' 
and exists(select DocumentName from [index-01] i2 where word like 'тест%' and (i1.DocumentName = i2.DocumentName and i1.Paragraph = i2.Paragraph))
and exists(select DocumentName from [index-01] i2 where word = 'проверка' and (i1.DocumentName = i2.DocumentName and i1.Paragraph = i2.Paragraph))
14 мар 17, 12:43    [20292967]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
RAnthon
aleks2
1. Работает - не трогай.
2. Если очень хочется - in (select замени на exists( ... ). Хотя в твоем контексте разница невелика.
3. Если хочешь "учет следования" - ну дык и пиши с учетом следования. Где у тя "порядок"?


Так исходя из запроса получается в моем случае опорным словом является последнее слово в запросе, потом зная его координаты (Section, Paragraph) мы проверяем есть ли следующие слова с такими же координатами. Как тут учесть следование?

И да я немного переписал запрос и убрал ненужную координату Section
SELECT DocumentName ,Paragraph ,Indx FROM [Index-01] i1 
WHERE Word = 'поиска' 
and exists(select DocumentName from [index-01] i2 where word like 'тест%' and (i1.DocumentName = i2.DocumentName and i1.Paragraph = i2.Paragraph))
and exists(select DocumentName from [index-01] i2 where word = 'проверка' and (i1.DocumentName = i2.DocumentName and i1.Paragraph = i2.Paragraph))


SELECT DocumentName ,Paragraph ,Indx FROM [Index-01] i1 
WHERE Word = 'поиска' 
and exists(select * from [index-01] i2 where word like 'тест%' and i1.DocumentName = i2.DocumentName and i1.Paragraph < i2.Paragraph 
where
 exists(select * from [index-01] i3 where word = 'проверка' and i1.DocumentName = i2.DocumentName and i2.Paragraph < i3.Paragraph))


Как-то так.
14 мар 17, 14:58    [20293796]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
пардон
SELECT DocumentName ,Paragraph ,Indx FROM [Index-01] i1 
WHERE Word = 'поиска' 
and exists( select * from [index-01] i2 
                   where word like 'тест%' and i1.DocumentName = i2.DocumentName and i1.Paragraph < i2.Paragraph 
                            and exists(select * from [index-01] i3 where word = 'проверка' and i1.DocumentName = i2.DocumentName and i2.Paragraph < i3.Paragraph
                                          )
             )
14 мар 17, 14:59    [20293801]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
А на INTERSECT никак РПЦ запрет наложила - не православная конструкция?
14 мар 17, 17:28    [20294621]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
Я впринципе так и предполагал, подскажите ещё пожалуйста по поводу индекса. Как правильно он должен быть создан?

aleks2
пардон
SELECT DocumentName ,Paragraph ,Indx FROM [Index-01] i1 
WHERE Word = 'поиска' 
and exists( select * from [index-01] i2 
                   where word like 'тест%' and i1.DocumentName = i2.DocumentName and i1.Paragraph < i2.Paragraph 
                            and exists(select * from [index-01] i3 where word = 'проверка' and i1.DocumentName = i2.DocumentName and i2.Paragraph < i3.Paragraph
                                          )
             )
15 мар 17, 07:57    [20295999]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
Руслан Дамирович
А на INTERSECT никак РПЦ запрет наложила - не православная конструкция?


А можно запрос в студию?
15 мар 17, 07:59    [20296001]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
RAnthon
А можно запрос в студию?

А вас, что в гугле забанили, или Роскомпозор внес MSDN в черные списки, или у вас BOL нелицензионный.
Ну да ладно, по сравнению с другими...
CREATE TABLE #index (
  [word] VARCHAR(255) NOT NULL,
  [document] VARCHAR(255) NOT NULL,
  [section] INT NOT NULL,
  [paragraph] INT NOT NULL,
  [index] INT NOT NULL,
  PRIMARY KEY ( [word] )
)
;
SELECT
  [document],
  [section],
  [paragraph]
FROM
  #index
WHERE
  word like 'тест%'
INTERSECT
SELECT
  [document],
  [section],
  [paragraph]
FROM
  #index
WHERE
  word = 'проверка'
INTERSECT
SELECT
  [document],
  [section],
  [paragraph]
FROM
  #index
WHERE
  word = 'поиска'

А уж что с этим делать дальше можете сами решить.
P.S. Да и вообще ваш полнотекстовый поиск очень попахивает велосипедом.
15 мар 17, 11:13    [20296820]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
Руслан Дамирович
P.S. Да и вообще ваш полнотекстовый поиск очень попахивает велосипедом.

Хоть велосипед но приятно, ищет и индексирует как надо.
За INTERSECT спасибо, уже сам разобрался а по поводу правильного индекса к сожалению не доходит пока.
15 мар 17, 13:43    [20297775]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
RAnthon
Хоть велосипед но приятно, ищет и индексирует как надо.

Это он у вас едет не смотря на квадратные колеса, нефритовый жезл вместо седла и полное отсутствие руля - аккурат до первых ямок вроде нечеткого поиска.
Я тоже писал такие вещи и пришел к выводу, что простой LIKE с заменой орфограмм и полнотекстовый поиск куда эффективнее, потому что сделать поиск:
WHERE ' ' + text + ' ' LIKE ' тест% проверка % поиска '

в разы "нативнее" и понятнее.
Не нужно возиться с вашими объединениями, проверками и прочими сопутствующими задачами по подготовке ваших данных к поиску - разбиению по словам, слежению за порядком следования в таблице и в исходном документе, перестроения индекса при изменении.

P.S. К абстрактному велосипеду поиска можно и регулярные выражения прикрутить, и дистанцию Левенштейна. Но это монстры похлеще монстра Франкенштейна - эффективность поиска растет несоизмеримо затратам производительности (особенно последний).
Хотя с регулярками побольше "свободы" формирования запроса, но "если вы используете для решения проблемы регулярные выражения, у вас две проблемы".
15 мар 17, 14:58    [20298229]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
RAnthon
Member

Откуда:
Сообщений: 21
Это все хорошо, когда весь текстовый массив хранится в БД, у меня ситуация другая немного. Есть набор текстовых файлов (а-ля Word), я строю по ним индекс в БД, и даю инструмент пользователям для поиска по этому массиву. Он находит и скажу по производительности полностью устраивает, на индексе в 4 млн. строк ~ 1 сек.

Руслан Дамирович
Я тоже писал такие вещи и пришел к выводу, что простой LIKE с заменой орфограмм и полнотекстовый поиск куда эффективнее, потому что сделать поиск:
WHERE ' ' + text + ' ' LIKE ' тест% проверка % поиска '

в разы "нативнее" и понятнее.
Не нужно возиться с вашими объединениями, проверками и прочими сопутствующими задачами по подготовке ваших данных к поиску - разбиению по словам, слежению за порядком следования в таблице и в исходном документе, перестроения индекса при изменении.

P.S. К абстрактному велосипеду поиска можно и регулярные выражения прикрутить, и дистанцию Левенштейна. Но это монстры похлеще монстра Франкенштейна - эффективность поиска растет несоизмеримо затратам производительности (особенно последний).
Хотя с регулярками побольше "свободы" формирования запроса, но "если вы используете для решения проблемы регулярные выражения, у вас две проблемы".
16 мар 17, 05:53    [20300183]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить