Неоптимальная работа запроса

17.07.18

База данных - HighLoad оптимизация

+144 –
Шпаргалка к экзамену "Эксперт по технологическим вопросам".

… Вряд ли сможете думать о чем-то другом.

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

Признаков неоптимальных запросов насчитывается более двадцати, запомнить такой список непросто. Поэтому я решил их классифицировать и сгруппировать.  Итак:

  1. Ошибки в условиях
  2. Ошибки в соединениях
  3. Ошибки сортировки/группировки
  4. Некорректное использование виртуальных таблиц
  5. Прочие ошибки

Теперь подробнее.

 

Ошибки в формулировке условий: Параметры виртуальных таблиц, Соединения (ПО), ГДЕ

 

Проверять условия позже, чем надо

  • Данные передаются в плане запроса от оператора к оператору. Чем раньше удастся отсеять ненужные данные, тем быстрее будет работать запрос. Сначала идет формирование виртуальных таблиц, потом проверяются условия соединения (которые должны быть максимально простыми), позже всего проверяется условие «ГДЕ». Подробнее: //infostart.ru/public/527529/ 

Отсутствие индекса

  • Гораздо лучше, если для запроса есть индекс, который содержит все поля данных и условий. (покрывающий индекс для запроса)

 

Невозможность применен ия индекса (Преобразования)

  • Использование условия ИЛИ в запросах // Переписать запрос, используя "ОБЪЕДИНИТЬ ВСЕ". Кстати, проверка вхождения в список преобразуется внутри СУБД в условия "ИЛИ". В некоторых случаях оптимизатор может обработать "ИЛИ" правильно, например: "Контрагент.ИНН В &(Список)"
  • Подзапросы в условиях, в том числе неявные, сложные условия // Упрощайте, используйте временные таблицы, внутренние соединения, избегайте оператор проверки вхождения.

  • Преобразования, арифметические выражения над индексированным полем
     

Невозможность применен ия индекса (Принципиально)

  • Использование ПОДОБНО % // Вызывает сканирование таблицы, если процент помещен в начале шаблона.
  • Использование условия на «НЕ РАВНО» или «НЕ» // Если проверяем условие не вхождения в список, лучше использовать левое соединение и проверку IS NULL.
  • Использование поля составного типа примитивных и ссылочных значений // Поля составного типа преобразуются в СУБД в несколько столбцов.
    Источники     //infostart.ru/public/184361/, http://its.1c.ru/db/metod8dev#content:1828:hdoc
     

Ошибки в соедин ениях

  • Соединение с подзапросами (виртуальными таблицами) // В плане запросов преобразуются в малопривлекательный для больших объемов данных Nested loops.
  • Получение данных через точку от полей составного типа – неявные соединения // Пример полей: Регистратор, Субконто, Последовательность. Происходит левое соединение всех таблиц участвующих типов.
  • Сложные запросы, использующие большое количество соединений // Оказывается, оптимизатор MS SQL плохо обрабатывает запросы, где 6-8 соединений и больше. Соединение - самый тяжелый оператор.
  • Сложный, коррелированный запрос
  • Использование полного соединения для Postgres. Источник http://its.1c.ru/db/metod8dev#content:1556:hdoc

Ошибки сортировки/группировки

  • Использование ОБЪЕДИНИТЬ вместо ОБЪЕДИНИТЬ ВСЕ – лишняя сортировка
  • Повторная группировка виртуальных таблиц. Виртуальные таблицы остатков/оборотов сгруппированы по измерениям, участвующим в запросе
  • Выбрать первые + сортировка. Проблема, если сортировка по неиндексируемому полю, или не первому в индексе
  • Применять операцию "Различные" к выборке, заведомо не содержащей повторений.
     

Некорректное использование виртуальных таблиц

Вступайте в нашу телеграмм-группу Инфостарт

+144 –

Вы можете заказать платную адаптацию этой статьи под ваши задачи на «Бирже заказов».

  • 0% комиссии — оплата напрямую исполнителю;
  • Исполнители любого масштаба — от отдельных специалистов до команд под проект;
  • Прямой обмен контактами между заказчиком и исполнителем;
  • Безопасная сделка — при необходимости;
  • Рейтинги, кейсы и прозрачная система откликов.

См. также

Оптимизация запросов с оператором «В» для составных типов в 1С и СУБД Postgres

HighLoad оптимизация Программист 1С 8.3 1С:ERP Управление предприятием 2 Бесплатно (free)

Использование оператора «В» для полей или данных составного типа (например, Регистратор) может приводить к неочевидным проблемам.

10.11.2025    13593    ivanov660    48    

55
Проблемы производительности. Оператор различные в динамических списках

HighLoad оптимизация Программист 1С:Предприятие 8 1C:ERP Бесплатно (free)

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

18.02.2025    14707    ivanov660    39    

62
Проблемы производительности. Индексация с дополнительным упорядочиванием

HighLoad оптимизация Системный администратор Программист 1C:ERP Бесплатно (free)

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

04.02.2025    13252    ivanov660    21    

58
Как сервер 1С меняет параметры у PostgreSQL

HighLoad оптимизация Программист Россия Бесплатно (free)

А вы знали, что сервер 1С при соединении с базой на сервере PostgreSQL самостоятельно устанавливает некоторые параметры? Это важно знать при настройке сервера и отладке долгих запросов. Предлагаю разобраться.

27.08.2024    8814    soulner    10    

41
Длительные вызовы - CALL, SCALL

HighLoad оптимизация Технологический журнал Системный администратор Программист Бесплатно (free)

Обсудим поиск и разбор причин длительных серверных вызовов CALL, SCALL.

24.06.2024    17224    ivanov660    13    

64
Что не так с методом ЗаполнитьЗначенияСвойств

HighLoad оптимизация Программист 1С:Предприятие 8 Бесплатно (free)

Метод очень медленно работает, когда параметр приемник содержит намного меньше свойств, чем источник.

06.06.2024    23299    Evg-Lylyk    73    

46
[Замер] Кто самый быстрый в конфигураторе?

HighLoad оптимизация Механизмы платформы 1С Системный администратор Программист Стажер 1С:Предприятие 8 Бесплатно (free)

Сравним «Регистры сведений» с «Сервисами интеграции» и узнаем, кто быстрее!

28.03.2024    13234    dsdred    27    

43
Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Программист 1С:Предприятие 8 1C:Бухгалтерия Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    13115    spyke    29    

54
Вознаграждение за ответ
Показать полностью
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. пользователь 27.04.18 03:53
Сообщение было скрыто модератором.
...
2. quebracho 22 27.04.18 06:18 Сейчас в теме
Возможно ошибка в тексте публикации.
Прикрепленные файлы:
+ – Ответить
3. vasilev2015 2860 27.04.18 09:13 Сейчас в теме
(2) Здравствуйте !
Группировка в моем понимании связана с вычислением агрегатных функций.
Объединить (без ВСЕ), применяет различные (DISTINCT) в плане запросов - оператор сортировки.
+ – Ответить
15. пользователь 28.04.18 05:24
Сообщение было скрыто модератором.
...
33. пользователь 05.06.18 13:53
Сообщение было скрыто модератором.
...
4. vasilev2015 2860 27.04.18 09:16 Сейчас в теме
Коллеги,

я знаю что скоро экзамен, поэтому писал статью второпях.
Буду вносить правки.
Сообщайте о своих сомнениях активнее.
Заранее благодарен.
+ – Ответить
5. melenaspb 209 27.04.18 10:50 Сейчас в теме +0.5 $m
Использование условия на «НЕ РАВНО» или «НЕ» // Если проверяем условие не вхождения в список, лучше использовать внутреннее соединение и проверку IS NOT NULL.


А не ЛЕВОЕ и проверка ЕСТЬ NULL?
alex_zemlyansky; +1 – Ответить
6. vasilev2015 2860 27.04.18 11:40 Сейчас в теме
(5) Елена, спасибо за внимательность, исправлю.
+ – Ответить
7. headMade 144 27.04.18 13:03 Сейчас в теме
Для 8.3.12 описание новшеств есть?

Сами сдавали на эксперта или готовитесь?
+ – Ответить
8. vasilev2015 2860 27.04.18 13:13 Сейчас в теме
(7) в статье нет, но можете посмотреть сами в каталоге установки.
нет, не сдавал. Когда сдал - уже шпаргалки писать не нужно.
+ – Ответить
9. headMade 144 27.04.18 13:17 Сейчас в теме +0.5 $m
я бы еще добавил:
- Невыполнение регламентных операций
- Несоответствие индексов и условий (для каждого условия д.б. подходящий ин-декс: содержит все поля, перечисленнные в условии, поля в индексе идут подряд и между ними нет зазоров.).
у вас указано "Неполное условие на виртуальные таблицы" - на не совсем понятно о чем тут

я бы немного перефразировал:
"При этом неоптимально - получение ДАННЫХ через точку от полей составного типа", а не "Обращение через точку к полям составного типа"
+ – Ответить
12. vasilev2015 2860 27.04.18 14:03 Сейчас в теме
(9) Спасибо за полезное замечание. Когда писал статью сомневался про Регламентные операции. С одной стороны они влияют сильно, с другой - это не метаданные и не код. С остальным согласен.
+ – Ответить
10. Serg1701 153 27.04.18 13:49 Сейчас в теме
Полезная статья. Спасибо. Приятно, что не забываете о группе КИНО)))
alex_zemlyansky; +1 – Ответить
11. vasilev2015 2860 27.04.18 13:59 Сейчас в теме
(10) я боялся, что цитату не заметят. Хотел еще пожирнее сделать.
+ – Ответить
13. nickpugachev 27.04.18 19:36 Сейчас в теме +0.5 $m
В плане запросов преобразуются в малопривлекательный Nested loops

Nested loops самая дешевая операция и в некоторых условиях вполне привлекательная. Это экзаменатор будет уточнять 100% при первой же фразе, что NL плохо
Повторная группировка виртуальных таблиц // Виртуальные таблицы уже сгруппированы по измерениям

Группировка зависит от типа регистра. Срез последних у регистра сведений и обороты регистра накоплений по-разному себя будут вести при выборке не всех измерений в запросе, тоже на доп. вопрос экзаменатора попадете
+ – Ответить
14. vasilev2015 2860 27.04.18 20:35 Сейчас в теме
(13) Да, разумеется, Nested Loops малопривлекателен при большом количестве строк.
Подразумевалась повторная группировка остатков/оборотов.

Спасибо за уточнения.
+ – Ответить
19. nickpugachev 28.04.18 17:14 Сейчас в теме
(14) Опять же, последует вопрос что такое "большое количество строк" :) - до 3 строк в ведущей таблице - хорошо, больше - нет
Для оракла при этом есть нюансы, связанные с расположением данных, не помню их
+ – Ответить
20. vasilev2015 2860 28.04.18 18:26 Сейчас в теме
(19) Если есть примерный список вопросов - присылайте ))
+ – Ответить
25. nickpugachev 28.04.18 21:29 Сейчас в теме
(20) Это то что на моей сдаче было. Только заикнешься про что-либо - тут же начинают копать насколько ты это знаешь

На самом деле тест первого дня пройти не сложно.
Дальше нужно активничать, хорошо знать поиск и фильтрацию текстовых файлов (логов) из командной строки (grep и т.д.), достаточно быстро писать тестовые сценарии для центра тестирования. Ну и не бояться и выходить показывать свои решения.
+ – Ответить
16. vasilev2015 2860 28.04.18 09:12 Сейчас в теме
Отправил немного SM всем, кто помог сделать статью лучше.
:-))
+ – Ответить
17. KVG495 28.04.18 14:43 Сейчас в теме
Еще бы добавил: низкая селективность существующего индекса. Например если индексируемое поле - булево.
И еще два момента:
1. Условие в параметрах виртуальной таблицы должно быть простым. Если там начать строить сложные правила фильтрации. Например по реквизитам измерений, то система сформирует соединение с полным сканированием таблицы движений.
2. Хотел уточнить про соединение с виртуальными таблицами. В общем случае запрос к вирт. таб. лучше выносить в отдельный пакетный запрос иначе будет соединение с подзапросом - это все правильно.
Но! Представим след случай. Соединяемся с периодическим РС. Таблицей среза последних (или первых). Если для регистра установлены галки "Разрешить итоги: срез первых (последних)", тогда в платформа созтает дополнительные физические таблицы в БД. И если заведомо известно, что запрос получает актуальные итоги, то можно соединяться с виртуальной таблицей напрямую. Без предварительного создания временных таблиц.
+ – Ответить
18. vasilev2015 2860 28.04.18 16:52 Сейчас в теме
(17) Здравствуйте, Андрей !

Низкая селективность использованного индекса влечет сканирование таблицы. Только это не является ошибкой кода конфигурации или метаданных. Про условие я указывал: Ошибки в условиях Параметрах виртуальных таблиц, Соединения (ПО), ГДЕ >> Невозможность применения индекса (Преобразования) >> Подзапросы в условиях. Может, недостаточно выпукло - уточню. Виртуальные таблицы действительно разные по своей полезности. Остатки и срез на пустую дату обращаются к таблицам итогов. Реализация срез на определенную дату сама содержит соединение с подзапросом. Но тему про виртуальные таблицы в рамках этой статьи развивать не буду.

В любом случае - спасибо.
+ – Ответить
21. palsergeich 28.04.18 20:33 Сейчас в теме
(17)
Но! Представим след случай. Соединяемся с периодическим РС. Таблицей среза последних (или первых). Если для регистра установлены галки "Разрешить итоги: срез первых (последних)", тогда в платформа созтает дополнительные физические таблицы в БД. И если заведомо известно, что запрос получает актуальные итоги, то можно соединяться с виртуальной таблицей напрямую. Без предварительного создания временных таблиц.

Но запись в таблицу итогов среза последних - это тоже затраты, встречал случаи, когда запись в основную таблицу РС шла минимально фиксируемое время, а запись в таблицу итогов РС до секунды в серверной базе без других пользователей, что в общем то было не здорово. Но это был весьма древний релиз, возможно сейчас это не так.
Иногда лучшее враг хорошего.
А по теме - удачи с экзаменом, мне не хватило прямо совсем чуть чуть, даже экзаменатор сокрушался, как же я так в последнюю секунду срезался... Надеюсь Вам повезет больше.
И да категорически рекомендую подтянуть знания по анализу тех журнала, если есть пробелы, достаточно в рамках этой статьи. Доп вопрос из разряда посчитайте количество дедлоков СУБД в логах в этой папке (коряво формулирую) это более чем реально (подсказка важно не то что вы эталонно решите эту задачу, а то что вы знаете как ее решить, если экзаменатор видит что Вы знаете, но просто впали в ступор - поможет).
События тех журнала - тоже знать желательно все, вплоть до релиза платформы, в котором появилось.
Так же в фаворе postgres - но тут вопросы попроще.
На моей аттестации в группе, с которой я сдавал - вопросов и доп вопросов именно по теме статьи было мало и они были не сложные, а вот вопросов по ТЖ и postgress - много.
+ – Ответить
22. palsergeich 28.04.18 21:01 Сейчас в теме
Сложные запросы, использующие большое количество соединений // Оказывается, оптимизатор MS SQL плохо обрабатывает запросы, где больше 8 соединений. Соединение - самый тяжелый оператор.

Тут есть разночтения в зависимости от источника фигурирует цифра 6-8.
Я на экзамене ответил 6-8 и получил +
Соединение с подзапросами (виртуальными таблицами) // В плане запросов преобразуются в малопривлекательный Nested loops.
это не является ошибкой, на сколько я помню даже в настольной книге эксперта об этом написано, просто данный прием часто ведет к проблемам, но у меня есть примеры - когда именно подазпрос давал самый быстрый результат по времени исполнения.
Расчет остатков/оборотов по таблицам документов и таблицам движений регистров
в части таблицам движений регистров не всегда это так, если период меньше периода итогов (ну например обороты за неделю по высокооборотистому складу), то запрос к реальным таблицам может оказаться существенно быстрее, ибо план запроса, по по запросу написанным программистом, может быть проще плана запроса по автосгенерированному тексту запроса. На обучении приводили пример кстати.
И еще - отсутствие расчитанных итогов - тоже может привести к крайне неоптимальной работе запроса, все о них забывают и на экзамене очень желательно и об этом сказать. Если этого не сказать экзаменатор немножко попытается это вытащить, если не получится то вместо + будет +-
+ – Ответить
23. vasilev2015 2860 28.04.18 21:16 Сейчас в теме
(22) Спасибо, что Вы поделились реальным опытом. Очень важно знать, что идешь в верном направлении.
+ – Ответить
24. palsergeich 28.04.18 21:27 Сейчас в теме
(23) ну и просто категорически рекомендую пройти очное или заочное подготовку к экзамену Эксперт. Все что там рассказывают могут спросить на экзамене + есть спойлеры. И по большому счету - мне лично кое что очень пригодилось в реальной практике. Если быть активным - то это может аукнутся положительным бонусом на эказмене.
+ – Ответить
26. vasilev2015 2860 28.04.18 22:29 Сейчас в теме
(24) Вы составляли какие-то шпаргалки, пытаясь предугадать вопросы экзамена ?
+ – Ответить
27. palsergeich 28.04.18 23:42 Сейчас в теме +0.5 $m
(26) Если честно то я не серьезно отнесся к описанию самого экзамена, думал билет 3 вопроса + допы, прорешал все билеты и пошел)))) не обратил внимания на
Письменная часть состоит из теста.

В итоге эти 3 вопросы - чистая формальность. их по диагонали посмотрели + + +- какие то доп вопросы задали чисто формальные, не помню, уровень новичок, реально, у меня даже как таковых доп вопросов по этой части не было (ибо сами вопросы в текущих реалиях слабо актуальны)
Тестирование - 20 вопросов. Разные.
От. Что значит настройка техжуранла DBMSSQL, до вопросов в 5-6 строк, которые с первой попытки и не прочитаешь. Именно по ним и идут доп вопросы. Именно они то и важны, как оказалось). Вот именно тут и оказалась засада. Пример. Как определить были ли deadlock на уровне СУБД по тех журналу. Ответ: будет событие, дай бог памяти EXCP с текстом "Lock request time out period exceeded". (могу ошибаться, не пинайте), на что экзаменатор вам может дать ноутбук и сказать посчитайте количество этих событий вон в этой папочке. Шпаргалки тут бесполезны
+ – Ответить
34. AlX0id 06.06.18 08:08 Сейчас в теме
(27)
Lock request time out period exceeded

(27)
посчитайте количество этих событий

Это еще из самых простых задач, я бы сказал )) Мне досталась задача - в 27 гигах логов найти таймауты на блокировках и объяснить их причину (!)
+ – Ответить
28. пользователь 30.04.18 21:20
Сообщение было скрыто модератором.
...
29. Andrefan 05.05.18 12:55 Сейчас в теме +0.5 $m
Добрый день.
Я бы ещё вынес четвертым разделом "Остальные причины" и добавил бы туда
- Невыполнение регламентных операций. Симптом - количество ожидаемых строк сильно отличается от фактических. (коллега уже писал в комментариях, видел)
- Использование Полного внешнего соединения. В случае с Postgres особо критично.
- Выбрать первые + сортировка. Проблема, если сортировка по неиндексируемому полю, или не первому в индексе.
- Коррелированный запрос
- Фильтр по периоду в параметрах виртуальной таблицы Остатки без необходимости (без фильтра будут использовать только текущие итоги)
+ – Ответить
30. vasilev2015 2860 05.05.18 17:05 Сейчас в теме
(29) есть рациональное зерно.
+ – Ответить
31. Dach 434 04.06.18 17:59 Сейчас в теме
"Сначала идет формирование виртуальных таблиц, потом проверяются условия соединения (которые должны быть максимально простыми), позже всего проверяется условие «ГДЕ»"

Извините, но это не всегда так. В общем случае да, но в частном нет. Запрос вида

ВЫБРАТЬ
ДоговорыКонтрагентов.Ссылка КАК Ссылка
ИЗ
Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК Контрагенты
ПО ДоговорыКонтрагентов.Владелец = Контрагенты.Ссылка
ГДЕ
ДоговорыКонтрагентов.Ссылка = &Ссылка

выполнится так:

Сначала выполнится поиск конкретной строки в таблицы договоров, потом к ней будет выполнен джойн таблицы контрагентов. Оптимизатор запросов в общем и целом "не дурак" и он не будет сначала ко всей огромной таблице договоров приклеивать огромную таблицу контрагентов и после выполнять отбор. Говоря языком T-SQL, иными словами, операция INDEX RANGE SEEK более привлекательная и приоритетная, чем INDEX RANGE SCAN и еще более чем FULL SCAN и оптимизатор всегда старается использовать сначала ее. В этом легко убедиться, если посмотреть план запроса. Мало того, если в секции "ГДЕ" заменить отбор на любой неиндексированный реквизит (например "Номер") - все равно сначала выполнится отбор и потом только джойн. То есть оптимизатор каким-то образом прогнозирует затраты на выполнение запроса при компиляции плана. Подозреваю, что не последнюю роль тут играет свежая статистика таблиц.

Даже знаете, вот если логично рассуждать. Вы на форме списка открываете документ с 25 табличными частями. Запрос платформой будет сформирован с условием WHERE. И документ у вас быстро откроется, хотя табличные части - это на минуточку - совершенно другие таблицы БД.

Предлагаю вышеприведенную формулировку дополнить фразой "в общих случаях".

И добавить: "при написании запроса нужно стараться руководствоваться принципом максимальной нормализации и минимизации выборки в ведущей таблице перед выполнением к ней каких-либо соединений"
+ – Ответить
32. vasilev2015 2860 04.06.18 21:06 Сейчас в теме
(31) Да, по поводу порядка выполнения были знатные холивары.
Здесь имел ввиду логический порядок (без учета оптимизатора).
+ – Ответить
35. ELInfinito 19.10.18 13:05 Сейчас в теме
Отличная статья.
Заинтересовало следующее :

Некорректное использование виртуальных таблиц:
- Использование таблицы Обороты для периода, меньшего месяца.

Вопрос - в чем некорректность использования ?
На основании какого типа регистра делается вывод - "Обороты" или "Остатки" ?
+ – Ответить
36. vasilev2015 2860 19.10.18 13:53 Сейчас в теме
(35) Здравствуйте !

Речь идет о виртуальных таблицах .Обороты или .ОстаткиИОбороты.

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

Поэтому обращение к таблице напрямую быстрее.

На мой взгляд, эта неоптимальность не является критической, но знать нужно.
+ – Ответить
Для отправки сообщения требуется регистрация/авторизация