Про отсутствующие индексы и 1С

Хотите чтобы MS SQL подсказывал Вам, в каком объекте вы строите не оптимальные запросы или неверно спроектировали метаданные - тогда спросите у него это.
2,1 тыс. 251

Хотите чтобы MS SQL подсказывал Вам, в каком объекте вы строите не оптимальные запросы или неверно спроектировали метаданные - тогда спросите у него это.

Про отсутствующие индексы и 1С

Вместо вступления:

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

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

Итак перед началом ознакомления со статьей хочу заметить, что на моем пути встречались следующие категории людей по типу отношения к индексам СУБД:

  1. Бог его знает что такое индекс - отличительная особенность: круглые, удивленные глаза, как реакция на фразу “Запрос не попадает в индекс”.
  2. Каждое поле таблицы (реквизит объекта) необходимо проанализировать на предмет необходимости индексации- отличительная особенность: постоянный просмотр планов запроса, даже в случае выборки одной строки из таблицы с гарантированным размером не более 10 строк.
  3. Вы слишком сильно уповаете на индексы - отличительная особенность: гарантированная реакция с ярко выраженным апломбом, в виде “Оно вам не поможет”, при попытке коллеги просмотра административных представлений MS SQL илиплана выполнения сложного запроса в терминах SQL
  4. С индексами в каждом конкретном случае надо включать голову - компромиссный тип людей, отличительная особенность - знает что такое селективность, и понимает почему JOIN можно заменить на UNION и в чем будет выйгрыш.

И последний момент который необходимо отразить: к кому себя относит автор?

Тут ответ прост - скорее к последнему типу, по причинам:

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

Для работы нам понадобиться:

  • 1С Предприятие 8.2 - http://users.v8.1c.ru -  я использую версию 1С:Предприятие 8.2 (8.2.13.205)
  • Тестовая конфигурация  - режим совместимости 8.1, архитектура: 1С Сервер + MS SQL 2005 и выше (я использую Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64))
  • GameWithFire - http://main.1c-ei.ru/Articles/gamewithfire скачать можно по адресу http://dev.citykirov.ru/ или в составеEnterprise Integrator
  • любую Консоль запросов 1С

Отсутствующие индексы

Как не странно еще со времен 1С 7.7, мое желание запустить ее именно под MS SQL 2005 было связано, помимо всего прочего, еще и с тем фактом что сервер СУБД собирает статистику о выполнении запросов и предоставляет мне доступ к этой информации.

С появлением 1С 8.* ничего собственно не изменилось (за исключением того что теперь нет необходимости учить 1С работать с MS SQL 2005) - архитектура метаданных может оказаться не оптимальной, а запросы на языке 1С тоже - что вполне естественно: идеальных систем не существует в принципе.

Так вот - MS SQL хранит эту самую статистику и данная информация может быть очень даже полезной. Что это за статистика можно ознакомиться в относительно серьезной статье “Открытие скрытых данных для оптимизации производительности приложений”

Однако мы с Вами попробуем разобраться с одним из административных представлений MS SQL - а именно сsys.dm_db_missing_index_*

 Прежде чем мы начнем работать с этим представлением хочу заметить три основополагающих постулата применительно к нашей задаче:

  • то что SQL считает отсутствующим индексом- это фиксация события когда
    • SQL строил план запроса - то как он будет собирать данные по запросу чтобы отдать нам в 1С,
    • в запросе был наложен фильтр явно или не явно (секция ГДЕ, СОЕДИНЕНИЕ и т.д.)
    • SQL “хотел” использовать индекс, но не нашел его.
  • для анализа важны параметры Издержки индекса и Средний процент выигрыша, а это:
    • то какую неоправданную нагрузку Вы осуществляете на СУБД
    • что будет если вы создадите этот отсутствующий индекс - на сколько сократятся ваши затраты.
  • Для того чтобы SQL больше не считал Ваши запросы не оптимальными и перестал “семафорить” о том, что он дескать не нашел нормально способа поиска по таблице - исправлять Вы это должны на стороне 1С

Примечание: За рамки данной статьи конечно выходит анализ того - а не станет ли созданный по данной информации индекс потом например затратным по поддержке. Моя цель показать вам каким образом можно воспользоваться указанными данными при анализе производительности.

Сейчас попробуем на довольно простом примере убедиться что это очень просто:

Тестовый пример

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

Развертывать необходимо в клиент-серверном режиме с использованием сервера СУБД MS-SQL версии 2005 и выше.

Метаданные в данном случае совершенно простейшие:

Однако обращаю внимание - реквизит ДокументОснование - НЕ проиндексирован. Я про него забыл ;-).

 При запуске конфигурации будет запущен генератор документов - он создаст Вам сколько угодно документов. По умолчанию я выставляю 10000 документов

Генератор документов создает последовательно документы - в котором каждый следующий документ имеет основанием предыдущий по МоментуВремени документ такого же типа.

В конфигурацию встроена обработка “Отсутствующие индексы SQL” .

И сейчас она будет показывать пустую информацию (или ошибку если Вы забыли проинсталировать GameWithFire.dll)

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

 Открываем консоль запросов (причем я всем последнее время помимо UCR советую также Консоль запросов с трассировкой), и выполняем набор простых запросов.

ВЫБРАТЬ  Документ1.Ссылка

ИЗ   Документ.Документ1 КАК Документ1

ГДЕ   Документ1.ДокументОснование = &ДокументОснование

;

 ВЫБРАТЬ  Документ1.Ссылка

ИЗ  Документ.Документ1 КАК Документ1

ГДЕ  Документ1.ДокументОснование = &ДокументОснование  И НЕ Проведен

;

 ВЫБРАТЬ  Документ1.Ссылка

ИЗ    Документ.Документ1 КАК Документ1

ВНУТРЕННЕЕ СОЕДИНЕНИЕ  Документ.Документ1 КАК Документ1Основание

ПО Документ1.ДокументОснование =  Документ1Основание.Ссылка

;

И снова запускаем обработку “Отсутствующие индексы SQL”

А вот теперь в итоге посмотрите на запросы которые вы выполнили 
и на ту информацию которую Вам сообщил SQL:

Итак Ваше приложение выполняло запросы которые:

  • осуществляли работу с основной таблицей документ с ИД “Документ1”
  • вложенность не использовали (поле Состав предлагаемого индекса пустое)
  • накладывали фильтры:
    • на равенство (поле Поиск) - по реквизиту ДокументОснование
    • на больше/меньше/НЕ равно - по реквизиту Проведен
  • затраты на такие запросы сводно составили по этому документу 2683 “попугая” = [средняя стоимость одного запроса * средний процент  выйгрыша * (количество пользовательских операций поиска + количество пользовательских операций сканирования)]
  • если бы вы создали подобный индекс вы бы сократили свои издержки на 99%

Так как поле Проведен недоступно для индексирования из 1С - Вы легко сделаете вывод сами, я надеюсь - как исправить данную ситуацию.

 Отдельно хотел бы заметить - после  того как Вы вы выставите Индексацию у реквизита ДокументОснование - при выполнении тех же самых запросов - SQL уже не будет Вам сигнализировать о том “что кажется Вы забыли проиндексировать поля таблицы”.

В качестве эпилога:

Данная статья - это:

  • концепт инструмента мониторинга - еще очень много предстоит исследовать: как минимум уже есть желание:
    • преобразовать по такому же методу остальные SQL административные представления к именам 1С, заодно переписав функцию преобразования имен объектов SQL в 1С, на более стабильную
    • добавить определение селективности измерений регистра, как с признаком индексирования, так и без оного
  • написана в качестве “ночного скетча”, вместе с тестовой конфигурацией
  • фактически приглашение Вас к дискуссии и как минимум к прочтению статей MSDN и других технических ресурсов по работе с СУБД (если Вы конечно это еще не сделали).

Начать дискуссию

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

Меня на этот пост вдохновил вопрос пользователя в телеграм-канале. Она попросила помощи в заполнении упрощенной отчетности и уточнила — справится ли она сама, если она не бухгалтер? И, правда, а можно ли вести учет и при этом не иметь образования бухгалтера и всему научиться самому.

Иллюстрация: Вера Ревина/Клерк.ру

Курсы повышения
квалификации

18
Официальное удостоверение с занесением в госреестр Рособрнадзора

Сотрудник банка отдохнул за границей и обогатился за счет клиентки на 29 млн рублей

Сотрудники управления экономической безопасности и противодействия коррупции МВД по Ростовской области выявили факт многомиллионного мошенничества в особо крупном размере сотрудником банковской организации.

ВЭД

Три главных изменения 2024 года для бухгалтера ВЭД

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

Иллюстрация: Вера Ревина/Клерк.ру
Лучшие спикеры, новый каждый день

Обязательную маркировку ювелирных изделий перенесут на 2025 год

Чтобы снизить нагрузку с Пробирной палаты, которая будет ставить клеймо на украшения, Минфин перенесет срок обязательной маркировки на 1 сентября 2025 года.

С 1 мая запустят эксперимент по маркировке моторных масел

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

Календарь вебинаров для бухгалтера в апреле 2024. Акцент на проверки

Вебинар — простой способ разобраться в любом важном для бухгалтера вопросе. «Клерк» приглашает на вебинары экспертов-практиков, которые понятно и с примерами разберут любую тему. Вы можете задавать спикеру вопросы и он ответит на них в прямом эфире.

Иллюстрация: freepik/freepik
Опытом делятся эксперты-практики, без воды
Экспорт

Минпромторг сократит список экспортеров, которые могут получать транспортные субсидии

Изменения затронут только тех, кто занимается экспортом товаров из высокомаржинальных групп.

С 01 апреля 2024 года порог беспошлинного ввоза товаров, возможно, вернется к уровню €200 евро

Но подготовка новых документов не началась.

УК РФ

Опасное производство 2 года работало без лицензии и получило доход 26 млн рублей

В Ставропольском крае перед судом предстанет обвиняемый в незаконном предпринимательстве с извлечением дохода на сумму более 26 млн рублей.

Обеспечительные меры при ВНП, предварительные обеспечительные меры + защитный лайфхак

Всегда ли налоговики могут наложить обеспечительные меры (арест) на имущество по результатам проверки?

Иллюстрация: Вера Ревина / Клерк.ру
7
94

Кто имеет право работать неполный день

Если у сотрудников есть основания для неполной занятости, работодатель должен установить удобный для работника график и снизить нагрузку.

Календарь вебинаров для бухгалтера в апреле 2024. Платные и бесплатные

Собрали для вас анонсы вебинаров на апрель 2024 года.

Миникурсы, текстовые и видеоинструкции для бухгалтеров

Каким числом выставлять счет-фактуру на аванс: бухгалтеры разбираются с формулировками в НК. «Ночной бухгалтер» № 1652

В телеграм-канале «Клерка» бухгалтеры спорили о том, какую дату указать в счет-фактуре на аванс — день получения аванса или любую из последующих пяти дней, которые даны на выставление документа. Истину нашли (вроде).

Иллюстрация: Вера Ревина/Клерк.ру
172
IT-компании

Яндекс запустил линейку нейросетей YandexGPT третьего поколения

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

44
Бесплатно с Архив документов

Сколько лет все-таки хранить кадровые документы? Мини-курс

Разбираемся в мини-курсе, какие типы хранения кадровых документов выделяют и какова продолжительность хранения кадровых документов.

Сколько лет все-таки хранить кадровые документы? Мини-курс
НДФЛ

В 2024 году ФНС разошлет уведомления на НДФЛ с процентов по вкладам за 2023 год

В 2024 году вкладчикам впервые необходимо будет уплатить НДФЛ с процентов от банковских вкладов за 2023 год.

Перерасчет имущественных налогов будут делать по-новому

Физлица будут направлять в ИФНС заявления на перерасчет налога на имущество, земельного и транспортного, к которому можно приложить документы, а можно и не прикладывать. Налоговики будут рассматривать это заявление 30 дней.

Как разработать эффективную программу обучения для новых сотрудников

Обучение персонала — сложный и многогранный процесс. Если проводить его правильно, он может стать ключевым инструментом для усиления корпоративной культуры, увеличения продуктивности и стимулирования мотивации сотрудников.

Иллюстрация: создано с помощью ИИ OpenAI © Вера Ревина/Клерк.ру
5
46
УК РФ

Поиск бывшего гендира может вылиться в вымогательство

Сотрудники главного управления уголовного розыска МВД совместно с коллегами из Красноярского края задержали подозреваемых в серии вымогательств у бизнесменов.

Интересные материалы

⚡️Итоги дня: Ozon изменил расчет сбора, Дурова призвали внимательнее следить за Telegram, Камчатку трясет

Подготовили обзор главных событий дня. Все самое интересное, что писали и обсуждали в сети, в одной подборке.