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

26.04.12

База данных - HighLoad оптимизация

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

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

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

ИТАК:

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

Измерения:

  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

 

См. также

Планы обмена VS История данных

Обмен между базами 1C Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Вы все еще регистрируете изменения только на Планах обмена и Регистрах сведений?

11.12.2023    6436    dsdred    36    

112

1С-ная магия

Механизмы платформы 1С Бесплатно (free)

Язык программирования 1С содержит много нюансов и особенностей, которые могут приводить к неожиданным для разработчика результатам. Сталкиваясь с ними, программист начинает лучше понимать логику платформы, а значит, быстрее выявлять ошибки и видеть потенциальные узкие места своего кода там, где позже можно было бы ещё долго медитировать с отладчиком в поисках источника проблемы. Мы рассмотрим разные примеры поведения кода 1С. Разберём результаты выполнения и ответим на вопросы «Почему?», «Как же так?» и «Зачем нам это знать?». 

06.10.2023    18503    SeiOkami    46    

118

Валидация JSON через XDTO (включая массивы)

WEB-интеграция Универсальные функции Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    8847    YA_418728146    6    

141

MS SQL Server: изучаем планы запросов

Запросы HighLoad оптимизация Запросы Бесплатно (free)

Многие знают, что для ускорения работы запроса нужно «изучить план». При этом сам план обычно обескураживает: куча разноцветных иконок и стрелочек; ничего не понятно, но очень интересно! Аналитик производительности Александр Денисов на конференции Infostart Event 2021 Moscow Premiere рассказал, как выполняется план запроса и что нужно сделать, чтобы с его помощью находить проблемы производительности.

20.06.2023    16048    Филин    37    

113

Расширение глобального поиска 1С, или Глобальный поиск "на максималках"

Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Мало кто знает, что поле "Глобального поиска" в 1С можно доработать. Добавить свои варианты поиска, кнопочки в результатах и даже целые пользовательские меню.

27.03.2023    6960    SeiOkami    10    

140

Версионирование объектов VS История данных

Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Давайте разберемся в механизме «История данных» и поэкспериментируем для наглядности. Сравним «Версионирование объектов» и «Историю данных».

06.03.2023    18995    dsdred    54    

193

Практическая шпаргалка по новым возможностям языка запросов 1С

Механизмы платформы 1С Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

В предлагаемой статье решил привести примеры применения новых возможностей языка запросов 1С, начиная с версии платформы 8.3.20.

21.11.2022    23450    quazare    36    

122
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Yashazz 4709 26.04.12 19:56 Сейчас в теме
Почему в виртуальной таблице нет условий на контрагента и организацию? Это, если верно помню, убыстряет дело, они ведь - первые два измерения. Смыслово это кажется избыточностью, но - Гилёв рекомендует... И второе - кто вам вот так легко даст переделывать регистры? Надо не только архитектуру курочить, но и к имеющейся верные запросы писать.
4. erem 424 27.04.12 12:17 Сейчас в теме
(1) Yashazz, Если бы мне "не дали" изменить структуру регистра - я бы так и поступил - добавил бы условие по организации и контрагенту в отбор виртуальной таблицы.
2. headMade 144 26.04.12 23:38 Сейчас в теме
Мне кажется что достаточно было бы прописать в вирт. таблицу остатков отборы по Организации и Контрагенту (темболее что они однозначно определяются договором) это бы позволило использовать индекс табл. остатков без изменения порядка следования измерений.

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

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


Если не используется соединение, то откуда там может взяться этот самый NULL?
А так, в целом, за пытливость ума и ковыряние в носу - наверное плюс
9. AlexO 135 02.05.12 09:54 Сейчас в теме
(8) director04,
ну откуда берутся NULL в базе 1с? там все возможно словить..
Автор же просто привел тестовый пример ))
11. vvr908 446 03.05.12 12:37 Сейчас в теме
(8) director04,

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