Перерасчет итогов регистра бухгалтерии в 1С

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

Администрирование - Оптимизация БД (HighLoad)

8
Вариант перерасчета итогов частями (помесячно), если при типовом пересчете 1С зависает.

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

На предприятии, где я работаю имеется база 1С бухгалтерии 1.6, в которой велся учет с 2013 по 2016 годы. По счетам 41.01, 45.01 учет велся в разрезе 2 субконто. Третье субконто (партии) не использовался (себестоимость по среднему). Вдруг руководству потребовалось посмотреть карточку счетов 41.01, 45.01 в разрезе поставщиков, т.е. партий товара. Было принято решение заполнить 3 субконто (партии), используя для этого данные регистра "НДС партий товаров" (его вели, т.к. присутствовали реализации на эскпорт). Немного о специфике этой базы данных и сервера БД. Номенклатура товара более 100 тыс., размер базы 80 Г. Сервер  sql сервер Intel Xeon E5530 2.4 GHz 2 2 процессора (16 ядер) 48 ГБ ОЗУ, СУБД MS SQL 2012. 

Про саму методику заполнения 3 субконто на основании данных регистра "НДС парий товаров" подробно описывать не буду, если кому интересно пишите в комметариях, отвечу. Хочу только отметить, что выключил использование итогов (РегистрыБухгалтерии.Хозрасчетный.УстановитьИспользованиеИтогов(ЛОЖЬ)) и текущих итогов РегистрыБухгалтерии.Хозрасчетный.УстановитьИспользованиеИтогов(ЛОЖЬ) для ускорения работы с наборами движений 1С (чтобы кроме самих таблиц записей еще не происходил пересчет итогов). Итоги запланировать расчитать разово в конце обработки.

После того как 3-е субконто было заполнено начал расчитывать итоги. Но первый же месяц зависал на пересчете именно таблицы остаток 3-го субконто на несколько часов, не помогла даже реиндексация таблиц. Возможно большое кол-во строк в таблицах движений (около 10 млн) и  значений субконто (около 40 млн.)., так же сам запрос, приведенный ниже, сконструирован таким образом, т.е. многочисленные join-ны, чтобы привязать в к движениям значения всех 3-х субконто так же затратная операция.

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

  • Таблица движений (_AccRg5550);
  • Итоги без субконто (_AccRgAT05560);
  • Итоги 1 субконто (_AccRgAT15570);
  • Итоги 2 субконто (_AccRgAT25571);
  • Итоги 3 субконто (_AccRgAT35572);
  • Итоги оборотов между счетами (_AccRgCT5573);
  • Значения субконо (_AccRgED5574).

Прочие таблицы:   Виды субконто (_Acc3_ExtDim5522).

Для понимания, приведу фрагмент запроса по расчету 3-го субконто, полученный из Profiler:

SELECT
			T2._AccountDtRRef AS AccountRRef,
			T2._Fld5551RRef AS Fld5551RRef,
			T2._Fld5552DtRRef AS Fld5552RRef,
			T3._EDCount AS EDCount_,
			T5._Value_TYPE AS EDValue1_TYPE,
			T5._Value_RTRef AS EDValue1_RTRef,
			T5._Value_RRRef AS EDValue1_RRRef,
			T7._Value_TYPE AS EDValue2_TYPE,
			T7._Value_RTRef AS EDValue2_RTRef,
			T7._Value_RRRef AS EDValue2_RRRef,
			T9._Value_TYPE AS EDValue3_TYPE,
			T9._Value_RTRef AS EDValue3_RTRef,
			T9._Value_RRRef AS EDValue3_RRRef,
			T2._Fld5553 AS TurnoverDt5561_,
			CAST (0.0 AS NUMERIC (15, 2)) AS TurnoverCt5562_,
			T2._Fld5554Dt AS TurnoverDt5564_,
			CAST (0.0 AS NUMERIC (15, 2)) AS TurnoverCt5565_,
			T2._Fld5555Dt AS TurnoverDt5567_,
			CAST (0.0 AS NUMERIC (15, 3)) AS TurnoverCt5568_
		FROM
			_AccRg5550 T2
			INNER JOIN #tt1 T3 WITH (NOLOCK)
				ON T3._IDRRef = T2._AccountDtRRef
		 		AND T3._EDCount = @P1
			LEFT OUTER JOIN _Acc3_ExtDim5522 T4
				ON T4._Acc3_IDRRef = T2._AccountDtRRef
		 		AND T4._LineNo = @P2
			LEFT OUTER JOIN _AccRgED5574 T5
				ON T5._Period > = @P3
		 		AND T5._Period < @P4
		 		AND T5._Period = T2._Period
		 		AND T5._RecorderTRef = T2._RecorderTRef
		 		AND T5._RecorderRRef = T2._RecorderRRef
		 		AND T5._LineNo = T2._LineNo
		 		AND T5._Correspond = @P5
		 		AND T5._KindRRef = T4._DimKindRRef
			LEFT OUTER JOIN _Acc3_ExtDim5522 T6
				ON T6._Acc3_IDRRef = T2._AccountDtRRef
		 		AND T6._LineNo = @P6
			LEFT OUTER JOIN _AccRgED5574 T7
				ON T7._Period > = @P7
		 		AND T7._Period < @P8
		 		AND T7._Period = T2._Period
		 		AND T7._RecorderTRef = T2._RecorderTRef
		 		AND T7._RecorderRRef = T2._RecorderRRef
		 		AND T7._LineNo = T2._LineNo
		 		AND T7._Correspond = @P9
		 		AND T7._KindRRef = T6._DimKindRRef
			LEFT OUTER JOIN _Acc3_ExtDim5522 T8
				ON T8._Acc3_IDRRef = T2._AccountDtRRef
		 		AND T8._LineNo = @P10
			LEFT OUTER JOIN _AccRgED5574 T9
				ON T9._Period > = @P11
		 		AND T9._Period < @P12
		 		AND T9._Period = T2._Period
		 		AND T9._RecorderTRef = T2._RecorderTRef
		 		AND T9._RecorderRRef = T2._RecorderRRef
		 		AND T9._LineNo = T2._LineNo
		 		AND T9._Correspond = @P13
		 		AND T9._KindRRef = T8._DimKindRRef
		WHERE
			T2._Active = 0x01
			AND (T2._Period > = @P14
			AND T2._Period < @P15)

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

Сперва я скопировал таблицы движений и значений итогов в новые таблицы, очистив после этого оригинальные таблицы:

--движения
select * into _AccRg5550_copy from _AccRg5550 (nolock) 

--знач субконто
select * into _AccRgED5574_copy from _AccRgED5574 (nolock) 

--удаление движений в основной таблице движений
TRUNCATE TABLE _AccRg5550

--удаление движений в основной таблице значений субконто 
TRUNCATE TABLE _AccRgED5574

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

declare @ДатаНач datetime;
set @ДатаНач = '2013-01-01';
declare @ДатаКон datetime;
set @ДатаКон = '2013-01-31 23:59:59';

--удаление записей, оставшихся при расчете предыдущего месяца
TRUNCATE TABLE _AccRg5550
TRUNCATE TABLE _AccRgED5574

--заполнение записей из копий таблиц (только за месяц расчета)

--движения
insert into _AccRg5550 
select * from _AccRg5550_copy (nolock) as t1  
where t1._Period between @ДатаНач and @ДатаКон

--значения субконто
insert into _AccRgED5574 
select * from _AccRgED5574_copy (nolock) as t1  
where t1._Period between @ДатаНач and @ДатаКон

После выполнения очередного для каждого месяца по очистке и заполнения движений, выполнял непосредственный расчет итогов очередного месяца срествами 1С.

После расчета последнего месяца вернул исходные записи в таблицы движений и значений субконто:

--движения
TRUNCATE TABLE _AccRg5550
insert into _AccRg5550 
select * from _AccRg5550_copy (nolock)

--значения субконто
TRUNCATE TABLE _AccRgED5574
insert into _AccRgED5574 
select * from _AccRgED5574_copy (nolock) 

Для упрощения действий для каждого месяца написал обработку для 1С (помесячный очитка и заполнение таблиц в SQL + расчет итогов). Обработку прикладываю. Если кому поможет данная статья буду рад.

P.S. В данной статье привидены скрипты с названиями таблиц для моей базы данных, в вашем случае нужно использовать имена таблиц Вашей базы данных, посмотреть их можно разными способами, например через к функцию 1С ПолучитьСтруктуруХраненияБазыДанных. 

8

Скачать файлы

Наименование Файл Версия Размер
Перерасчет итогов регистра бухгалтерии в 1С:
.rar 7,33Kb
30.05.17
15
.rar 7,33Kb 15 Скачать

См. также

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

Комментарии
Избранное Подписка Сортировка: Древо
1. GROOVY 2469 30.05.17 15:09 Сейчас в теме
Превосходная статья, для тех кто хочет разобраться в механизмах регистров бухгалтерии.
Оставьте свое сообщение