Кейс по получению большой выборки данных в обработке порционно

01.06.23

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

В редких случаях требуется сложными запросами получить большую выборку данных и обработать её. Как переложить нагрузку с кластера 1С на СУБД.

Исходные данные:

  • лицензия 1С ПРОФ
  • платформа 8.3.22
  • оперативной памяти на сервере 1С 32 Гб

Описание проблемы:

Отраслевая отчёт-обработка, в которой с помощью сложных запросов с использованием множества временных таблиц строится результирующая (архитектуру реализации решения не обсуждаем, принимаем "как есть"). Результат нужно выгрузить в csv и отправить в надзорный орган.

Результат - таблица с более 800 тыс. строк и 30-тью колонками. Запрос на стороне СУБД исполняется достаточно быстро за период 2017-2023 гг, без нагрузки на сервер, план запроса - огромнейшая простыня на 5-ть экранов, визуально "по-диагонали" ничего криминального нет.

Выполнение отчёта генерирует исключение, в ТЖ “Недостаточно памяти для получения результата запроса к базе данных“, MemoryPeak > 4,5Гб для CALL.

Настройки параметров рабочего сервера "по умолчанию".

Согласно документации, настройка "Временно допустимый объем памяти процессов" при значении 0 в нашем случае равна 25 Гб (80% от ОЗУ). Настройка "Безопасный расход памяти за один вызов" при значении 0 в нашем случае равна 2.5 Гб (10% от "Временно допустимый объем памяти процессов").

Лицензия ПРОФ не позволяет изменять значение "Безопасный расход памяти за один вызов" (как раз "наш случай"). Исключительно ради эксперимента установил значение "Временно допустимый объем памяти процессов" (лицензия ПРОФ позволяет изменять данный параметр начиная с версии 8.3.19) в размер, 5-ти кратно превышающий размер ОЗУ 160 Гб - отчёт стал без проблем формироваться (расчётный "Безопасный расход памяти за один вызов" при этом получается 12,8 Гб). Но на продуктиве так делать, вероятно, нельзя.

Подходящий способ решения для нашей ситуации:

Очевидно, что нужно получать данные порциями (на ИТС даже есть статья, на это намекающая "Оптимизация использования оперативной памяти"). Но добавить ограничивающие выборку условия в нашем случае не представляется возможным (разумными трудозатратами) из-за чрезвычайно сложной логики работы.

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

Для примера я продемонстрирую простой виртуальной пример (не имеющий практической ценности). Исходный запрос:

	МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
	Запрос = Новый Запрос;
	Запрос.МенеджерВременныхТаблиц = МенеджерВременныхТаблиц;
	Запрос.Текст = 
		"ВЫБРАТЬ ПЕРВЫЕ 500000
		|	скДСП.Представление КАК Представление,
		|	скДСП.ДоговорКонтрагента.Представление КАК ДоговорКонтрагентаПредставление,
		|	скДСП.Контрагент.Представление КАК КонтрагентПредставление,
		|	скДСП.Организация.Представление КАК ОрганизацияПредставление,
		|	АВТОНОМЕРЗАПИСИ() КАК Индекс
		|ПОМЕСТИТЬ врем
		|ИЗ
		|	Документ.скДСП КАК скДСП
		|
		|ИНДЕКСИРОВАТЬ ПО
		|	Индекс";
	
	РезультатЗапроса = Запрос.Выполнить();

Получение и обработка результата порциями:

	Запрос.Текст = 
		"ВЫБРАТЬ
		|	врем.Индекс КАК Индекс,
		|	врем.Представление КАК Представление,
		|	врем.ДоговорКонтрагентаПредставление КАК ДоговорКонтрагентаПредставление,
		|	врем.КонтрагентПредставление КАК КонтрагентПредставление,
		|	врем.ОрганизацияПредставление КАК ОрганизацияПредставление
		|ИЗ
		|	врем КАК врем
		|ГДЕ
		|	врем.Индекс МЕЖДУ &Индекс1 И &Индекс2";

	Для аа = 1 По 5 Цикл
		Запрос.УстановитьПараметр("Индекс1", 1 + 100000 * (аа-1));
		Запрос.УстановитьПараметр("Индекс2", 100000 * аа);
		РезультатЗапроса = Запрос.Выполнить();
		ВыборкаДетальныеЗаписи = РезультатЗапроса.Выбрать();
		
		инд = 0;
		Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
			Если инд = 0 Тогда
				Сообщить(ВыборкаДетальныеЗаписи.Индекс);
			КонецЕсли;
			а1 = ВыборкаДетальныеЗаписи.Представление;
			а2 = ВыборкаДетальныеЗаписи.ДоговорКонтрагентаПредставление;
			а3 = ВыборкаДетальныеЗаписи.КонтрагентПредставление;
			а4 = ВыборкаДетальныеЗаписи.ОрганизацияПредставление;
			инд = инд + 1;
		КонецЦикла;
		Сообщить("Получено: " + инд);
	КонецЦикла;

В цикле я изначально знаю, что записей фиксированное количество, для правильной обработки нужно получить количество строк в результате:

	Запрос.Текст = 
		"ВЫБРАТЬ
		|	КОЛИЧЕСТВО(*) КАК КолВо
		|ИЗ
		|	врем КАК врем";
	ВыборкаДетальныеЗаписи = Запрос.Выполнить().Выгрузить();
	ВсегоЗаписей = ВыборкаДетальныеЗаписи[0].КолВо;
	Сообщить("Всего записей: " + ВсегоЗаписей);

И обязательно обратить внимание на документацию для функции АВТОНОМЕРЗАПИСИ(): "Начальное значение счетчика зависит от используемой СУБД и, в общем случае, может быть любым. Не гарантируется, что начальное значение счетчика будет равно 1 для любой временной таблицы.". Но гарантируется "последовательно возрастающее значение". Дополним наш код получением начального значения счётчика:

	ИндексПервый = 0;
	Если ВсегоЗаписей > 0 Тогда
		Запрос.Текст = 
			"ВЫБРАТЬ ПЕРВЫЕ 1
			|	врем.Индекс КАК Индекс
			|ИЗ
			|	врем КАК врем
			|
			|УПОРЯДОЧИТЬ ПО
			|	Индекс";
		ВыборкаДетальныеЗаписи = Запрос.Выполнить().Выгрузить();
		ИндексПервый = ВыборкаДетальныеЗаписи[0].Индекс;
		Сообщить("Первый индекс: " + ИндексПервый);
	КонецЕсли;

Тестируем:

Всего записей: 500 000
Первый индекс: 1
1
Получено: 100 000
100 001
Получено: 100 000
200 001
Получено: 100 000
300 001
Получено: 100 000
400 001
Получено: 100 000

Дорабатываем наш отчёт по данной методике, тестируем. MemoryPeak при этом не поднимается > 1.5 Гб. Цель достигнута.

См. также

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

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

24.06.2024    5447    ivanov660    12    

56

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

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

06.06.2024    9631    Evg-Lylyk    61    

44

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

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

13.03.2024    5251    spyke    28    

49

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    7797    vasilev2015    20    

42

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

2 стартмани

15.02.2024    12723    256    ZAOSTG    84    

115

HighLoad оптимизация Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    5890    glassman    19    

42

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    15014    doom2good    49    

71
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. ivanov660 4583 01.06.23 11:31 Сейчас в теме
Думаю, что проблема изначально в желании пользователя получить отчет размером в 800 тыс. строк. Я предполагаю, что он выгружается в эксель и смотрится там. С человеческой точки возможностей - нельзя объять необъятное.
Возможно стоит обсудить данную проблему с пользователем и сделать другой отчет или отчеты, который поможет решить задачу пользователя. А то из-за этого приходится так выкручиваться
METAL; info1i; корум; kser87; awk; +5 Ответить
2. AlexSTAL 125 01.06.23 13:31 Сейчас в теме
(1) Владимир, добрый день! Нет, это не желание пользователя изначально. Я в тексте специально указал, что это выгрузка без отображения на экране для передачи в надзорный орган
3. ivanov660 4583 01.06.23 16:38 Сейчас в теме
(2)
1. Пропустил, теперь увидел. Спасибо.
2. Тогда тут не объяснишь(
METAL; info1i; +2 Ответить
4. tormozit 7238 01.06.23 20:37 Сейчас в теме
Можно заметно сократить объем данных выборки, если не получать сразу представления ссылок, а выбрать сами ссылки (желательно простого типа) и получать представления ссылок при выводе каждой строки в файл выгрузки. Это конечно замедлит вывод данных. Но тут довольно экзотическая ситуация, поэтому в ней это может быть приемлемо. Еще можно не для всех типов ссылок это применить, а только для часто повторяющихся, тогда и на скорости вывода не так сильно скажется.
itoptimum; +1 Ответить
15. Cyberhawk 135 09.06.23 08:52 Сейчас в теме
(4)
не получать сразу представления ссылок, а выбрать сами ссылки (желательно простого типа)
А как такое сделать?
5. AlexSTAL 125 01.06.23 21:10 Сейчас в теме
(4) Сергей, спасибо, проведу эксперименты! Но это у меня в демо-примере представления получаются (не знаю зачем так сделал), а в реальном отчёте только ссылки...
6. TMV 14 01.06.23 22:10 Сейчас в теме
Что-то изменилось с этим?

АВТОНОМЕРЗАПИСИ

Описание:

Функция предназначена для формирования поля с уникальным, последовательно возрастающим значением во временной таблице. Функцию можно использовать в том случае, если необходимо создать ключевое поле во временной таблице. Значение, сформированное этой функцией, будет уникально в пределах временной таблицы, при формировании которой использовалась функция. Начальное значение счетчика зависит от используемой СУБД и, в общем случае, может быть любым. Не гарантируется, что начальное значение счетчика будет равно 1 для любой временной таблицы.
7. AlexSTAL 125 02.06.23 07:41 Сейчас в теме
(6)
Что-то изменилось с этим?

Так, что не так? Смысла комментария не могу понять.
Можно выделить из комментария жирный текст, скопировать в буфер, нажать Ctrl+F и поискать по текущей статье.
корум; mip128; +2 Ответить
8. ivanov660 4583 02.06.23 14:54 Сейчас в теме
(7)
Думаю вопрос в том, что может произойти так: начальное значение счетчика встанет, к примеру, в значение 100 000. Поэтому часть данных не будет считана. Т.к. от 0 до 100 000 будет пусто. А от 500 000 до 600 000 по условию счетчик не дойдет.
Чтобы от такого застраховаться, то можно сначала получить максимальное значение этого счетчика и от него уже считать интервалы.
P.S. Т.е. было бы хорошо, если коллега вместо намека, задал более прямой вопрос.
9. AlexSTAL 125 02.06.23 14:55 Сейчас в теме
(8) Так в моей статье именно так и написано, цитата: "Дополним наш код получением начального значения счётчика:"
10. ivanov660 4583 02.06.23 15:07 Сейчас в теме
(9) В самом алгоритме "Получение и обработка результата порциями" данного смещения сейчас не видно. С моей точки зрения, дополнение сейчас выглядит как демонстрация отдельной задачи, возможно стоило бы ее перенести выше и вставить значение индекса первого в код алгоритма.
Мне можете не отвечать, идею и проблему я понял. Спасибо.
AlexSTAL; +1 Ответить
11. kser87 2473 02.06.23 15:54 Сейчас в теме
Не хватает памяти, поэтому приняли решение поместить во временную таблицу. Что?
12. AlexSTAL 125 02.06.23 17:05 Сейчас в теме
(11) Мне кажется вы не читали статью...
mrChOP93; +1 Ответить
13. Jimbo 10 06.06.23 16:16 Сейчас в теме
Годнота, автору респект. Временная таблица хранится в tempdb СУБД - там не смотрели на сколько прирост ?
14. AlexSTAL 125 06.06.23 19:52 Сейчас в теме
(13) Благодарю! Не, на стороне СУБД не анализировал. Там ситуация управляемая - добавил места и решил проблему, в отличие от механизмов 1С
16. Child0fTheSun 01.09.24 14:50 Сейчас в теме
Как избежать того момента, что номера не всегда начинаются с 1?
Оставьте свое сообщение