ИТАК:
Есть регистр накопления остатков «РасчетыПоПриобретениюВВалютеОрганизации» со следующей структурой:
Измерения:
- Организация, типа справочник «Организации»
- Контрагент, типа справочник «Контрагенты»
- ДоговорКонтрагента, типа справочник «ДоговорыКонтрагентов»
- Сделка, типа документ «Счет на оплату поставщику»
- Счет оплаты, типа ПланыСчетов.Хозрасчетный
- ДокументРасчетовСКонтрагентом, типа документ поступления и оплаты…
Ресурсы:
- СуммаВзаиморасчетов
- СуммаРегл
Есть запрос по остатками этого регистра:
ВЫБРАТЬ
ЕСТЬNULL(СУММА(РасчетыПоПриобретениюВВалютеОрганизацииОстатки.СуммаВзаиморасчетовОстаток), 0) КАК СуммаВзаиморасчетовОстаток
ИЗ
РегистрНакопления.РасчетыПоПриобретениюВВалютеОрганизации.Остатки(
&МоментВремени,
ДоговорКонтрагента = &ДоговорКонтрагента
И Сделка = &Сделка
И СчетОплаты = &СчетОплаты
И ДокументРасчетовСКонтрагентом = &ДокументРасчетовСКонтрагентом) КАК РасчетыПоПриобретениюВВалютеОрганизацииОстатки
Отборы по Организации и Контрагенту не устанавливаются, так как они однозначно определяются договором (справочник «ДоговорыКонтрагентов» подчинен справочнику «Контрагенты» и в нем есть реквизит «Организация»).
В отборе по периоду виртуальной таблицы используется момент времени – это документ с датой «01.11.2011 00:00:00»
Поймаем запрос в профайлере:
exec sp_executesql N'SELECT
ISNULL(CAST(CAST(SUM(T1.Fld22268Balance_) AS NUMERIC(38, 8)) AS NUMERIC(38, 8)),0.0)
FROM (
SELECT
CAST(SUM(T2.Fld22268Balance_) AS NUMERIC(38, 8)) AS Fld22268Balance_
FROM (
SELECT
ISNULL(CAST(CAST(SUM(T3._Fld22268) AS NUMERIC(33, 8)) AS NUMERIC(27, 2)),0.0) AS Fld22268Balance_
FROM _AccumRgT22270 T3
WHERE T3._Period = @P1
AND (((((T3._Fld22262RRef = @P2) AND (T3._Fld27856_TYPE = @P3)) AND (T3._Fld22264RRef = @P4)) AND (T3._Fld23634_TYPE = @P5 AND T3._Fld23634_RTRef = @P6 AND T3._Fld23634_RRRef = @P7)))
UNION ALL
SELECT
CAST(ISNULL(CAST(CAST(SUM(CASE WHEN T4._RecordKind = 0.0 THEN -T4._Fld22268 ELSE T4._Fld22268 END) AS NUMERIC(27, 8)) AS NUMERIC(21, 2)),0.0) AS NUMERIC(27, 2)) AS Fld22268Balance_
FROM _AccumRg22261 T4
WHERE (T4._Period > @P8 OR T4._Period = @P8 AND (T4._RecorderTRef > @P9 OR T4._RecorderTRef = @P9 AND T4._RecorderRRef >= @P10))
AND T4._Period < @P1
AND T4._Active = @P11
AND (((((T4._Fld22262RRef = @P2) AND (T4._Fld27856_TYPE = @P3)) AND (T4._Fld22264RRef = @P4)) AND (T4._Fld23634_TYPE = @P5 AND T4._Fld23634_RTRef = @P6 AND T4._Fld23634_RRRef = @P7)))
) T2
) T1', N'
@P1 datetime, @P2 varbinary(16), @P3 varbinary(1), @P4 varbinary(16),
@P5 varbinary(1), @P6 varbinary(4), @P7 varbinary(16), @P8 datetime,
@P9 varbinary(4), @P10 varbinary(16), @P11 varbinary(1)',
{ts '4011-12-01 00:00:00'}, 0xB5B202BFAC10409211E033900477750B, 0x01, 0x8596608F1DCF9AE642B3C7529403EE34,
0x08, 0x00000111, 0xB1B202BFAC10409211E117376ABAD6C1, {ts '4011-11-01 00:00:00'},
0x00000170, 0x8D0F02BFAC10409211E1219D93CB1649, 0x01
Видим, что виртуальная таблица «Остатки» преобразовалась в агрегатный («SUM(T1.Fld22268Balance_)») запрос с вложенным объединением («UNION ALL») двух запросов.
Разберем первый запрос в объединении:
Первый запрос выполняется к таблице итогов регистра («FROM _AccumRgT22270 T3»). Данные в таблице итогов хранятся по каждому месяцу (поле «_Period») в разрезе всех измерений и ресурсов. В секции «WHERE» устанавливается отбор по месяцу итогов («WHERE T3._Period = @P1») и всем измерениям, указанным в параметрах отбора виртуальной таблицы. Обратим внимание, что параметр «@P1» равен {ts '4011-12-01 00:00:00'}, то есть 01.12.2011 00:00:00 (используется смещение дат 2000), а запрос Мы выполняем на 01.11.2011 00:00:00… То есть итоги выбираются по состоянию на следующий месяц…
Разберем второй запрос в объединении:
Второй запрос выполняется к таблице движений регистра («FROM _AccumRg22261 T4»).
В секции «WHERE» устанавливается отбор по моменту времени («WHERE (T4._Period > @P8 OR T4._Period = @P8 AND (T4._RecorderTRef > @P9 OR T4._RecorderTRef = @P9 AND T4._RecorderRRef >= @P10))»).
Вспомним что момент времени это «период плюс ссылка». Ссылка – это тип ссылки («_RecorderTRef») и собственно сама ссылка («_RecorderRRef»). Отбор по моменту времени выполняется по условию «Больше или равно». Момент времени, используемый как отбор по периоду виртуальной таблицы, задается параметрами @P8, @P9, @P10.
Дальше через «И» в секции «WHERE» задается условие «AND T4._Period < @P1», где @P1 равен {ts '4011-12-01 00:00:00'} – это период на который Мы выбирали итоги из таблицы итогов в первом запросе объединения.
Дальше через «И» следуют условия на активность (нам нужны только активные записи, не активные не могут влиять на остатки) и отбор по всем измерениям, указанным в параметрах отбора виртуальной таблицы.
В секции «SELECT» происходит накопления ресурса в зависимости от вида движения («SUM(CASE WHEN T4._RecordKind = 0.0 THEN -T4._Fld22268 ELSE T4._Fld22268 END)»): Если вид движения приход («_RecordKind = 0.0»), то ресурс берется с отрицательным знаком, иначе с положительным.
То есть из таблицы движений выбираются записи с моментом времени больше или равным условию отбора по периоду виртуальной таблицы и периодом меньшим даты начала следующего месяца. При этом ресурсы накапливаются с отрицательным знаком.
При объединении запросов из результата первого (по таблице итогов) вычитается результат второго (по основной таблице) и таким образом получается остаток на нужный нам период (условие по периоду виртуальной таблицы).
Зачем все это знать…
Лично мне это помогло разобраться с избыточной блокировкой, которая не давала возможности одновременного проведения документов поступления по разным контрагентам:
При проведении документов поступления сначала анализируются остатки этого регистра, для определения был ли аванс или нет, а потом происходит запись в этот регистр.
Естественное требование к системе – возможность одновременного проведения пользователями документов по разным контрагентам или договорам. Но, если система настроена неправильно, то это требование выполняться не будет…
Итак:
При выполнении запроса очень важно что бы для отбора строк запрос использовал индекс! В этом случае запрос будет выполняться быстро и блокировать для записи только те строки таблиц, используемых в запросе, которые удовлетворяют отбору…
Если запрос не использует индекс для отбора строк или использует малоселективный индекс (индекс, которому соответствует большое количество строк таблицы), то запрос будет выполняться медленно и блокировать всю (или почти всю) таблицу для записи…
Для выполнения нашего требования запрос должен использовать индекс и первые поля этого индекса должны использоваться в отборе запроса. В нашем случае, это поле - измерение ДоговорКонтрагента.
Для таблицы итогов регистра накопления платформа создает кластерный индекс с включением в него периода итогов и всех измерений в том порядке как они заданы в конфигураторе, не зависимо от того установлено ли для измерения свойство «Индексировать» или нет… Из этого следует, что если Мы переставим «ДоговорКонтрагента» в списке измерений на первое место, то получим нужный нам индекс… Кстати не используемые в отборах запросов измерения (Организация и Контрагент) лучше передвинуть в самый конец списка, тогда могут сработать такие поля индекса как «Сделка», «СчетОплаты» и «ДокументРасчетовСКонтрагентом».
Для таблицы движений регистра накопления платформа создает два индекса: кластерный индекс по периоду, в состав которого входит период, регистратор и номер строки, а также индекс по регистратору, в состав которого входит регистратор и номер строки.
Если для измерения установлено свойство «Индексировать», то платформа создаст дополнительный индекс, включив в него это измерение, период, регистратор и номер строки. Из этого следует, что установив у измерения «ДоговорКонтрагента» свойство «Индексировать», Мы получим требуемый нам индекс…
Таким образом структура регистра изменилась на следующую:
Измерения:
- ДоговорКонтрагента, типа справочник «ДоговорыКонтрагентов» (ИНДЕКСИРУЕТСЯ)
- Сделка, типа документ «Счет на оплату поставщику»
- Счет оплаты, типа ПланыСчетов.Хозрасчетный
- ДокументРасчетовСКонтрагентом, типа документ поступления и оплаты…
- Организация, типа справочник «Организации»
- Контрагент, типа справочник «Контрагенты»
Ресурсы:
- СуммаВзаиморасчетов
- СуммаРегл
После этого избыточные блокировки исчезли, и пользователи смогли одновременно проводить документы по разным контрагентам и договорам.
Информация про индексы, которые создает платформа, находится здесь: http://kb.1c.ru/articleView.jsp?id=68
