Скорость выполнения отчета в самописной базе очень низкая

Внимание! Тема закрыта. Добавлять сообщения в закрытую тему запрещено.
1. Minakov00078 38 17.03.21 12:56 Сейчас в теме
Добрый день, коллеги! У нас есть самописная база на основе БСП с одним документом типа "Документ" и с одним регистром "Регистр 1") типа "Регистр остатков". У регистра измерение1, Измерения2 и ресурс Количество. Измерения - это Справочник 1 и Справочник 2. В базе около 2 млн записей по "Регистру 1"
Делаю отчет на СКД - в запросе виртуальная таблица - отбор на параметры внутри виртуальной таблицы, в качестве полей получаю Справочник.Наименование.

Отчет за год без отборов первый раз строится 12 минут, следом запускаешь - зависает на 1,5 часа, если подождать 3-5 минут то отчет формируется за 12 минут. Опытным путем замечено, когда rphost сдувается до 0, тогда отчет формируется 12 минут.
Мы спустились до платформы 8.3.14.1694 чтобы сделать настройки сервера 1с до ограничений 9 сентября 2019 года

Вопрос: как ускорить формирование отчета до 30 секунд. Количество выводимых записей может достигать 10 000
Прикрепленные файлы:
По теме из базы знаний
Вознаграждение за ответ
Показать полностью
Найденные решения
43. serge_focus 4 18.03.21 04:43 Сейчас в теме
На самом деле можно и довести выполнение до 30 секунд или и меньше. 2 млн строк - это при правильной организации данных ни о чем. По сути сделать мини OLAP куб. Или иначе создать сущность данных, содержащую в виде нужных срезов готовые агрегированные данные. И из нее тащить отборами по индексам в СКД. А так на пальцах получается серверы получая данные помещают их в оперативную память бездушно выжырая ее. И когда следует очередной вызов запроса за небольшое время происходит утечка памяти и свопинг на диск. А это очень медленная операция. О чем и говорит картинка. Если делаем запрос, через время - сервер уже сам отпустил кеш. И еще "Вангую" - 1С сервер 32 битный. И возможно сервера развернуты на виртуалках.
В общем нужно смотреть на ситуацию в комплексе. Для начала все мониторить начиная с поведения железа (примитивные метрики ресурсов сервера память, диск (инпут/оутпут) ) - проц седается редко. Ну и какие запросы сыпятся на скуль. Прочие рекомендации по оптимизации уже сказаны в обсуждении выше. А вообще - задача выглядит как тест на собеседовании.
Остальные ответы
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
2. nomad_irk 76 17.03.21 13:03 Сейчас в теме
(1)Какая виртуальная таблица регистра используется в запросе?

Я правильно понимаю, что параметры отбора виртуальной таблицы - строковые значения, а не ссылочные?
10. Minakov00078 38 17.03.21 14:46 Сейчас в теме
(2) Используется таблица Обороты, отборы -это ссылочного типа данные.
40. nomad_irk 76 17.03.21 17:37 Сейчас в теме
(10)Лично мне пока не совсем понятно, для чего используется тип регистра "Остатки"?
9. user856012 14 17.03.21 13:46 Сейчас в теме
(1)
У нас есть самописная база на основе БСП с одним документом типа "Документ" и с одним регистром "Регистр 1") типа "Регистр остатков". У регистра измерение1, Измерения2 и ресурс Количество. Измерения - это Справочник 1 и Справочник 2. В базе около 2 млн записей по "Регистру 1"
Делаю отчет на СКД - в запросе виртуальная таблица - отбор на параметры внутри виртуальной таблицы, в качестве полей получаю Справочник.Наименование.
Хорошее словесное описание фотографии больного, проблем с выбором лечения возникнуть не должно. :)
Вопрос: как ускорить формирование отчета до 30 секунд.
С 12 минут (в лучшем случае) до 30 секунд? Не видя больного ни базы, ни отчета... вы большой оптимист!

Можно попробовать произнести волшебное заклинание "Сим-салабим-ахалай-махалай!" 24 раза (12/0,5), а если не поможет... искать другое заклинание! ;)
11. Minakov00078 38 17.03.21 14:47 Сейчас в теме
(9) Вы можете задать конретные вопросы, я дам конретные ответы, а так приятно было отвлечься от проблемы почитав Ваш комментарий.
38. RustamZz 17.03.21 17:00 Сейчас в теме
(1) А почему Продажи это регистр остатков, а не оборотов? Ну и сгруппировать в первом запросе лишнее.
39. spacecraft 17.03.21 17:27 Сейчас в теме
(1)
У нас есть самописная база на основе БСП с одним документом типа "Документ" и с одним регистром "Регистр 1") типа "Регистр остатков".

Вот и корень проблемы. Виртуальная таблица "Обороты" в РН с видом регистра "Остатки" не использует таблицу итогов оборотов и все данные получает из таблицы "Движения".
Нужно переделывать РН в вид "Обороты".

Да и подозреваю, что сам регистр никогда не сводится, а только накапливается.
SlavaKron; FatPanzer; serge_focus; Sashares; nomad_irk; +5 Ответить
52. Minakov00078 38 18.03.21 19:31 Сейчас в теме
(39) Вношу уточнение. у нас используется регистр оборотов.
Прикрепленные файлы:
3. SlavaKron 17.03.21 13:15 Сейчас в теме
Может быть, не включены итоги для регистра?
starik-2005; +1 Ответить
4. nomad_irk 76 17.03.21 13:17 Сейчас в теме
(3)у ТС, регистр накопления, судя по
с одним регистром "Регистр 1") типа "Регистр остатков"
6. SlavaKron 17.03.21 13:17 Сейчас в теме
18. Minakov00078 38 17.03.21 15:10 Сейчас в теме
(12)ЗЩа
(3) Использование итогов включено
Прикрепленные файлы:
5. DenisCh 17.03.21 13:17 Сейчас в теме
А какие суммы получаете? остатки, обороты? В каких разрезах?
21. Minakov00078 38 17.03.21 15:13 Сейчас в теме
(5) Результат запроса ниже представлен.
7. starik-2005 3060 17.03.21 13:26 Сейчас в теме
Без самого запроса и структуры базы достаточно сложно что-то сказать. 2 млн записей - это не так и много. 12 минут - это скорее всего говорит или о том, что индексы в запросе не используются, или о том, что СУБД не обслуживается.

Если более подробно расскажете, то вполне можно помочь выяснить направление.

Ну и очевидный совет - через ТиИ запустите реструктуризацию таблиц, обновите статистику, дефрагментируйте индексы.
13. Minakov00078 38 17.03.21 14:55 Сейчас в теме
(7) Очевидные вещи делали через SQL

Ниже текст запроса отчета

ВЫБРАТЬ
	ПродажиОбороты.Продавец КАК Продавец,
	ПродажиОбороты.Покупатель КАК Покупатель,
	ПродажиОбороты.Номенклатура КАК Номенклатура,
	СУММА(ПродажиОбороты.КоличествоОборот) КАК Количество,
	ПродажиОбороты.Период КАК Период,
	СУММА(ПродажиОбороты.Цена * ПродажиОбороты.КоличествоОборот) КАК Стоимость,
	СУММА(ПродажиОбороты.ОбъемПродукцииОборот) КАК ОбъемПродукцииОборот
ПОМЕСТИТЬ ВТ_СредниеЦены
ИЗ
	РегистрНакопления.Продажи.Обороты(, , Месяц, ) КАК ПродажиОбороты

СГРУППИРОВАТЬ ПО
	ПродажиОбороты.Продавец,
	ПродажиОбороты.Покупатель,
	ПродажиОбороты.Номенклатура,
	ПродажиОбороты.Период
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ПродажиОбороты.Продавец КАК Продавец,
	ПродажиОбороты.Покупатель КАК Покупатель,
	ПродажиОбороты.Номенклатура КАК Номенклатура,
	ПродажиОбороты.Количество КАК Количество,
	ПродажиОбороты.Период КАК Период,
	ВЫБОР
		КОГДА ПродажиОбороты.Количество = 0
			ТОГДА 0
		ИНАЧЕ ПродажиОбороты.Стоимость / ПродажиОбороты.Количество
	КОНЕЦ КАК Цена,
	ПродажиОбороты.ОбъемПродукцииОборот КАК ОбъемПродукцииОборот,
	ПродажиОбороты.Покупатель.ИНН КАК ПокупательИНН,
	ПродажиОбороты.Покупатель.Регион КАК Регион,
	ПродажиОбороты.Покупатель.Родитель КАК КаналПродаж,
	ПродажиОбороты.Продавец.ИНН КАК ПродавецИНН,
	ПродажиОбороты.Покупатель.Наименование КАК _Покупатель,
	ПродажиОбороты.Номенклатура.Сорт КАК Сорт
ИЗ
	ВТ_СредниеЦены КАК ПродажиОбороты
Показать
19. starik-2005 3060 17.03.21 15:10 Сейчас в теме +2 $m
(13)
1. А зачем тут временная таблица?
2. А зачем тут агрегация?
3. Период в данном случае - это секунда. В чем смысл за секунду собирать данные? Если бы это был целый месяц, тогда смысл бы имела и агрегация (типа "НАЧАЛОПЕРИОДА(Период, МЕСЯЦ) КАК Период") - это существенно сократило бы количество значений в выборке и временная таблица могла бы тут быть полезной (да и то вряд ли, ибо она дальше на 100% вся целиком используется. Я так понимаю, что Вы потом это все еще раз сворачиваете в СКД, из-за этого действительно все может долго работать, ибо вытягивает из базы 2кк записей, соединяет их с номенклатурой и контрагентами, бесмысленно агрегирует, а потом еще раз все усредняет в СКД (но могу и ошибиться тут с периодом, если он указан как "Месяц" - надо смотреть, но если и так, то агрегация тут не нужна).
4. В данном случае разыменоывание (обращение через точку) не должно снижать производительность, но только в том случае, если в покупателе, продавце и номенклатуре не любая ссылка, а только контрагенты и товары.
27. Minakov00078 38 17.03.21 15:39 Сейчас в теме
(19)
1. Временная таблица нужна, чтобы на втором проходе посчитать средневзвешенную цену, в связи с этим агрегация исопользуется
Ниже код расчета цены по данным временной таблицы
ВЫБОР
	КОГДА ПродажиОбороты.Количество = 0
		ТОГДА 0
	ИНАЧЕ ПродажиОбороты.Стоимость / ПродажиОбороты.Количество
КОНЕЦ

2. Период -это месяц, см скрин в приложении.
Прикрепленные файлы:
15. Minakov00078 38 17.03.21 15:00 Сейчас в теме
(7) Приложил структуру регистра, измерения, повторюсь, ссылочного типа.
Прикрепленные файлы:
8. ptolomei 17.03.21 13:32 Сейчас в теме
имеет смысл проверить индексы по полям выборки, порядок следования полей в выборке. Ну и да сам запрос посмотреть бы еще
16. Minakov00078 38 17.03.21 15:00 Сейчас в теме
(8)Запрос выложил см выше
12. Fox-trot 161 17.03.21 14:50 Сейчас в теме
текст запроса покажешь?
14. Minakov00078 38 17.03.21 14:57 Сейчас в теме
17. ptolomei 17.03.21 15:02 Сейчас в теме
Двойка за точки, выбирай данные через точку ранее, и через соединение, а не в результирующей выборке. В этом случае скуль делает свои дополнительные запросы и соединения и часто не оптимальные. это для затравки
ПродажиОбороты.Покупатель.ИНН КАК ПокупательИНН,
ПродажиОбороты.Покупатель.Регион КАК Регион,
ПродажиОбороты.Покупатель.Родитель КАК КаналПродаж,
ПродажиОбороты.Продавец.ИНН КАК ПродавецИНН,
ПродажиОбороты.Покупатель.Наименование КАК _Покупатель,
ПродажиОбороты.Номенклатура.Сорт КАК Сорт

СУММА(ПродажиОбороты.Цена * ПродажиОбороты.КоличествоОборот) КАК Стоимость, - существует вероятность деления на ноль, это все таки оборотная таблица.

Самописная база, да? Цену лучше сделать не измерением, а ресурсом, ну это так имхо =)
29. Minakov00078 38 17.03.21 15:44 Сейчас в теме
(17) Отчет после изменений за год сфомировался за 25 минут, это ни о чем не говорит, изменения по точка не дали ожидаемого результата. Вероятность деления на ноль будет только в том случае, если есть знак деления в запросе и нет проверки на ноль количество. В моем запросе есть проверка:
ВЫБОР
КОГДА ПродажиОбороты.Количество = 0
ТОГДА 0
ИНАЧЕ ПродажиОбороты.Стоимость / ПродажиОбороты.Количество
КОНЕЦ
30. starik-2005 3060 17.03.21 15:45 Сейчас в теме
(29)
Отчет после изменений за год сфомировался за 25 минут
А итоговый отчет что должен показать? Среднюю цену за месяц по покупателю и номенклатуре?
33. Minakov00078 38 17.03.21 15:48 Сейчас в теме
(30)Да, ниже показаны настройки помпановки.
32. vv2 17.03.21 15:47 Сейчас в теме
(29) а почему в регистре нет стоимости?
СУММА(ПродажиОбороты.Цена * ПродажиОбороты.КоличествоОборот) КАК Стоимость
34. Minakov00078 38 17.03.21 15:49 Сейчас в теме
(32)Потому что считается средневзвешенная цена а не среднелинейная.
35. ptolomei 17.03.21 15:55 Сейчас в теме
(29) все, пора в отпуск - *, принял за "/". Мой косяк, но тем не менее, если есть выборка через точку, то лучше делать соединение к профильным таблицам.


Выбрать 
Регистр.покупатель,
Регистр.продавец,
Регистр.товар,
спрТовар.Качество
Из 
Регистр как Регистр
Левое Соединение спрТовары Как спрТовар
По Регистр.Товар = спрТовар.Ссылка
Показать
20. SlavaKron 17.03.21 15:11 Сейчас в теме
Этот запрос в консоли сколько выполняется?
ВЫБРАТЬ
ПродажиОбороты.Продавец КАК Продавец,
ПродажиОбороты.Покупатель КАК Покупатель,
ПродажиОбороты.Номенклатура КАК Номенклатура,
СУММА(ПродажиОбороты.КоличествоОборот) КАК Количество,
ПродажиОбороты.Период КАК Период,
СУММА(ПродажиОбороты.Цена * ПродажиОбороты.КоличествоОборот) КАК Стоимость,
СУММА(ПродажиОбороты.ОбъемПродукцииОборот) КАК ОбъемПродукцииОборот
ИЗ
РегистрНакопления.Продажи.Обороты(, , Месяц, ) КАК ПродажиОбороты

СГРУППИРОВАТЬ ПО
ПродажиОбороты.Продавец,
ПродажиОбороты.Покупатель,
ПродажиОбороты.Номенклатура,
ПродажиОбороты.Период
Показать
22. Minakov00078 38 17.03.21 15:24 Сейчас в теме
(20)
ВЫБРАТЬ
ПродажиОбороты.Продавец КАК Продавец,
ПродажиОбороты.Покупатель КАК Покупатель,
ПродажиОбороты.Номенклатура КАК Номенклатура,
СУММА(ПродажиОбороты.КоличествоОборот) КАК Количество,
ПродажиОбороты.Период КАК Период,
СУММА(ПродажиОбороты.Цена * ПродажиОбороты.КоличествоОборот) КАК Стоимость,
СУММА(ПродажиОбороты.ОбъемПродукцииОборот) КАК ОбъемПродукцииОборот
ИЗ
РегистрНакопления.Продажи.Обороты(, , Месяц, ) КАК ПродажиОбороты

СГРУППИРОВАТЬ ПО
ПродажиОбороты.Продавец,
ПродажиОбороты.Покупатель,
ПродажиОбороты.Номенклатура,
ПродажиОбороты.Период
Показать
Ваш запрос уже 10 минут висит в консоли запросов
23. SlavaKron 17.03.21 15:27 Сейчас в теме
(22) Это ваш же запрос для временной таблицы. Да, наверно следовало обработкой проверить время выполнения без вывода данных (Запрос.Выполнить()).
24. Minakov00078 38 17.03.21 15:32 Сейчас в теме
(17) Запрос изменил, выполнение висит уже более 10 минут- отбор 1 год.

ВЫБРАТЬ
	ПродажиОбороты.Продавец КАК Продавец,
	ПродажиОбороты.Покупатель КАК Покупатель,
	ПродажиОбороты.Номенклатура КАК Номенклатура,
	СУММА(ПродажиОбороты.КоличествоОборот) КАК Количество,
	ПродажиОбороты.Период КАК Период,
	СУММА(ПродажиОбороты.Цена * ПродажиОбороты.КоличествоОборот) КАК Стоимость,
	СУММА(ПродажиОбороты.ОбъемПродукцииОборот) КАК ОбъемПродукцииОборот,
	ПродажиОбороты.Покупатель.ИНН КАК ПокупательИНН,
	ПродажиОбороты.Покупатель.Регион КАК ПокупательРегион,
	ПродажиОбороты.Покупатель.Родитель КАК ПокупательРодитель,
	ПродажиОбороты.Продавец.ИНН КАК ПродавецИНН,
	ПродажиОбороты.Покупатель.Наименование КАК ПокупательНаименование,
	ПродажиОбороты.Номенклатура.Сорт КАК НоменклатураСорт
ПОМЕСТИТЬ ВТ_СредниеЦены
ИЗ
	РегистрНакопления.Продажи.Обороты(, , Месяц, ) КАК ПродажиОбороты

СГРУППИРОВАТЬ ПО
	ПродажиОбороты.Продавец,
	ПродажиОбороты.Покупатель,
	ПродажиОбороты.Номенклатура,
	ПродажиОбороты.Период
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ПродажиОбороты.Продавец КАК Продавец,
	ПродажиОбороты.Покупатель КАК Покупатель,
	ПродажиОбороты.Номенклатура КАК Номенклатура,
	ПродажиОбороты.Количество КАК Количество,
	ПродажиОбороты.Период КАК Период,
	ВЫБОР
		КОГДА ПродажиОбороты.Количество = 0
			ТОГДА 0
		ИНАЧЕ ПродажиОбороты.Стоимость / ПродажиОбороты.Количество
	КОНЕЦ КАК Цена,
	ПродажиОбороты.ОбъемПродукцииОборот КАК ОбъемПродукцииОборот,
	ПродажиОбороты.ПокупательИНН КАК ПокупательИНН,
	ПродажиОбороты.ПокупательРегион КАК Регион,
	ПродажиОбороты.ПокупательРодитель КАК КаналПродаж,
	ПродажиОбороты.ПродавецИНН КАК ПродавецИНН,
	ПродажиОбороты.ПокупательНаименование КАК _Покупатель,
	ПродажиОбороты.НоменклатураСорт КАК Сорт
ИЗ
	ВТ_СредниеЦены КАК ПродажиОбороты
Показать

(23)
25. Minakov00078 38 17.03.21 15:32 Сейчас в теме
(23) Я и глазами проверю, уже 17 минут выполнение, попросту говоря "завис" запрос.
26. SlavaKron 17.03.21 15:38 Сейчас в теме
(25) Я про то, что в результате может получиться большой объем данных. Если выполнять в консоли, основное время тратится на вывод результата. А если поля ссылочного типа, так еще и на получение представлений для них.
Кстати, каково количество записей в результате запроса?
Fox-trot; +1 Ответить
28. Minakov00078 38 17.03.21 15:41 Сейчас в теме
(26)Запрос еще работает, в аналогичных случаях это занимало 1,5 часа
41. Minakov00078 38 17.03.21 18:14 Сейчас в теме
(20)Прошло 3 часа, обработка все идёт. Будем считать, что запрос без отборов не выполняется в консоли.
31. Minakov00078 38 17.03.21 15:47 Сейчас в теме
Прикладываю настройки компановки данных для полноты картины
Прикрепленные файлы:
37. starik-2005 3060 17.03.21 16:13 Сейчас в теме
(31) уберите из запроса ВТ, все расчеты делайте в компоновке (в ресурсах тех же). В итоге СКД сам пересоберет отчет только по нужным полям и сгруппирует, как надо.
Fox-trot; +1 Ответить
46. Minakov00078 38 18.03.21 17:24 Сейчас в теме
(3) у нас версия 1С 1С:Предприятие 8.3 (8.3.16.1148)
(37) Это не ускоряет отчет. Помогает сокращение количество выводимых группировок, например без номенклатуры и контрагентов отчет формируется до 30 секунд, как только добавляем группировки - время значительно увеличивается. получается у 1С слабый участок на вывод большого массива информации в табличный вид.
36. ptolomei 17.03.21 15:58 Сейчас в теме
Кстати, если выборка делается под СКД, то лучше ее делать абсолютно плоской, т.к. все равно все расчеты можно (и как оно сделано на картинке) сделать именно через настройки СКД.
47. Minakov00078 38 18.03.21 17:25 Сейчас в теме
(36) Ответил выше на порядок выборки и агрегацию в рамках запроса и в рамках компановки данных
42. serge_focus 4 18.03.21 00:53 Сейчас в теме +3 $m
А что мешает в SQL Profiler ситуацию проанализировать. Сразу станет понятней.
Кстати - СКД та еще "штучка" , особенно в умелых руках пользователей бездумно добавляющих отборы и группировки. По этому для "тяжелых" запросов лучше лучше контролировать процесс формирования запроса самому и только результат пихать в СКД, для постобработки и отображения .
43. serge_focus 4 18.03.21 04:43 Сейчас в теме
На самом деле можно и довести выполнение до 30 секунд или и меньше. 2 млн строк - это при правильной организации данных ни о чем. По сути сделать мини OLAP куб. Или иначе создать сущность данных, содержащую в виде нужных срезов готовые агрегированные данные. И из нее тащить отборами по индексам в СКД. А так на пальцах получается серверы получая данные помещают их в оперативную память бездушно выжырая ее. И когда следует очередной вызов запроса за небольшое время происходит утечка памяти и свопинг на диск. А это очень медленная операция. О чем и говорит картинка. Если делаем запрос, через время - сервер уже сам отпустил кеш. И еще "Вангую" - 1С сервер 32 битный. И возможно сервера развернуты на виртуалках.
В общем нужно смотреть на ситуацию в комплексе. Для начала все мониторить начиная с поведения железа (примитивные метрики ресурсов сервера память, диск (инпут/оутпут) ) - проц седается редко. Ну и какие запросы сыпятся на скуль. Прочие рекомендации по оптимизации уже сказаны в обсуждении выше. А вообще - задача выглядит как тест на собеседовании.
45. Minakov00078 38 18.03.21 15:59 Сейчас в теме
(44)Запрос только один.)
(43)Это реальная задача в реальной среде
48. Minakov00078 38 18.03.21 17:28 Сейчас в теме
(43)
серверы получая данные помещают их в оперативную память бездушно выжырая ее
- Похоже на правду. Пока решение- накладывать отборы при группировках по номенклатуре или контрагентах и запрещать программно вывод сразу всех группировок.
44. hamsar 16 18.03.21 10:10 Сейчас в теме
каждый запрос отдельно в тз, тз кодом соединяешь в скд выводишь как источник данных тз. Группировки и сортировки в запросе убираешь, делаешь их на уровне скд
49. Minakov00078 38 18.03.21 17:29 Сейчас в теме
(44) Запрос у нас один, не думаю что использование ТЗ объемом в 200 300 тыс строк ускорит формирование отчета.
50. hamsar 16 18.03.21 18:45 Сейчас в теме
(49) запроса 2, группировки убрать, такую задачу я так решал, ваша проблема переполнение памяти сервера 1с, тз решило мою проблема схожую с вашей.
51. Fox-trot 161 18.03.21 19:27 Сейчас в теме
удалил второй запрос? ведь достаточно первый подшаманить
и возможно есть смысл хранить период сразу в месяцах, но это уже дело вкуса
53. Minakov00078 38 18.03.21 19:36 Сейчас в теме
Тему закрываем. Всем спасибо за участие.
Оставьте свое сообщение

Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот