gifts2017

Проблема быстродействия при использовании позиции документа вместо Даты в виртуальных таблицах

Опубликовал Алексей Бочков (Aleksey.Bochkov) в раздел Программирование - Практика программирования

Граница и МоментВремени - это прикладные объекты, являющиеся комбинацией даты и ряда других параметров, которые позволяют указать системе точную границу получения данных из таблиц регистров.
Например, частой ситуацией является наличие нескольких документов в рамках одной секунды - в этом случае, разработчики используют позицию документа для правильного получения остатков в обработках проведения, т.к. при использовании просто даты данные могут быть некорректными.
Но при этом использование Границы и МоментаВремени может ухудшить производительность, замедлив выполнение даже элементарных запросов к виртуальным таблицам.

 

Рассмотрим конкретный пример.

Возьмем регистр накопления "ПартииТоваровНаСкладах" и получим из него остатки с отбором по Номенклатуре и Складу.

Текст запроса:

(во временных таблицах с номенклатурой и складами значений немного)

Структура регистра:

Есть дополнительный индекс по измерению Склад.

В самой таблице движений 38,7 миллионов записей за Январь-Май 2015 г.

 

Граница рассчитанных итогов - конец Апреля 2015 г.

 

Выполняем запрос с разным значением параметра "период":

- первый и второй раз укажем позицию документа с датой "15.04.2015  4:02:33";

- третий и четвертый раз укажем просто дату "15.04.2015  4:02:33".

 

Выполняем эти запросы несколько раз и получаем следующие результаты:

  MAXDOP = 0 MAXDOP = 2 MAXDOP = 1
1 запрос 4.497 7.751 13.877
2 запрос 4.212 8.630 13.833
3 запрос 1.740 1.758 1.720
4 запрос 1.615 1.641 1.665

Замеры сделаны для 3х разных значений "Max Degree of Parallelism" сервера MSSQL.

Есть разные мнения по поводу параметра MAXDOP. 1С-ники чаще склоняются к установке единицы. А вот, например, мнение DBA, которые администрируют другие виды приложений :).
Но вопрос в другом.

Во всех 4х случаях платформа генерирует примерно одинаковые запросы с двумя частями:

1) выборка из таблицы итогов регистра накопления на "будущую" дату "01.05.2015";

2) выборка строк из таблицы движений за период с "15.04.2015  4:02:33" по "01.05.2015 00:00:00".

Конечный остаток получается разницей этих двух таблиц.

 

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

А вот с выборкой из таблицы движений явно есть проблема.

Для условия с позицией документа запрос имеет вид:

SELECT
...
FROM dbo._AccumRg14485 T6
WHERE (T6._Period > '4015-04-15 04:02:33' OR T6._Period = '4015-05-01 00:00:00' AND (T6._RecorderTRef > 0x00000135 OR T6._RecorderTRef = 0x00000135 AND T6._RecorderRRef >= 0x80D9005056B6018411E4E33ED0E5D9C0)) AND T6._Period < '4015-05-01 00:00:00'
AND T6._Active = 0x01
AND ...

Для условия с датой запрос имеет вид:

SELECT
...
FROM dbo._AccumRg14485 T6
WHERE T6._Period >= '4015-04-15 04:02:33' AND T6._Period < '4015-05-01 00:00:00'
AND T6._Active = 0x01
AND ...

Явно в первом случае запрос более сложный для восприятия оптимизатором.

Вынесем оба подзапроса в SQL Server management Studio и посмотрим как они выполняются:

Статистика ввода/вывода и длительность выполнения:

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

 

Планы запросов:

Структура идентичная, но используемые параметры разные.
Для первого запроса Seek Predicate указан только один (период меньше 1 мая), а для второго - оба ограничения по периоду.

 

Вариант решения проблемы:

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

Например, при записи документа контролировать - есть ли записи в регистре в данной секунде ранее текущего документа по такому же Складу. Если нет, то отбирать остатки по дате, если да - по позиции документа:

Запрос = Новый Запрос(
"ВЫБРАТЬ ПЕРВЫЕ 1
|	1 КАК Поле1
|ИЗ
|	РегистрНакопления.ПартииТоваровНаСкладах КАК ПартииТоваровНаСкладах
|ГДЕ
|	ПартииТоваровНаСкладах.Период = &Период
|	И НЕ ПартииТоваровНаСкладах.Регистратор = &Регистратор
|	И ПартииТоваровНаСкладах.Склад = &Склад
|	И ПартииТоваровНаСкладах.МоментВремени < &МоментВремени");
	
Запрос.УстановитьПараметр("Период", 		Дата);
Запрос.УстановитьПараметр("Регистратор", 	Ссылка);
Запрос.УстановитьПараметр("Склад", 	 	Склад);
Запрос.УстановитьПараметр("МоментВремени", 	МоментВремени());

ДокументЕдинственныйВПериоде = Запрос.Выполнить().Пустой());

....

Если ДокументЕдинственныйВПериоде Тогда
 СтруктураПараметровДляЗапросаКОстаткам.Вставить("Период", Дата);
Иначе
 СтруктураПараметровДляЗапросаКОстаткам.Вставить("Период", МоментВремени());
КонецЕсли;

При этом вариант упрощения запроса с позицией документа, который позволит устранить эту проблему, существует. К сожалению, сделать это могут только разработчики платформы.
Я описал эту ситуацию и вариант оптимизации в партнерской конференции.
У кого есть доступ - буду благодарен за "+1" в той теме.

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Алексей Белый (mrstomak) 09.06.15 13:09
Молодец, что раскопал такой дикий промах.
Получается, что в случае получения остатков по дате, ближе к концу месяца, происходит обход ВСЕХ архивных записей.

Интересно, как на партнерке прокомментируют.
2. artem Петров (gonzo111) 09.06.15 14:03
потестил замером производительности действительно с моментом медленее , добавил вышеуказанный код, спасибо
3. Алексей 1 (AlX0id) 09.06.15 14:34
У меня в половине запросов используется Граница, автор напугал прям :)
Протестил для границы с конструктором без использования момента времени - все норм вроде, такой же запрос, как и в случае даты. Момент времени в моих задачах никогда не находил применения, потому тестить не стал, поверю автору на слово )
4. Игорь Фелькер (Brawler) 09.06.15 14:54
Часто бывает так, что один документ двигает множество регистров и не всегда один и тот же вид документов двигает их все одновременно.
Выходит проверять нужно вообще все регистры у которых документ значится регистратором.
Уж лучше пусть пока страдает производительность, но будет более читабельным код, а 1С ну может лет через 5 исправит эту оплошность, а когда исправит, то не придется удалять костыли из программного кода.

Конечно в самых жутких по быстродействию документах подход будет оправдан, но во все лепить не стоит эти проверки.
aexeel; zqzq; +2 Ответить 2
5. Роман С (Dach) 09.06.15 16:41
Эх, вот бы разработчики платформы увеличили размерность даты до миллисекунд. Не надо было бы гадать, сколько документов и в каком порядке лежат "внутри секунды". Ведь SQL то поддерживает это: https://msdn.microsoft.com/ru-ru/library/ms187819.aspx
wolfsoft; Артано; Danil.Potapov; Восьмой; +4 Ответить 2
6. Александр Медведев (anig99) 09.06.15 16:46
Я так понимаю, что проблема возникает только при использовании МоментВремени и в незначительной степени от ГраницаВремени? Причем проверяли только на ГраницаВремени.Исключая ?
А для ГраницаВремени.Включая какая статистика?
Просто в заголовке статьи Граница и Момент указаны как равнозначные факторы, а по статье видно, что они влияют в разной степени. Это путает.
7. Сергей Рудаков (fishca) 09.06.15 22:05
Правильная статья! Плюс и здесь и там получил, спасибо за информацию!
8. Алексей Бочков (Aleksey.Bochkov) 10.06.15 01:21
(4) Brawler,
Да, согласен. Везде это вставлять нет смысла. Только там где проблема стабильно воспроизводится и изменение принесет заметный результат.
В базе, где эту проблему обнаружил, оказалось достаточно сделать это только для запроса по партиям.

(5) ИМХО, я бы не добавлял. Это тогда нужно эти миллисекунды в интерфейс для пользователей выносить. А если не выносить, то по какому принципу устанавливать миллисекунды для документов создаваемых задним числом? Слишком сложно..

(6) Согласен. Надо по-удачнее переформулировать. Проблема возникает при использовании МоментаВремени и Границы, созданной на основании МоментаВремени/Ссылки.
Граница с датой работает нормально.
Исключая\Исключая - не влияет на результат, т.к. там условие в запросе незначительно меняется.
9. Андрей Кейних (Bronislav) 10.06.15 06:01
Подтверждаю, тоже столкнулся с этой бедой некоторое время назад. Заметил после установки maxDOP = 2, в тж стали попадать аналогичные запросы к движениям регистра. Забороть получилось только отказавшись от момента времени.
А речь про какую версию платформы? В тексте не увидел. У меня это вылезло на 8.3.5.
10. Андрей Гореликов (alon) 10.06.15 09:30
Могу предположить, что если бы дата документа была не 15.04, а 02.04, то выборка из таблицы итогов выполнялась бы уже наоборот - на 01.04. И добивалась бы движениями с 01 по 02. И тогда уже надо проверять наличие документов не ранее текущего, а после него.
11. Алексей Бочков (Aleksey.Bochkov) 10.06.15 10:05
(9) Bronislav,
Проверял на 8.3.5 и 8.3.6.

(10) alon,
Я не помню как это было в 8.2, но 8.3 считает остатки с конца месяца для любых дат.
12. Василий Коровин (vasyak319) 10.06.15 10:28
Я бы не правил алгоритмы конфигурации - тут я согласен с (4) "Уж лучше пусть пока страдает производительность, но будет более читабельным код", а вместо этого сделал бы скрипт, который добавил бы в базу индекс, про который речь в зелёной надписи на скриншоте с планом запросов. Всё, что нужно, это запускать этот скрипт после каждой реструктуризации регистра, что вряд ли будет часто, если вообще будет.
13. Евгений Сосна (pumbaE) 10.06.15 11:23
(8) Aleksey.Bochkov, им бы еще в базе хранить дату и время в UTC, а не в непонятно каком времени.
14. Алексей 1 (AlX0id) 11.06.15 10:15
(12) vasyak319,
И после каждой загрузки дтшки, видимо, и после каждого ТИИ? Что гораздо более вероятно..
15. Василий Коровин (vasyak319) 11.06.15 11:02
(14) AlX0id, если вы регулярно загружаете из dtшки рабочую базу, то что-то у вас с этой базой глубоко не так.
16. Kostya Zhurov (It-developer) 11.06.15 11:08
Прикольно. А не может действовать кеш SQL на запросы 3 и 4?
17. Kostya Zhurov (It-developer) 11.06.15 11:15
А сам запрос

Запрос = Новый Запрос(
"ВЫБРАТЬ ПЕРВЫЕ 1
| 1 КАК Поле1
|ИЗ
| РегистрНакопления.ПартииТоваровНаСкладах КАК ПартииТоваровНаСкладах
|ГДЕ
| ПартииТоваровНаСкладах.Период = &Период
| И НЕ ПартииТоваровНаСкладах.Регистратор = &Регистратор
| И ПартииТоваровНаСкладах.МоментВремени < &МоментВремени");

Запрос.УстановитьПараметр("Период", Дата);
Запрос.УстановитьПараметр("Регистратор", Ссылка);
Запрос.УстановитьПараметр("МоментВремени", МоментВремени());

ДокументЕдинственныйВПериоде = Запрос.Выполнить().Пустой());

насколько он быстрый? не дает тормозов производительности?
18. a exeel (aexeel) 11.06.15 11:16
(5) Dach, У этого подхода есть и обратная сторона — интерактивно вводить дату/время станет совсем не удобно. Но даже если это сделать, исходя из личного опыта, для большого потока документов этого также будет не достаточно.
19. Kostya Zhurov (It-developer) 11.06.15 11:19
(12) vasyak319, Читаемость кода улучшается с увеличением количества комментариев.
Вы, наверно, во франче работаете?
Надо искать "золотую середину" между читаемостью и производительностью, но все же выводя производительность на первое место! В плане данного примера код достаточно читаем и если это даст даже 5-10% прироста скорости проведения конечно же нужно внедрять!
20. Алексей 1 (AlX0id) 11.06.15 12:38
(15) vasyak319,
Речь не совсем о моих базах ) А о том, что диапазон случаев, когда придется применять предлагаемый в (12) скрипт - чутка шире, нежели указанный в посте.
21. Алексей Бочков (Aleksey.Bochkov) 11.06.15 21:05
(12) на картинке система предлагает индекс для второго (более быстрого) запроса :).

(16) It-developer,
Про проведении замеров статистика была обновлена, индексы дефрагментированы, кэш очищен.
Результаты брал из 2-3 итерации.

(17) - этот запрос выполняется очень быстро и оптимально.
22. г. Казань Рустем Гумеров (Rustig) 14.06.15 20:48
(0) а на какой задаче обнаружилась проблема? почему документы продаж заведены на одно и тоже время с точностью до секунды? почему при проведении остатки не проверяются сразу? какая конфа? сколько пользователей? филиальная структура? тогда почему списание с одного склада? заранее спасибо за подробности
23. г. Казань Рустем Гумеров (Rustig) 14.06.15 20:56
(0) опережая ответ на свои вопросы, хотел еще раз уточнить: какие разрезы учета номенклатуры? по характеристикам? по сериям? по складам? по другим измерениям? какие другие используются измерения учета?
в сутках 86400 секунд - может быть имеет смысл при записи документа проверять последовательность и записывать с датой +1сек от предыдущего документа? поступления товаров при этом записывать на начало дня.
будет ли такое предложение приемлемым решением?
24. Vainemeinen Vainemeinen (Vainemeinen) 15.06.15 21:24
Эта фича с разными отборами остатков по дате и моменту времени тянется еще с 8.0 / 8.1, и разница во времени была большая - на одной и той же базе запрос по дате выполнялся 1-3 сек, по моменту времени мог выполняться более 3 минут (база была большая - более 200 ГБ). В результате приходилось переписывать все запросы остатков в модулях проведения на запросы по дате, а документы с одинаковыми датами и временем искусственно растаскивать во времени, сдвигая их посекундно, чтобы не было нескольких документов с одинаковым временем - благо документов хоть было и много, но все же меньше, чем 86400 в день.
25. Алексей Бочков (Aleksey.Bochkov) 16.06.15 02:14
(22) Rustig,
Мне кажется, что эти вопросы не так важны тут.
Конкретно в этой конфигурации проблема проявилась на партиях. Решить ее можно было бы разными путями, вплоть до отказа от оперативного партионного учета с расчетом результата в конце месяца, но я другую мысль пытался донести.
При достаточном стечении обстоятельств проблема может проявиться на любых регистрах. Я не проверял, но скорее всего, с регистрами бухгалтерии аналогичная ситуация.
Фактически использование МоментаВремени\Границы требуется крайне редко (мала вероятность попаданию в одну секунду зависящих друг от друга документов - например, по одному и тому же складу), поэтому нет смысла использовать более "тяжелый" запрос, когда "легкий" даст такой же результат.

P.S. - в запросе в конце статьи добавил отбор по Складу. т.к. документы в той же секунде по другим складам не влияют на результат.
26. г. Казань Рустем Гумеров (Rustig) 16.06.15 23:33
(25) наоборот, важно: я думал услышать, что некая обработка регламентно создает документы продаж и сажает списание на одну секунду по одному складу условно говоря, каждые 20 минут.... и тогда естественно эту проблему можно решить другим способом- разнести документы посекундно.
а после вашего ответа складывается впечатление, что в базе настолько много менеджеров-продавцов, что в программе волей-неволей создаются каждую секунду по два-три документа реализации. на мой взгляд уникальный случай...
27. Андрей Бурмистров (Andreynikus) 19.06.15 11:23
Спасибо за статью, очень грамотно и интересно написано.
28. antz Петров (antz) 07.07.15 12:35
[зануда mode on]

Усугубить - значит "увеличить, усилить".

[зануда mode off]

За статью спасибо.
Aleksey.Bochkov; +1 Ответить
29. Антонио (Fragster) 11.05.16 20:29
Плюсанул на партнерском, спасибо!
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа