До начала оптимизации дело обстояло так:
В отдельных месяцах проведение Регламентной операции "Рассчитать себестоимость (БУ, НУ)" удавалось осуществить только в выходные дни - проведение длилось более суток. При этом происходил лавинообразный рост служебной базы tempdb до астрономических 360 Гб.
Конфигурация:
- Управление производственным предприятием, редакция 1.3 (1.3.79.2)
- 1С:Предприятие 8.3 (8.3.8.1784)
- Microsoft SQL Server 2012
- Используется партионный учет
- Расчет себестоимости ведется по переделам
- Размер базы (.mdf) 38 Гб
Решение:
1. В борьбе с разрастанием служебной базы tempdb кардинально решить вопрос удалось следующим образом:
В процедурах:
- ПроцедурыРасчетаСебестоимостиВыпуска.СформироватьТекстЗапросаЗаполнениеКорректировкиВстречногоВыпускаПродукции();
- ПроцедурыРасчетаСебестоимостиВыпуска.СформироватьТекстЗапросаПоВыпускуПродукцииИЗатратамНаВыпуск();
- ПроцедурыРасчетаСебестоимостиВыпуска.СформироватьТекстЗапросаПоЗатратамНаВыпуск();
- ПроцедурыРасчетаСебестоимостиВыпуска.СформироватьТекстЗапросаБазаРаспределенияЗатратНаПродукцию();
- РасчетСебестоимостиВыпускаРаспределениеПоПеределам.СформироватьТекстЗапросаВыпускБезПрямыхРасходов();
- РасчетСебестоимостиВыпускаРаспределениеПоПеределам.СформироватьТекстЗапросаВыпускНаРаспределяемыеРасходы();
- РасчетСебестоимостиВыпускаРаспределениеПоПеределам.СформироватьТекстЗапросаМатериальныеПроизводственныеРасходы();
- РасчетСебестоимостиВыпускаРаспределениеПоПеределам.СформироватьТекстЗапросаНематериальныеПроизводственныеРасходы();
- РасчетСебестоимостиВыпускаРаспределениеПоПеределам.СформироватьТекстЗапросаНематериальныеПроизводственныеРасходыНУ();
1.1. В текстах запросов конструкции языка связанные с обращением к реквизиту через точку заменяем на явное соединение с соответствующей таблицей. Особенно это актуально для справочника Номенклатура (реквизит ВестиУчетПоСериямВНЗП).
Пример: РасчетСебестоимостиВыпускаРаспределениеПоПеределам.СформироватьТекстЗапросаМатериальныеПроизводственныеРасходы()
//+ - добавленные строки
ВЫБРАТЬ РАЗЛИЧНЫЕ
...
//ВЫБОР КОГДА ЗатратыНаВыпуск.Продукция.ВестиУчетПоСериямВНЗП ТОГДА
ВЫБОР КОГДА ЕСТЬNULL(ЗатратыНаВыпускПродукция.ВестиУчетПоСериямВНЗП, ЛОЖЬ) ТОГДА //+
...
//ВЫБОР КОГДА ЗатратыНаВыпуск.Затрата.ВестиУчетПоСериямВНЗП ТОГДА
ВЫБОР КОГДА ЕСТЬNULL(ЗатратыНаВыпускЗатрата.ВестиУчетПоСериямВНЗП, ЛОЖЬ) ТОГДА //+
...
ИЗ
РегистрНакопления.ЗатратыНаВыпускПродукции%СуффиксУчета% КАК ЗатратыНаВыпуск
...
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК ЗатратыНаВыпускПродукция //+
ПО ЗатратыНаВыпускПродукция.Ссылка = ЗатратыНаВыпуск.Продукция //+
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК ЗатратыНаВыпускЗатрата //+
ПО ЗатратыНаВыпускЗатрата.Ссылка = ЗатратыНаВыпуск.Затрата //+
1.2. В текстах запросов условия следующего вида (имеет смысл для вложенных запросов с объемистой выборкой):
ГДЕ
ВыпускПродукции.Продукция В (
ВЫБРАТЬ РАЗЛИЧНЫЕ
БазаРаспределенияЗатрат.Продукция
ИЗ
РегистрСведений.БазаРаспределенияЗатрат%СуффиксУчета% КАК БазаРаспределенияЗатрат
ГДЕ
БазаРаспределенияЗатрат.Период МЕЖДУ &НачДата И &КонДата
И БазаРаспределенияЗатрат.РаспределениеКосвенныхЗатрат = &РаспределениеКосвенныхЗатрат
И БазаРаспределенияЗатрат.РасчетСебестоимостиВыпуска
)
заменяем на внутреннее соединение следующего вида:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ РАЗЛИЧНЫЕ
БазаРаспределенияЗатрат.Продукция
ИЗ
РегистрСведений.БазаРаспределенияЗатрат%СуффиксУчета% КАК БазаРаспределенияЗатрат
ГДЕ
БазаРаспределенияЗатрат.Период МЕЖДУ &НачДата И &КонДата
И БазаРаспределенияЗатрат.РаспределениеКосвенныхЗатрат = &РаспределениеКосвенныхЗатрат
И БазаРаспределенияЗатрат.РасчетСебестоимостиВыпуска
) КАК БазаРаспределенияЗатрат
ПО БазаРаспределенияЗатрат.Продукция = ВыпускПродукции.Продукция
В результате этих двух доработок (1.1 и 1.2) максимальный рост tempdb в итоге составил 1.8 Гб, так же существенно увеличилась скорость выполнения запросов.
2. Поиск из результата запроса заменяем на поиск строк из таблицы значений методом НайтиСтроки (в местах где происходит поиск в цикле с многотысячным количеством итераций)
Пример: ПроцедурыРасчетаСебестоимостиВыпуска.КорректировкаДвиженийПоВыпускуПродукции()
//+ - добавленные строки
//ВыборкаПоВыпуску = РезультатЗапросаПоВыпускуПродукции.Выбрать();
ТаблицаПоВыпуску = РезультатЗапросаПоВыпускуПродукции.Выгрузить(); //+
...
ОбходПоЗатратам = РезультатЗапросаПоЗатратамНаВыпускПродукции.Выбрать();
Пока ОбходПоЗатратам.Следующий() Цикл
СтруктураПоискаВыпуск = ПолучитьСтруктуруПоискаСтрокВыпускаПродукции(
СтруктураШапкиДокумента,
ОбходПоЗатратам
);
...
//ВыборкаПоВыпуску.Сбросить();
//Пока ВыборкаПоВыпуску.НайтиСледующий(СтруктураПоискаВыпуск) Цикл // На этой строчке замер производительности показывал многочасовые ожидания
МассивСтрокПоВыпуску = ТаблицаПоВыпуску.НайтиСтроки(СтруктураПоискаВыпуск); //+
Для каждого ВыборкаПоВыпуску Из МассивСтрокПоВыпуску Цикл //+
...
КонецЦикла;
...
КонецЦикла;
3. Перебор строк с нулевым переделом в отсортированной таблице заменяем на перебор элементов массива полученного из таблицы методом НайтиСтроки, то есть исключаем затратную операцию сортировки.
Пример: РасчетСебестоимостиВыпускаРаспределениеПоПеределам.СоздатьТабПеределов() - здесь удалось достичь 7-ми кратного выигрыша по времени !!!
//+ - добавленные строки
//МаксИндекс = ТабПеределов.Количество() - 1;
...
Пока ПроставленПередел Цикл
...
//ТабПеределов.Сортировать("НомерПередела Убыв"); // На этой строчке замер производительности показывал многочасовые ожидания (в нашей таблице до 6 млн. записей)
//ТекСтрока = ТабПеределов.Найти(0, "НомерПередела");
СтрокиСПустымиПеределами = ТабПеределов.НайтиСтроки(Новый Структура("НомерПередела", 0)); //+
//Если ТекСтрока = Неопределено Тогда
Если СтрокиСПустымиПеределами.Количество() = 0 Тогда //+
Прервать;
КонецЕсли;
//Индекс = ТабПеределов.Индекс(ТекСтрока);
...
//ИндексСтроки = Индекс;
//Пока ИндексСтроки <= МаксИндекс Цикл
МаксИндекс = СтрокиСПустымиПеределами.ВГраница(); //+
Для Сч=0 По МаксИндекс Цикл //+
ИндексСтроки = МаксИндекс - Сч; //+
//СтрокаТаблицы = ТабПеределов[ИндексСтроки];
СтрокаТаблицы = СтрокиСПустымиПеределами[ИндексСтроки]; //+
...
СтрокаТаблицы.НомерПередела = 1;
СтрокиСПустымиПеределами.Удалить(ИндексСтроки); //+ // Удаление текущей строки с проставленным переделом
...
//ИндексСтроки = ИндексСтроки + 1;
КонецЦикла;
...
КонецЦикла;
4. Так же был применён метод асинхронной записи регистров. По этому методу выигрыш по времени в наших условиях составил до 30%
Суть метода:
В документе Расчет себестоимости в оригинальном алгоритме реализована запись движений регистров порциями по 1000 строк по мере расчета.
Если НаборЗаписейБазаРаспределенияЗатрат.Количество() = 1000 Тогда
НаборЗаписейБазаРаспределенияЗатрат.Записать(Ложь);
КонецЕсли;
Здесь метод Записать(Ложь); дописывает записи к уже существующим в информационной базе, а затем записи текущего набора очищает. При этом, в наших условиях, время расчета очередной порции и время записи строк этого набора в базу примерно сопоставимы.
Выносим процесс записи в отдельную процедуру и запускаем ее в фоновом задании передавая записи набора в виде таблицы значений через параметры. В этом случае расчет очередной порции строк начнется не дожидаясь завершения записи предыдущей.
Графически эти два варианта выглядят так:
| 1. Последовательные Расчет и Запись | ||||||||||
| Основной поток | Расчет 1 | Запись 1 | Расчет 2 | Запись 2 | Расчет 3 | Запись 3 | Расчет 4 | Запись 4 | Расчет 5 | Запись 5 |
| 2. Асинхронные Расчет и Запись | ||||||||||
| Основной поток | Расчет 1 | Расчет 2 | Расчет 3 | Расчет 4 | Расчет 5 | Ожидание | ||||
| Фоновое задание 1 | Запись 1 | |||||||||
| Фоновое задание 2 | Запись 2 | |||||||||
| Фоновое задание 3 | Запись 3 | |||||||||
| Фоновое задание 4 | Запись 4 | |||||||||
| Фоновое задание 5 | Запись 5 | |||||||||
Замечания и ограничения применимости данного метода:
- При стандартном проведении запись в регистры из фонового задания невозможна из-за конфликта блокировок (режим управления блокировкой данных - автоматический), метод можно реализовать только при так называемом "Проведении вне транзакции".
- Как следствие из предыдущего, в коде необходимо предусмотреть признаки по которым при стандартном проведении должен выполняться последовательный алгоритм, а при проведении вне транзакции алгоритм с асинхронной записью.
- Перед внедрением данного метода необходимо выполнить замер производительности. Может оказаться, что в Ваших условиях программно-аппаратной реализации, запись в информационную базу происходит достаточно быстро по сравнению с расчетом или наоборот. В этих случаях затраты времени на выгрузку записей набора в таблицу значений для передачи в фоновое задание и запуск фонового задания могут оказаться выше ожидаемого выигрыша.
- Фоновые задания по каждому виду регистра необходимо выстраивать в стек, в котором каждое последующее задание должно ожидать завершение предыдущего.
- В местах записи последней порции набора записей (до условия: Если НаборЗаписейБазаРаспределенияЗатрат.Модифицированность() Тогда) необходимо выполнить ожидание завершения фоновых заданий, запущенных в контексте по данному регистру.
Реализация метода:
Задачи.РегламентныеОперацииЗакрытияМесяца.ФормаЗадачи - на командную панель формы добавляем кнопку "Провести вне транзакции" и назначаем следующий обработчик:
Процедура КоманднаяПанельДокументыРОПровестиВнеТранзакции(Кнопка)
Ответ = Вопрос("Внимание! Проведение документов вне транзакции можно выполнять только,
|если одновременно не вводятся первичные документы в периоде, предшествующем проводимому документу.
|Провести документы вне транзакции?", РежимДиалогаВопрос.ДаНет, 100, КодВозвратаДиалога.Нет);
Если Ответ <> КодВозвратаДиалога.Да Тогда
Возврат;
КонецЕсли;
//Перед проведением упорядочим сформированные документы: сначала УУ, потом БУ, потом НУ
//Для документа РасчетСебестоимости важно чтобы сначала провелся документ БУ, а лишь затем НУ
СформированныеДокументы.Сортировать("ОтражатьВУправленческомУчете убыв, ОтражатьВБухгалтерскомУчете убыв, ОтражатьВНалоговомУчете убыв");
МассивДокументы = ПолучитьВыбранныеДокументы(Истина);
Для каждого Строка из МассивДокументы Цикл
ДокументОбъект = Строка.Документ.ПолучитьОбъект();
Если ДокументОбъект <> Неопределено И НЕ ДокументОбъект.ПометкаУдаления Тогда
Попытка
ДокументОбъект.Заблокировать();
Исключение
ВызватьИсключение "Не удалось заблокировать документ " + Строка.Документ + ", " + ОписаниеОшибки();
КонецПопытки;
Попытка
Если ТипЗнч(Строка.Документ) = Тип("ДокументСсылка.РасчетСебестоимостиВыпуска") Тогда
Если ДокументОбъект.Проведен Тогда
ДокументОбъект.Проведен = Ложь;
ДокументОбъект.Записать(РежимЗаписиДокумента.Запись);
КонецЕсли;
// Также у непроведенного документа могут остаться движения, если предыдущее проведение вне транзакции завершилось внештатно (аварийно). Такие движения необходимо очистить.
ЛУ_КлиентСервер.ОчиститьДвиженияДокумента(ДокументОбъект.Движения, ДокументОбъект.Ссылка);
ДокументОбъект.мУдалятьДвижения = Ложь;
Отказ = Ложь;
глЗначениеПеременнойУстановить("НаборыДвиженийЗаписыватьАсинхронно", Истина, Истина); // Взводим флаг асинхронной записи движений
ДокументОбъект.ОбработкаПроведения(Отказ, РежимПроведенияДокумента.Неоперативный);
глЗначениеПеременнойУстановить("НаборыДвиженийЗаписыватьАсинхронно", Ложь, Истина); // Отключаем асинхронную запись
Если Не Отказ Тогда
Для Каждого ТекущееДвижение Из ДокументОбъект.Движения Цикл
Если ТекущееДвижение.Модифицированность() Тогда
ТекущееДвижение.Записать();
КонецЕсли;
КонецЦикла;
ДокументОбъект.Проведен = Истина;
ДокументОбъект.Записать(РежимЗаписиДокумента.Запись);
КонецЕсли;
Иначе // Типовой функционал проведения
ДокументОбъект.Записать(РежимЗаписиДокумента.Проведение);
КонецЕсли;
Исключение
глЗначениеПеременнойУстановить("НаборыДвиженийЗаписыватьАсинхронно", Ложь, Истина);
ВызватьИсключение "Не удалось провести документ " + Строка.Документ + ", " + ОписаниеОшибки();
КонецПопытки;
ДокументОбъект.Разблокировать();
Строка.ДокументПроведен = ДокументОбъект.Проведен;
КонецЕсли;
КонецЦикла;
КонецПроцедуры
В местах записи очередной порции движений добавляем вызов функции: НаборЗаписейЗаписатьАсинхронно(НаборЗаписей)
Если НаборЗаписейЗатратыНаВыпуск.Количество() = 1000 Тогда
Если НЕ ЛУ_КлиентСервер.НаборЗаписейЗаписатьАсинхронно(НаборЗаписейЗатратыНаВыпуск) Тогда //+
НаборЗаписейЗатратыНаВыпуск.Записать(Ложь);
КонецЕсли; //+
КонецЕсли;
НаборЗаписейЗаписатьАсинхронно - функция общего модуля ЛУ_КлиентСервер (Сервер, Клиент обычное приложение) со следующим листингом:
Функция НаборЗаписейЗаписатьАсинхронно(НаборЗаписей) Экспорт
// Проверка активности режима асинхронной записи
НаборыДвиженийЗаписыватьАсинхронно = Неопределено;
Если НЕ РаботаСОбщимиПеременными.ПолучитьИзКэшаКонфигурации("НаборыДвиженийЗаписыватьАсинхронно", НаборыДвиженийЗаписыватьАсинхронно, Неопределено, Ложь)
или НЕ НаборыДвиженийЗаписыватьАсинхронно Тогда
Возврат Ложь;
КонецЕсли;
// Передаем набор записей для записи в фоновое задание,
// которое выстраивается в очередь в виде стека с другими фоновыми заданиями по данному регистру,
// очищаем набор записей в текущем контексте и передаем управление назад в процедуру проведения документа,
// тоесть исключаем ожидание записи регистра для основного потока
ТипРегистра = Неопределено;
НаборЗаписейМетаданные = НаборЗаписей.Метаданные();
Если Метаданные.РегистрыНакопления.Содержит(НаборЗаписейМетаданные) Тогда
ТипРегистра = "РегистрНакопления";
ИначеЕсли Метаданные.РегистрыБухгалтерии.Содержит(НаборЗаписейМетаданные) Тогда
ТипРегистра = "РегистрБухгалтерии";
ИначеЕсли Метаданные.РегистрыСведений.Содержит(НаборЗаписейМетаданные) Тогда
ТипРегистра = "РегистрСведений";
ИначеЕсли Метаданные.РегистрыРасчета.Содержит(НаборЗаписейМетаданные) Тогда
ТипРегистра = "РегистрРасчета";
Иначе
Возврат Ложь;
КонецЕсли;
ИмяРегистра = НаборЗаписей.Метаданные().Имя;
Замещение = Ложь;
АктивныеФоновыеЗадания = ФоновыеЗадания.ПолучитьФоновыеЗадания(Новый Структура("Состояние, Наименование", СостояниеФоновогоЗадания.Активно, ИмяРегистра));
Для Сч = 0 По АктивныеФоновыеЗадания.ВГраница() Цикл
АктивныеФоновыеЗадания[Сч] = АктивныеФоновыеЗадания[Сч].УникальныйИдентификатор;
КонецЦикла;
ПараметрыЗадания = Новый Массив;
ПараметрыЗадания.Добавить(ТипРегистра);
ПараметрыЗадания.Добавить(ИмяРегистра);
ПараметрыЗадания.Добавить(НаборЗаписей.Отбор.Регистратор.Значение);
ПараметрыЗадания.Добавить(НаборЗаписей.Выгрузить());
ПараметрыЗадания.Добавить(АктивныеФоновыеЗадания);
ПараметрыЗадания.Добавить(Замещение);
ФоновыеЗадания.Выполнить("ЛУ_ВызовСервера.ФоновоеЗаданиеЗаписатьНаборЗаписей", ПараметрыЗадания,, ИмяРегистра);
НаборЗаписей.Очистить();
Возврат Истина;
КонецФункции
ФоновоеЗаданиеЗаписатьНаборЗаписей - функция общего модуля ЛУ_ВызовСервера (Сервер, Вызов сервера, Привилегированный) со следующим листингом:
Процедура ФоновоеЗаданиеЗаписатьНаборЗаписей(ТипРегистра, ИмяРегистра, Регистратор, ТаблицаДвижений, АктивныеФоновыеЗадания, Замещение) Экспорт
Если ТипРегистра = "РегистрНакопления" Тогда
НаборЗаписейЗадания = РегистрыНакопления[ИмяРегистра].СоздатьНаборЗаписей();
Если ТаблицаДвижений <> Неопределено Тогда
НаборЗаписейЗадания.мТаблицаДвижений = ТаблицаДвижений;
ОбщегоНазначения.ВыполнитьДвижениеПоРегистру(НаборЗаписейЗадания);
КонецЕсли;
Иначе
Если ТипРегистра = "РегистрБухгалтерии" Тогда
НаборЗаписейЗадания = РегистрыБухгалтерии[ИмяРегистра].СоздатьНаборЗаписей();
ИначеЕсли ТипРегистра = "РегистрСведений" Тогда
НаборЗаписейЗадания = РегистрыСведений[ИмяРегистра].СоздатьНаборЗаписей();
ИначеЕсли ТипРегистра = "РегистрРасчета" Тогда
НаборЗаписейЗадания = РегистрыРасчета[ИмяРегистра].СоздатьНаборЗаписей();
КонецЕсли;
Если ТаблицаДвижений <> Неопределено Тогда
НаборЗаписейЗадания.Загрузить(ТаблицаДвижений);
КонецЕсли;
КонецЕсли;
НаборЗаписейЗадания.Отбор.Регистратор.Установить(Регистратор);
// Проверим завершение предыдущих заданий по данному регистру
Для каждого ФоновоеЗаданиеУИД Из АктивныеФоновыеЗадания Цикл
ФоновоеЗадание = ФоновыеЗадания.НайтиПоУникальномуИдентификатору(ФоновоеЗаданиеУИД);
Если НЕ ФоновоеЗадание = Неопределено
и ФоновоеЗадание.Состояние = СостояниеФоновогоЗадания.Активно
Тогда
ФоновоеЗадание.ОжидатьЗавершения();
КонецЕсли;
КонецЦикла;
НаборЗаписейЗадания.Записать(Замещение);
КонецПроцедуры
В местах записи последней порции набора записей выполняем ожидание завершения фоновых заданий, запущенных в контексте по данному регистру.
ЛУ_КлиентСервер.ОжиданиеЗавершенияФоновыхЗаданий(СтруктураДвижений.ДвиженияЗатратыНаВыпуск); //+
Если СтруктураДвижений.ДвиженияЗатратыНаВыпуск.Модифицированность() Тогда
СтруктураДвижений.ДвиженияЗатратыНаВыпуск.Записать(Ложь);
КонецЕсли;
ОжиданиеЗавершенияФоновыхЗаданий - функция общего модуля ЛУ_КлиентСервер (Сервер, Клиент обычное приложение) со следующим листингом:
Процедура ОжиданиеЗавершенияФоновыхЗаданий(НаборЗаписей) Экспорт
// Проверка активности режима асинхронной записи
НаборыДвиженийЗаписыватьАсинхронно = Неопределено;
Если НЕ РаботаСОбщимиПеременными.ПолучитьИзКэшаКонфигурации("НаборыДвиженийЗаписыватьАсинхронно", НаборыДвиженийЗаписыватьАсинхронно, Неопределено, Ложь)
или НЕ НаборыДвиженийЗаписыватьАсинхронно Тогда
Возврат;
КонецЕсли;
ИмяРегистра = НаборЗаписей.Метаданные().Имя;
АктивныеФоновыеЗадания = ФоновыеЗадания.ПолучитьФоновыеЗадания(Новый Структура("Состояние, Наименование", СостояниеФоновогоЗадания.Активно, ИмяРегистра));
Если АктивныеФоновыеЗадания.Количество() > 0 Тогда
ФоновыеЗадания.ОжидатьЗавершения(АктивныеФоновыеЗадания);
КонецЕсли;
КонецПроцедуры
Более подробные листинги смотри во вложении
Вложенный файл ПроцедурыРасчетаСебестоимостиВыпуска.zip содержит модули подвергшиеся оптимизации согласно описанным в статье методам и предназначены для конфигурации Управление производсвенным предприятем 1.3 (1.3.79.2). Возможна установка на более ранние или старшие релизы. Клиент - Серверный вариант. Обычные формы.
Модули предназначены для самостоятельного внедрения. Код всех модулей полностью открыт. Возможна доработка собственными силами. Дополнительное лицензирование не требуется.
Обновление модулей, связанные с изменением типовой конфигурации, не планируется. Так как данный функционал в типовой конфигурации более не развивается.
Состав файла:
1. ПроцедурыРасчетаСебестоимостиВыпуска.cf
Помечаем для объединения только следующие общие модули:
- ПроцедурыРасчетаБазыРаспределенияЗатрат
- ПроцедурыРасчетаСебестоимостиВыпуска
- РасчетСебестоимостиВыпускаРаспределениеПоПеределам
- ЛУ_КлиентСервер
- ЛУ_ВызовСервера
2. Задачи.РегламентныеОперацииЗакрытияМесяца.ФормаЗадачи.txt
- На форме задачи в командной панели необходимо добавить кнопку "Провести вне транзакции" и назначить обработчик из этого файла
