Каждый технический специалист или системный администратор, работающий с базами данных 1С, рано или поздно сталкивается с ситуацией непонятного и критического падения производительности. Одной из таких неприятных проблем является длительное зависание системы (на 10–15 минут) при попытке добавить нового контрагента по ИНН. В этот момент диспетчер задач показывает стопроцентную загрузку дисковой подсистемы (HDD или SSD), а процессор практически бездействует, ожидая завершения операций ввода-вывода (I/O). Смена аппаратного обеспечения, покупка дорогостоящих NVMe-накопителей или замена процессора не дают никакого результата. В этой статье мы подробно разберем, почему возникает эта проблема, проанализируем внутреннее поведение СУБД и напишем готовое решение для исправления этой ошибки раз и навсегда.
Для начала давайте разберем логику работы платформы 1С:Предприятие при заполнении реквизитов контрагента по ИНН. В этот момент программа выполняет следующие действия:
СведенияОЗагрузкеСубъектовРФ() общего модуля адресного классификатора.Именно на четвертом шаге скрывается «узкое место» системы. Внутри этой функции выполняется запрос, соединяющий временную таблицу классификатора регионов с физической таблицей регистра сведений РегистрСведений.АдресныеОбъекты.
Поскольку адресный классификатор содержит детальную информацию обо всех улицах, домах, городах и муниципальных делениях Российской Федерации, физический регистр РегистрСведений.АдресныеОбъекты является одной из самых объемных таблиц во всей базе данных 1С. Количество записей в нем может исчисляться десятками миллионов строк, а объем занимаемого пространства на диске — гигабайтами.
Давайте посмотрим на то, как устроен оригинальный запрос БСП в функции СведенияОЗагрузкеСубъектовРФ(). Программа передает список субъектов РФ в качестве параметра запроса (таблицы значений) и помещает его во временную таблицу Классификатор. После этого происходит левое соединение (ЛЕВОЕ СОЕДИНЕНИЕ) этой временной таблицы с многомиллионной физической таблицей регистра адресных объектов.
Главная ошибка разработчиков БСП заключается в отсутствии индексирования временной таблицы по соединяемому полю КодСубъектаРФ.
Когда СУБД пытается связать неиндексированную временную таблицу (в которой находится всего около 89 строк субъектов РФ) с гигантским регистром адресных сведений, планировщик запросов не может эффективно применить индекс физического регистра. Вместо быстрого точечного поиска (операция Index Seek) СУБД вынуждена выполнять полное сканирование (Index Scan или Table Scan) всей многомиллионной таблицы адресных ориентиров для каждой из строк временной таблицы. Это приводит к так называемому "сканированию в цикле" (Nested Loops с полным перебором).
В результате файловая СУБД 1С начинает последовательно вычитывать гигабайты данных с физического диска в оперативную память. Диск моментально нагружается на 100%, а пользователь видит колесо ожидания в течение 10–15 минут.
Многие программисты отмечают, что на клиент-серверных базах (под управлением Microsoft SQL Server или PostgreSQL) эта проблема практически не проявляется или проходит незаметно за доли секунды. Почему так происходит?
Дело в том, что промышленные СУБД обладают интеллектуальными и сложными оптимизаторами запросов. Видя неоптимальное соединение, планировщик SQL Server может самостоятельно принять решение на лету построить хеш-таблицу в оперативной памяти (Hash Match Join) или автоматически создать временный индекс в служебной базе TempDB. Файловый же движок базы данных 1С (файл 1Cv8.1CD) обладает гораздо более простым оптимизатором. Он не умеет самостоятельно компенсировать ошибки разработчика в структуре запроса, поэтому выполняет соединение «в лоб», парализуя работу жесткого диска.
Чтобы исправить эту проблему и при этом сохранить конфигурацию на полной поддержке (без снятия с замка, что критически важно для последующих беспроблемных обновлений), мы применим механизм Расширений конфигурации. Рассмотрим этот процесс по шагам.
Откроем конфигуратор нашей базы данных, перейдем в меню Конфигурация — Расширения конфигурации. Создадим новое расширение, снимем галочки "Активно", "Безопасный режим" и "Защита от опасных действий" для обеспечения беспрепятственного выполнения кода запроса (после отладки галочку "Активно" нужно будет вернуть).
Найдем в основной конфигурации общий модуль, отвечающий за работу с адресным классификатором (обычно это служебный модуль БСП, например, АдресныйКлассификаторСлужебный или АдресныйКлассификаторВнутренний). Кликнем по нему правой кнопкой мыши и выберем пункт Добавить в расширение. Установим для заимствованного метода аннотацию &Вместо.
Заменим стандартный метод БСП оптимизированной версией. Ниже представлен готовый программный код функции СведенияОЗагрузкеСубъектовРФ(), который решает проблему со стопроцентной загрузкой диска:
Функция Расш1_СведенияОЗагрузкеСубъектовРФ() Экспорт
Классификатор = РегистрыСведений.АдресныеОбъекты.КлассификаторСубъектовРФ();
// Выбираем все возможные данные - и из регистра, и из классификатора.
// Считаем, что уникальность обеспечивается не идентификатором (что правильно), а кодом субъекта
// из-за особенностей платформы по передаче уникального идентификатора в таблицу-параметр запроса.
Запрос = Новый Запрос("ВЫБРАТЬ
| Параметр.Наименование КАК Наименование,
| Параметр.Сокращение КАК Сокращение,
| Параметр.КодСубъектаРФ КАК КодСубъектаРФ
|ПОМЕСТИТЬ Классификатор
|ИЗ
| &Классификатор КАК Параметр
|
|ИНДЕКСИРОВАТЬ ПО
| КодСубъектаРФ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Классификатор.Наименование + "" "" + Классификатор.Сокращение КАК Представление,
| Классификатор.КодСубъектаРФ КАК КодСубъектаРФ,
| ЗагруженныеВерсииАдресныхСведений.Версия КАК Версия,
| ЗагруженныеВерсииАдресныхСведений.ДатаВерсии КАК ДатаВерсии,
| ЗагруженныеВерсииАдресныхСведений.ДатаЗагрузки КАК ДатаЗагрузки,
| АдресныеОбъекты.Идентификатор КАК Идентификатор,
| НЕ ЗагруженныеВерсииАдресныхСведений.Версия ЕСТЬ NULL КАК Загружено,
| ДОБАВИТЬКДАТЕ(ЗагруженныеВерсииАдресныхСведений.ДатаЗагрузки, МЕСЯЦ, 1) < &ТекущаяДата КАК Устарело
|ИЗ
| Классификатор КАК Классификатор
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АдресныеОбъекты КАК АдресныеОбъекты
| ПО (АдресныеОбъекты.КодСубъектаРФ = Классификатор.КодСубъектаРФ)
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗагруженныеВерсииАдресныхСведений КАК ЗагруженныеВерсииАдресныхСведений
| ПО Классификатор.КодСубъектаРФ = ЗагруженныеВерсииАдресныхСведений.КодСубъектаРФ
|ГДЕ
| АдресныеОбъекты.Уровень = 1
|
|УПОРЯДОЧИТЬ ПО
| КодСубъектаРФ");
Запрос.УстановитьПараметр("Классификатор", Классификатор);
Запрос.УстановитьПараметр("ТекущаяДата", ТекущаяДатаСеанса());
РезультатВыборкиЗагруженныхСведений = Запрос.Выполнить();
Если РезультатВыборкиЗагруженныхСведений.Пустой() Тогда
ВыполнитьНачальноеЗаполнение();
РезультатВыборкиЗагруженныхСведений = Запрос.Выполнить();
КонецЕсли;
ЗагруженныеСведения = РезультатВыборкиЗагруженныхСведений.Выгрузить();
ЗагруженныеСведения.Индексы.Добавить("Идентификатор");
ЗагруженныеСведения.Индексы.Добавить("КодСубъектаРФ");
ЗагруженныеСведения.Индексы.Добавить("Загружено");
// Корректируем идентификаторы из-за особенностей платформы по передаче уникального
// идентификатора в таблицу-параметр запроса.
Для Каждого Строка Из ЗагруженныеСведения Цикл
Если Не ЗначениеЗаполнено(Строка.Идентификатор) Тогда
СтрокаКлассификатора = Классификатор.Найти(Строка.КодСубъектаРФ, "КодСубъектаРФ");
Если СтрокаКлассификатора <> Неопределено Тогда
Строка.Идентификатор = СтрокаКлассификатора.Идентификатор;
КонецЕсли;
КонецЕсли;
КонецЦикла;
Возврат ЗагруженныеСведения;
КонецФункции
Проанализируем детально, за счет чего измененный запрос начинает выполняться мгновенно:
Классификатор по полю КодСубъектаРФ сразу после ее наполнения данными из параметра. Теперь файловая СУБД знает физическое расположение каждой записи и может использовать быстрый бинарный поиск при соединении таблиц.ИЗ. Теперь левое соединение начинается с проиндексированной таблицы Классификатор, к которой последовательно пристыковываются данные из физического регистра АдресныеОбъекты и таблицы версий.АдресныеОбъекты.Уровень = 1 теперь отрабатывает корректно и без лишних накладных расходов, поскольку СУБД имеет быстрый доступ к связующему полю КодСубъектаРФ.Применив это простое расширение, мы исключили тяжелое сканирование таблиц диска. Время выполнения операции добавления контрагента по ИНН сокращается с мучительных 10 минут до сотых долей секунды, а нагрузка на жесткий диск снижается до нулевых показателей.