Про типовые ситуации при работе с итогами регистра бухгалтерии опубликовано много материала. Я хотел бы рассказать о своем опыте, когда пришлось применить некоторый творческий подход, возможно кому то он пригодится.
На предприятии, где я работаю имеется база 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С ПолучитьСтруктуруХраненияБазыДанных.
