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

08.07.15

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

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

 

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

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

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

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

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

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

В самой таблице движений 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" в той теме.

sql оптимизация граница МоментВремени

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

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

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2349    spyke    25    

38

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7349    149    ZAOSTG    66    

95

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5760    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

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

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8577    ivanov660    6    

75

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

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

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    4999    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15955    skovpin_sa    14    

97

Как эффективно настроить autovacuum в Postgres для 1С

HighLoad оптимизация Администрирование СУБД Платформа 1С v8.3 Бесплатно (free)

Кто не любит убирать мусор? Думаю, практически все, а вот в Postgres это обязательный ритуал для эффективной работы. Как эффективно настроить уборку за 1С в Postgres, можно прочитать в этой статье и еще раз задуматься о бесплатности Postgres.

05.08.2023    4971    1CUnlimited    5    

51
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. mrstomak 422 09.06.15 13:09 Сейчас в теме
Молодец, что раскопал такой дикий промах.
Получается, что в случае получения остатков по дате, ближе к концу месяца, происходит обход ВСЕХ архивных записей.

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

Конечно в самых жутких по быстродействию документах подход будет оправдан, но во все лепить не стоит эти проверки.
aexeel; zqzq; +2 Ответить
8. Aleksey.Bochkov 3659 10.06.15 01:21 Сейчас в теме
(4) Brawler,
Да, согласен. Везде это вставлять нет смысла. Только там где проблема стабильно воспроизводится и изменение принесет заметный результат.
В базе, где эту проблему обнаружил, оказалось достаточно сделать это только для запроса по партиям.

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

(6) Согласен. Надо по-удачнее переформулировать. Проблема возникает при использовании МоментаВремени и Границы, созданной на основании МоментаВремени/Ссылки.
Граница с датой работает нормально.
Исключая\Исключая - не влияет на результат, т.к. там условие в запросе незначительно меняется.
13. pumbaE 10.06.15 11:23 Сейчас в теме
(8) им бы еще в базе хранить дату и время в UTC, а не в непонятно каком времени.
12. vasyak319 150 10.06.15 10:28 Сейчас в теме
Я бы не правил алгоритмы конфигурации - тут я согласен с (4) "Уж лучше пусть пока страдает производительность, но будет более читабельным код", а вместо этого сделал бы скрипт, который добавил бы в базу индекс, про который речь в зелёной надписи на скриншоте с планом запросов. Всё, что нужно, это запускать этот скрипт после каждой реструктуризации регистра, что вряд ли будет часто, если вообще будет.
14. AlX0id 11.06.15 10:15 Сейчас в теме
(12) vasyak319,
И после каждой загрузки дтшки, видимо, и после каждого ТИИ? Что гораздо более вероятно..
15. vasyak319 150 11.06.15 11:02 Сейчас в теме
(14) AlX0id, если вы регулярно загружаете из dtшки рабочую базу, то что-то у вас с этой базой глубоко не так.
20. AlX0id 11.06.15 12:38 Сейчас в теме
(15) vasyak319,
Речь не совсем о моих базах ) А о том, что диапазон случаев, когда придется применять предлагаемый в (12) скрипт - чутка шире, нежели указанный в посте.
19. It-developer 24 11.06.15 11:19 Сейчас в теме
(12) vasyak319, Читаемость кода улучшается с увеличением количества комментариев.
Вы, наверно, во франче работаете?
Надо искать "золотую середину" между читаемостью и производительностью, но все же выводя производительность на первое место! В плане данного примера код достаточно читаем и если это даст даже 5-10% прироста скорости проведения конечно же нужно внедрять!
21. Aleksey.Bochkov 3659 11.06.15 21:05 Сейчас в теме
(12) на картинке система предлагает индекс для второго (более быстрого) запроса :).

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

(17) - этот запрос выполняется очень быстро и оптимально.
5. Dach 371 09.06.15 16:41 Сейчас в теме
Эх, вот бы разработчики платформы увеличили размерность даты до миллисекунд. Не надо было бы гадать, сколько документов и в каком порядке лежат "внутри секунды". Ведь SQL то поддерживает это: https://msdn.microsoft.com/ru-ru/library/ms187819.aspx
wolfsoft; Артано; Danil.Potapov; Восьмой; +4 Ответить
18. aexeel 73 11.06.15 11:16 Сейчас в теме
(5) Dach, У этого подхода есть и обратная сторона — интерактивно вводить дату/время станет совсем не удобно. Но даже если это сделать, исходя из личного опыта, для большого потока документов этого также будет не достаточно.
6. anig99 2841 09.06.15 16:46 Сейчас в теме
Я так понимаю, что проблема возникает только при использовании МоментВремени и в незначительной степени от ГраницаВремени? Причем проверяли только на ГраницаВремени.Исключая ?
А для ГраницаВремени.Включая какая статистика?
Просто в заголовке статьи Граница и Момент указаны как равнозначные факторы, а по статье видно, что они влияют в разной степени. Это путает.
7. fishca 1254 09.06.15 22:05 Сейчас в теме
Правильная статья! Плюс и здесь и там получил, спасибо за информацию!
9. Bronislav 10.06.15 06:01 Сейчас в теме
Подтверждаю, тоже столкнулся с этой бедой некоторое время назад. Заметил после установки maxDOP = 2, в тж стали попадать аналогичные запросы к движениям регистра. Забороть получилось только отказавшись от момента времени.
А речь про какую версию платформы? В тексте не увидел. У меня это вылезло на 8.3.5.
11. Aleksey.Bochkov 3659 10.06.15 10:05 Сейчас в теме
(9) Bronislav,
Проверял на 8.3.5 и 8.3.6.

(10) alon,
Я не помню как это было в 8.2, но 8.3 считает остатки с конца месяца для любых дат.
10. alon 171 10.06.15 09:30 Сейчас в теме
Могу предположить, что если бы дата документа была не 15.04, а 02.04, то выборка из таблицы итогов выполнялась бы уже наоборот - на 01.04. И добивалась бы движениями с 01 по 02. И тогда уже надо проверять наличие документов не ранее текущего, а после него.
16. It-developer 24 11.06.15 11:08 Сейчас в теме
Прикольно. А не может действовать кеш SQL на запросы 3 и 4?
17. It-developer 24 11.06.15 11:15 Сейчас в теме
А сам запрос

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

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

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

насколько он быстрый? не дает тормозов производительности?
22. RustIG 1301 14.06.15 20:48 Сейчас в теме
(0) а на какой задаче обнаружилась проблема? почему документы продаж заведены на одно и тоже время с точностью до секунды? почему при проведении остатки не проверяются сразу? какая конфа? сколько пользователей? филиальная структура? тогда почему списание с одного склада? заранее спасибо за подробности
25. Aleksey.Bochkov 3659 16.06.15 02:14 Сейчас в теме
(22) Rustig,
Мне кажется, что эти вопросы не так важны тут.
Конкретно в этой конфигурации проблема проявилась на партиях. Решить ее можно было бы разными путями, вплоть до отказа от оперативного партионного учета с расчетом результата в конце месяца, но я другую мысль пытался донести.
При достаточном стечении обстоятельств проблема может проявиться на любых регистрах. Я не проверял, но скорее всего, с регистрами бухгалтерии аналогичная ситуация.
Фактически использование МоментаВремени\Границы требуется крайне редко (мала вероятность попаданию в одну секунду зависящих друг от друга документов - например, по одному и тому же складу), поэтому нет смысла использовать более "тяжелый" запрос, когда "легкий" даст такой же результат.

P.S. - в запросе в конце статьи добавил отбор по Складу. т.к. документы в той же секунде по другим складам не влияют на результат.
26. RustIG 1301 16.06.15 23:33 Сейчас в теме
(25) наоборот, важно: я думал услышать, что некая обработка регламентно создает документы продаж и сажает списание на одну секунду по одному складу условно говоря, каждые 20 минут.... и тогда естественно эту проблему можно решить другим способом- разнести документы посекундно.
а после вашего ответа складывается впечатление, что в базе настолько много менеджеров-продавцов, что в программе волей-неволей создаются каждую секунду по два-три документа реализации. на мой взгляд уникальный случай...
23. RustIG 1301 14.06.15 20:56 Сейчас в теме
(0) опережая ответ на свои вопросы, хотел еще раз уточнить: какие разрезы учета номенклатуры? по характеристикам? по сериям? по складам? по другим измерениям? какие другие используются измерения учета?
в сутках 86400 секунд - может быть имеет смысл при записи документа проверять последовательность и записывать с датой +1сек от предыдущего документа? поступления товаров при этом записывать на начало дня.
будет ли такое предложение приемлемым решением?
24. Vainemeinen 82 15.06.15 21:24 Сейчас в теме
Эта фича с разными отборами остатков по дате и моменту времени тянется еще с 8.0 / 8.1, и разница во времени была большая - на одной и той же базе запрос по дате выполнялся 1-3 сек, по моменту времени мог выполняться более 3 минут (база была большая - более 200 ГБ). В результате приходилось переписывать все запросы остатков в модулях проведения на запросы по дате, а документы с одинаковыми датами и временем искусственно растаскивать во времени, сдвигая их посекундно, чтобы не было нескольких документов с одинаковым временем - благо документов хоть было и много, но все же меньше, чем 86400 в день.
27. Andreynikus 1360 19.06.15 11:23 Сейчас в теме
Спасибо за статью, очень грамотно и интересно написано.
28. antz 07.07.15 12:35 Сейчас в теме
[зануда mode on]

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

[зануда mode off]

За статью спасибо.
Aleksey.Bochkov; +1 Ответить
29. Fragster 1137 11.05.16 20:29 Сейчас в теме
Плюсанул на партнерском, спасибо!
Оставьте свое сообщение