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

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

Здесь принято сообщать о результатах: помог совет или нет.
10 сен 09, 19:45    [7644796]     Ответить | Цитировать Сообщить модератору
 Re: слишком медленный поиск  [new]
test1111
Guest
немного странно, что сервер не смог оптимизировать мой предыдущий запрос. тогда так:

select p.* from
(
	select w1.pid from
	(
		select * from t_word w1 where w1.wid = [id слова 'хорошая' в словаре]
	) w1
	inner join 
	(
		select * from t_word w1 where w1.wid = [id слова 'погода' в словаре]
	) w2 on w1.pos=w2.pos-1 and w1.pid=w2.pid
) fp
left join t_phrs p on p.id = fp.pid
11 сен 09, 00:48    [7645416]     Ответить | Цитировать Сообщить модератору
 Re: слишком медленный поиск  [new]
test1111
Member

Откуда:
Сообщений: 5
Да, кстати, а за что вы первичные ключи так не любите?
11 сен 09, 01:00    [7645430]     Ответить | Цитировать Сообщить модератору
 Re: слишком медленный поиск  [new]
Tosh
Member

Откуда: Vladivostok
Сообщений: 2956
Я ведь уже давал ТС ссылку на статью, которая мне в свое время для решения аналогичной задачи очень помогла.
Общий смысл моей идеи был такой:
  • Делаем отдельную табличку, в которую складываем абсолютно все слова, встречающиеся в поисковых полях
  • Делаем отдельную таблицу связей, в которой храним информацию о положении слова в тексте (собственно само положение, идентификатор слова, идентификатор поискового поля, идентификатор записи в таблице, в которой расположено поисковое поле)
  • При вводе пользователем поискового запроса (какой-то фразы), эта фраза бъется на слова и складывается в отдельную таблицу слов с предикатами соединения (у меня это была табличная переменная)
  • Далее остается сделать только хитрый джоин полученной таблицы слов со словарем и таблицей связей. На выходе получим идентификаторы записей, в которые входят слова из введенного пользователем запроса и доп информацию, которая нам может понадобиться (идентификатор поискового поля если их несколько или несколько таблиц, ранг или количество вхождений и т.п.)

    Все достаточно просто.
    Заполнение поискового индекса (словаря слов и таблицы связей) у меня было выполнено в отдельной процедуре, которая делала полное перестроение таблиц по рассписанию. Была отдельная процедура, которая делал индексацию "на лету" на основе отдельной таблице флагов, которая заполнялась в триггерах, навешанных на основные таблицы и пишущих идентификаторы измененных данных.

    В общем - вопрос решаемый и получилось очень приемлемо по быстродействию (что, правда, не скажешь про объем базы - всетаки получается отдельные таблицы поискового индекса со своими ключами и несколько отдельных служебных таблиц)
    ______________________________
    Чем чаще программист жалуется на чужой soft, тем хуже он делает свой.
  • 11 сен 09, 02:28    [7645534]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Alexes
    Member

    Откуда:
    Сообщений: 1100
    vv40in
    для чего отказываться от identity? и пр...?

    Для того, чтобы не возвращать identity из T_DICTIONARY и T_PHRASES.

    vv40in
    Alexes
    я бы ... разбивал фразы на слова тоже в клинтском приложении, и загружал бы данные во все таблицы с помощью Bulk copy.
    примерно так и делаю при записи.

    Как это вы через ADO работаете с Bulk copy?

    vv40in
    если я правильно понял, Вы ведете речь о заполнении таблиц. и это может повлиять лишь на скорость заполнения, но не поиска.

    Да.
    Алгоритм поиска попробуйте такой:
    set nocount on
    
    declare @wordPos table
    (
    	pos		int primary key,
    	word_id	int
    )
    
    insert into @wordPos(pos, word_id)
    select 1, id from T_DICTIONARY where text = 'Нечего'
    insert into @wordPos(pos, word_id)
    select 2, id from T_DICTIONARY where text = 'мне'
    insert into @wordPos(pos, word_id)
    select 3, id from T_DICTIONARY where text = 'мозг'
    insert into @wordPos(pos, word_id)
    select 4, id from T_DICTIONARY where text = 'парить'
    
    declare @phraseIds table (phrase_id int primary key)
    
    declare
    	@pos			int,
    	@i				int,
    	@wordCount		int,
    	@word_id		int,
    	@phraseCount	int
    select @wordCount = count(*) from @wordPos
    
    --первое слово
    select top (1)
    		@pos = pos,
    		@word_id = word_id
    	from @wordPos
    	order by pos
    --фразы первого слова
    insert into @phraseIds(phrase_id)
    select wi.phrase_id
    	from t_word_ids wi
    	where
    		wi.word_id = @word_id
    		and wi.pos = @pos
    		and exists(select 1
    				from t_prases p
    				where
    					p.id = wi.phrase_id
    					and p.InterfaceId=1
    					and p.SysTime<=CONVERT(datetime,'2009-09-10 23:59:59', 120)
    					and p.SysTime>=CONVERT(datetime, '2000-01-01 00:00:00', 120))
    --количество фраз
    set @phraseCount = @@rowcount
    --счётчик слов
    set @i = 1
    --пока есть подходящие фразы И не просмотрены все слова
    while @phraseCount > 0 and @i < @wordCount
    begin
    	--перейти к следующей позиции
    	select top (1)
    			--позиция
    			@pos = @pos,
    			--новое текущее слово
    			@word_id = word_id,
    			--
    			@i = @i + 1
    		from @wordPos
    		where pos > @pos
    		order by pos
    	
    	--удалить ранее найденные фразы, в которых НЕ встречается текущее слово на заданной позиции
    	delete p
    		from @phraseIds p
    		where not exists(select 1
    				from t_word_ids wi
    				where
    					wi.word_id = @word_id
    					and wi.pos = @pos
    					and wi.phrase_id = p.phrase_id)
    	--количество оставшихся фраз
    	set @phraseCount = @phraseCount - @@rowcount
    end
    
    select top (3000) p.*
    	from t_prases p
    	inner join @phraseIds p1 on
    		p1.phrase_id = p.id
    11 сен 09, 09:26    [7645825]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Alexes
    Member

    Откуда:
    Сообщений: 1100
    Чуть-чуть ошибся. Вместо
    select top (1)
    			--позиция. !!!Здесь я ошибочно приравнял переменную саму себе
    			@pos = @pos,
    			--новое текущее слово
    			@word_id = word_id,
    			--
    			@i = @i + 1
    		from @wordPos
    		where pos > @pos
    		order by pos
    
    нужно
    select top (1)
    			--позиция
    			@pos = pos,
    			--новое текущее слово
    			@word_id = word_id,
    			--
    			@i = @i + 1
    		from @wordPos
    		where pos > @pos
    		order by pos
    
    11 сен 09, 14:38    [7647939]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    кстати, забыл. нашел способ ускорить работу запроса поиска. добавил with(index(..)). похоже, оптимизатор mssql очень умным себя считает, как говорил один мой знакомый. такой оптимизации еще не видел, ну, как и многого другого :)
    так что, спасибо за помощь
    14 сен 09, 12:04    [7654405]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    Alexes,
    спасибо. попробую сравнить скорости работы нашего оптимизированного запроса и Вашего. я видел, хотя мне это непонятно, что в sql иногда сложные многоходовые программы работают быстрее простых.
    14 сен 09, 12:40    [7654683]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    Tosh,
    спасибо. это интересно. правда, я не понимаю фундаментальных различий с тем, что у нас тут сейчас работает.
    Вы не можете сказать, какие объемы (в "строках" (и длина строк), Мб) были в Ваших базах.
    Каковы скорости поступления входных данных? заполнения? Это нужно чтобы понять, каковы пределы скорости работы. У нас оч.требовательно к скорости и объемам данных (для индексации ведь потребуется доп.место)

    кр.того, как не хотелось, но заказчику оно оказалось надо и .. придется разбираться и со стеммингом. , а это...
    как-то непутёво получилось. сдаём заказчику всё, типа, по пунктам, одно за другим, вместо того, чтобы сразу поставить нам конечую задачу, и ее разрабатывать.
    Похоже на впустую потраченное наше время. Но не Ваше, уважаемые помощники. Я узнал много нового. За это отдельное спасибо.

    теперь надо бы еще опробовать sphinx в деле. пишут, что он оч.быстро индексирует.
    Вопрос: не может ли кто-н показать сравнительные результаты скорости индексации известных поисковых движков (втч mssql) (открытых и нет)?
    и если sphinx быстрее, чем mssql, вожно ли его прикрутить к mssql? только как здесь?
    14 сен 09, 12:51    [7654784]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    Alexes
    vv40in
    для чего отказываться от identity? и пр...?

    Для того, чтобы не возвращать identity из T_DICTIONARY и T_PHRASES.

    лишние возвращаемые значения сильно замедляют работу?

    Alexes
    vv40in
    Alexes
    я бы ... разбивал фразы на слова тоже в клинтском приложении, и загружал бы данные во все таблицы с помощью Bulk copy.
    примерно так и делаю при записи.

    Как это вы через ADO работаете с Bulk copy?

    я ж не сказал "именно так", а "примерно так". вместо bulk использую процедуру с большим числом входных параметров (сразу с десяток слов или фраз в таблицы). это не слишком неоптимально?
    а насчет bulk. действительно, как? в ado вроде BeginTrans - это не то?
    14 сен 09, 13:05    [7654872]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    Alexes,
    Alexes
    insert into @wordPos(pos, word_id)
    select 1, id from T_DICTIONARY where text = 'Нечего'

    (м.б. я неправильно понял):
    по-моему, Ваш запрос будет работать, только если пользователю нодо найти предложения с заранее известными позициями слов (в данном случае 1,2,3,4). Если же позиции слов неизвестны, надо найти в предложении 4 слова, расположенные в определенной последовательности (в данном случае - строго одно за другим) и не важны точные позиции этих слов - Ваш запрос не сработает, и его невозможно модифицировать для неизвестных позиций (ну, только если выполнять в цикле по позициям :) ).
    14 сен 09, 13:24    [7655016]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    vv40in
    а насчет bulk. действительно, как?
    нашел
    14 сен 09, 15:23    [7656061]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    test1111
    немного странно, что сервер не смог оптимизировать мой предыдущий запрос. тогда так:
    select p.* from
    (
    	select w1.pid from
    	(
    		select * from t_word w1 where w1.wid = [id слова 'хорошая' в словаре]
    	) w1
    	inner join 
    	(
    		select * from t_word w1 where w1.wid = [id слова 'погода' в словаре]
    	) w2 on w1.pos=w2.pos-1 and w1.pid=w2.pid
    ) fp
    left join t_phrs p on p.id = fp.pid
    

    то же самое - без указания with(index) работает медленно.
    требуется:
    select * from t_word w1 WITH(INDEX(IX_T_WORD_IDS)) where ..
    так работает оч.быстро
    14 сен 09, 19:23    [7657662]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Tosh
    Member

    Откуда: Vladivostok
    Сообщений: 2956
    vv40in
    Tosh,
    спасибо. это интересно. правда, я не понимаю фундаментальных различий с тем, что у нас тут сейчас работает.
    Вы не можете сказать, какие объемы (в "строках" (и длина строк), Мб) были в Ваших базах.
    Каковы скорости поступления входных данных? заполнения? Это нужно чтобы понять, каковы пределы скорости работы. У нас оч.требовательно к скорости и объемам данных (для индексации ведь потребуется доп.место)

    Объемы - порядка 600-800 тыс строк текста. По длинне сказать сложно - у меня под поиском стояло порядка 16-20 полей разной длинны (от 50 до 8000 символов) и в разных таблицах. В общем индекс состоял примерно из 1 млн. уникальных слов (я не решал задачу словоформ и синонимов - не надо было). Время поиска тоже очень сильно разнилось в зависимости от количества параметров, заполненных оператором и сложности этих параметров (использование предикатов "И", "ИЛИ", "НЕ" и пордяка операций). Среднее время поиска и отдачи результата оператору (количество строк, отдаваемых оператору не лимитировалось - иначе можно было бы и ускорить) равняется от 1 до 4-6 секунд.
    По объему БД - тут отдельный вопрос. Практически половина строковых полей базы была под индексом + я не удалял временные данные, которые появлялись при перестроении + дополнительные данные, которые необходимы для разностного билда. Итого получалось, что вся служебная информация (сам индекс + временные данные) занимал примерно столькоже по объему, сколько и сама БД (на момент сдачи работы - примерно около 1,5-2ГБ только этих данных).
    Время полного билда удалось свести к промежутку от 30 секунд до 2 минут (в зависимости от нагруженности сервера - операторы продолжали работать). Задержка в работе операторов (при перекидывании данных из временной таблицы индекса в новую) равнялась примерно 3 секунды (полный билд выполнялся раз или два сутки во время наименьшей загрузки базы). Частичный билд выполнялся раз в час (это то, что успели редакторы внести в базу, но не было внесено в поисковый индекс). Его работа строитась по тем же принципам, что и полный билд, только данные брерутся не все, а только те, которые были добавлены или изменены за время, прошедшее с момента последнего частичного билда (описания данных складываются в отдельную таблицу триггерами с пометкой времени).
    Самое главное - нельзя использовать курсоры и циклы. Даже разбиение запроса пользователя на слова должно происходить прямым селектом, иначе ни о каком быстродействии не может быть и речи.
    В общем - в разработку и обкатку системы было вложено порядка 2-х лет работы (было перепробовано куча вариантов), но сама система уже лет 6 как работает без сбоев даже после моего ухода с проекта (писал все сам в одного)
    15 сен 09, 05:38    [7658472]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Tosh
    Member

    Откуда: Vladivostok
    Сообщений: 2956
    test1111
    немного странно, что сервер не смог оптимизировать мой предыдущий запрос. тогда так:

    select p.* from
    (
    	select w1.pid from
    	(
    		select * from t_word w1 where w1.wid = [id слова 'хорошая' в словаре]
    	) w1
    	inner join 
    	(
    		select * from t_word w1 where w1.wid = [id слова 'погода' в словаре]
    	) w2 on w1.pos=w2.pos-1 and w1.pid=w2.pid
    ) fp
    left join t_phrs p on p.id = fp.pid
    

    а вы в этом решении не могли бы мне подсказать как будет выглядеть запрос если пользователь ввел 4 или 8 слов в запросе? (так - ради интереса)
    15 сен 09, 05:39    [7658474]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    test1111
    Member

    Откуда:
    Сообщений: 5
    Tosh,

    Я честно говоря так и не понял постановку задачи - поэтому отвечаю на более-менее конкретно поставленные вопросы :-)
    15 сен 09, 12:04    [7659954]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    test1111
    Member

    Откуда:
    Сообщений: 5
    Tosh

    а вы в этом решении не могли бы мне подсказать как будет выглядеть запрос если пользователь ввел 4 или 8 слов в запросе? (так - ради интереса)


    Невозможно написать один запрос для выборки данных на все случаи жизни.

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

    Или, если такой вариант не устраивает, разработать простейший промежуточный язык запросов, скрипты на котором будут парситься и динамически выполняться на стороне сервера.

    Можно вообще подойти к решению задачи принципиально по-другому, как советовали некоторые участники форума. Все зависит от постановки задачи и имеющихся ресурсов для ее выполнения.
    15 сен 09, 12:24    [7660153]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Tosh
    Member

    Откуда: Vladivostok
    Сообщений: 2956
    test1111
    Tosh

    а вы в этом решении не могли бы мне подсказать как будет выглядеть запрос если пользователь ввел 4 или 8 слов в запросе? (так - ради интереса)


    Невозможно написать один запрос для выборки данных на все случаи жизни.

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

    Или, если такой вариант не устраивает, разработать простейший промежуточный язык запросов, скрипты на котором будут парситься и динамически выполняться на стороне сервера.

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

    Просто именно от того, что Вы советуете я отказался в самом начале - этот путь ведет только к динамическому SQL и, как следствие, к очень чувствительной потери скорости работы. Поверьте - этот путь ведет к тупику.
    Вариант со своим промежуточным языком достаточно сложен и мно, в частности, был отброшен из-за невозможности все это реализовать на TSQL.
    В моем случае было заранее известно количество и состав полей, которые может запросить пользователь (надо ведь заранее строить индекс) - вот от этого и стоит отталкиваться (грубо говоря - у меня была хранилка, которая на входе получала 20 полей, значения которых были отдельными поисковыми критериями для полей базы)
    16 сен 09, 05:58    [7664887]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    Tosh
    test1111
    Tosh

    а вы в этом решении не могли бы мне подсказать как будет выглядеть запрос если пользователь ввел 4 или 8 слов в запросе? (так - ради интереса)


    Невозможно написать один запрос для выборки данных на все случаи жизни.

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

    Или, если такой вариант не устраивает, разработать простейший промежуточный язык запросов, скрипты на котором будут парситься и динамически выполняться на стороне сервера.

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

    Просто именно от того, что Вы советуете я отказался в самом начале - этот путь ведет только к динамическому SQL и, как следствие, к очень чувствительной потери скорости работы. Поверьте - этот путь ведет к тупику.
    Вариант со своим промежуточным языком достаточно сложен и мно, в частности, был отброшен из-за невозможности все это реализовать на TSQL.
    В моем случае было заранее известно количество и состав полей, которые может запросить пользователь (надо ведь заранее строить индекс) - вот от этого и стоит отталкиваться (грубо говоря - у меня была хранилка, которая на входе получала 20 полей, значения которых были отдельными поисковыми критериями для полей базы)

    у меня, дейстительно, динам.sql (формируется на стороне сервера, да это неважно). но я не думаю, что узким местом здесь является время компиляции запроса (разве,что вот - этакая его оптимизация ;) ) в любом случае время компиляции запроса несравнимо меньше времени его выполнения на моих объемах. кстати, после принудительного указания индекса (типа, ручной оптимизации) запрос отрабатывает в 15-40 раз быстрее): время поиска 4ёх слов среди 4.1 млн строк текста (для нас - небольшой объём) снизилось с 41 сек до 1-3 сек (почему-то стабильно одинакового времени не показывает). для уверенности перед запуском запросов, я рестартовывал сервер sql. более того, в моем случае просто невозможно было отказаться от динам.sql по причине того, что этот запрос лишь часть другого (хотя, полагаю, у нас воще всё сделано неверно с точки зрения профессионалов sql :( ).
    16 сен 09, 10:43    [7665700]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    извиняюсь. опечатка в пред.посте. надо читать не
    "..узким местом здесь является время компиляции запроса.."
    а
    "..узким местом здесь не является время компиляции запроса.."
    16 сен 09, 12:07    [7666432]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Tosh
    Member

    Откуда: Vladivostok
    Сообщений: 2956
    В общем - я не буду Вам доказывать чем плох динамический SQL, почему его нежелательно использовать и как он влияет на скорость выполнения запросов - тут уже не один раз эта тема поднималась и обсуждалась.
    По поводу ускорения/замедления, да и вообще для любого разговора надо видеть что именно у Вас работает, а вы не показали код, который занимается у Вас поиском. Особенно тот, который выполняется у Вас в динамике
    ______________________________
    Чем чаще программист жалуется на чужой soft, тем хуже он делает свой.
    16 сен 09, 12:38    [7666649]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Alexes
    Member

    Откуда:
    Сообщений: 1100
    vv40in
    (м.б. я неправильно понял):
    по-моему, Ваш запрос будет работать, только если пользователю нодо найти предложения с заранее известными позициями слов (в данном случае 1,2,3,4). Если же позиции слов неизвестны, надо найти в предложении 4 слова, расположенные в определенной последовательности (в данном случае - строго одно за другим) и не важны точные позиции этих слов - Ваш запрос не сработает, и его невозможно модифицировать для неизвестных позиций (ну, только если выполнять в цикле по позициям :) ).

    Поняли Вы правильно. Если хотите, чтобы учитывались не абсолютные позиции, а относительные, то вот Вам такой вариант.
    set nocount on
    
    declare @wordPos table
    (
    	--относительный номер позиции слова в фразе
    	pos			int not null,
    	--идентификатор слова
    	word_id		int not null,
    	--величина, определяющая порядок перебора слов
    	sortOrder	int not null primary key
    )
    --Здесь в качестве sortOrder используем номер позиции,
    --но можно вычислить его, например, из рейтинга распространённости слова.
    --Более редкие слова будут иметь меньшее значение sortOrder,
    --так чтобы рассмотреть их первыми, сократив исходное число фраз.
    insert into @wordPos(pos, word_id, sortOrder)
    select 1, id, 1 from T_DICTIONARY where text = 'Нечего'
    insert into @wordPos(pos, word_id, sortOrder)
    select 2, id, 2 from T_DICTIONARY where text = 'мне'
    insert into @wordPos(pos, word_id, sortOrder)
    select 3, id, 3 from T_DICTIONARY where text = 'мозг'
    insert into @wordPos(pos, word_id, sortOrder)
    select 4, id, 4 from T_DICTIONARY where text = 'парить'
    
    declare @phraseIds table
    (
    	phrase_id 	int not null,
    	--!!!теперь это относительная позиция
    	pos			int not null,
    	primary key (phrase_id, pos)
    )
    
    declare
    	--порядок сортировки
    	@sortOrder		int,
    	--первая рассмотренная позиция (на основании её формируется исходный набор фраз)
    	@firstPos		int,
    	--расстояние между текущей позицией и первой позицией
    	@posDiff		int,
    	--счётчик цикла
    	@i				int,
    	@wordCount		int,
    	@word_id		int,
    	@phraseCount	int
    select @wordCount = count(*) from @wordPos
    
    print @wordCount
    --первое слово
    select top (1)
    		@sortOrder = sortOrder,
    		@firstPos = pos,
    		@word_id = word_id
    	from @wordPos
    	order by sortOrder
    --фразы первого слова
    insert into @phraseIds(phrase_id, pos)
    select wi.phrase_id, wi.pos
    	from t_word_ids wi
    	where
    		wi.word_id = @word_id
    --количество фраз
    set @phraseCount = @@rowcount
    
    --счётчик слов
    set @i = 1
    --пока есть подходящие фразы И не просмотрены все слова
    while @phraseCount > 0 and @i < @wordCount
    begin
    	--перейти к следующему слову
    	select top (1)
    			--порядок сортировки
    			@sortOrder = sortOrder,
    			--разница прыдыдущей и новой позиции
    			@posDiff = pos - @firstPos,
    			--новое текущее слово
    			@word_id = word_id,
    			--
    			@i = @i + 1
    		from @wordPos
    		where sortOrder > @sortOrder
    		order by sortOrder
    	
    	--удалить ранее найденные фразы, в которых НЕ встречается текущее слово на заданной позиции
    	delete p
    		from @phraseIds p
    		where not exists(select 1
    				from t_word_ids wi
    				where
    					wi.word_id = @word_id
    					and wi.pos = p.pos + @posDiff
    					and wi.phrase_id = p.phrase_id)
    	--количество оставшихся фраз
    	set @phraseCount = @phraseCount - @@rowcount
    end
    
    select top (3000) p.*
    	from t_prases p
    	inner join @phraseIds p1 on
    		p1.phrase_id = p.id
    	where
    		p.InterfaceId=1
    		and p.SysTime<=CONVERT(datetime,'2009-09-10 23:59:59', 120)
    		and p.SysTime>=CONVERT(datetime, '2000-01-01 00:00:00', 120)
    
    16 сен 09, 13:48    [7667215]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    Alexes, спасибо. это круто! таких возможностей sql я не мог себе представить (как и много чего другого :). так ведь получается, что практически любые запросы можно реализовать без динам.sql!

    но вот скорость выполнения при тех же условиях (4.1 млн строк текста, 65 тыс слов в словаре, 29 млн записей в таблице индексов) составила 1 мин. :( оптимизировать, указав индексы, не удалось.
    мой старый запрос, оптимизированный индексами, работает 1-3 сек. однако и это, наверное, долго. ведь ,например, google - сообщает о 116 млн результатов поиска слова "sql" за 0.24 сек!
    скорее всего, конечно, он тут выдает только статистику (сколько) и первые 10 результатов.
    но всё равно - впечатляет.
    здесь хвастают скростью работы sphinx. тоже впечатляет. так что, похоже, я в принципе пошёл не по правильному пути.
    а может ли похвастать mssql fts такой скоростью поиска и индексации? пробовал только что еще раз mssql. индексация оч.долгая. поиск - оч.неплохо! причем с морфологией. но только по-английски и по-русски(Cyr..). индексация, если ее не запускать в автомате, а создавать fts-индекс по заполнению таблицы вполне устраивает.
    но что делать с русским транслитом? добавлять его в тезаурус - что ли? а еще часто встречаются слова, написанные русскими буквами, но не по-русски.
    нерешабельно как-то это всё :(
    16 сен 09, 16:59    [7668938]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    Senya_L
    Member

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

    Вы так и не сообщили о результатов по совету добавить индекс. Я же совет давал, не чтобы показать свою "крутость", мне самому интересен результат.
    16 сен 09, 17:05    [7668987]     Ответить | Цитировать Сообщить модератору
     Re: слишком медленный поиск  [new]
    vv40in
    Member

    Откуда:
    Сообщений: 122
    Senya_L
    предлагаю изменить декларацию индекса IX_word_ids на
    CREATE UNIQUE CLUSTERED INDEX IX_word_ids ON word_ids(word_id ASC, phrase_id asc)
    и сделать всем индексам REBUILD (на всякий пожарный).

    Senya_L
    vv40in, Вы так и не сообщили о результатов по совету добавить индекс. Я же совет давал, не чтобы показать свою "крутость", мне самому интересен результат.

    Спасибо!
    прошу извинить меня. я не обратил внимания, что Вы предложили составной индекс (т.е. не заметил,что добавлен phrase_id)

    я забыл сразу написАть, что довольно давно экспериментировал с таким составным индексом, и почему-то забраковал его. не помню по какой причине.
    абстрактные рассуждения по созданию такого индекса были такие:
    если не принимать во внимание sql (допустим, если делать этот поиск вручную, например, если подобные таблицы (структуры данных) вовсе не в sql-server). тот, кто будет заниматься поиском слов во фразе:
    1. собрал бы индексы слов из словаря (t_dictionary)
    2. нашел бы эти индексы в t_word_ids (это поиск в дереве индекса t_word_ids.word_id)
    3. если нужно использовать позиции, то, по нашему предположению, каждому индексу t_word_ids.word_id должна быть сопоставлено опять же дерево поиска t_word_ids.phrase_id. И если так, то поиск должен быть оч.быстрым: после нахождения t_word_ids.word_id сразу имеем короткое дерево t_word_ids.phrase_id для данного t_word_ids.word_id, а не ищем по отдельному общему большому дереву t_word_ids.phrase_id.
    а проэкспериментировав сейчас, обнаружил, что с таким индексом свё действительно быстро работает! и причем даже без указания with(index(..)) в запросе! очевидно, причина в том, что я раньше не производил REBUILD, как Вы указали это сделать (до REBUILD запрос работал медленно!). Вот в чем дело!
    16 сен 09, 19:08    [7669830]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить