Одна из причин медленной работы табеля (ЗУП 2.5, клиент-сервер, MS SQL Server)

Публикация № 445421

Администрирование - Администрирование данных 1С - Статистика базы данных

табель оптимизация производительность скорость SQL интерфейс ЗУП 8.2 план запросов тормозит

61
В форме типового документа "Табель учета рабочего времени" было обнаружено не совсем очевидное и довольно узкое место, заметно влияющее на скорость интерактивной работы с ячейками табеля.

Началось все с того, что я решил проанализировать рабочий MS SQL-сервер на предмет избыточных и недостающих индексов в базах 1С (навеяно статьей "Для чего НЕ нужны индексы").

Для анализа использовал запросы-шаблоны из этой статьи SQL Server: Базы данных и индексы.

"Прогнал" на одной из самых больших баз ЗУП запрос "Missing Indexes in current database by Index Advantage":

 Запрос анализа недостающих индексов SQL

Получилась вот такая интересная картинка:

Результат запроса


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

Судя по last_user_seek, проблема актуальна - запрос к данной таблице выполнялся всего несколько минут назад (анализ индексов выполнялся в 12:00 18.01.2016). А судя по значениям unique_compiles и user_seeks, отбор по колонкам из equality_columns данной таблицы выполняется довольно часто.

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

Структура БД

 

Выяснилось, что это регистр "Графики работы по видам времени". Поля_Fld2990, _Fld6561 и _Fld2988Rref, по которым требуется создать недостающий индекс (equality_columns), соответствуют измерениям регистра Месяц, План и ВидУчетаВремени. Проверим, существует ли индекс, удовлетворяющий такому набору полей:

Индексы регистра

 

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

Штатными средствами 1С создать требуемый индекс, не меняя порядок измерений в регистре, к сожалению, не удастся. Да, и прежде, чем что-то делать с индексами, лучше сначала проанализировать источник проблемы, запрос 1С, не использующий имеющиеся в СУБД индексы. 

Необходимо найти в конфигурации ЗУП запрос, выполняющий отбор по колонкам Месяц + План + ВидУчетаВремени регистра "Графики работы по видам времени" без отбора по полю ГрафикРаботы. Для этого я использовал глобальный поиск по конфигурации (искал по тексту "РегистрСведений.ГрафикиРаботыПоВидамВремени"). Для сужения области поиска учитывал информацию из колонки included_columns, в которой перечисляются получаемые в искомом запросе поля. 

Наиболее вероятный кандидат на проблемный запрос был обнаружен в модуле формы документа "Табель учета рабочего времени", в функции ПолучитьНормуВремениПоДню, вызываемой при вводе в ячейку табеля:

Запрос до оптимизации

В нем есть все искомые поля и отборы. Но есть проблема - в запросе явно используется отбор по полю ГрафикРаботы, которого по условиям нашего поиска быть как раз не должно. Очевидно, что здесь должен быть задействован уже существующий индекс по всем измерениям. Зачем тут нужен какой-то другой индекс и почему все-таки был выбран именно этот запрос?

Я остановил свое внимание на этом запросе только потому, что отбор по ГрафикРаботы здесь организован через ГДЕ ... В (ВЫБРАТЬ ... ИЗ ...). Ранее я уже неоднократно сталкивался с тем, что при таком написании условий отбора оптимизатор SQL срабатывает некорректно и не использует уже имеющиеся индексы (см. Ускоряем заполнение документа "Формирование записей книги покупок"). К тому же, именно на табель, а, точнее, на его интерактивную часть, указывали некоторые косвенные признаки, специфические для организации, в которой ведется учет.

Замер времени выполнения данного запроса показал длительность ~1,5 сек., что очень существенно для функции, срабатывающей при вводе в ячейку табеля. Это косвенно подтверждает, что найден тот самый проблемный запрос.

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

Итак, проверим мое предположение о некорректной отработке условия "ГДЕ ... В ()". Перепишем запрос на более предсказуемое внутреннее соединение:

 Запрос после оптимизации

 

Замер оптимизированной версии запроса показал ~0.08 секунд, то есть время выполнения уменьшилось почти в 20 раз.

В идеале, конечно, для подтверждения моих догадок, надо было заглянуть в исходный и результирующих план запроса через Profiler, но, уж простите, поленился. Данная "особенность" отработки "ГДЕ ... В (...)" на MS SQL уже набила оскомину и я был на 99,99% уверен, что увидел бы там какой-нибудь медленный Index Scan, который после оптимизации превратился бы в быстрый Index Seek. Я просто подождал один день после исправления проблемного запроса и снова выполнил анализ индексов - проблема, как я и ожидал, стала неактуальной.

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

ЗУП 2.5.97.1 (8.2.19.83), SQL Server 2005 (9.00.4053.00, 64-bit)


См. также:

Оптимизированная версия Т-13

61

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. chmv 19.01.16 11:36 Сейчас в теме
Спасибо. Табель на 100 человек проводится довольно долго, а если достигает 3000-4000 то очень долго. Сейчас быстрее. Насчет правильности пока не знаю
2. KAPACEB.AA 398 19.01.16 12:10 Сейчас в теме
(1) chmv,
Затронутая в статье проблема к проведению табеля отношения не имеет. Доработка потенциально устраняет только интерфейсные "тормоза" при работе с ячейками табеля.
Adilgeriy; chmv; echo77; +3 Ответить
3. 31337 25 19.01.16 16:51 Сейчас в теме
Спасибо, очень ясно описал и решение не сложное.
4. Pim 168 19.01.16 18:47 Сейчас в теме
1. Такой вариант оптимизации работает только для MS SQL или в остальных случаях тоже помогает?
2. Не быстрее будет вместо внутреннего соединения получить массив сотрудников (отдельным запросом), а уже затем использовать этот массив в качестве параметра в секции ГДЕ (на файловых базах в подобных случаях помогало).
5. KAPACEB.AA 398 19.01.16 21:00 Сейчас в теме
(4) Pim,
1. В остальных случаях не проверял )
2. Возможно сработает предложенный вариант, не пробовал. По моему опыту, соединение даёт более предсказуемый результат.
9. hornet_X 1 22.01.16 08:29 Сейчас в теме
(5) Оптимизация прошла успешно SQL Server 10.50.6220.0 ОС 6.1.7601. Спасибо за статью)
7. the1 395 20.01.16 14:04 Сейчас в теме
(4) Pim, скорее всего прироста не будет, т.к. будет использоваться тот же оператор В(&МассивСотрудников)
6. rasswet 82 20.01.16 07:40 Сейчас в теме
спасибо, очень подробно и ясно написано. Приятно читать!
8. bulas 184 20.01.16 14:36 Сейчас в теме
Время формирования Табеля уменьшилось (на количество строк по сотрудникам - 2300), так что прием действенный.
10. soulsteps 73 22.01.16 10:17 Сейчас в теме
Почему не вынесли подзапрос во временную таблицу с последующим индексированием поля "График работы", следующим же пакетом получали оставшиеся данные. Интересно, как бы изменилось время выполнения...
11. KAPACEB.AA 398 22.01.16 10:21 Сейчас в теме
(10) soulsteps,
Пробовал, на скорость не повлияло. В данном случае гораздо важнее задействовать индекс по физической таблице. Создание и индексирование временной таблицы, вероятно, добавит только дополнительные издержки. Хотя для надежности работы запроса, возможно, стоит сделать именно так, как Вы предложили.
12. kovgard 148 15.02.16 18:08 Сейчас в теме
Хорошая аналитическая работа. Проверил на файловой версии БД - реальное ускорение. Спасибо!
dj_serega; +1 Ответить
13. KAPACEB.AA 398 16.02.16 10:09 Сейчас в теме
(12) kovgard,
Спасибо за обратную связь.
Интересно, что на файловой сработало - честно говоря, были сомнения.
14. chmv 22.06.16 11:05 Сейчас в теме
А ПРИ ПРОВЕДЕНИИ КАК УСКОРИТЬ?
15. KAPACEB.AA 398 22.06.16 16:08 Сейчас в теме
(14) chmv,
До проведения пока руки не дошли.
Зато на днях удалось оптимизировать Т-13 (см. http://infostart.ru/public/532649/)
Оставьте свое сообщение

См. также

Базовый курс по разработке мобильных 1C-приложений для Android-устройств. Третий поток. Онлайн-интенсив с 11 февраля по 05 марта 2020 г. Промо

Данный онлайн-курс предусматривает изучение базовых принципов создания приложений для операционной системы Android, работающих на мобильной платформе “1С:Предприятие”. Курс предназначен для тех, кто уже имеет определенные навыки конфигурирования и программирования в системе “1С:Предприятие” при разработке прикладных решений для “обычных” компьютеров, но пока ещё не занимался разработкой 1С-приложений, предназначенных для работы на мобильных устройствах.

7500 рублей

Агрегатные функции СКД, о которых мало кто знает 359

Статья Программист Нет файла v8 v8::СКД 1cv8.cf Бесплатно (free) Практика программирования

Пользуетесь ли Вы всеми возможными агрегатными функциями, которые предоставляет система компоновки данных? Если Вы используете только: СУММА, КОЛИЧЕСТВО, МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ, то эта статья для Вас.

05.09.2019    16844    ids79    44       

Программы для исполнения 54-ФЗ Промо

С 01.02.2017 контрольно-кассовая техника должна отправлять электронные версии чеков оператору фискальных данных - правила установлены в 54-ФЗ ст.2 п.2. Инфостарт предлагает подборку программ, связанных с применением 54-ФЗ, ККТ и электронных чеков.

Отслеживание выполнения фонового задания 144

Статья Программист Нет файла v8 1cv8.cf Бесплатно (free) Практика программирования Универсальные функции Разработка

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

17.08.2019    13772    ids79    16       

Функции СКД: ВычислитьВыражение, ВычислитьВыражениеСГруппировкойМассив 266

Статья Программист Нет файла v8 v8::СКД 1cv8.cf Бесплатно (free) Практика программирования

Подробное описание и использование внутренних функций системы компоновки данных: Вычислить, ВычислитьВыражение, ВычислитьВыражениеСГруппировкойМассив, ВычислитьВыражениеСГруппировкойТаблицаЗначений.

08.08.2019    19877    ids79    31       

1C:Предприятие для программистов: Запросы и отчеты. Второй поток. Онлайн-интенсив с 17 марта по 16 апреля 2020 г. Промо

Данный онлайн-курс предусматривает углубленное изучение языка запросов и возможностей системы компоновки данных, которые понадобятся при разработке отчетов, работающих на платформе “1С:Предприятие” в рамках различных прикладных решений. Курс предназначен для тех, кто уже имеет определенные навыки конфигурирования и программирования в системе “1С:Предприятие”, а также для опытных пользователей различных прикладных решений, которые используют в своей работе отчеты разного назначения.

6500 рублей

СКД - наборы данных и связи между ними, создание собственной иерархии, вложенные отчеты 145

Статья Программист Нет файла v8 v8::СКД 1cv8.cf Бесплатно (free) Практика программирования

Набор данных объект. Использование в схеме компоновки нескольких наборов данных. Различные варианты связи наборов: объединение, соединение. Использование иерархии в отчетах на СКД. Создание собственной иерархии, иерархия детальных записей. Использование вложенных схем в отчетах на СКД.

26.07.2019    17789    ids79    8       

Перенос данных БП 3.0 => УТ 11 / КА 2 / ERP 2 (ЕРП) (перенос остатков, документов и справочной информации из "1С:Бухгалтерия предприятия 8", ред.3.0). Обновлено до БП 3.0.73.х, УТ 11.4.10.х, КА 2.4.10.х., ERP 2.4.10.х! Промо

Переносятся документы за выбранный период, справочная информация и остатки по счетам бух. учета в программу УТ 11 / КА 2 / ЕРП 2 (ERP). Переносятся все возможные виды операций ввода остатков на нужную дату. Есть отбор по периоду переноса документов и фильтр по организации, доступен выбор даты ввода остатков. Если нужно переносить что-то дополнительно, то обычно бесплатно добавляем это в перенос . Смотрите видеодемонстрацию со звуком - советами по переносу и рекомендациями настройки программ.

29700 руб.

СКД - использование расширений языка запросов, секция ХАРАКТЕРИСТИКИ 156

Статья Программист Нет файла v8 v8::СКД Бесплатно (free) Инструментарий разработчика Практика программирования

Автоматическое и не автоматическое заполнение полей компоновки данных. Использование расширений языка запросов для СКД «{…}», секция ВЫБРАТЬ, секция ГДЕ, параметры виртуальных таблиц. Автоматизированное использование дополнительных данных в запросе: секция ХАРАКТЕРИСТИКИ.

17.07.2019    14380    ids79    27       

Регистры сведений. За кулисами 129

Статья Программист Нет файла v8 1cv8.cf Бесплатно (free) Практика программирования Разработка

Небольшие заметки по внутреннему устройству регистров сведений.

09.07.2019    11601    YPermitin    12       

Готовые переносы данных из различных конфигураций 1C Промо

Рекомендуем готовые решения для переноса данных из различных конфигураций 1C. C техподдержкой от разработчиков и гарантией от Инфостарт.

"Меньше копипаста!", или как Вася универсальную процедуру писал 184

Статья Программист Стажер Нет файла v8 v8::СКД 1cv8.cf Бесплатно (free) Практика программирования Разработка

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

04.07.2019    10281    SeiOkami    49       

Создание отчетов с помощью СКД - основные понятия и элементы 219

Статья Программист Нет файла v8 v8::СКД Бесплатно (free) Практика программирования Математика и алгоритмы

Основные принципы работы СКД. Понятия схемы компоновки и макета компоновки. Описание основных элементов схемы компоновки: наборы данных, поля, вычисляемые поля, ресурсы, параметры.

25.06.2019    24752    ids79    17       

Базовый курс для начинающих 1С-программистов. Пятый поток. Онлайн-курс с 12 февраля по 15 апреля 2020 г. Промо

Данный онлайн-курс является начальной ступенью по изучению базовых принципов программирования в системе “1С:Предприятие” и предназначен для обучения 1С-программированию “с нуля”.

4500/9500 рублей

Многопоточное ускорение однопользовательских нагрузок в 1С + Microsoft SQL Server 2017 179

Статья Программист Нет файла v8 v8::Запросы Бесплатно (free) Практика программирования Разработка

Взаимодействие с Microsoft SQL Server нередко вызывает трудности у 1С-ников, а потому интересны любые моменты, связанные с его использованием. О своем опыте работы с новым SQL Server 2017 участникам конференции Infostart-2018 рассказал директор ООО «Аналитика софт» Дмитрий Дудин.

11.06.2019    13987    dmurk    134       

Регистры накопления. Структура хранения в базе данных 178

Статья Программист Нет файла v8 1cv8.cf Бесплатно (free) Практика программирования Разработка

Структура хранения регистров накопления в базе данных для платформы 1С:Предприятие 8.x. Первая часть в серии публикаций.

16.05.2019    22190    YPermitin    27       

Новый раздел на Инфостарте - Electronic Software Distribution Промо

Инфостарт напоминает: на нашем сайте можно купить не только ПО, связанное с 1С. В нашем арсенале – ESD-лицензии на ПО от ведущих вендоров: Microsoft, Kaspersky, ESET, Dr.Web, Аскон и другие.

  • Низкие цены, без скрытых платежей и наценок
  • Оперативная отгрузка
  • Возможность оплаты с личного счета (кешбек, обмен стартмани на рубли и т.п.)
  • Покупки идут в накопления для получения скидочных карт лояльности Silver (5%) и Gold (10%)

Выполнение внешней обработки в фоновом задании 149

Статья Программист Нет файла v8 1cv8.cf Бесплатно (free) Практика программирования Разработка

Подробное описание подхода к созданию длительной операции на основе внешней обработки. Реализация протестирована на 1С 8.3.12.1714 (x64).

11.05.2019    14037    Eret1k    23       

Выгрузка документа по условию 5

Статья Программист Нет файла v8 Бесплатно (free) Практика программирования Разработка

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

25.04.2019    8089    m-rv    2       

Перенос документов, остатков и справочников КА 1.1 => КА 2 / УТ 11. Обновлено до КА 2.4.10.х и УТ 11.4.10.х! Промо

Более 130 компаний выполнили переход на КА 2 или УТ 11 с помощью нашей разработки! Позволяет перенести не только остатки и справочники (как типовая обработка), но и документы за нужный период времени. Предоставляем техподдержку, оперативно исправляем замечания, выпускаем обновления при выходе новых релизов программ 1С. Вы можете проверить разработку до покупки: сделаем бесплатный тестовый перенос из вашей базы КА 1.1 и предоставим доступ к базе-результату через веб-клиент!

29700 руб.

Как настроить правильную техподдержку (helpdesk, service desk на коленке) 39

Статья Программист Бизнес-аналитик Руководитель проекта Нет файла v8 УУ Управление взаимоотношениями с клиентами (СRM) Документооборот и делопроизводство Монитор заказов Учет рабочего времени Бесплатно (free) Управление услугами и сервисом

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

24.04.2019    9380    siddy    0       

5 простых шагов и 15 минут на разворачивание инструмента мониторинга проблем производительности базы 1С 203

Статья Системный администратор Программист Нет файла v8 Windows Бесплатно (free) Производительность и оптимизация (HighLoad)

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

18.04.2019    20062    ivanov660    68       

Новогодние скидки на авторское ПО Промо

В преддверии праздника в Маркетплейсе на Инфостарт действует скидка на все платные авторские программы. Размер скидок начинается от 10%. Советуем не откладывать покупки, многие наши партнеры повышают цены на свои продукты именно в начале нового года.

Как прикрутить ГУИД к регистру сведений 23

Статья Программист Нет файла v8 Бесплатно (free) Практика программирования Перенос данных из 1C8 в 1C8 Разработка

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

16.04.2019    10666    m-rv    16       

О расширениях замолвите слово... 195

Статья Программист Стажер Нет файла v8 Бесплатно (free) Практика программирования Разработка

О чём стоит задуматься при принятии решения о создании расширения конфигурации…

07.04.2019    19332    ellavs    125       

Перенос данных УТ 10.3 => УТ 11 / КА 2 / ERP 2 (ЕРП 2) (документы, остатки и справочная информация из "1С:Управление торговлей, ред. 10.3" в УТ 11 / КА 2 / ERP 2). Обновлен до УТ 10.3.56.х, УТ 11.4.10.х, КА 2.4.10.х и ERP 2.4.10.х! Промо

Уже более 100 компаний приобрели перенос и выполнили переход на УТ 11 / КА 2 / ERP 2 с помощью нашей разработки! Обработка перехода с УТ 10.3 на УТ 11 / КА 2 / ERP 2 позволяет перенести не только остатки на указанную дату (как типовой перенос), но и все возможные документы за выбранный период. При выходе новых релизов этих программ оперативно выпускаем обновление обработки. Предоставляем техническую поддержку. Можем сделать бесплатный тестовый перенос!

29700 руб.

Git-репозитории для 1С-кода (опыт использования при небольших проектах) 202

Статья Программист Стажер Нет файла v8 Windows Бесплатно (free) Практика программирования Разработка

Инструкции по взаимодействию с Git-репозиторием, которые писались для тех наших программистов, которые вообще никогда не работали с Git (руководства в духе "Как получить код из git-репозитория?", "Как отправить код в git-репозиторий")...

28.03.2019    15919    ellavs    83       

Трюки с внешними источниками данных 167

Статья Программист Нет файла v8 1cv8.cf Бесплатно (free) Практика программирования Разработка

Некоторые трюки для преодоления ограничений внешних источников данных.

14.03.2019    17000    YPermitin    53       

С 2020 года сервис «Продление поддержки конфигурации 1С:УПП» подорожает вдвое Промо

Успейте продлить поддержку УПП до повышения цен! Фирма «1С» предупредила об изменении цен на сервис «Продление поддержки конфигурации "1С:Управление производственным предприятием"». С 1 января 2020 года сервис подорожает в два раза.

Возможности типовых шаблонов ограничения доступа на уровне записей (RLS) 174

Статья Программист Нет файла v8 v8::Права Бесплатно (free) Практика программирования БСП (Библиотека стандартных подсистем) Роли и права

Краткий обзор применения типовых шаблонов ограничения доступа на уровне записей в конфигурациях, созданных на базе БСП: #ПоЗначениям, #ПоНаборамЗначений, #ПоЗначениямРасширенный, #ПоЗначениямИНаборамРасширенный

03.02.2019    19910    ids79    9       

Перенос данных КА 1.1 / УПП 1.3 => БП 3.0 (перенос остатков, документов и справочников из "1С:Комплексная автоматизация 1.1" / УПП 1.3 в "1С:Бухгалтерия 3.0"). Обновлен до версий КА 1.1.115.х, УПП 1.3.127.х! Промо

Разработка позволяет перенести остатки по всем счетам бух.учета в программу "1С:Бухгалтерия предприятия 8", ред. 3.0 на выбранную дату начала ведения учета. Также переносятся документы за период и вся необходимая справочная информация. Правила оперативно обновляю при выходе новых релизов. Рассылка обновлений правил бесплатно в течение 12 месяцев. Есть видеодемонстрация проведения переноса данных. Конфигурации при использовании обмена остаются полностью типовыми. Перенос данных возможен в Бухгалтерию 3.0 версии ПРОФ, КОРП или базовую.

24700 руб.

EnterpriseData – часть 2. Процесс выгрузки данных 128

Статья Программист Нет файла v8 v8::УФ Россия Бесплатно (free) Практика программирования Обмен через XML

Основные этапы выгрузки данных через ED, обработчики событий выгрузки, правила обработки данных, правила конвертации объектов, конвертация свойств первого и второго этапов, процедуры БСП, используемые при выгрузке данных, структура «КомпонентыОбмена».

26.12.2018    15625    ids79    27       

Новый подход к обмену данными EnterpriseData 210

Статья Программист Нет файла v8 v8::УФ Россия Бесплатно (free) Практика программирования Обмен через XML

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

14.12.2018    25440    ids79    72