gifts2017

Виртуальная таблица «Остатки» регистра накопления и избыточные блокировки

Опубликовал Sasha Erem (erem) в раздел Администрирование - Оптимизация БД (HighLoad)

Виртуальная таблица «Остатки» регистра накопления – одна из самых популярных в конфигурациях. В статье объясняется работа виртуальной таблицы на уровне СУБД (MSSQL) и каким образом можно настроить регистр, что бы избавиться от избыточных блокировок, связанных с этой таблицей.

ИТАК:

Есть регистр накопления остатков «РасчетыПоПриобретениюВВалютеОрганизации» со следующей структурой:

Измерения:

  1. Организация, типа справочник «Организации»
  2. Контрагент, типа справочник «Контрагенты»
  3. ДоговорКонтрагента, типа справочник «ДоговорыКонтрагентов»
  4. Сделка, типа документ «Счет на оплату поставщику»
  5. Счет оплаты, типа ПланыСчетов.Хозрасчетный
  6. ДокументРасчетовСКонтрагентом, типа документ поступления и оплаты…

Ресурсы:

  1. СуммаВзаиморасчетов
  2. СуммаРегл

 

Есть запрос по остатками этого регистра:

ВЫБРАТЬ

               ЕСТЬ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»), то ресурс берется с отрицательным знаком, иначе с положительным.

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

 

При объединении запросов из результата первого (по таблице итогов) вычитается результат второго (по основной таблице) и таким образом получается остаток на нужный нам период (условие по периоду виртуальной таблицы).

 

Зачем все это знать…

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

 

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

Естественное требование к системе – возможность одновременного проведения пользователями документов по разным контрагентам или договорам. Но, если система настроена неправильно, то это требование выполняться не будет…

 

Итак:

При выполнении запроса очень важно что бы для отбора строк запрос использовал индекс! В этом случае запрос будет выполняться быстро и блокировать для записи только те строки таблиц, используемых в запросе, которые удовлетворяют отбору…

 

Если запрос не использует индекс для отбора строк или использует малоселективный индекс (индекс, которому соответствует большое количество строк таблицы), то запрос будет выполняться медленно и блокировать всю (или почти всю) таблицу для записи…

 

Для выполнения нашего требования запрос должен использовать индекс и первые поля этого индекса должны использоваться в отборе запроса. В нашем случае, это поле - измерение ДоговорКонтрагента.

 

Для таблицы итогов регистра накопления платформа создает кластерный индекс с включением в него периода итогов и всех измерений в том порядке как они заданы в конфигураторе, не зависимо от того установлено ли для измерения свойство «Индексировать» или нет… Из этого следует, что если Мы переставим «ДоговорКонтрагента» в списке измерений на первое место, то получим нужный нам индекс… Кстати не используемые в отборах запросов измерения (Организация и Контрагент) лучше передвинуть в самый конец списка, тогда могут сработать такие поля индекса как «Сделка», «СчетОплаты» и «ДокументРасчетовСКонтрагентом».

 

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

Если для измерения установлено свойство «Индексировать», то платформа создаст дополнительный индекс, включив в него это измерение, период, регистратор и номер строки. Из этого следует, что установив у измерения «ДоговорКонтрагента» свойство «Индексировать», Мы получим требуемый нам индекс…

 

Таким образом структура регистра изменилась на следующую:

Измерения:

  1. ДоговорКонтрагента, типа справочник «ДоговорыКонтрагентов» (ИНДЕКСИРУЕТСЯ)
  2. Сделка, типа документ «Счет на оплату поставщику»
  3. Счет оплаты, типа ПланыСчетов.Хозрасчетный
  4. ДокументРасчетовСКонтрагентом, типа документ поступления и оплаты…
  5. Организация, типа справочник «Организации»
  6. Контрагент, типа справочник «Контрагенты»

Ресурсы:

  1. СуммаВзаиморасчетов
  2. СуммаРегл

 

После этого избыточные блокировки исчезли, и пользователи смогли одновременно проводить документы по разным контрагентам и договорам.

 

Информация про индексы, которые создает платформа, находится здесь: http://kb.1c.ru/articleView.jsp?id=68

 

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

Наименование Файл Версия Размер
Виртуальная таблица Остатки.doc 35
.doc 48,50Kb
26.04.12
35
.doc 48,50Kb Скачать

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Яков Коган (Yashazz) 26.04.12 19:56
Почему в виртуальной таблице нет условий на контрагента и организацию? Это, если верно помню, убыстряет дело, они ведь - первые два измерения. Смыслово это кажется избыточностью, но - Гилёв рекомендует... И второе - кто вам вот так легко даст переделывать регистры? Надо не только архитектуру курочить, но и к имеющейся верные запросы писать.
2. Анатолий Бритько (headMade) 26.04.12 23:38
Мне кажется что достаточно было бы прописать в вирт. таблицу остатков отборы по Организации и Контрагенту (темболее что они однозначно определяются договором) это бы позволило использовать индекс табл. остатков без изменения порядка следования измерений.

И насколько я понимаю суть индексов, то даже измерение порядка следования без указания условий отбора по организации и Контрагенту не позволит на все 100 использовать индекс т.к. еще доп будет сканирование таблицы или индекса для отбора по этим измерениям.
3. Василий Антонов (khaoos) 27.04.12 06:36
Действительно, в данном случае нужно было использовать отбор в виртуальной таблице на организацию и контрагента, чтобы не "портить" регистр и использовать уже имеющийся индекс. Так как если регистр "тяжело" используется в конфигурации, нужно перелопачивать конфигурацию в поисках запросов к этому регистру с целью их исправления. Приходилось этим заниматься один раз, но это было оправдано - прирост производительности составил до шести раз. А вот индексация договора - правильная идея: именно для отбора в таблице движений, так как договор мельче "гранулирует" таблицу движений, тем самым меньше записей будет блокироваться. Автору статьи рекомендую почитать статьи с диска ИТС (раздел про вопросы крупных внедрений, кажется), там все эти вещи про блокировки есть. А также книжку Габец "Реализация прикладных задач в системе 1С Предприятие 8.2" из серии "Профессиональная разработка". Хотя если удобнее через профайлер изучать - тоже способ :).
4. Sasha Erem (erem) 27.04.12 12:17
(1) Yashazz, Если бы мне "не дали" изменить структуру регистра - я бы так и поступил - добавил бы условие по организации и контрагенту в отбор виртуальной таблицы.
5. Sasha Erem (erem) 27.04.12 17:29
(2) headMade, Я смотрел план выполнения запроса в профайлере и по таблице итогов Clustered index seek выполняется без дополнительных условий (секции "Predicates" нет) и все поля отбора попадают в секцию "Seek predicates" - из этого я делаю вывод, что доп. сканирования таблицы не происходит...
6. Валерий Гайдабура (director04) 30.04.12 22:36
(5) erem, Александр, я думаю, что вы несколько погорячились с перемещением измерений типа Организация (а возможно и Контрагент) на самые нижние позиции. С точки зрения решения вашей локальной задачи, быть может вы и правы. Эффективность использования индекса действительно выше у самых первых. А вот если посмотреть на данную проблему в долгосрочной перспективе - то, скорее всего это ошибочное решение.
Дело в том, что измерения "Организация" и "Контрагент" - это базовые группировки для любых отчетов. Теперь же, все остальные отчеты, которые будут использовать любые таблицы данного регистра, резко понизят свое быстродействие. Особенно, это будет сказываться при декартовом соединении.
В качестве резюме можно сказать: успешное решение локальной (тактической) задачи, не всега удачно, для решения долгосрочной (стратегической) задачи. А иногда, может вызывать прямо противоположный эффект.
bestkso; vvr908; Bassgood; Denis_Viktorovich; +4 Ответить 1
7. Денис (1cspecialist) 01.05.12 14:13
(0) Для полноты статьи я бы порекомендовал автору добавить информацию о том, почему вообще в его примере возникли блокировки, т.к. не для всех очевидно, что чтение в транзакции накладывает блокировки.
8. Валерий Гайдабура (director04) 01.05.12 23:28
(0) Не совсем понятно, для чего вообще используется синтаксис

ЕСТЬNULL(СУММА(РасчетыПоПриобретениюВВалютеОрганизацииОстатки.СуммаВзаиморасчетовОстаток), 0)


Если не используется соединение, то откуда там может взяться этот самый NULL?
А так, в целом, за пытливость ума и ковыряние в носу - наверное плюс
9. Алекс Ю (AlexO) 02.05.12 09:54
(8) director04,
ну откуда берутся NULL в базе 1с? там все возможно словить..
Автор же просто привел тестовый пример ))
10. Альтаир (Altair777) 02.05.12 23:03
(0) Очень частный и не убедительный пример. Регистр РасчетыПоПриобретениюВВалютеОрганизации, имхо, не такой критичный по производительности в базе данных.
Есть гораздо более "тяжелые" регистры. Почему автор не приводит пример их "успешной" переделки?
11. Владислав Рожевский (vvr908) 03.05.12 12:37
(8) director04,

Мне вообще неочевиден смысл конструкции ISNULL(СУММА(Поле1),0).
Думается, что на NULL надо проверять до суммирования: СУММА(ISNULL(Поле1, 0)).
12. Валерий Гайдабура (director04) 03.05.12 13:47
(11) vvr908, NULL в запросах может появляться в одном случае - при соединении двух таблиц. Другие случаи мне неизвестны.
В данном случае, соединение не используется, то есть, проверка на NULL - тоже замедляет (в некоторой степени) выполнение запроса.
13. Владислав Рожевский (vvr908) 03.05.12 13:55
(12) director04, мы говорим о разных вещах.
Понятно, что в данном конкретном случае проверка вообще лишняя. Я просто заметил, что имеет смысл проверять значение на NULL перед тем, как мы попытаемся что-то с ним сделать (например, взять сумму), а не после.
director04; +1 Ответить 1
14. Валерий Гайдабура (director04) 03.05.12 22:41
(13) vvr908, Согласен, сумму с NULL брать неразумно ))))
15. Sasha Erem (erem) 07.05.12 23:51
(6) director04, Речь идет о таблице итогов, которая используется только в виртуальных таблицах, поэтому непосредственно с ней в запросах 1С соединение сделать невозможно. При соединении с виртуальной таблицей индексы вообще использоваться не будут, если только ее не проиндексировать с помощью временной таблицы...
16. direktorSan (direktorSan) 09.06.12 20:35
(12) director04, Если мне не изменяет память, то есть еще один случай, когда в запросе даже БЕЗ соединений может появиться NULL.
Если для реквизита справочника свойство "Использовать" = "Для элемента" ("Для группы"), то в запросе из справочника для групп (элементов) значение этого реквизита = NULL!