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

Откуда: Москва
Сообщений: 691
добрый день коллеги.

у меня настал момент когда таблица по которой внешнее приложение выполняет однообразный поиск, перевалила объемом за несколько десятков миллионов, и индекс стал не такой эффективный как раньше, что делать дальше, не подскажете? секционировать? или как-то качественно менять структуру.

запрос такой -


select top 100 ... 
from table 
where name like '%' + @query_string + '%'
order by dt desc



индекс некластерный по ( dt desc, name )
dt - date
name - varchar
24 апр 17, 18:00    [20430452]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
J.d,

У вас просто просмотр индекса, поиск не работает из-за первого '%'.
24 апр 17, 18:11    [20430473]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
J.d
where name like '%' + @query_string + '%'
При таком предикате эффективных индексов не бывает.
Он у вас просто сканируется целиком вместо самой таблицы.

Пробуйте другие механизмы. Например FTS.
24 апр 17, 18:11    [20430475]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
J.d
Member

Откуда: Москва
Сообщений: 691
да, вы правы увидел счас - Clustered index scan. вместо некластерного
24 апр 17, 18:18    [20430486]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
J.d
Member

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

не разу не юзал полнотекстовый поиск. хорошо, попробую изучить спасибо за наводку)
24 апр 17, 18:19    [20430487]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
J.d,
полнотекстный ищет только от начала слова, из середины не умеет, если что.

Секционирование вам никак не сократит объем просмотра при таком поиске.
24 апр 17, 18:22    [20430493]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Здесь только на уровне приложения можно оптимизировать если понимать логику. Ну например если вы ищите по вхождению номера накладной которое имеет префиксы и много другого текста то лучше цифровой номер выделить в отдельное поле, реализовать для него отдельную кнопку поиска. Искать будет быстрее, нагружать сервер будет меньше. (разумеется пользователей нужно уведомить, в некоторых случаях можно задавать в начале вопрос, для обучения. Вы уверены что не хотите искать только по номеру?)
24 апр 17, 20:12    [20430717]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Вообще у меня раньше была такая мысль, но руки не дошли. Для произвольного поля (с составными данными) по которым осуществляется поиск генерируется автоматизировано триггер который будет понимать каким образом делить поле на ряд доп. структурированных полей. Например понимать префикс, отделять цифровой номер(хотя можно более продвинуто, по частым запросам секционировать) и т .д. и т.п. Генерация этого триггера должна быть на основании анализа данных этого поля в таблице и анализа запросов(трасса по like %%). В этом случае можно было бы реализовать(разумеется в некоторых случаях) автоматизированный механизм более продвинутой индексации коррелирующей с составом и количеством запросов в системе.
24 апр 17, 20:30    [20430764]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Ах да, забыл добавить самое главное. Простым обывателям данная технология пригодилась бы только если бы ее реализовать через параметр функцию в которой было бы преобразования поиска по обратному алгоритму генерации триггера. Фактичекски условие ПолеСоставное like %% разбивалось бы на поиск по ряду полей в том числе и %%(это могут быть линковочные таблицы с ссылкой на первичные ключи основной). В моем случае имеется возможность подменять запросы на лету. Можно было бы все реализовать автоматизировано.
24 апр 17, 20:39    [20430787]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
o-o
Guest
МуМу
Для произвольного поля (с составными данными) по которым осуществляется поиск генерируется автоматизировано триггер который будет понимать каким образом делить поле на ряд доп. структурированных полей. Например понимать префикс, отделять цифровой номер(хотя можно более продвинуто, по частым запросам секционировать) и т .д. и т.п.

триггер это такая sp, реагирующая на селект, да?
она же еще и секционирует, я правильно понимаю?
пишу я один селект, мне таблицу секционируют по такому-то полю,
пишу другой селект, мне ее же секционируют по другому полю.
а вот 10 пользователей с 10 разными полями в where одновременно лезут в одну и ту же таблицу,
и 10 триггеров(!), срабатывающих на селект(!) секционируют бедную таблицу аж в 10 вариантах
по 10 разным полям, и все это одновременно(!)

не могли бы вы....эээ...возле собственных терминов,
совпадающих с общепринятыми в названии,
но имеющими какой-то одному вам известный смысл,
писать еще и перевод с общеизвестного на МуМу-язык?
а то ведь кошмары по ночам начнут сниться после прочтения процитированного
24 апр 17, 20:49    [20430819]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Первое, триггеров на селекты не бывает. Это я понимаю. Второе, вы знаете как работают триггеры автогенерации в репликации, например? Ну конечно же вопрос риторический - конечно знаете. Теперь представим, что у вас есть большое составное поле(явно не вторая нормальная форма) а также большое количество записей.(в моей практике сплошь и рядом, поиск по договору и и т.п. ). У вас есть масса запросов like %% которые в плане селективности ищут небольшой набор данных(например вхождение цифр). Вы могли бы провести анализ текущих данных и анализ запросов - конечно же смогли бы! Вопрос - как оптимизировать запросы? - ответ понятен. Оптимизировать клиентское приложение, структурировать, нормализировать данные.(это можно и без триггеров) Вместо одного неоптимального индекса с фулсканом по всей таблице разбить ее на часть составных(можно линковочных таблиц вместо доп. полей.). А можно ли сделать универсально и без адаптации приложения? - Ответ можно, только руки не доходят. Если не понятно объяснил то могу объяснить на конкретном примере.
24 апр 17, 21:14    [20430893]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Триггер в данном случае обычный такой триггер. На основании статистики запросов мы понимаем что например в запросе like ПолеСоставное %XXXXXXXXXXXБлаБла конструкция XXXXXXXXX повторяется 90% случаев. На основании статистики триггер понимает это и формирует два поля в доп. линковочной таблице Поле ХХХХХХХХХ становится отдельным индексированным полем а поле БлаБла все остальное. Поиск вместо like ПолеСоставное %XXXXXXXXXXXБлаБла становится типа ЧастоИспользуемыеЗначения = "XXXXXXXXXXX" and все остальное like %БлаБла. Хотя можно еще проверять на селективность и еще более эффективно строить структуру. Разумеется в случае изменения статистики запросов(например префикс по компании больше перестал попадать в запросы в силу ее расформирования) нужно перестраивать статистику(как в случае обычной статистики).
24 апр 17, 21:24    [20430930]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
То есть это пример для запросов с фулл сканом по лайку в которых можно строить индексы(не типовые индексы, скорее секции) ориентированные на запросы. То есть, нет запросов к определенной области данных? - она не будет индексирована, секционирована(не в классическом понимания этого слова ). Много запросов - будет индексирована максимально селективно.
24 апр 17, 21:29    [20430947]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
o-o
Guest
МуМу
Первое, триггеров на селекты не бывает. Это я понимаю. Второе, вы знаете как работают триггеры автогенерации в репликации, например? Ну конечно же вопрос риторический - конечно знаете. Теперь представим, что у вас есть большое составное поле(явно не вторая нормальная форма) а также большое количество записей.(в моей практике сплошь и рядом, поиск по договору и и т.п. ). У вас есть масса запросов like %% которые в плане селективности ищут небольшой набор данных(например вхождение цифр). Вы могли бы провести анализ текущих данных и анализ запросов - конечно же смогли бы! Вопрос - как оптимизировать запросы? - ответ понятен. Оптимизировать клиентское приложение, структурировать, нормализировать данные.(это можно и без триггеров) Вместо одного неоптимального индекса с фулсканом по всей таблице разбить ее на часть составных(можно линковочных таблиц вместо доп. полей.). А можно ли сделать универсально и без адаптации приложения? - Ответ можно, только руки не доходят. Если не понятно объяснил то могу объяснить на конкретном примере.

т.е. уже секционирование это не partitioning(partition function + partition schema),
а разбиение поля на несколько, так?
и триггер тоже не триггер, ибо это вовсе не то, что является триггером в RDBMS, правильно?
и эскалация не эскалация,
только вот я не первый день на форуме и мне это понятно,
а кто случайно на форум зайдет, что должен думать, что это палата N6?
как бы корректно было бы писать МуМу-триггер, МуМу-секционирование, МуМу-эскалация.
а вот уже желающим ознакомиться с подробностями МуМу-реализации выложить свои соображения
все с той же пометкой МуМу (с)
24 апр 17, 21:42    [20430983]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
o-o
Guest
Владислав Колосов
J.d,
полнотекстный ищет только от начала слова, из середины не умеет, если что.

Секционирование вам никак не сократит объем просмотра при таком поиске.

вот это вы так думали.
потому что на уме у вас один лишь традиционный FTS и такое же секционирование.
а вот на подходе MyMy-FTS, непонятно только, почему бы не в отдельной теме
24 апр 17, 21:56    [20431025]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
То о-о. Вот пример моего поста с применением практическим и реально дающим эффект(в проектах 20-и применял, не верите? могу показать выборочно).
автор
Здесь только на уровне приложения можно оптимизировать если понимать логику. Ну например если вы ищите по вхождению номера накладной которое имеет префиксы и много другого текста то лучше цифровой номер выделить в отдельное поле, реализовать для него отдельную кнопку поиска. Искать будет быстрее, нагружать сервер будет меньше. (разумеется пользователей нужно уведомить, в некоторых случаях можно задавать в начале вопрос, для обучения. Вы уверены что не хотите искать только по номеру?)

К чему такой скепсис? Я вам завтра реально работающий пример кода выложу. Вы мне расскажете как это можно реализовать типовым функционалом, Ок? Аргумент что переписывать приложение не годится, я это уже указывал как вариант.
24 апр 17, 22:04    [20431049]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
o-o
Guest
МуМу
То о-о. Вот пример моего поста с применением практическим и реально дающим эффект(в проектах 20-и применял, не верите? могу показать выборочно).

а что я-то.
вот Колосов тоже не верит, что секционирование поможет.
кстати, в соседней теме Критик переключает секции,
расположенные в разных файлах, вопреки документации,
утверждающей, что это возможно лишь в пределах одной и той же ФГ.
но я ему уже верю, у него дело происходит в темпдб (перельем сперва все в темпдб,
потом из него в целевую базу, профит налицо)
т.е. на свете есть куча разных секционирований, о которых мы и не подозреваем.
так что вам я тоже верю
+
24 апр 17, 22:16    [20431090]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
То о-о. Ладно, бумага все стерпит. Эксперимент тоже. Завтра, к вечеру покажу пару примеров(T-SQL) и с практической рекомендацией разбиения на структурированные поля.(хотя тут все тривиально) а также с примером применения методологии оптимизации поиска по like % требующих изменения кода приложения. Концептуально на примере опишу вариант универсального решения не требующий изменения кода приложения.
У Вас есть сомнения?
24 апр 17, 22:28    [20431115]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
o-o
Guest
МуМу
бумага все стерпит.

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

Откуда:
Сообщений: 1134
А мне многое типовое не нравится. Поэтому и репликатор свой пришлось писать так как были недостатки в типовом. Кластер основанный на Always-on тоже пришлось писать свой так как типовая реализация не устраивала. Средства мониторинга пришлось писать свои и т.д и т.п. Как то сложно зацикливаться в рамках типовой парадигмы. Терминология не правильная?- дело вкуса! Как назвать вещи которых нет или они отличаются от типового функционала? Главное результат. Сейчас проект по адаптации технологии блокчейн под типовые СУБД MSSQL и 1С например для российского рынка адаптирую. Вы можете мне дать готовую терминологию и методологию под эту задачу?
24 апр 17, 23:06    [20431174]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
МуМу
Member

Откуда:
Сообщений: 1134
В качестве бонуса и терпенье за флуд автору топика выложу завтра несколько примеров(пускай и вырожденных) оптимизации конструкций like %.
24 апр 17, 23:13    [20431189]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
o-o
т.е. уже секционирование это не partitioning(partition function + partition schema),
а разбиение поля на несколько, так?
и триггер тоже не триггер, ибо это вовсе не то, что является триггером в RDBMS, правильно?
и эскалация не эскалация,
только вот я не первый день на форуме и мне это понятно,
Не придирайтесь, тот, кто первый день, думаю, тоже поймёт. Всё же прозрачно (это я не про функции OpenGL), контекст применения слов указан.

Те, кто совсем тупые, они ищут готовый код для копипаста, им длинные тексты не повредят :-)
o-o
Владислав Колосов
Секционирование вам никак не сократит объем просмотра при таком поиске.
вот это вы так думали.
потому что на уме у вас один лишь традиционный FTS и такое же секционирование.

Владислав Колосов как раз отвечал на вопрос о традиционном секционировании и традиционном FTS; конечно, если он прочитал позже пост МуМу, то тоже понял, о чём речь.
25 апр 17, 00:16    [20431244]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
МуМу
Как назвать вещи которых нет или они отличаются от типового функционала?

как угодно, но только чтобы было понятно что это НЕ типовой функционал )

МуМу
Главное результат.

вам намекают что главное не водить в заблуждение собеседников
25 апр 17, 08:57    [20431535]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
J.d
Member

Откуда: Москва
Сообщений: 691
пока не пришел ни к чему.

у меня возникла мысль
select top 100 ... 
from table 
where name like '%' + @query_string + '%'
order by dt desc



так тут идет сортировка по dt а с учетом того что при отсутсвии указания order by mssql сортирует по PK, можно сделать так чтоб строки при записи, вставлялись уже упорядоченно. соответственно запрос без order by будет выполнятся быстрее в разы.

Тут советовали выделять поля при вставке, мне боюсь не подходит так как у меня поле - это название организации. и моя задача вывести для веб приложения самые свежие 50 по указананной подстроке. колво организаций построянно же растет.

если мой сомнительный план рухнет, буду смотреть в сторону кеша или in-mem db. так же есть идея при вставки разбивать наименование на слова, записывать слова в отдельную таблицу, делать связь слово - организация (многие ко одному), далее при поиске искать среди органиченного кол-ва строк в таблице слов и по связям находить организации.

всем спасибо за внимание и участие.
25 апр 17, 11:42    [20432081]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
J.d,

сортировка без явного указания ORDER BY не детерминирована.
25 апр 17, 11:44    [20432087]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить