Анти-оптимизация: как мы ускорили запрос в 4 раза, сделав его неоптимальным

02.07.19

Разработка - Запросы

В этой статье приведен пример неочевидной "оптимизации" запроса, которая противоречит всем правилам, описанным в книгах для подготовки к сертификации "1С:Эксперт по технологическим вопросам", а также преподаваемым на курсах подготовки экспертов.

Цель этой статьи - показать, что оптимизация запросов "по учебнику" не всегда работает, и иногда приходится использовать подходы, которые идут вразрез всем правилам, описанным на курсах и в книгах для 1С:Экспертов по технологическим вопросам.

Более 2-х лет я занимаюсь вопросами повышения производительности 1С за счет оптимизации программного кода. Одна из частых задач (особенно при борьбе с ожиданиями на блокировках) - это повышение режима совместимости конфигурации 1С.

Описание проблемы

Один из клиентов обратился к нам для перевода конфигурации из режима совместимости с 8.1 в режим "без совместимости" (версия платформы 8.2.19.102).

Код конфигурации был адаптирован под 8.2, режим совместимости благополучно изменен.

Примечание: версия СУБД в процессе повышения режима совместимости не изменялась.

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

Поиск выполнялся следующим образом: пользователь в специальном окне вводил строку поиска и нажимал Enter. На 8.1 поиск выполнялся около 5 секунд. При переходе на 8.2 стал выполняться 20 секунд (в 4 раза дольше). Нужно было ускорить поиск хотя бы до старых значений в 5 секунд.

Анализ и решение

При поиске номенклатуры выполнялся вот такой запрос:

С помощью SQL Server Profiler были получены тексты запросов и планы выполнения для платформы 8.1 и для платформы 8.2.

Текст запроса в терминах SQL (платформа 8.1):

План запроса (платформа 8.1):

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

Текст запроса в терминах SQL (платформа 8.2):

План запроса (платформа 8.2):

Несмотря на то, что сканов в этом плане запроса не было, запрос выполнялся в 4 раза дольше (20 сек.), чем этот же самый запрос на платформе 8.1 (5 сек.).

Любые попытки оптимизации этого запроса по стандартным методикам не привели к нужному результату - он продолжал стабильно выполняться в 3-4 раза дольше, чем до повышения режима совместимости.

Тогда было принято решение намеренно "ухудшить" запрос, чтобы повлиять на выбор плана запроса оптимизатором СУБД, и получить скан таблицы справочника (как было в 8.1).

Для этого в текст запроса были добавлены выражения над полями условий, запрос стал выглядеть так:

План запроса стал следующим:

То есть, мы получили такой же план запроса, который был при режиме совместимости с 8.1 - полный скан таблицы справочника.

При этом запрос ускорился в 4 раза: с 20 сек. до 5 сек.

Таким образом, конкретно в этом случае, скан таблицы оказался быстрее поиска по индексу.

В этой статье постарался показать, что оптимизация "по учебнику", работает не всегда.

Если вам понравилась статья, поставьте "+" :)

эксперт оптимизация ускорение запрос тормозит ускорить поиск номенклатуры производительность

См. также

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

13000 руб.

02.09.2020    119970    656    389    

701

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5622    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    1964    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6094    29    mkalimulin    23    

48

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1714    vandalsvq    7    

28

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5287    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15962    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. SlavaKron 02.07.19 15:45 Сейчас в теме
А сколько элементов было в справочнике?
11. igordynets 87 02.07.19 16:46 Сейчас в теме
(1) несколько сотен тысяч
2. hlop11 02.07.19 15:49 Сейчас в теме
Избавиться от "ИЛИ" в секции "ГДЕ" не пробовали?
sergey_garin; fishca; Crazy_Max; soulsteps; ogroup; Wishich; alalsl; Азбука Морзе; lmnlmn; alex-l19041; +10 Ответить
8. igordynets 87 02.07.19 16:44 Сейчас в теме
(2) пробовали) это первое что сделали - не помогло, стоимость плана маленькая - запрос выполняется долго)
Summer_13; SeiOkami; +2 Ответить
29. nicxxx 254 03.07.19 06:42 Сейчас в теме
(2) MSSLQ сам неявно избавился от "ИЛИ". Посмотрите на план запроса 8.2. Оптимизатор параллельно читает таблицу 4 раза, а затем объединяет результат операцией Concatenation. То же самое происходит при использовании UNION ALL. Пруф https://sqlperformance.com/2017/05/sql-plan/union-all-optimization.
36. hlop11 03.07.19 09:07 Сейчас в теме
(29) Конечно избавился и потратил на это (на оптимизацию) своё время.
3. acanta 02.07.19 15:51 Сейчас в теме
Или это внутренние соединения таблицы сама с собой несколько раз?
4. Sashares 34 02.07.19 15:54 Сейчас в теме
Поддерживаю, приведите варианты оптимизации, которые получились "по всем правилам" и которые не сработали.
13. igordynets 87 02.07.19 16:49 Сейчас в теме
(4) сходу все не вспомню, из того что делали:
- переписывали через ОБЪЕДИНИТЬ ВСЕ
- в параметр наименование передавали строку с фиксированным началом (например, вместо "%строкапоиска%" использовали "строкапоиска%".
16. spacecraft 02.07.19 17:04 Сейчас в теме
(13)
в параметр наименование передавали строку с фиксированным началом (например, вместо "%строкапоиска%" использовали "строкапоиска%".

Вот с этого и нужно было начинать. При Like и подстановочных символах не может использоваться именно поиск по индексу. Был полный перебор индексов, причем 4 разных. Во втором был просто перебор таблицы один раз.
Для таких случаев придумали Полнотекстовый поиск.
zqzq; Crazy_Max; asved.ru; +3 Ответить
17. Diversus 2306 02.07.19 17:11 Сейчас в теме
(16)
Для таких случаев придумали Полнотекстовый поиск.

Хехе... Знаем проходили... Поищите ошибки при полнотекстовом поиске в интернете.
Да, этот механизм предназначен именно для этого, но оно же не работает как надо.
Для реально больших баз, были ошибка с дампами (отключаешь полнотекстовый поиск - все ок, выполняешь поиск дамп и вылет из программы) и место заканчивалось в папке с файлами индекса, а его же на другой диск не перенесешь в отличие от файлов того же сиквела...
Поэтому полнотекстовый поиск как бы есть, но для больших данных нужны свои велосипеды.
wowik; FreeArcher; +2 Ответить
24. asved.ru 36 03.07.19 02:24 Сейчас в теме
(17)
Дамп - ситуация расследуемая и устранимая.
Индекс ППД, а точнее каталог кластера, легко и непринужденно переносится куда угодно симлинком или в командной строке запуска службы.
38. A_Max 19 03.07.19 09:42 Сейчас в теме
(17)
а его же на другой диск не перенесешь

Очень даже можно перенести. Windows уже давно умеет хард/софт линки.
111. buganov 200 10.07.19 06:07 Сейчас в теме
(16) не совсем так. Like ЧтоТо% очень хорошо в индекс попадет. А вот, если поставить % в начало, то без вариантов индекс будет сканироваться.
5. Diversus 2306 02.07.19 16:02 Сейчас в теме
Вообще 1С рекомендует уходить от "ИЛИ" в секции "ГДЕ" и заменить на конструкцию вида "ОБЪЕДИНИТЬ ВСЕ".
В вашем случае можно было бы попробовать:
"ВЫБРАТЬ
|  Номенклатура.Ссылка
|ИЗ
|  Справочник.Номенклатура КАК Номенклатура
|ГДЕ
|  Номенклатура.Наименование ПОДОБНО &Наименование
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
|  Номенклатура.Ссылка
|ИЗ
|  Справочник.Номенклатура КАК Номенклатура
|ГДЕ
|  Номенклатура.Код ПОДОБНО &Наименование
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
|  Номенклатура.Ссылка
|ИЗ
|  Справочник.Номенклатура КАК Номенклатура
|ГДЕ
|  Номенклатура.ПолнАртикул ПОДОБНО &Наименование
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
|  Номенклатура.Ссылка
|ИЗ
|  Справочник.Номенклатура КАК Номенклатура
|ГДЕ
|  Номенклатура.КодРозницы ПОДОБНО &Наименование;"
Показать
Пруф: Использование логического ИЛИ в условиях
Не понятно правда на сколько это будет быстрее в вашем случае, но официальная рекомендация такая.
maXon777; gubanoff; skalex; Daynestro07; soulsteps; Uncore; AntonNV; Stim213; vasilev2015; Азбука Морзе; acanta; +11 Ответить
6. SlavaKron 02.07.19 16:09 Сейчас в теме
(5) По идее, оптимизация через "ОБЪЕДИНИТЬ ВСЕ" — это первое, что приходит в голову. Но автор пишет: "Любые попытки оптимизации этого запроса по стандартным методикам не привели к нужному результату". Вот и гадай, что он имел в виду под "стандартными методиками".
10. igordynets 87 02.07.19 16:46 Сейчас в теме
(6) согласен, следовало уточнить) через ОБЪЕДИНИТЬ ВСЕ переписали сразу когда увидели запрос. Результата это не дало)
35. SeiOkami 3418 03.07.19 08:39 Сейчас в теме
(10) перепишите статью и заново на модерацию. А то вам теперь только про это и будут говорить.
GreenDragon; +1 Ответить
47. AntonNV 03.07.19 10:22 Сейчас в теме
(10)Т.Е. это уже не актуально писать через объединить ?
31. nicxxx 254 03.07.19 06:43 Сейчас в теме
(6) Первое, что должно прийти в голову - посмотреть план запроса. В данном случае UNION ALL ничего не даст, см. (29).
7. vasilev2015 2677 02.07.19 16:36 Сейчас в теме
(5) я бы добавил: нужно писать максимально простой запрос, отдельный для каждого варианта поиска.
9. igordynets 87 02.07.19 16:44 Сейчас в теме
(5) да, этот вариант был первым, который использовали) не помогло
12. trntv 25 02.07.19 16:49 Сейчас в теме
(5) вы написали тоже самое, что делал план долгого запроса на скриншоте, за исключением того, что вы забыли сгруппировать результат. То есть 4 раза будут перебираться все элементы одной и той же таблицы. Вы как бы таким образом пишите, что вам надо его перебирать именно 4 раза.

Понятно что LIKE в индексе будет заниматься тем же самым перебором, что и без индекса. У него (у LIKE) другого варианта и нет.
Быстрее конечно перебрать все элементы 1 раз, сравнивая их на 4 различных условия.

А может кто-то все же добавил индекс на один из реквизитов при повышении совместимости? Например, до измениний были индексы только на реквизитах "Код", "Артикул", "КодРозницы". А после добавили индекс на "Наименование".
14. igordynets 87 02.07.19 16:54 Сейчас в теме
(12) проверяли, по всем полям поиска ("Код", "Артикул", "КодРозницы", "Наименование") индексы были
112. buganov 200 10.07.19 06:09 Сейчас в теме
(14) у Вас в условии ПОДОБНО % в начале. Не важно есть индекс или нет, поиска не будет 100%
18. psih12 132 02.07.19 17:15 Сейчас в теме
(5) Проверяли мы на своей базе рекомендации 1С. Из справочника "Номенклатура" нужно было отобрать данные по 8-ми условиям артикула. Отбор составлял 270 000 позиций номенклатуры с 43 реквизитами. Так вот, обычным отбором с помощью "ИЛИ" замер показал 23 секунды, а запросами с помощью "ОБЪЕДИНИТЬ ВСЕ" 25 секунд, плюс текст запроса становится совершенно громоздким. Так что не всё так просто с этими рекомендациями.
morin; AntonNV; Dach; +3 Ответить
19. Diversus 2306 02.07.19 17:18 Сейчас в теме
(18) На то это и рекомендации :) Применять их или нет зависит от ситуации.
113. buganov 200 10.07.19 06:11 Сейчас в теме
(18) по одному полю с "ИЛИ" 1С нормально ищет в индексе
30. nicxxx 254 03.07.19 06:42 Сейчас в теме
41. lobonosov@mail.ru 03.07.19 09:57 Сейчас в теме
(5)
анчивалось в папк

В данном случае если вместо ПОДОБНО было строгое равенство, тогда было бы оптимальнее: было бы 4 поиска по индексам и объединение.
44. igordynets 87 03.07.19 10:03 Сейчас в теме
(41) Да, согласен, поиск на равенство работал бы максимально быстро. Но поиск на строгое равенство не всегда устраивает клиента.
63. Drak0n 187 03.07.19 13:04 Сейчас в теме
(5)Результат Вашего запроса не будет равен изначальному... )
15. Diversus 2306 02.07.19 17:04 Сейчас в теме
На одной из конференций Инфостарт Event, по-моему, выступал представитель компании Софтпоинт и он предложил решение задачи поиска номенклатуры, которое будет отрабатывать очень быстро.

Добавил регистр сведений с измерениями:

- Подстрока (индекс)
- Номенклатура

и регламентное задание, которое бы перезаполняло этот регистр:
А именно: разбивало бы исходную строку на подстроки:
Пример:
Туфли женские (наименование)
3-х буквенные:
туф
уфл
фли
жен
енс
нск
кие
4-х буквенные:
туфл
уфли
женс
енск
нски
ские
5-ти буквенные
и т.д.

Каждой такой подстроке соответствует номенклатура "Туфли женские". А дальше разбиваем исходную строку поиска на слова и делаем запрос к регистру, который работает без ПОДОБНО, через равно.
Да получается много данных сохраняем и огромный регистр, который строится долго, но поиск работает очень быстро.
Вот такой вариант по поиску был предложен.

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

Все это имеет смысл только для баз где ОЧЕНЬ много номенклатуры. И люди пользуются такими извращениями потому, что деваться в общем-то некуда...
Krio2; pavloffav; AntonNV; +3 Ответить
20. Dorosh 176 02.07.19 18:43 Сейчас в теме
(15)
Все это имеет смысл только для баз где ОЧЕНЬ много номенклатуры.


И когда ОЧЕНЬ МНОГО свободного места на дисках.
catv; for_sale; +2 Ответить
25. asved.ru 36 03.07.19 02:26 Сейчас в теме
(15) Т.е. накостылили свой ППД несмотря на существование такового как в платформе, так и в СУБД.
39. Diversus 2306 03.07.19 09:46 Сейчас в теме
(25) Повторю - это не моя реализация. Это было на одном из докладов IE, сотрудник Софтпоинта рассказывал о таком способе.
С другой стороны, если это будет работать быстро, то почему нет?
27. stepan96 72 03.07.19 05:51 Сейчас в теме
50. Indgo 337 03.07.19 10:37 Сейчас в теме
(15)На мой взгляд безсмысленная оптимизация, СУБД самостоятельно это делает. И вообще Полнотекстовый поиск для чего вам?
52. for_sale 970 03.07.19 10:39 Сейчас в теме
(15)
Бред, а не решение. Автор этого решения, видимо, даже не подумал о том, что 99% этих буквосочетаний не будут нужны никогда. Потому что в 99.99% случаев пользователи ищут по началу слова, а не по середине, если нужны "туфли", то никто не будет писать "уфл".

Затем автор этого бредового решения не подумал о том, что даже если номенклатуры в справочнике миллион, то даже группировка по двум первым буквам уже даёт выборку всего в пару тысяч элементов, что вполне уже подъёмно даже для 1С.

Соответственно, если уж хочется решать это всё через регистр, то можно весь миллион номенклатуры разбить по словам, у каждого слова взять две-три первые буквы, а дальше уже составлять индекс - "туф" как начало любого из слов в номенклатуре присутствует в этих двух тысячах номенклатур. А во время поиска, когда пользователь набрал "туфли ж", выбирается вся номенклатура, у которой индекс = "туф" (равно, не подобно), дальше уже из этих пары тысяч строк по Имя Подобно "%туфли ж%" за несколько мс выбирается всё нужное.

Нет террабайтов бесполезных индексов, нет часов ожидания перестроения бесполезных индексов, нет ожидания пользователей.
SirStefan; Daynestro07; Crazy_Max; ZELENKIN; ZOMI; +5 2 Ответить
59. Diversus 2306 03.07.19 12:21 Сейчас в теме
(52)
Бред, а не решение. Автор этого решения, видимо, даже не подумал о том, что 99% этих буквосочетаний не будут нужны никогда. Потому что в 99.99% случаев пользователи ищут по началу слова, а не по середине, если нужны "туфли", то никто не будет писать "уфл".

Я передал саму мысль. Понятное дело, что в чистом виде без оптимизаций никак.
93. ildarovich 7846 03.07.19 21:28 Сейчас в теме
(15) Это очень избыточное решение. (52) - движение в верном направлении, Но еще правильнее выделить из заполненной части строк все возможные "суффиксы". Например, "абракадабра" разобьется на "абракадабра", "бракадабра", "ракадабра", "акадабра", "кадабра", "адабра", "дабра", "абра", "бра", "ра", "а". На слова делить не нужно!

Тогда обычный индекс легко и быстро поможет находить слова любой нужной длины.

В общем то, полнотекстовый поиск именно так и устроен. Можно еще про суффиксные массивы и деревья почитать. В Копипастомер, кстати, применяется. Избыточность небольшая - для каждой записи справочника создается в среднем L записей, где - L - средняя длина заполненной части строки. Например, для миллиона записей будет 10 миллионов записей суффикса. В случае (15) таких записей будет гораздо больше - L x L / 2.

По моему, 5 секунд слишком много, нужно либо полнотекстовый поиск настроить, а если не получится, сделать свой, самодельный. То есть, нужно сосредоточить борьбу не на отрезке 5 - 20 секунд, а 0.1 - 5.
SerVer1C; akR00b; ZLENKO; Daynestro07; Dach; Krio2; +6 Ответить
94. acanta 03.07.19 22:04 Сейчас в теме
(93) подскажите пожалуйста, где можно подробнее почитать про полнотекстовый поиск и можно ли в 1с как то его регулировать (соотношение обьем-скорость, обновление), кроме того что отключить и поиск и фоновое задание по его обновлению.
95. for_sale 970 04.07.19 11:28 Сейчас в теме
(93)
Не понял, чем эти суффиксы помогут для поиска. В вашем примере "абра" попала в суффиксы, потому что она два раза в слове встречается. Возьмём "туфли женские". Начинаю писать "туф", ни один из суффиксов ("уфли женские", "фли женские" и т.п.) мне не поможет в поиске. И опять приходим к десяткам миллионов ненужных данных. При этом если у нас была таблица, где есть:
"ту" (или "туф") - "туфли женские"
"ту" (или "туф") - "туфли мужские"
"бо" (или "бот") - "ботинки женские"
"бо" (или "бот") - "ботинки мужские"

, то поиск занимал бы миллисекунды. Я реализовывал такой поиск городов. Задача была - реализовать внешнюю обработку на базе на замке, соответственно, реестр хранился в виде текста, извлекался оттуда в соответствие, где ключ - две первые буквы названия, значение - массив всех данных о городах, которые начинаются на эти две буквы. В файле было 250 тысяч строк, первичное кэширование (из файла JSON в соответствие) занимало 4 сек, поиск занимал миллисекунды, пользователь вводил от двух букв и больше и ему показывалась выпадающая подсказка, пользователь вообще не ощущал, что что-то в фоне происходит. А в фоне отбирались первые две буквы, получался соответствующий массив из пары сотен-пары тысяч строк и по нему искалось Подобно "ТоЧтоВвёлПользователь%". А уж если использовать не внешние объекты, которые надо конвертировать, а БД, то там и без кэширования можно обойтись.

В результате размер индекса или равен размеру справочника, или в 2-3 раза больше (если индексировать начало каждого слова в наименовании). Искать можно только по началу слов, но, повторюсь, в моей практике я ни разу не встречал, чтобы кому-то пришло в голову искать "туфли" как "уфли". Более того, надо, наверное, всерьёз поработать над бизнес-процессами и НСИ, если такая нужда у кого-то возникает. Даже, как тут где-то указывалось, если у меня "Искитимцемент цемент мешок 10 кг", то не знаю, зачем бы я искал "мент" или "шок".

Поэтому, повторюсь, так и не понял, зачем нужно резать слова на суффиксы.

Про ПП в 1С пишут, что были глюки и ошибки. Сам я когда-то включал ППИ в базе 1С, но ни разу не дождался, чтобы он построился до конца. Было это довольно давно, но осадочек остался.
96. spacecraft 04.07.19 11:42 Сейчас в теме
(95) не всегда можно уложиться в префикс.
Пример:
- пеностирол
- пенополистирол
- полистирол
Как найти их все?
97. for_sale 970 04.07.19 12:15 Сейчас в теме
(96)
Давайте определимся - мы говорим о пользователе, который ищет конкретную номенклатуру или об абстрактной ситуации, в которой кому-то, например, для отчёта, понадобилась номенклатура со всякими вхождениями?

В дин.списках 1С есть поле поиска, которое также может помочь в поиске вхождений. Как и форма поиска. Для описанной вами ситуации вполне подойдёт. Если же мы говорим о ситуации из статьи, когда, например, оператору, нужно подобрать определённую позицию номенклатуры в документ, "пенос" даст первый, "пеноп" даст второй, "поли" третий из ваших элементов. Плюс на "пено" выпадут оба первых, можно будет выбрать вручную.
98. spacecraft 04.07.19 12:30 Сейчас в теме
(97) почему принудительно ограничиваете сценарии поиска номенклатуры? К данному примеру, нужно найти утеплитель разновидности стирол. Какой конкретно (поли- пено- пенополи-) может выбирать заказчик. Тут главное найти их все из доступных и ему показать.
Если ограничивать только узкими рамками для конкретного сценария, то можно сказать, что нечеткий поиск вообще не нужен. "Мы вот сразу знаем наименование всех номенклатур".
99. for_sale 970 04.07.19 13:29 Сейчас в теме
(98)
Сценарии могут быть разные, для разных сценариев могут быть разные подходы.

Для вашего сценария вообще ничего изобретать не нужно. Подходит интересующийся клиент - "А что у вас из стирола есть?" Менеджер вбивает в типовой поиск "стирол" и получает все вхождения. Время ожидания зависит от базы, пусть даже 20 секунд - это не критично для этого сценария, никто не умрёт. За минуту собрал несколько позиций, показал цену, рассказал о товаре.

Другой сценарий - сидит оператор, например, на складе, и целый день вбивает в документы номенклатуру. Ему нужны "пенос", "поли", "пеноп". Позицию он хотя бы примерно знает. А может и совсем знает, если вбивает по бумажке, например, или по памяти из частых позиций. Время поиска для него критично, 20 секунд на каждую номенклатуру - это не только невыгодно, но и психически сложно, будет бунт.

Изобретать механизмы для первого сценария - ну, наверное, можно, если прям большущая нужда. Но мешать его со вторым, делать общий механизм - это раздутые трудозатраты на разработку и негарантируемый результат (по вашему сценарию придётся делать десятки, если не сотни миллионов индексов, очень не уверен, что вообще будет существовать приемлемое по затратам решение, которое даст меньше секунды на поиск). Для второго сценария описанный мною механизм как один из вариантов по трудозатратам выйдет в несколько часов. И эти смешные затраты решат проблему по одному из сценариев здесь и сейчас.

Поэтому никакого "ограничения" сценариев нет, есть разумное разделение сценариев по нуждам, реализации и затратам. Если есть какая-то панацея - предлагайте. Конечно, одно решение на все случаи жизни лучше, чем несколько. Если нет, тогда два разных эффективных решения или хотя бы одно эффективное на один сценарий - это лучше, чем полный бардак в ожидании панацеи.
100. spacecraft 04.07.19 13:35 Сейчас в теме
(99)
Поэтому никакого "ограничения" сценариев нет, есть разумное разделение сценариев по нуждам

это называется подогнать вопрос под ответ.
Вариантов "приставок" может быть неограниченное количество. Помнить все довольно трудно. Причем это только для одного корня. А этих ситуаций может быть неограниченное количество. Вот корень слова запомнить проще, чем все возможные "приставки"
Мы же рассматриваем универсальное решение. Вообще такое решение давно придумано: ППД, но в данном случае его отбросили и изобретают свой велосипед.
101. acanta 04.07.19 13:40 Сейчас в теме
(100) я бы её назвала это полнотекстовым поиском.
Предположи, товар в базе может называться английскими буквами или начинаться с кавычки, слова, которое обычно не произносимо или сокращения.
Можно конечно настроить rls, но что если оператор один, а привязки rls к клиенту нет?
К складу эту роль ограничения rls играют остатки, и то, если они есть.
for_sale; +1 Ответить
102. for_sale 970 04.07.19 13:41 Сейчас в теме
это называется подогнать вопрос под ответ.

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

Я вам описал сценарий, с которым я работал и который успешно решил, этот сценарий сильно похож на тот, который описан в статье. Есть замеры и трудозатраты.

По вашему сценарию, очевидно, нужно другое решение. Вы при этом никакого другого решения не предлагаете, при этом заявляете, что решение совершенно другого сценария плохое, потому что оно не решает ваш сценарий. Ну ок, пойду удалю код у клиента, скажу, что был неправ, на форуме сказали, что решение неправильное.
103. acanta 04.07.19 13:47 Сейчас в теме
(102) скорее это попытка определить, какую задачу решает некое почти универсальное решение.
104. for_sale 970 04.07.19 13:49 Сейчас в теме
(103)
Задача описана чуть выше, в первом моём комменте. И потом ещё пару раз повторил описание)
105. spacecraft 04.07.19 13:49 Сейчас в теме
(102) все началось с вашего вопроса: "Не понял, чем эти суффиксы помогут для поиска. В вашем примере "абра" попала в суффиксы, потому что она два раза в слове встречается. Возьмём "туфли женские". Начинаю писать "туф", ни один из суффиксов ("уфли женские", "фли женские" и т.п.) мне не поможет в поиске"
Т.е. вы сами придумали себе свой сценарий и под него все подводите. Обсуждение же было про универсальный поиск.
Никто не говорил, что ваше решение не имеет права на жизнь. Вы просто влезли в обсуждение универсального решения со своим узкоспециализированным и выдаете его как единственно верное.
Вы при этом никакого другого решения не предлагаете

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

Удачи вам. Дальше продолжать общения с вами не вижу смысла.
106. for_sale 970 04.07.19 13:59 Сейчас в теме
(105)
Повторюсь, сценарий в статье описывал похожий на мой сценарий. Поэтому я привёл решение своего сценария.

По суффиксам я не понял тогда и не понимаю сейчас, чем они могут помочь. У нас есть справочник с миллионом строк, по которому поиск осуществляется очень долго. Нам предлагается создать ещё один справочник, больше первого в 10-50 раз, запустить работы по его наполнению и поддержке - резать слова, собирать их по буквенно по 2-3-4-5 и так до скольки?, пересобирать куски при каждом изменении каждой номенклатуры и при создании новой, перестраивать индексы базы по этому монстру каждый раз, а с блокировками что? Это в фоне будет выполняеться? Т.е. новая номенклатура найдётся только завтра, после перелопачивания? Или сегодня подождём полчасика, пока всё перестроится? Ночных операторов нанимать, чтобы днём не дай бог никто номенклатуру не поменял)) А колво букв какое - 10? А если пользователь наберёт 11 букв, то мы ему вывалим критикал эррор и выключим 1С, чтобы не повадно было) Или все буквы сколько есть? Представляю, что за индекс будет там, где номенклатура "Шарикоподшипники самзанные КЛМКПСФНС103443КУППРЕТЫФ Искитимглавшарикоподшипник"))

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

Дальше продолжать общения с вами не вижу смысла.

Постараюсь не сильно реветь от этой потери)) Потому что так хочется на техническом форуме читать обидки, а не технические решения, а тут на тебе, обломали меня))
108. ildarovich 7846 04.07.19 14:33 Сейчас в теме
(95) С алгоритмами на строках часто так - они не очевидны. Вот, например, вы хотите найти все вхождения подстроки в строку. Предполагаю, вы думаете, что нужно по очереди прикладывать подстроку к строке и производить сравнение, то есть тратить N х М операций, где М - длина строки, а N - подстроки. Алгоритм Боурера-Мура (изобретенный, кстати в 1984 году, когда кругом повсюду уже были компьютеры) работает по другому и быстрее. Но это я отвлекся...
Относительно "туфель". Суффикс здесь не лингвистическое понятие. Я же привел пример: "абракадабра" - слово и "абракадара" - его первый "суффикс". То есть "туфли женские" - слово и "туфли женские" - один из его суффиксов.
Если вы хотите сэкономить и не хранить "уфли женские" как малоиспользуемое сочетание, то "уф" В ЭТОМ МЕСТЕ тоже зря будет храниться.
А тут вы сможете найти очень быстро и точно за одно обращение к индексу целые "ботинки женские", а боты, ботильоны и другая ботва будет пропущена.
Вообще минимизировать число разделяющих признаков можно, если задано множество наименований. Но тут мы не говорим про словарь и его возможные ограничения, тут ведь и артикулы и коды есть, то есть строками считаем произвольный набор символов, где и "уфли" могут какой-то смысл иметь. Не расслышал первую букву, например, в названии улицы или фамилии. Акунин или Окунин - как тут найти? - Пиши "кунин", полнотекстовый поиск его сразу найдет.

То есть предложенная схема хранит одновременно и единички и двойки и тройки и четверки и так далее и все только храня суффиксы, которых гораздо меньше, чем подстрок.
SlavaKron; +1 Ответить
21. Dorosh 176 02.07.19 18:47 Сейчас в теме
В тему анти-оптимизации: никогда не забуду одного случая из своего опыта. Доставшийся мне запрос собирал данные по таб. части документа. Как весь из себя правильный прог, я возмутился и переписал на "канонический" способ - получения из регистра накопления. В результате время выполнения увеличилось в разы. Пришлось вернуть обратно.
23. acanta 02.07.19 19:34 Сейчас в теме
(21) имхо, это рекомендации времен дбф.
33. mpeg1989 130 03.07.19 08:23 Сейчас в теме
(21) на уровне субд все это таблицы. Плюс регистров в том, что там могут быть собраны данные из нескольких типов документов. Ну и различные итоги и агрегаты. Если точно знать, что запросом будем получать каждую запись и по одному типу документов, то можно и таблицу документа взять.
22. PerlAmutor 129 02.07.19 19:27 Сейчас в теме
В защиту рекомендаций 1С по оптимизации запросов могу сказать, что там есть упоминание того, что запросы могут работать быстрее до оптимизации чем после, но проводить оптимизацию все равно рекомендуется по той причине, что один и тот же запрос может быстро выполняться на одном сервере и недопустимо долго на другом. А оптимизированный запрос может работать медленнее, но время его выполнения будет стабильным/предсказуемым в большинстве случаев.

Насчет скриншота итогового плана запроса есть сомнения. До этого было сканирование 4 разных не кластерных индекса, а в итоге идет сканирование по кластерному индексу, который еще и Primary Key похоже... Что-то тут не так. Он обрезанный?
for_sale; +1 Ответить
120. nickpugachev 14.03.21 12:40 Сейчас в теме
(22) Кластерный индекс это сама таблица. Можете смело заменять это название на Table Scan. Для таблиц SQL Server с кластерным индексом (да, PK в общем случае)
26. nvv1970 03.07.19 05:50 Сейчас в теме
Очень поверхностная статья без знания СУБД. Мы стукнули по телевизору и он стал показывать лучше... (С сожалению именно такие ощущения)
Нет, конечно это не оптимизация. Даже для частного случая. Это ещё выстрелит вам в ногу. Вы заставили свалиться запрос в один скан и параллелизм, почему сам поиск работает долго по каждому индексу - не разобрались. При такой оптимизации можно уже и индексы отключать. О записи то вы почему-то не стали беспокоиться.
Так можно поступить только "пока не разберемся'.
Давайте детали:
Версия СУБД. Совместимость базы. Maxdop=0? Или сколько? Почему при наличии maxdop разница в 4 раза? Очень странно. Нужны цифры...
Сколько записей в таблице, сколько записей в выборке по каждому индексу.
Прогрет ли кэш? Покажите чтения и время для каждого случая по всем (пяти) индексам отдельно.
План запроса свежий или из кэша? закэширован для какого параметра?

ПС: 5 сек - это тоже не результат. Что делает этот запрос? Если это список, то где ПЕРВЫЕ?
skalex; fishca; rudak_a; qwinter; for_sale; brr; oleg-m; DarkUser; Dach; PLAstic; Serj1C; +11 2 Ответить
42. igordynets 87 03.07.19 09:58 Сейчас в теме
(26)
Версия СУБД. Совместимость базы. Maxdop=0? Или сколько?

Версия СУБД: Microsoft SQL Server 2008 R2 10.50.6000.34
maxdop = 8
compatibility level = 100

Почему при наличии maxdop разница в 4 раза? Очень странно. Нужны цифры...

Проверялась работа с разными значениями maxdop, в том числе и с выключенным (=1), результат значительно не изменялся.

Прогрет ли кэш?
План запроса свежий или из кэша? закэширован для какого параметра?

Кэш прогревался. Тестирование выполнялось с предварительной очисткой кэша и его прогревом.

Что делает этот запрос? Если это список, то где ПЕРВЫЕ?

запрос выполняет поиск номенклатуры по части строки, поиск выполняется по разным реквизитам (это не динамический список)

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


Эту информацию, к сожалению, сейчас нет возможности предоставить - доступа к базе уже нет.
48. nvv1970 03.07.19 10:24 Сейчас в теме
(42) Спасибо за детали.
Очень жаль, что уже нет доступа к данным и больших подробностей. Можно было хотя бы отдельно таблицу выдрать/сохранить.
Я такие вещи тестирую прямо в SSMS. Можно рекомпиляцию делать (для избежания снифинга), хоть как-то контролировать кэши, можно чтения видеть, замеры и т.п. Из 1С проводить тесты - так себе...
Очень важный момент: как бы повел себя оптимизатор на 2012-2017 версиях. Теперь мы этого не узнаем. ((

Проверялась работа с разными значениями maxdop, в том числе и с выключенным (=1), результат значительно не изменялся.
Время скана кластерного то точно должно было меняться. Интересно сколько оно занимало при =1.

Во всех подобных случаях не стоит рассматривать примеры запросов как что-то универсальное (а-ля вот такая таблица с таким-то количеством строк). Подобные странности проявляются на определенных данных, с определенным статистическим распределением значений.

PS: прошу прощения за лишнюю резкость в предыдущем посте.
igordynets; +1 Ответить
79. igordynets 87 03.07.19 15:07 Сейчас в теме
(48) Спасибо Вам за развернутый комментарий. Возьму на заметку некоторые моменты)

PS: прошу прощения за лишнюю резкость в предыдущем посте.

Все нормально, к критике отношусь положительно - в ней рождается истина)
bytecoded; +1 Ответить
28. kimskiysanya 03.07.19 05:53 Сейчас в теме
А зачем текст запроса в статье картинкой сделали?
semagin@gmail.com; +1 3 Ответить
32. HAMMER_59 244 03.07.19 07:06 Сейчас в теме
Из личного опыта - никаких гарантий нет, что по одному и тому же запросу будет построен один и тот же план выполнения. То что разово сработало ни о чем не говорит, и даже не разово сработало. Может месяцами работать, а потом хоп и сюрприз.
Ну его нафиг такую оптимизацию.
34. pbazeliuk 1954 03.07.19 08:27 Сейчас в теме
Используйте для поиска более подходящие инструменты, например, Elasticsearch.
43. igordynets 87 03.07.19 10:00 Сейчас в теме
(34)
Elasticsearch

Напишите, пожалуйста, где можно прочитать про этот инструмент и как с ним работать в связке с 1С
56. pbazeliuk 1954 03.07.19 10:51 Сейчас в теме
37. insurgut 207 03.07.19 09:27 Сейчас в теме
Как-то не однозначно все. Если даже взять первоначальные данные - 5 секунд на поиск подобных в справочнике из несколько сотен тысяч элементов. Не многовато? Для примера аналогичный запрос по справочнику с 900 тысяч элементов отрабатывает менее секунды (если быть точнее, около 300-400 миллисекунд). Самое интересное, что конструкция ВЫРАЗИТЬ никак не влияет на производительность - +/- время одинаковое.
40. DarkUser 03.07.19 09:47 Сейчас в теме
А почему во втором плане запроса есть сортировка?
45. capitan 2461 03.07.19 10:12 Сейчас в теме
Отличная статья как не надо оптимизировать запросы я считаю

Решение задачи методом подселения козла.
Подозреваю юзеры жаловались и на 5 сек поиск номенклатуры, им сделали 20 сек, а потом вернули 5 сек и наступило счастье )

На самом деле не хватает информации по задаче.

Самое главное - сколько секунд ищется такой конструкцией
"ВЫБРАТЬ
|  Номенклатура.Ссылка
|ИЗ
|  Справочник.Номенклатура КАК Номенклатура
|ГДЕ
|  Номенклатура.Наименование ПОДОБНО &Наименование


 сколько секунд ищется такой конструкцией
"ВЫБРАТЬ
|  Номенклатура.Ссылка
|ИЗ
|  Справочник.Номенклатура КАК Номенклатура
|ГДЕ
|  Номенклатура.Наименование = &Наименование
Показать


и по какому полю чаще всего пользователи ищут
и если они в большинстве случаев правильно вводят начало слова а я думаю 100% они не ищут по середине - то можно искать по равенству, а не по подобию - это будет 0.5 сек к примеру вместо 5 сек
46. insurgut 207 03.07.19 10:22 Сейчас в теме
(45) там вероятнее всего проблема в железе, потому что даже подобие отрабатывает на сотнях тысяч элементов менее секунды на нормальном сервере.
53. capitan 2461 03.07.19 10:41 Сейчас в теме
(46)Это не обязательно в железе, может быть фрагментация индексов или БД, судя по переходу с 8.1 там все не новенькое
54. FreeArcher 158 03.07.19 10:42 Сейчас в теме
(45) Вот как раз по середине и ищут, например такая фраза "цем иск 50", означает "Цемент (ОАО "Искитимцемент") 50кг /25".
У нас примерно так же сделан поиск и простых альтернатив этому я не вижу.
igordynets; acanta; +2 Ответить
57. capitan 2461 03.07.19 11:39 Сейчас в теме
(54)Т.е. вы считаете что поиском ПОДОБНО &Наименование
"цем иск 50", найдется "Цемент (ОАО "Искитимцемент") 50кг /25"
???
58. acanta 03.07.19 12:07 Сейчас в теме
(57) что проще/дешевле/круче - регистр сведений с частями названия и их соответствие конкретной номенклатуре или нейросеть с распознаванием голоса.
60. FreeArcher 158 03.07.19 12:35 Сейчас в теме
(57) Если разбить по словам и сделать несколько запросов то найдется.
78. igordynets 87 03.07.19 15:04 Сейчас в теме
(45) Спасибо за критику)
Самое главное - сколько секунд ищется такой конструкцией

запрос с ПОДОБНО только по одному полю выполнялся после повышения совместимости около 5 секунд
запрос на равенство - меньше 0,5 секунды.

и по какому полю чаще всего пользователи ищут
и если они в большинстве случаев правильно вводят начало слова а я думаю 100% они не ищут по середине - то можно искать по равенству, а не по подобию - это будет 0.5 сек к примеру вместо 5 сек


то что на равенство будет работать в десятки раз быстрее - сомнений нет)
По поводу "100% не ищут по середине" - тоже так думали, но вариант с поиском по строке с фиксированным началом не устроил пользователей - искали и по середине тоже, хотя работал он быстрее.
51. Sybr 241 03.07.19 10:38 Сейчас в теме
Я бы попробовал вручную создать индекс состоящий из ссылки и всех полей поиска. Скорее всего запрос стал бы работать быстрее 5 секунд.
55. acanta 03.07.19 10:49 Сейчас в теме
У нас было искать в найденном.
61. Dach 371 03.07.19 13:00 Сейчас в теме
Почему не попробовали сделать "составной" реквизит - поле поиска? Например: КлючАналитикиПоиска (строка, 200, индекс) = Наименование + Код + ПолнАртикул + ....;

Во-первых, такая строка 99% будет уникальной, а значит и индекс будет селективным.
Во-вторых, это будет один индекс, а не 3 (5,10 и т.д.), в плане будет поиск по 1-му индексу, а не по 3-м и последующая конкатенация результатов.

Очевидное решение, лежит на поверхности, неужели никто не догадался???
ZLENKO; acanta; CyberCerber; +3 Ответить
64. Dach 371 03.07.19 13:16 Сейчас в теме
(61) плюс можно сделать такой реквизит чуть более "интеллектуальным", проверять при сложении ключевых полей их строковое "покрытие" друг другом и не дублировать строки при сложении, например:

Наименование = Стол дубовый
Код = 00012345;
Артикул = 12345;
ПолноеНаименование = Стол дубовый лаковый производство Россия

КлючАналитикиПоиска = 00012345 Стол дубовый лаковый производство Россия

Еще можно административно принять в компании шаблон заполнения всех этих полей, написать обработчики на эти поля, шаблон поиска для подстановки в запрос формировать также, как сам ключ. Уже масса вариантов. А пока на "оптимизацию" ну никак не тянет, уж извините
SlavaKron; +1 Ответить
66. spacecraft 03.07.19 13:25 Сейчас в теме
(64) и чем этот индекс поможет в поиске, скажем "стол"? Потребуется искать по Like %стол%. Будет так же полное сканирование всего индекса. А из-за селективности = 1, будет такое же количество записей, как и в самой таблице. Да, тут будет только 1 индекс, но не сильно будет отличаться от просто полного сканирования таблицы.
Да еще и длины строки может не хватить поместить все выбранные реквизиты.
68. Dach 371 03.07.19 13:34 Сейчас в теме
(66) именно так, полное сканирование индекса в этом случае. Только там проверка по всем 3 полям, а тут по 1-му. Должно быть быстрее в любом случае. Плюс все-таки сортировка же есть в индексе, а в самой таблице (кластерном индексе) - там сортировка по ссылке. Так что быстрее все-таки будет
69. spacecraft 03.07.19 13:40 Сейчас в теме
(68) сортировка в данном случае (при полном сканировании) не играет никакой роли. Да, Сравнений будет меньше, но принципиальной разницы не получится.
70. Dach 371 03.07.19 13:55 Сейчас в теме
(69) Вы забываете еще и про статистику, коллега. И касаемо фулл-скана таблицы справочника навряд ли она будет использована, потому что поиск идет не по ссылке. А вот в случае с отдельным реквизитом - да. Согласно документации, статистика создается автоматически при создании индекса и дальше уже заполняется. Так что после ваших нескольких поисков like %стол% - она заполнится и запрос отработает быстрее. Система будет знать, что часто ищут по %стол% и будет знать, какие строки соответствуют этому условию.

Это конечно требует проверки на практике, но повторюсь - решение то очевидное.
71. spacecraft 03.07.19 14:06 Сейчас в теме
(70)
статистика создается автоматически при создании индекса и дальше уже заполняется.

И как поможет тут статистика? Статистика это не еще одна разновидность индекса.
72. Dach 371 03.07.19 14:20 Сейчас в теме
(71) думаю, что поможет

Начиная с версии MS SQL 2005:

"String summary statistics: частота распределения подстрок при анализе символьных полей. Помогает оптимизатору лучше оценивать селективность условий с оператором LIKE."

"Объект статистики содержит сводную строковую статистику, позволяющую уточнить оценку количества элементов для предикатов запроса, использующих оператор LIKE, например WHERE ProductName LIKE '%Bike'. Сводная строковая статистика хранится отдельно от гистограммы и создается в первом ключевом столбце объекта статистики, если он имеет тип char, varchar, nchar, nvarchar, varchar(max), nvarchar(max), text или ntext."

Ссылки на пруфы могу прикрепить, но думаю и сами найдете легко и просто
73. spacecraft 03.07.19 14:23 Сейчас в теме
(72)
позволяющую уточнить оценку количества элементов для предикатов запроса

Вот это ни о чем не говорит? Эта статистика нужна оптимизатору плана запросов для выбора таблиц/индекса в котором делать поиск. Т.к. поиск по определению тут невозможен, то будет выбор: или перебор таблицы или индекса. Никакого дополнительного увеличения быстродействия статистика не даст.
77. Dach 371 03.07.19 15:04 Сейчас в теме
(73) Логика построения плана согласно статистике - это все хорошо и понятно.

Вы меня раззадорили. Я не поленился и провел эксперимент.

У меня есть в тестовой базе документ ЭлектронныйЗаказ. Всего 2 млн строк в таблице документа.

Вот такой запрос:
ВЫБРАТЬ
	ЭлектронныйЗаказ.Ссылка КАК Ссылка
ИЗ
	Документ.ЭлектронныйЗаказ КАК ЭлектронныйЗаказ
ГДЕ
	ЭлектронныйЗаказ.Комментарий ПОДОБНО "%1%"


В поле "Комментарий" пишут адрес доставки, так что цифра "1" там встречается очень и очень часто.

1. Вариант 1. Поле Комментарий не индексируется.
Запрос после прогревания кэша стаблильно отрабатывает за 20 с небольшим секунд. Возвращает 178 тыс. строк.
План запроса точь в точь как у автора (за небольшим исключением, у меня в базе есть еще разделение данных по общему реквизиту "Организация", так что у меня не scan, а seek индекса (но в данном контексте это не существенно).
seek кластерного индекса (то есть, по сути - самой таблицы) 99% - параллелизм 1%

Оптимизатор запроса подсказывает, цитирую:
/*
Отсутствуют сведения об индексе из v8_66BC_e7.sqlplan
Обработчик запросов считает, что реализация следующего индекса может сократить стоимость запроса на 90.1642%.
*/

/*
USE [KRR]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Документ.ЭлектронныйЗаказ] ([Организация],[Комментарий])
INCLUDE ([Ссылка])
GO
*/
Показать



2. Вариант 2. Я включил для поля "Комментарий" - "Индексировать с доп. упорядочиванием".
Тот же самый запрос. Прогрел кэш. Вернулись те же 178 тыс. строк. Отработало за 1.9 секунды.

План запроса:

Поиск в индексе (не-кластерный) 100% - Вложенные циклы (внутреннее соединение) 0%
Compute scalar 0% -----------------------------

Оптимизатор никаких индексов до-создать не советует.

20+ секунд ---> 1.9 секунды.

Так что данная логика вполне жизнеспособна.

Не верите - возьмите и повторите, как говорится....

Надеюсь, звучит убедительно.

Планы прикладываю.
Прикрепленные файлы:
1.sqlplan
2.sqlplan
igordynets; AlX0id; acanta; +3 Ответить
80. acanta 03.07.19 15:17 Сейчас в теме
(77) можно ли сделать к тому же комментарию четыре разных поиска с условием или?
Будут ли они параллельно выполнять чтение?
81. Dach 371 03.07.19 15:25 Сейчас в теме
(80)

Да можно конечно, только результат будет такой же как у автора - план разобьет эти ИЛИ на несколько seek-ов и потом будет объединять...

Будет ли параллельно выполнять - это уж как у вас в БД настроен параллелизм.

Я поэтому и предложил - "составной" строковый реквизит, грамотно заполненный, с отдельным индексом. Думаю, из исходных 5 секунд можно сделать 0.5
86. spacecraft 03.07.19 17:06 Сейчас в теме
(77) я правильно понял, что эксперимент проводили со строковым реквизитом неограниченной длины? И еще ему и индекс сделали?
А теперь то же самое с нормальным реквизитом. И не %1%, а более осмысленное типа %стул%. Чтобы приблизить к оригиналу.
И по поводу кеша. Вот один пользовать делает %стол%, второй %белый стол% и т.д. Какая вероятность попадания в кеш при таком поиске?
87. Dach 371 03.07.19 17:12 Сейчас в теме
(86) Реквизит имеет ограниченную длину. 150 символов.

Так что он нормальный. %1% вполне осмысленное в моем случае, так как в этом поле хранится что-то вроде:

"12345 На деревню дедушке"
"Москва, улица Мира, д. 134, 345671"
"Позвонить после обработки по телефону +79991234567"

и т.д.

Так что мой эксперимент очень близок к оригиналу. Шаблон %1% как видите - не особо избирательный. И тем не менее, на порядок ускорилось.
89. Dach 371 03.07.19 17:31 Сейчас в теме
(86) я понимаю Ваш скепсис. В теории сказано, что like не может использовать индексы. И тем не менее - результаты налицо. Я доподлинно не знаю, как это работает. Имеется ввиду - "внутри" операций scan и seek. Может в seek там хэши строк вычисляются и сравниваются, может еще что.

Для алгоритма какая разница, какой шаблон %стол% или %белый стол%? Я думаю, никакой абсолютно.

Ради интереса, сделал вот такие шаблоны:

"%аптека%" 98 тыс строк вернулось
"%ООО%" 59 тыс срок
"%стол%" 69 строк
"%стул%" 0 строк (ну не торгуем мы стульями, уж извините xD)

"%аптека фарм%" 3 строки

Везде результаты 1.9-2.1 секунды.

Я на этом эксперимент закончу, можете повторить при желании и отписаться о результатах
116. ildarovich 7846 11.07.19 21:31 Сейчас в теме
(89) Если я правильно понял, вы ищете объяснение почему
В теории сказано, что like не может использовать индексы
То есть в ваших экспериментах (на "практике") как будто бы получается, что индекс каким-то волшебным образом ускоряет не поиск по началу строки - шаблону типа 1%, как должен в теории, но и по середине строки по шаблону %1% ускоряет тоже, хотя в теории не должен.

Предложу другое объяснение. Индекс - это файл, содержащий индексируемое поле. Он существенно меньше исходной таблицы. Часто помещается в оперативную память и остается в ней. По этому полный скан этого файла в поиске подстроки будет быстрее как раз настолько, насколько получилось у вас, чем сканирование основной таблицы. То есть не за счет каких-то хитрых структур данных или статистики (???). А за счет меньшего размера файла. А когда в теории говорится, что индекс ускоряет поиск по шаблону 1%, речь идет о более существенном ускорении, чем до 2-х секунд.
Это можно проверить. Будет интересно проверить тут же зависимость времени от длины подстроки в шаблоне %подстрока%. Зависимость должна быть. Чем длиннее строка, тем быстрее поиск.
117. Dach 371 12.07.19 09:12 Сейчас в теме
(116)

Добрый день, Сергей. Да, в теории не должен. В плане не полный скан, а именно поиск в индексе, то есть seek. Мне кажется, просто этот оператор для like работает несколько иначе, чем просто перебор строк и разбор каждой на предмет соответствия условию. А вот как именно он это делает - это я уже не знаю.
Оставьте свое сообщение